Интеграция Python и PostgreSQL: руководство по подключению

Раздел: Взаимодействие с внешними сервисами -> Работа с PostgreSQL

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

Как выполнить подключение к PostgreSQL и выполнить простой SQL запрос наиболее эффективным способом?

Наиболее распространенным и производительным решением является использование драйвера psycopg2. Он написан на C и обеспечивает полную поддержку протокола PostgreSQL. Для установки выполняется команда:

pip install psycopg2-binary

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

При установке в продакшене рекомендуется psycopg2 (без суффикса binary), собранный из исходников для достижения максимальной совместимости с системными библиотеками.

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

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    dbname='mydb',
    user='user',
    password='secret'
)
cur = conn.cursor()
cur.execute('SELECT version();')
version = cur.fetchone()
print(version)
cur.close()
conn.close()

('PostgreSQL 16.2 (Debian 16.2-1.pgdg120+1) on x86_64-pc-linux-gnu',)

Пояснение: psycopg2.connect() создает соединение, курсор позволяет выполнять SQL и получать результаты. После работы соединение и курсор обязательно закрываются.

Типичная ошибка: OperationalError: connection to server at 'localhost' (127.0.0.1), port 5432 failed: Connection refused. Причины: сервер PostgreSQL не запущен, неверный порт, файрволл. Решение: проверить статус сервера (systemctl status postgresql), настроить pg_hba.conf для разрешения подключений.

Другая проблема: ValueError: bytea expected in binary format при работе с бинарными данными - необходимо использовать psycopg2.Binary() или адаптеры.

Как организовать асинхронное взаимодействие с PostgreSQL для повышения производительности?

Для асинхронного программирования (asyncio) используется драйвер asyncpg. Он полностью асинхронен, написан на C и часто работает быстрее psycopg2 в многопоточных сценариях. Установка:

pip install asyncpg

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

import asyncio
import asyncpg

async def main():
    conn = await asyncpg.connect(
        host='localhost',
        port=5432,
        database='mydb',
        user='user',
        password='secret'
    )
    version = await conn.fetchval('SELECT version();')
    print(version)
    await conn.close()

asyncio.run(main())

Используется в высоконагруженных приложениях (веб-серверы на aiohttp, FastAPI).

Ошибка: asyncpg.exceptions.ConnectionDoesNotExistError: cannot assign requested address - исчерпаны порты при большом количестве соединений. Решение: использовать пул соединений (asyncpg.create_pool).

Проблема с кодировкой: по умолчанию asyncpg использует UTF-8, что редко вызывает ошибки.

Как упростить работу с базой данных через объектно-реляционное отображение?

Библиотека SQLAlchemy поддерживает два режима: Core (низкоуровневый) и ORM (высокоуровневый). Установка:

pip install sqlalchemy psycopg2-binary

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

from sqlalchemy import create_engine, text

engine = create_engine('postgresql+psycopg2://user:secret@localhost:5432/mydb')
with engine.connect() as conn:
    result = conn.execute(text('SELECT version();'))
    version = result.fetchone()
    print(version)

Целесообразен в крупных проектах, где требуется абстракция от конкретной СУБД, миграции, управление транзакциями.

Ошибка: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server on socket '/var/run/postgresql/.s.PGSQL.5432' failed - неверный путь к сокету. Решение: явно указать host='localhost' или изменить строку подключения.

Как подключиться к PostgreSQL без зависимостей от C библиотек?

Драйвер pg8000 реализован полностью на Python. Установка:

pip install pg8000

Пример:

import pg8000.native as pg

conn = pg.Connection(
    host='localhost',
    port=5432,
    database='mydb',
    user='user',
    password='secret'
)
result = conn.run('SELECT version();')
print(result)

Подходит для сред, где компиляция C-расширений затруднена (например, некоторые облачные платформы).

Производительность pg8000 ниже, чем у psycopg2 и asyncpg. Ошибка: pg8000.exceptions.InterfaceError: connection is closed - использование закрытого соединения.

Общие проблемы подключения: таймауты, SSL-сертификаты, аутентификация. При использовании SSL укажите параметр sslmode в строке подключения. Для пула соединений применяйте psycopg2.pool.ThreadedConnectionPool или asyncpg.create_pool.

Расширенные примеры работы с PostgreSQL в Python

Ниже приведены дополнительные сценарии, часто возникающие при интеграции.

1. Работа с транзакциями в psycopg2

Пример

import psycopg2

conn = psycopg2.connect('postgresql://user:secret@localhost/mydb')
cur = conn.cursor()
try:
    cur.execute('BEGIN;')
    cur.execute('INSERT INTO users(name) VALUES (%s);', ('Alice',))
    cur.execute('UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;')
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f'Transaction failed: {e}')
finally:
    cur.close()
    conn.close()

Явное управление транзакциями позволяет откатывать изменения при ошибках.

2. Bulk insert с использованием execute_values

Пример

from psycopg2.extras import execute_values
import psycopg2

conn = psycopg2.connect('postgresql://user:secret@localhost/mydb')
cur = conn.cursor()
data = [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
execute_values(cur, 'INSERT INTO users (id, name) VALUES %s', data)
conn.commit()
cur.close()
conn.close()

execute_values формирует один многострочный INSERT, что ускоряет вставку тысяч записей.

Записи добавлены: 3

3. Асинхронный пул соединений (asyncpg)

Пример

import asyncio
import asyncpg

async def main():
    pool = await asyncpg.create_pool(
        host='localhost',
        database='mydb',
        user='user',
        password='secret',
        min_size=2,
        max_size=10
    )
    async with pool.acquire() as conn:
        result = await conn.fetchval('SELECT count(*) FROM users;')
        print(f'Total users: {result}')
    await pool.close()

asyncio.run(main())

Пул управляет несколькими соединениями, снижая накладные расходы на создание.

4. Работа с JSON полями (SQLAlchemy ORM)

Пример

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

engine = create_engine('postgresql+psycopg2://user:secret@localhost/mydb')
Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    attributes = Column(JSON)

Base.metadata.create_all(engine)

with Session(engine) as session:
    product = Product(name='Laptop', attributes={'color': 'silver', 'ram': '16GB'})
    session.add(product)
    session.commit()
    result = session.query(Product).first()
    print(result.attributes['color'])

silver

5. Вызов хранимой процедуры (pg8000)

Пример

import pg8000.native as pg

conn = pg.Connection(
    host='localhost',
    database='mydb',
    user='user',
    password='secret'
)
# Предположим, есть функция: CREATE FUNCTION get_user_count() RETURNS INTEGER...
result = conn.run('SELECT get_user_count();')
print(f'User count via function: {result[0][0]}')

Драйвер pg8000 поддерживает обычные SQL вызовы функций.

6. Использование Connection Pool в psycopg2

Пример

from psycopg2.pool import SimpleConnectionPool

pool = SimpleConnectionPool(1, 10,
    host='localhost',
    database='mydb',
    user='user',
    password='secret'
)
conn = pool.getconn()
cur = conn.cursor()
cur.execute('SELECT current_database();')
print(cur.fetchone())
pool.putconn(conn)
pool.closeall()

('mydb',)

Позволяет повторно использовать соединения.

Подключение PostgreSQL в Python - comments

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