Pandas: чтение базы данных SQL

Pandas предоставляет набор функций для прямого взаимодействия с базами данных SQL. Результаты запросов можно напрямую считывать в DataFrame, а DataFrame также можно записывать обратно в базу данных. Это позволяет аналитикам данных избегать ручной обработки подключений к базе данных и разбора результатов, значительно упрощая взаимодействие между базами данных и Python.

Обзор основных функций

Функция	Назначение	Тип возвращаемого значения
`pd.read_sql()`	Выполняет SQL-запросы или считывает всю таблицу (универсальная функция, работающая в обоих сценариях)	DataFrame
`pd.read_sql_query()`	Выполняет SQL-запросы, подходит для сложных запросов	DataFrame
`pd.read_sql_table()`	Напрямую читает всю таблицу, поддерживает только подключения SQLAlchemy	DataFrame
`DataFrame.to_sql()`	Записывает DataFrame в таблицу базы данных	None / int

В практической работе рекомендуется использоватьpd.read_sql()потому что он автоматически определяет, выполнить запрос или прочитать всю таблицу, в зависимости от переданных параметров, обеспечивая наилучшую совместимость.pd.read_sql_table()Поддерживает только подключения к движку SQLAlchemy; не поддерживает нативныеsqlite3и другие подключения DB-API.

Установление подключения к базе данных

Сам Pandas не подключается напрямую к базам данных. Для установления подключения требуется сторонняя библиотека, которая затем передаётся. Есть два основных подхода:движок SQLAlchemy(рекомендуется) инативное подключение DB-API(легковесное и простое).

Способ 1: SQLAlchemy (рекомендуется)

SQLAlchemy — самый популярный инструментарий для работы с базами данных в Python. Он поддерживает все основные базы данных и обладает наилучшей совместимостью с Pandas:

pip install sqlalchemy

Пример

from sqlalchemy import create_engine

# Формат строки подключения SQLAlchemy: тип базы данных + driver://username:password@host:port/database name
# SQLite (файловая база данных, имя пользователя и пароль не требуются)
engine = create_engine("sqlite:///mydata.db")

# MySQL
engine = create_engine("mysql+pymysql://root:password@localhost:3306/mydb")

# PostgreSQL
engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/mydb")

# SQL Server
engine = create_engine("mssql+pyodbc://user:password@server/mydb?driver=ODBC+Driver+17+for+SQL+Server")

# Проверить, успешно ли установлено подключение
with engine.connect() as conn:
print("Подключение успешно")

Способ 2: нативное подключение sqlite3 (только для SQLite)

Встроенный в Pythonsqlite3модуль не требует дополнительной установки и подходит для легковесных локальных баз данных SQLite:

Пример

import sqlite3

# Подключение к файловой базе данных SQLite (автоматически создаётся, если файл не существует)
conn = sqlite3.connect("mydata.db")

# Подключение к базе данных в памяти (данные исчезают после завершения программы; подходит для тестирования)
conn_memory = sqlite3.connect(":memory:")

# Подключение необходимо закрыть вручную после использования
# conn.close()

pd.read_sql() Чтение данных

Базовый синтаксис

pd.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Описание основных параметров:

Параметр	Type	Описание
`sql`	str	SQL-запрос или имя таблицы (связано с`con`хранения)
`con`	Объект подключения	Объект engine SQLAlchemy или объект соединения DB-API
`index_col`	str или list	Устанавливает указанный столбец как индекс строк DataFrame
`params`	list или dict	Значения параметров для параметризованных SQL-запросов, предотвращающие SQL-инъекции
`parse_dates`	list или dict	Разбирает указанный столбец как тип datetime
`chunksize`	int	Читает фрагментами, возвращая итератор объектов DataFrame с указанным количеством строк в каждом фрагменте

1. Чтение всей таблицы

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Прочитать всю таблицу employees
df = pd.read_sql("employees", con=engine)
print(df.head())
print(f"Всего {len(df)} строк, {len(df.columns)} столбцов")

2. Выполнение SQL-запросов

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Запрос с условиями
df = pd.read_sql("SELECT * FROM employees WHERE department = 'IT'", con=engine)

