Импорт данных из файла Excel в базу данных SQL Server с помощью Python
Пересказ статьи Levi Masonde. Import Data from an Excel file into a SQL Server Database using 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
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.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой