Python sql server excel

SQL-Ex blog

Импорт данных из файла Excel в базу данных SQL Server с помощью Python

Есть много способов загрузить данные из Excel в SQL Server, но иногда полезно использовать те инструменты, которые вы знаете лучше всего. В этой статье мы рассмотрим как загружать данные из Excel в SQL Server с помощью Python.

Используемые инструменты

• Экземпляр SQL Server
• Python, версия 3.11.0.
• Visual Studio Code, версия 1.72.1.
• Windows 10 PC или Windows Server 2019/2022.

Установка базы данных — создание тестовой базы данных и таблицы

Имеется несколько способов создать базу данных и таблицы в SQL Server, но ниже мы пройдем через использование SQLCMD для создания базы данных, если вы не имеете SQL Server Management Studio или Azure Data Studio.

Откройте командую строку Windows или запустите новую терминальную сессию из Visual Studio Code, нажав CTRL + SHFT + `.

Для запуска SQLCMD используйте следующую команду sqlcmd -S -E , чтобы подключиться к SQL Server. Параметр -S указывает экземпляр SQL Server, а параметр -E означает использование доверительного подключения.

После аутентификации создадим новую базу данных следующей командой:

CREATE DATABASE ExcelData; 
GO

Используйте эту команду SQLCMD для подтверждения создания базы данных:

SELECT name FROM sys.databases 
GO

Изображение ниже представляет вывод команды, который показывает все имеющиеся базы данных этого экземпляра SQL Server.

Для переключения на новую базу данных используйте следующую команду:

Будет получено подтверждение изменение контекста, как показано ниже:

Теперь мы можем создать таблицу в этой базе данных.

CREATE TABLE EPL_LOG(ID int NOT NULL PRIMARY KEY); 
GO

Отлично! Вы создали таблицу с именем EPL_LOG и первичным ключом ID. Нам нужен только первый столбец, а программа загрузки создаст остальные столбцы на основе файла-источника.

Конфигурация ядра

Ядро помечает начальную точку вашего приложения SQLAlchemy. Ядро описывает пул соединений и диалект для BDAPI (Python Database API Specification), спецификацию в рамках Python для определения общих шаблонов использования для всех пакетов подключения к базам данных, которые в свою очередь взаимодействуют с указанной базой данных.

Для открытия нового терминала нажмите CTRL + SHFT + ` в Visual Studio Code.

Используйте следующую команду npm в окне терминала для установки модуля SQLAlchemy.

Создайте файл Python с именем DbConn.py, вставьте в него нижеприведенный код и измените источник данных на требуемый. Это — ядро SQLAlchemy, которое взаимодействует с SQL Server через Python.

import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc

conn = urllib.parse.quote_plus(
‘Data Source Name=MssqlDataSource;’
‘Driver=;’
‘Server=POWERSERVER\POWERSERVER;’
‘Database=ExcelData;’
‘Trusted_connection=yes;’
)