# Запрос соединения нескольких таблиц
sql = """
SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 10000
ORDER BY e.salary DESC
"""
df = pd.read_sql(sql, con=engine)
print(df.head(10))

3. Параметризованные запросы (предотвращение SQL-инъекций)

Когда условия запроса поступают от пользовательского ввода,всегда используйте параметризованные запросыи не используйте конкатенацию строк для SQL:

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# ❌ Опасный подход: конкатенация строк SQL, риск SQL-инъекции
dept = "IT"
# df = pd.read_sql(f"SELECT * FROM employees WHERE department = '{dept}'", engine)

# ✅ Безопасный подход: используйте ? placeholders (sqlite3) или именованные параметры :name (SQLAlchemy)
# Стиль SQLite / DB-API (с использованием ?)
df = pd.read_sql(
"SELECT * FROM employees WHERE department = ? AND salary > ?",
con=engine,
params=["IT", 8000] # params — это список, подставляющий ? по позиции
)

# Стиль именованных параметров SQLAlchemy (с использованием :param_name)
from sqlalchemy import text
with engine.connect() as conn:
df = pd.read_sql(
text("SELECT * FROM employees WHERE department = :dept AND salary > :min_salary"),
con=conn,
params={"dept": "IT", "min_salary": 8000}
)

print(df)

4. Установка столбца индекса

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Установить столбец id из базы данных в качестве индекса строк DataFrame
df = pd.read_sql("SELECT * FROM employees", con=engine, index_col="id")
print(df.head())

# Использовать несколько столбцов в качестве составного индекса
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
index_col=["year", "month"] # Составной индекс
)

5. Парсинг столбцов с датами

Поля с датами, хранящиеся в базе данных, по умолчанию считываются как строки. С помощьюparse_datesпараметра их можно напрямую преобразовать вdatetime：

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Преобразовать столбцы created_at и updated_at в тип datetime
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
parse_dates=["created_at", "updated_at"]
)

print(df.dtypes)
# created_at datetime64[ns]
# updated_at datetime64[ns]

# Также можно указать формат разбора (для нестандартных форматов даты)
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
parse_dates={"created_at": "%Y%m%d"} # Разобрать формат вроде "20240115" как дату
)

Чтение больших данных по частям (chunksize)

Когда таблица базы данных содержит очень большой объём данных, чтение всего сразу в память может вызвать OOM (out of memory). Используяchunksizeпараметр, данные можно читать пакетами, обрабатывая только часть за раз:

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:password@localhost/bigdata")

# chunksize=10000 означает чтение 10000 строк за раз и возврат итератора
chunks = pd.read_sql("SELECT * FROM large_table", con=engine, chunksize=10000)

# Обрабатывать порцию за порцией (в памяти одновременно только 10000 строк)
result_list = []
for i, chunk in enumerate(chunks):
# Применить логику обработки к каждому чанку (например, фильтрация, агрегация и т. д.)
processed = chunk[chunk["status"] == "active"]
result_list.append(processed)
print(f"Обработан чанк {i+1}, корректных строк в текущем чанке: {len(processed)}")

# Объединить все обработанные чанки в один DataFrame
final_df = pd.concat(result_list, ignore_index=True)
print(f"Итоговое количество корректных строк данных: {len(final_df)}")

pd.read_sql_query() и pd.read_sql_table()

pd.read_sql_query(): Выполняет только запросы

иpd.read_sql()Функциональность в основном такая же, но он принимает только SQL-запросы, а не имена таблиц. Параметры точно такие же, что подходит для сценариев, где нужно чётко различать операции "запрос" и "чтение таблицы":

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

df = pd.read_sql_query(
"SELECT name, salary FROM employees WHERE salary > 5000",
con=engine
)
print(df)

pd.read_sql_table(): Читает всю таблицу (только SQLAlchemy)

pd.read_sql_table()Специально используется для чтения всей таблицы, с поддержкой фильтрации столбцов и строк через параметры, ноподдерживает только подключения через движок SQLAlchemyи не поддерживает нативные подключения, такие как sqlite3:

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Читать указанные столбцы из таблицы employees
df = pd.read_sql_table(
"employees",
con=engine,
columns=["id", "name", "salary", "department"] # Читать только эти столбцы
)

