SQL-Ex blog

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

• Экземпляр 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 означает использование доверительного подключения.

sqlcmd -S  -E

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

CREATE DATABASE ExcelData;

GO

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

SELECT name FROM sys.databases

GO

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



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

USE ExcelData;

GO

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



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

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.

npm install 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={SQL Server};'
'Server=POWERSERVER\POWERSERVER;'
'Database=ExcelData;'
'Trusted_connection=yes;'
)

try:
coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
print("Passed")

except:
print("failed!")

Запись в 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')

try:
df.to_sql('EPL_LOG',con=coxn,if_exists='replace')

except:
pass
print("Failed!")

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.