Взаимодействие Python с реляционными базами данных

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

Основные подходы к работе с SQL из Python

Как выполнить простой SQL запрос к SQLite и PostgreSQL?

Для работы с SQLite используется встроенная библиотека sqlite3, а для PostgreSQL – сторонняя psycopg2. Оба драйвера предоставляют низкоуровневый доступ к базам данных.

import sqlite3

# Подключение к SQLite (файл базы данных)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Создание таблицы
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  name TEXT NOT NULL,
                  email TEXT UNIQUE)''')

# Вставка данных
cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', ('Alice', 'alice@example.com'))
conn.commit()

# Выборка
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())

# Закрытие соединения
conn.close()

Sql server python (sql server и python)

Для PostgreSQL подключение аналогично:

import psycopg2

conn = psycopg2.connect(host='localhost', dbname='test', user='user', password='pass')
cursor = conn.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) UNIQUE)')
cursor.execute('INSERT INTO users (name, email) VALUES (%s, %s)', ('Bob', 'bob@example.com'))
conn.commit()
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())
cursor.close()
conn.close()

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

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

  • SQL-инъекции – никогда не подставляйте значения напрямую через f-строки. Используйте параметризованные запросы (? для sqlite3, %s для psycopg2).
  • Забыли вызвать commit() – изменения не сохранятся.
  • Не закрыли соединение – может привести к утечке ресурсов.

Цели использования: sqlite3 подходит для прототипирования, встроенных приложений и тестов; psycopg2 – для production-систем на PostgreSQL.

Как упростить работу с SQL, используя объектно-реляционное отображение?

Библиотека SQLAlchemy предоставляет ORM (Object-Relational Mapping), позволяющий работать с таблицами как с классами Python.

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    email = Column(String(100))

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

with Session(engine) as session:
    user = User(name='Charlie', email='charlie@example.com')
    session.add(user)
    session.commit()
    
    users = session.query(User).all()
    for u in users:
        print(u.name, u.email)

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

Проблемы и решения:

  • Ленивая загрузка связанных объектов – используйте joinedload для явной подгрузки.
  • Настройка сессии – всегда используйте контекстный менеджер with Session.

Используется в приложениях со сложной схемой данных, требующих удобной миграции и валидации.

Каким образом использовать асинхронные запросы?

Асинхронные драйверы (aiosqlite для SQLite, asyncpg для PostgreSQL) позволяют не блокировать event loop при выполнении запросов.

import asyncio
import aiosqlite

async def main():
    async with aiosqlite.connect('example.db') as db:
        async with db.execute('SELECT * FROM users') as cursor:
            async for row in cursor:
                print(row)

asyncio.run(main())

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

Ошибки:

  • Забыли запустить event loop – используйте asyncio.run().
  • Смешивание синхронного и асинхронного кода – избегайте блокирующих вызовов внутри корутин.

Применяется в высоконагруженных веб-приложениях (FastAPI, aiohttp) для увеличения пропускной способности.

Как загрузить SQL результат в pandas DataFrame для анализа?

Библиотека pandas умеет напрямую выполнять SQL-запросы через pd.read_sql.

import pandas as pd
import sqlite3

conn = sqlite3.connect('example.db')
df = pd.read_sql('SELECT * FROM users', conn)
print(df.head())
conn.close()

Сложности:

  • Большие наборы данных – используйте аргумент chunksize для чтения по частям.
  • Несоответствие типов – pandas может привести типы к своим стандартам; проверяйте dtypes.

Идеально для аналитических задач, где требуется дальнейшая обработка данных средствами pandas.

Продвинутые приёмы работы с SQL в Python

Транзакции и точки сохранения

Использование контекстного менеджера для автоматического commit/rollback.

Пример 
import sqlite3

conn = sqlite3.connect('example.db')
try:
    with conn:
        conn.execute('UPDATE users SET name=? WHERE id=?', ('David', 1))
        # При ошибке внутри блока with произойдёт rollback
except sqlite3.Error as e:
    print('Ошибка:', e)
finally:
    conn.close()
(изменения зафиксированы только при успешном завершении блока)

Массовая вставка (bulk insert) с executemany

Пример 
import sqlite3

data = [('User1', 'u1@example.com'), ('User2', 'u2@example.com')]
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.executemany('INSERT INTO users (name, email) VALUES (?, ?)', data)
conn.commit()
conn.close()
Две записи добавлены за один вызов.

Для PostgreSQL более эффективен метод copy_from:

Пример 
import psycopg2
from io import StringIO

conn = psycopg2.connect(...)
cursor = conn.cursor()
buffer = StringIO()
buffer.write('User3\tu3@example.com\nUser4\tu4@example.com\n')
buffer.seek(0)
cursor.copy_from(buffer, 'users', sep='\t', columns=('name', 'email'))
conn.commit()
conn.close()

Пул соединений (connection pool)

Для многопоточных приложений используется пул, чтобы не создавать новое соединение на каждый запрос.

Пример 
from psycopg2 import pool

postgres_pool = pool.SimpleConnectionPool(1, 10, host='localhost', dbname='test', user='user', password='pass')
conn = postgres_pool.getconn()
# работа с conn
postgres_pool.putconn(conn)
postgres_pool.closeall()

Для SQLite пул не требуется, так как sqlite3 поддерживает только одно пишущее соединение.

Работа с JSON полями в PostgreSQL

Пример 
import psycopg2

conn = psycopg2.connect(...)
cursor = conn.cursor()
cursor.execute('''CREATE TABLE config (
                  id SERIAL PRIMARY KEY,
                  settings JSONB)''')

import json
data = {'theme': 'dark', 'lang': 'ru'}
cursor.execute('INSERT INTO config (settings) VALUES (%s)', (json.dumps(data),))
cursor.execute('SELECT settings->>'theme' FROM config')
print(cursor.fetchone())
conn.commit()
conn.close()
('dark',)

Миграции схемы с Alembic

Alembic (часть SQLAlchemy) позволяет версионировать изменения структуры БД.

Пример 
# Создание миграции:
# alembic revision --autogenerate -m "add age column"
# Применение:
# alembic upgrade head

Работа с несколькими базами данных одновременно

Можно открыть несколько соединений и выполнять запросы синхронно или асинхронно.

Пример 
import sqlite3
import psycopg2

conn_sqlite = sqlite3.connect('local.db')
conn_pg = psycopg2.connect(...)

data_sqlite = conn_sqlite.execute('SELECT * FROM users').fetchall()
for row in data_sqlite:
    conn_pg.execute('INSERT INTO users (name, email) VALUES (%s, %s)', (row[1], row[2]))
conn_pg.commit()

conn_sqlite.close()
conn_pg.close()

Работа с SQL через Python - comments

En
Sql через python (python)