# Также поддерживает указание schema (режим в базе данных)
df = pd.read_sql_table(
"employees",
con=engine,
schema="hr" # Читать таблицу employees в схеме hr
)

print(df.head())

Запись DataFrame в базу данных (to_sql)

ИспользованиеDataFrame.to_sql()Вы можете записать DataFrame в базу данных, с поддержкой создания новой таблицы, добавления данных или перезаписи исходной таблицы:

Базовый синтаксис

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

if_existsПараметр определяет поведение, когда целевая таблица уже существует:

Значение if_exists	Поведение	Сценарий использования
`'fail'`(По умолчанию)	Вызывает ошибку, если таблица уже существует	Предотвращает случайную перезапись существующих данных
`'replace'`	Сначала удаляет исходную таблицу, затем заново создаёт таблицу и записывает в неё	Полное обновление данных
`'append'`	Добавляет данные в существующую таблицу, не изменяя структуру таблицы	Инкрементально записывает новые данные

Пример

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Подготовка примера данных
df = pd.DataFrame({
"name": ["Чжан Сань", "Ли Сы", "Ван У"],
"department": ["IT", "HR", "IT"],
"salary": [12000, 8000, 15000]
})

# Записать DataFrame в таблицу employees; заменить её, если она уже существует
df.to_sql(
"employees",
con=engine,
if_exists="replace", # Перезаписать исходные данные
index=False # Не записывать индекс строк DataFrame в базу данных (обычно не требуется)
)

# Добавить новые данные в существующую таблицу (примечание: имена столбцов и типы данных должны совпадать)
new_employees = pd.DataFrame({
"name": ["Zhao Liu"],
"department": ["Finance"],
"salary": [11000]
})
new_employees.to_sql("employees", con=engine, if_exists="append", index=False)

# Проверить результат записи
result = pd.read_sql("SELECT * FROM employees", con=engine)
print(result)

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

При записи можно использоватьdtypeпараметр, чтобы явно указать тип каждого столбца в базе данных:

Пример

import pandas as pd
from sqlalchemy import create_engine, Integer, String, Float, DateTime

engine = create_engine("sqlite:///mydata.db")

df = pd.DataFrame({
"id": [1, 2, 3],
"name": ["Alice", "Bob", "Charlie"],
"score": [92.5, 88.0, 95.3],
})

df.to_sql(
"students",
con=engine,
if_exists="replace",
index=False,
dtype={
"id": Integer(), # Тип Integer
"name": String(50), # Строка переменной длины, максимум 50 символов
"score": Float() # Число с плавающей точкой
}
)

Полный пример использования

Ниже приведён полный пример чтения данных о продажах из базы данных SQLite и их анализа:

Пример

import pandas as pd
import sqlite3
from sqlalchemy import create_engine

# Шаг 1: Подготовка тестовой базы данных
engine = create_engine("sqlite:///sales.db")
conn = sqlite3.connect("sales.db")

# Создать образец данных и записать его в базу данных
sales_data = pd.DataFrame({
"order_id": [1001, 1002, 1003, 1004, 1005, 1006],
"product": ["Laptop", "Phone", "Tablet", "Laptop", "Phone", "Tablet"],
"amount": [6999, 3999, 2999, 7299, 4299, 3199],
"quantity": [2, 5, 3, 1, 4, 2],
"order_date": ["2024-01-10", "2024-01-15", "2024-01-20",
"2024-02-05", "2024-02-10", "2024-02-18"]
})
sales_data.to_sql("sales", con=engine, if_exists="replace", index=False)

# Шаг 2: Прочитать данные и разобрать даты
df = pd.read_sql(
"SELECT * FROM sales",
con=engine,
parse_dates=["order_date"]
)
print("Исходные данные:")
print(df)
print()