try:
coxn = create_engine(‘mssql+pyodbc:///?odbc_connect=<>‘.format(conn))
print(«Passed»)

Запись в SQL Server

Мы будем использовать Pandas, который является быстрым, гибким и легким в использовании инструментом с открытыми кодами для манипуляции и анализа данных, встроенным в язык программирования Python. Python может читать данные Excel в программе Python, используя функцию pandas.read_excel().

Для простоты этой демонстрации, сохраним файл Excel в папке проекта Visual Studio Code, чтобы нам не пришлось указывать путь. Это позволит вам игнорировать параметр io (любая валидная строка пути) функции read_excel().

Мы будем также использовать openpyxl в качестве движка для чтения файлов Excel. Выполните следующую команду pip в окне терминала, чтобы установить openpyxl.

pip install pandas openpyxl

Создайте еще один файл с именем ExcelToSQL.py, содержащий код ниже. Этот код будет читать файл Excel и записывать в созданную ранее таблицу базы данных.

//ExcelToSQL.py
from pandas.core.frame import DataFrame
import pandas as pd
from DbConn import coxn

df = pd.read_excel(‘sportsref_download.xlsx’, engine = ‘openpyxl’)

else:
print(«saved in the table»)
print(df)

Теперь щелкнем кнопку Play в верхнем правом углу окна Visual Studio Code для выполнения скрипта. В терминале появится вывод данных.

Для проверки сохранения данных в базе откройте SSMS и выберите данные из таблицы. Вы можете также использовать SQLCMD для подключения к экземпляру и выполнения следующего кода.

USE ExcelData; 
GO 
SELECT * FROM EPL_LOG

Изображение ниже показывает какие данные сейчас находятся в базе данных.

Заключение

Python выполняет большую работу, действуя как посредник между Excel и SQL Server. Вы можете транслировать любые статичные данные Excel в более гибкий набор данных, перемещая его в базу данных, которая обладает большей доступностью и легче интегрируется с другим системами.

Перемещайте данные Excel в SQL Server данным способом. Поскольку pandas сохраняет данные в DataFrame, ими легко манипулировать и изменять перед занесением в базу данных SQL Server.

Обратные ссылки

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Источник

Export SQL Server table to Excel in Python

In this tutorial, we will learn how to export a SQL Server table to an Excel file in Python. We will go through two main steps: creating a sample table in SQL Server, using SSMS or directl in Python, preparing an empty Excel file with formatting, and exporting the content of the table to the file in Excel. We will be using the openpyxl and pyodbc libraries in Python for this task. By the end of this tutorial, you will have a clear understanding of how to export data from a SQL Server database to an Excel file using Python.

1. Create a sample SQL Server table

To demonstrate how to export a SQL Server table to an Excel file in Python, we first need a sample table in our SQL Server database.

For this, we can use the following SQL script to create a simple table named employees with four columns:

1.1 Code to create the sample table using SSMS

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ); INSERT INTO employees VALUES (1, 'John Smith', 'Sales', 50000); INSERT INTO employees VALUES (2, 'Jane Doe', 'Marketing', 60000); INSERT INTO employees VALUES (3, 'Bob Johnson', 'IT', 70000); INSERT INTO employees VALUES (4, 'Alice Wong', 'HR', 55000);

1.2 Code to create the table directly with Python

Of course it is also possible to create the table directly from Python. To do so, you can use the pyodbc library in Python to connect to your SQL Server database and execute SQL queries. Here’s an example code that creates the “employees” table and inserts the four rows directly from Python code:

import pyodbc # connect to the SQL Server database connection = pyodbc.connect('Driver=;' 'Server=localhost;' 'Database=Expert-Only;' 'Trusted_Connection=yes;') # create the employees table cursor = connection.cursor() cursor.execute(""" CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ) """) # insert the rows into the employees table cursor.execute("INSERT INTO employees VALUES (1, 'John Smith', 'Sales', 50000)") cursor.execute("INSERT INTO employees VALUES (2, 'Jane Doe', 'Marketing', 60000)") cursor.execute("INSERT INTO employees VALUES (3, 'Bob Johnson', 'IT', 70000)") cursor.execute("INSERT INTO employees VALUES (4, 'Alice Wong', 'HR', 55000)") # commit the changes and close the connection connection.commit() connection.close()

To learn more on how to manage SQL Server tables in Python, read this tutorial.

And that’s it! With these three steps, we can export SQL Server data into an MS Excel document in Python.

3. Conclusion on SQL Server table export into Excel in Python

You have successfully learned how to export a SQL Server table to an Excel file in Python. By following the three steps outlined in this tutorial, you can now connect to your MS SQL database. Then retrieve data from a table, and export it to an Excel file with proper formatting using Python. This skill can be useful for a wide range of data analysis and reporting tasks. Continue to explore and practice with these libraries and tools to enhance your Python data manipulation skills.

