Работа с SQL Server из Python: полное руководство

Раздел: Python -> SQLite и PostgreSQL

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

Основные способы подключения и выполнения запросов

Какое решение обеспечивает стабильное и производительное соединение с SQL Server из Python?

Наиболее эффективным и рекомендуемым способом является использование библиотеки pyodbc в сочетании с официальным ODBC-драйвером от Microsoft. Это обеспечивает полную поддержку возможностей SQL Server, включая последние версии, типы данных и безопасность.

Установка драйвера и библиотеки:


# Установка ODBC-драйвера (пример для Windows)
# Скачать с сайта Microsoft и установить
# Для Linux: sudo apt-get install msodbcsql17

# Установка pyodbc
pip install pyodbc

Sql server python (sql server и python)

Пример подключения и выполнения запроса:


import pyodbc

conn_str = (
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost\\SQLEXPRESS;'
    'DATABASE=AdventureWorks;'
    'Trusted_Connection=yes;'
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute('SELECT TOP 5 Name, ListPrice FROM Production.Product')
rows = cursor.fetchall()
for row in rows:
    print(f'{row.Name}: ${row.ListPrice:.2f}')
cursor.close()
conn.close()

Sql через python (работа с sql через python)

Пояснения: Trusted_Connection=yes использует аутентификацию Windows. Для SQL Server аутентификации укажите UID и PWD.

Типичные ошибки:

'ODBC Driver 17 for SQL Server' not found - драйвер не установлен. Скачайте его с сайта Microsoft.
Login failed for user - неверные учетные данные или отключен SQL Server аутентификация.
Cannot open database - база данных не существует или нет доступа.

Решение: проверьте строку подключения, права доступа, используйте Trusted_Connection при наличии Windows-аутентификации.

Как альтернативно подключиться к SQL Server без ODBC?

Библиотека pymssql использует FreeTDS, что упрощает установку на Linux без отдельного ODBC-драйвера. Однако она может не поддерживать некоторые новые возможности SQL Server.


import pymssql

conn = pymssql.connect(server='localhost', user='sa', password='YourPassword!', database='AdventureWorks')
cursor = conn.cursor()
cursor.execute('SELECT COUNT(*) FROM SalesOrderHeader')
count = cursor.fetchone()[0]
print(f'Всего заказов: {count}')

Python postgresql (подключение к postgresql из python)

Ошибки: Unable to connect to Adaptive Server - проблемы с FreeTDS или сетевыми настройками. Настройка файла freetds.conf.

Как использовать универсальный движок SQLAlchemy для работы с SQL Server?

SQLAlchemy предоставляет высокоуровневый интерфейс и ORM, поддерживая множество СУБД. Для SQL Server в качестве диалекта используется mssql+pyodbc.


from sqlalchemy import create_engine, text

engine = create_engine(
    'mssql+pyodbc://sa:YourPassword!@localhost/AdventureWorks?driver=ODBC+Driver+17+for+SQL+Server',
    echo=True
)
with engine.connect() as conn:
    result = conn.execute(text('SELECT TOP 3 * FROM Person.Address'))
    for row in result:
        print(row)

Python база данных таблицу (создание таблицы в базе данных python)

Проблемы: неправильный формат URL - экранируйте пробелы в driver через + или %20. Также возможны ошибки при использовании Trusted_Connection - в таком случае URL: mssql+pyodbc:///?odbc_connect=...

Как быстро загрузить данные из SQL Server в pandas DataFrame и обратно?

Pandas интегрируется с SQLAlchemy, позволяя одной строкой читать таблицу и записывать результаты.


import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(...) # как выше
df = pd.read_sql('SELECT * FROM SalesOrderDetail', engine, chunksize=1000)
total = 0
for chunk in df:
    total += chunk['OrderQty'].sum()
print(f'Суммарное количество: {total}')

Ошибка: DatabaseError: Execution failed - проверьте права на выполнение запроса. Большие данные - используйте chunksize.

Пример 1: Параметризованные запросы и управление транзакциями в pyodbc

Параметризация предотвращает SQL-инъекции и автоматически преобразует типы. Для включения быстрой массовой вставки используйте fast_executemany.

Пример


import pyodbc

conn = pyodbc.connect(conn_str, autocommit=False)
cursor = conn.cursor()
cursor.fast_executemany = True

# Параметризованная вставка
data = [('Widget', 12.99, 100), ('Gadget', 24.99, 50)]
cursor.executemany('INSERT INTO Products (Name, Price, Stock) VALUES (?, ?, ?)', data)
conn.commit()
print('Вставлено:', cursor.rowcount)

Вставлено: 2

При ошибке можно откатить транзакцию:

Пример


try:
    cursor.execute('UPDATE Products SET Stock = Stock - 10 WHERE ProductID = ?', (1,))
    cursor.execute('UPDATE Products SET Stock = Stock + 10 WHERE ProductID = ?', (2,))
    conn.commit()
except Exception as e:
    conn.rollback()
    print('Ошибка:', e)

Ошибка: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'Products'.") - если таблицы нет.

Пример 2: Выполнение хранимой процедуры с выходным параметром

Используйте cursor.execute с синтаксисом SET @param = ? и получите значение через cursor.fetchone после выхода.

Пример


import pyodbc

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
# Создаем процедуру (один раз)
cursor.execute('''
CREATE PROCEDURE GetTotalOrders
    @CustomerID INT,
    @Total INT OUTPUT
AS
    SELECT @Total = COUNT(*) FROM SalesOrderHeader WHERE CustomerID = @CustomerID
''')
conn.commit()

# Вызов
customer_id = 11000
cursor.execute('SET @Total = 0; EXEC GetTotalOrders ?, @Total OUTPUT', (customer_id,))
total = cursor.fetchone()[0]
print(f'Заказов для клиента {customer_id}: {total}')

Заказов для клиента 11000: 5

Пример 3: Использование SQLAlchemy Core для массовой вставки

SQLAlchemy Table и insert упрощают вставку с преобразованием типов.

Пример


from sqlalchemy import Table, MetaData, create_engine, insert

engine = create_engine(...)
metadata = MetaData()
customers = Table('Customers', metadata, autoload_with=engine)

data = [
    {'CustomerName': 'John Doe', 'Email': 'john@example.com'},
    {'CustomerName': 'Jane Smith', 'Email': 'jane@example.com'}
]
with engine.connect() as conn:
    conn.execute(insert(customers), data)
    conn.commit()
print('Добавлено клиентов:', len(data))

Добавлено клиентов: 2

Пример 4: Загрузка CSV в SQL Server с автоматическим созданием таблицы через pandas

Pandas умеет создавать таблицу на основе DataFrame и записывать данные за несколько итераций.

Пример


import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(...)
df = pd.read_csv('sales.csv', nrows=1000)
df.to_sql('SalesStaging', engine, if_exists='replace', index=False, chunksize=500)
print('Таблица создана и заполнена')

Таблица создана и заполнена

Пример 5: Использование fast_executemany для повышения скорости вставки

При вставке тысяч строк fast_executemany значительно ускоряет процесс, объединяя вставки в один пакет.

Пример


import pyodbc
import time

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.fast_executemany = True

rows = [(i, f'Product_{i}', i*10.0) for i in range(10000)]
start = time.time()
cursor.executemany('INSERT INTO ProductsFast (ProductID, Name, Price) VALUES (?, ?, ?)', rows)
conn.commit()
print(f'Вставка 10000 записей за {time.time()-start:.2f} сек.')

Вставка 10000 записей за 0.12 сек.

SQL Server и Python - comments

Sql server python (python)