# Шаг 3: Рассчитать продажи по продуктам
sql_agg = """
SELECT product,
COUNT(*) AS order_count,
SUM(amount * quantity) AS total_sales,
AVG(amount) AS avg_unit_price
FROM sales
GROUP BY product
ORDER BY total_sales DESC
"""
summary = pd.read_sql(sql_agg, con=engine)
print("Сводка продаж по продуктам:")
print(summary)
print()

# Шаг 4: Записать сводные результаты обратно в базу данных
summary.to_sql("sales_summary", con=engine, if_exists="replace", index=False)
print("Сводные данные записаны в таблицу sales_summary")

conn.close()

Результат выполнения приведённого выше кода:

原始数据：
   order_id product  amount  quantity order_date
0      1001     笔记本    6999         2 2024-01-10
1      1002      手机    3999         5 2024-01-15
2      1003      平板    2999         3 2024-01-20
3      1004     笔记本    7299         1 2024-02-05
4      1005      手机    4299         4 2024-02-10
5      1006      平板    3199         2 2024-02-18

各产品销售汇总：
  product  订单数  总销售额   平均单价
0     手机    2   36991   4149.0
1    笔记本    2   21297   7149.0
2     平板    2   15395   3099.0

汇总数据已写入 sales_summary 表

Типичные проблемы и примечания

1. Соединение необходимо закрывать после использования

При использовании нативного соединения DB-API (например, sqlite3) необходимо вручную закрыть соединение после операции. Рекомендуется использоватьwithоператор для автоматического управления им:

Пример

import sqlite3
import pandas as pd

# Используйте оператор with; соединение автоматически закрывается при выходе, и не будет утечки, даже если возникнет исключение
with sqlite3.connect("mydata.db") as conn:
df = pd.read_sql("SELECT * FROM employees", con=conn)

print(df) # Соединение было автоматически закрыто, но данные df всё ещё доступны

2. Не читайте все данные из большой таблицы напрямую

Для больших таблиц с миллионами строк или более прямоеSELECT *чтение всех данных исчерпает память. Следует в первую очередь фильтровать данные на уровне SQL (WHERE, LIMIT) или использоватьchunksizeчтение по частям.

3. read_sql_table поддерживает только SQLAlchemy

Если вы используете собственное соединение DB-API, например sqlite3, чтобы вызвать егоpd.read_sql_table(), будет вызвана ошибкаNotImplementedError. Пожалуйста, используйтеpd.read_sql()или переключитесь на движок SQLAlchemy.

4. Параметр index у to_sql по умолчанию имеет значение True

По умолчаниюto_sqlиндекс строк DataFrame (0, 1, 2...) также записывается в базу данных, создавая столбец с именемindexcolumn, который обычно избыточен. Рекомендуется явно передаватьindex=False。

5. Драйверы баз данных нужно устанавливать отдельно

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

Базы данных	Пакет драйвера	Команда установки
MySQL	PyMySQL	`pip install pymysql`
PostgreSQL	psycopg2	`pip install psycopg2-binary`
SQL Server	pyodbc	`pip install pyodbc`
Oracle	cx_Oracle	`pip install cx_Oracle`
SQLite	Встроенный	Установка не требуется

Другие расширения

Чтение и запись данных

Ядро обработки данных

Справочное руководство

Статистика и примеры

Pandas: чтение базы данных SQL

Обзор основных функций

Установление подключения к базе данных

Способ 1: SQLAlchemy (рекомендуется)

Пример

Способ 2: нативное подключение sqlite3 (только для SQLite)

Пример

pd.read_sql() Чтение данных

Базовый синтаксис

1. Чтение всей таблицы

Пример

2. Выполнение SQL-запросов

Пример

3. Параметризованные запросы (предотвращение SQL-инъекций)

Пример

4. Установка столбца индекса

Пример

5. Парсинг столбцов с датами

Пример

Чтение больших данных по частям (chunksize)

Пример

pd.read_sql_query() и pd.read_sql_table()

pd.read_sql_query(): Выполняет только запросы

Пример

pd.read_sql_table(): Читает всю таблицу (только SQLAlchemy)

Пример

Запись DataFrame в базу данных (to_sql)

Базовый синтаксис

Пример

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

Пример

Полный пример использования

Пример

Типичные проблемы и примечания

Пример