To go further, this time on file management and compression, this tutorial shows how to zip and unzip files into archives using the zipfile python module.

Источник

How to import an Excel file into SQL Server using Python?

In this tutorial, we will learn how to import an Excel file into a SQL Server table using Python. Let’s delve into the process of manipulating data with Python scripts. Our journey will encompass three main stages: crafting a sample Excel file containing data, constructing a table in SQL Server to accommodate the data, and seamlessly importing the Excel data into the SQL Server table.

To achieve this, we will employ the power of Python’s openpyxl and pyodbc libraries. By the conclusion of this tutorial, you will possess a solid grasp of importing data from an Excel file into a SQL Server database, harnessing the versatility and efficiency of Python.

1. Prepare a sample Excel file with some sample data

To demonstrate how to import data from an Excel file to a SQL Server table using Python, we first need a sample Excel file with data. In this example, we will use an Excel file named employees.xlsx containing employee data with the following headers:

Save the Excel file to a directory, for example, in my case, I use this Windows path: C:\data\employees.xlsx

2. Create a SQL Server table to store the data

Before importing the data from the Excel file, we need to create a table in SQL Server to store the data. We will create a simple table named employees with four columns:

CREATE TABLE dbo.employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT );

Use SQL Server Management Studio (SSMS) or execute the SQL query using the pyodbc library in Python to create the table in your SQL Server database.

3. Import the data from the Excel file into SQL Server

Now, we will use the openpyxl library to read data from the Excel file and the pyodbc library to insert the data into the SQL Server table. The following example code demonstrates how to read data from the employees.xlsx file and insert it into the employees table:

import openpyxl import pyodbc # Load the Excel workbook and select the active worksheet workbook = openpyxl.load_workbook("C:\\data\\employees.xlsx") worksheet = workbook.active # Connect to the SQL Server database connection = pyodbc.connect('Driver=;' 'Server=localhost;' 'Database=Expert-Only;' 'Trusted_Connection=yes;') cursor = connection.cursor() # Read data from the Excel file and insert it into the SQL Server table for row in range(2, worksheet.max_row + 1): column=1).value name = worksheet.cell(row=row, column=2).value department = worksheet.cell(row=row, column=3).value salary = worksheet.cell(row=row, column=4).value cursor.execute("INSERT INTO dbo.employees (id, name, department, salary) VALUES (?, ?, ?, ?)", id, name, department, salary) # Commit the changes and close the connection connection.commit() connection.close()

In the code above, we perform a few actions:

1. We first load the Excel workbook and then select the active worksheet.
2. Then, we connect to the SQL Server database using the pyodbc library.
3. We loop through the rows in the Excel worksheet, starting from row 2 to ignore the header row.
4. And we read the data from each cell in the row.
5. Finally, we insert the data into the employees table using an INSERT INTO SQL query and commit the changes.

After executing the script, you should see the data from the Excel file successfully imported into the SQL Server table.

Conclusion on importing Excel data into SQL Server with Python

You have successfully learned how to import data from an Excel file into a SQL Server table using a Python script. By following the steps outlined in this tutorial, you can now read data from an Excel file and insert it into a SQL Server table with proper formatting using Python. This skill can be useful for a wide range of data analysis, reporting tasks, and data migration projects. Continue to explore and practice with these libraries and tools to enhance your Python data manipulation skills.

To go further, consider the three following additional resources:

• Explore the Pandas library for more sophisticated data manipulation and analysis tasks in Python.
• Learn how to manage Excel files with advanced formatting using the openpyxl library.
• Discover other techniques to import and export data to and from SQL Server using different file formats and tools.

By combining the knowledge from these resources, you can create more advanced data processing workflows, automate data import and export tasks, and develop custom solutions for your data management needs using Python and SQL Server.

From our Python tutorials on importing data to SQL Server database

Источник