Работа с PostgreSQL в Python: от подключения до сложных запросов

PostgreSQL — универсальная объектно-реляционная СУБД (ОРСУБД), которая применяется в веб-приложениях, аналитических системах и корпоративных сервисах. Она обеспечивает строгое соблюдение принципов ACID (от англ. атомарность, согласованность, изолированность, долговечность) и легко масштабируется с ростом нагрузки. Интеграция PostgreSQL с Python позволяет без проблем взаимодействовать с данными на всех этапах жизненного цикла приложения. Рассказываем, как выстроить работу.

Инструкции

Иллюстрация для статьи на тему «Работа с PostgreSQL в Python: от подключения до сложных запросов»

11 марта 2026 11 минут

Марина СувороваГлавный редактор блога

Время чтения11 минут

Дата публикации11 марта 2026

Продукты из этой статьи:

Evolution Managed PostgreSQL®

Содержание

Требования и подготовка к работе
Установка библиотеки psycopg2
Создание базы данных и пользователя PostgreSQL
Подключение к базе данных и работа с курсором
Операции с таблицами
Работа с транзакциями и типами данных

Требования и подготовка к работе

Перед началом работы изучите стандарт Python Database API Specification v2.0 (PEP 249) и основные модули для взаимодействия с базами данных (БД).

Python Database API Specification v2.0

Python Database API Specification v2.0 (PEP 249) — официальный стандарт Python, который позволяет обеспечить совместимость разных компонентов для взаимодействия с реляционными базами данных. Спецификация задает структуру работы с БД, набор методов и подход к устранению ошибок. Основные понятия, которые к ней относятся:

Connection — объект подключения к БД, который отвечает за управление транзакциями и закрытие соединения;
Cursor — привязанный к Connection объект для выполнения SQL-запросов и вывода результатов;
execute / executemany — методы выполнения одиночных и множественных запросов;
Exceptions — стандартизированная иерархия исключений для корректной обработки ошибок.

Благодаря DB-API 2.0 код, написанный для PostgreSQL с использованием драйвера psycopg2, будет иметь схожую структуру с кодом для другой СУБД (например, MySQL с драйвером mysql-connector-python), поскольку все драйверы реализуют единый интерфейс. Однако переносимость кода между разными СУБД не гарантируется — могут потребоваться изменения из-за различий в SQL-диалектах, типах данных и поведении драйверов.

Обзор модулей для работы с PostgreSQL

Основной модуль, рекомендуемый активным сообществом — psycopg2. Это официальный Python-драйвер для работы с базой данных PostgreSQL, написанный на языке C. Он позволяет использовать все возможности СУБД В аналитических системах и веб-приложениях. Задачи psycopg2:

обеспечение соблюдения стандарта DB-API 2.0, описанного в спецификации PEP 249 для реализации единого подхода к работе с БД;
поддержка синхронных подключений (с возможностью низкоуровневого неблокирующего ввода-вывода, но без поддержки современного asyncio);
безопасная работа с параметризованными запросами в целях защиты от SQL-инъекций;
корректная обработка таких типов данных, как JSON, массивы, UUID и других;
поддержка транзакций, пулов соединений и расширенных возможностей PostgreSQL.

Благодаря модулю можно выполнять весь цикл работы с базой данных — от подключения к серверу PostgreSQL до обработки ошибок. Адаптер psycopg2 стал стандартом для интеграции СУБД с Python в веб-приложениях, микросервисах и аналитических системах.

В 2021 году вышла новая реализация драйвера — psycopg 3 (часто называемый psycopg3). Это стабильная, готовая к промышленному использованию версия, которая рекомендуется для новых проектов. Она поддерживает asyncio, бинарный протокол, подготовленные запросы (prepared statements) и современные возможности Python (типизация). Драйвер psycopg2 остается отличным выбором для поддержки существующего кода и проектов, где важна максимальная стабильность проверенного временем решения. Выбор между ними зависит от требований проекта:

psycopg 3 — для новых разработок, поддерживает Python 3.10+ и PostgreSQL 10+;
psycopg2 — для поддержки существующего кода и проектов, где требуется совместимость с более широким спектром версий: Python 3.9–3.14 и PostgreSQL 7.4–18.

Установка библиотеки psycopg2

Перед началом работы с PostgreSQL из Python установите драйвер psycopg2. Убедитесь, что рабочее окружение соответствует минимальным требованиям.

Установка и конфигурация

Чтобы без проблем установить драйвер и обеспечить его корректную работу, проверьте окружение. Базовые требования для установки psycopg2 (сборка из исходников):

Python: версии 3.9 – 3.14 (поддержка конкретных версий зависит от версии драйвера; psycopg2 2.9.10 поддерживает Python 3.9–3.14);
PostgreSQL сервер: версии 7.4 – 18;
Клиентская библиотека libpq: версии 9.1 и выше (должна быть установлена в системе);
Для сборки из исходного кода: компилятор C (gcc/clang), заголовочные файлы Python (python3-dev/python3-devel), заголовочные файлы libpq (libpq-dev/libpq-devel) и утилита pg_config (обычно входит в состав -dev пакетов PostgreSQL).

На Linux перед развертыванием psycopg2 нужно установить зависимости PostgreSQL. Команда для Debian/Ubuntu:

Дарим до 20 000 бонусов

4 000 бонусов — физическим лицам, 20 000 бонусов — юридическим

sudo apt install libpq-dev python3-dev

После подготовки окружения установите psycopg2 через pip:

pip install psycopg2

Менеджер пакетов с использованием локальных PostgreSQL компилирует расширение из исходного кода. При таком способе установки драйвер использует системную версию libpq и будет одновременно с операционной системой получать обновления безопасности.

На macOS сборка библиотеки из исходников обычно происходит через Homebrew. Установите PostgreSQL и libpq:

brew install postgresql

Проверьте, что libpq доступна:

brew link --force libpq

Установите psycopg2 стандартной командой:

pip install psycopg2

Если вы не хотите разворачивать PostgreSQL локально или вам нужна готовая инфраструктура для продакшн-проекта, обратите внимание на облачные решения. Например, Evolution Managed PostgreSQL от Cloud.ru предоставляет полностью управляемую базу данных с автоматическим резервным копированием, масштабированием и мониторингом — с ней вы можете сосредоточиться на разработке приложения, не думая об администрировании.

Psycopg2-binary для быстрой установки

Чтобы установить psycopg2 в упрощенном порядке, можно использовать psycopg2-binary. Это предварительно скомпилированная версия библиотеки psycopg2, которая не требует наличия компилятора C и заголовочных файлов PostgreSQL. С ее помощью можно установить драйвер одной командой:

pip install psycopg2-binary

Для продакшена этот вариант не рекомендуется, поскольку встроенная версия libpq не получает автоматических обновлений и может не соответствовать требованиям безопасности. Способ подойдет для создания тестовых окружений, локальной разработки, контейнеров и CI/CD-пайплайнов. Также это оптимальный сценарий для установки psycopg2 на Windows, поскольку сборка из источников для этой операционной системы сложнее.

Важно знать о psycopg2-binary

Пакет psycopg2-binary удобен для локальной разработки, тестирования и CI/CD, но не рекомендуется для production-окружений по следующим причинам:

Безопасность: binary-пакет включает статически скомпилированные версии libpq и OpenSSL. Они не получают автоматических обновлений безопасности через системный менеджер пакетов (apt, yum).
Совместимость: Встроенные библиотеки могут конфликтовать с версиями, установленными в операционной системе, что приводит к непредсказуемому поведению.
Производительность: Сборка из исходников (pip install psycopg2) позволяет оптимизировать код под конкретную систему и версию libpq.

Для разработки — psycopg2-binary, для продакшена — psycopg2 (сборка из исходников). Кроме того, статически скомпилированная в binary-пакет версия библиотеки OpenSSL (libssl) может конфликтовать с другими Python-модулями, использующими системную версию OpenSSL. Это может приводить к трудноотлаживаемым ошибкам, вплоть до падения интерпретатора (segmentation fault). Данная проблема стала одной из ключевых причин разделения пакетов на psycopg2 и psycopg2-binary.

Управляемый PostgreSQL

Масштабируйте БД в 2 клика с SLA 99,7%

Подробнее

Создание базы данных и пользователя PostgreSQL

Перед подключением к PostgreSQL из приложений, подготовите СУБД к работе — создайте базу данных и пользователя с необходимыми полномочиями. Это можно сделать через утилиту psql либо прямо из Python с помощью psycopg2.

Использование командной строки psql

Утилита psql входит в стандартную поставку PostgreSQL и позволяет управлять сервером из командной строки. Способы создания базы данных с ее помощью для разных операционных систем:

ОС	Как запустить psql	Пример команд
Linux	sudo -u postgres psql — через командную строку	sql\nCREATE USER app_user WITH PASSWORD 'strong_password';\nCREATE DATABASE app_db OWNER app_user;\nGRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;\n
macOS	psql postgres — от имени пользователя	sql\nCREATE USER app_user WITH PASSWORD 'strong_password';\nCREATE DATABASE app_db OWNER app_user;\n
Windows	Откройте командную строку (cmd) или PowerShell. Затем выполните команду: psql -U postgres. Система запросит пароль для пользователя postgres. При этом, если команда не найдена, добавьте путь к папке bin вашей установки PostgreSQL (например, C:\Program Files\PostgreSQL\15\bin) в системную переменную PATH или используйте полный путь к исполняемому файлу.	sql\nCREATE USER app_user WITH PASSWORD 'strong_password';\nCREATE DATABASE app_db OWNER app_user;\n

Использование psycopg2 для создания базы данных

Можно создать базы данных и пользователей непосредственно из Python, отправляя из приложения SQL-команды. Пример скрипта:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

conn = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="admin_password",
    host="localhost",
    port=5432
)

conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()

cursor.execute("CREATE USER app_user WITH PASSWORD 'strong_password';")
cursor.execute("CREATE DATABASE app_db OWNER app_user;")

cursor.close()
conn.close()

Этот сценарий можно применять под учетной записью с административными правами.

Подключение к базе данных и работа с курсором

После создания базы данных нужно корректно к ней подключиться и научиться работать с курсором, который отвечает за выполнение SQL-запросов. Описываем основные этапы.

Соединение с PostgreSQL

Для создания новой сессии используется функция psycopg2.connect(). Она создает объект соединения, с помощью которого можно работать с сервером PostgreSQL. Для подключения нужно указать следующие параметры:

dbname — имя базы данных, к которой нужно подключиться;
user — имя пользователя;
password — пароль пользователя;
host — адрес сервера;
port — порт (5432 по умолчанию).

Весь цикл работы с базой данных можно представить как четкую последовательность шагов. От подключения к серверу до закрытия соединения — каждый этап выполняет свою задачу. Взгляните на упрощенную схему:

Алгоритм работы с БД: подключение → запрос → commit (если нужно) → закрытие.

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

import psycopg2

conn = psycopg2.connect(
    dbname="app_db",
    user="app_user",
    password="strong_password",
    host="localhost",
    port=5432
)

Когда соединение установится, создайте объект курсора:

cursor = conn.cursor()

Можно создать несколько курсоров, но достаточно и одного. Он будет выступать интерфейсом для отправки SQL-запросов и получения результатов.

Использование курсора для выполнения запросов

Курсор позволяет выполнять SQL-операции, например, читать, вставлять записи, обновлять и удалять данные. Вот примеры команд с кратким описанием:

Операция	Суть	Запрос	Нюансы
Получение данных	Выполнение запросов на чтение данных из таблиц БД PostgreSQL	python\ncursor.execute(\"SELECT id, name FROM users;\")\nrows = cursor.fetchall()\nfor row in rows:\n print(row)\n	Для чтения данных commit() не нужен
fetchone()	Извлечение одной следующей строки из результата запроса	python\nrow = cursor.fetchone()\n	Используется, если нужна обработка одной записи
fetchmany(n)	Возвращение указанного количества строк из результата запроса	python\nrows = cursor.fetchmany(10)\n	Применяется для порционной обработки данных
fetchall()	Возвращение всех строк из результата запроса	python\nrows = cursor.fetchall()\n	«Тяжелый» запрос при больших выборках данных
INSERT — добавление данных	Внесение новых записей в таблицу	python\ncursor.execute(\n \"INSERT INTO users (name, email) VALUES (%s, %s)\",\n (\"Ivan\", \"ivan@example.com\")\n)\nconn.commit()\n	%s в команде нужен для защиты от SQL-инъекций
UPDATE — изменение данных	Обновление записей в таблице	python\ncursor.execute(\n \"UPDATE users SET email = %s WHERE name = %s\",\n (\"newmail@example.com\", \"Ivan\")\n)\nconn.commit()\n	Изменения применяются только после commit()
DELETE — удаление данных	Удаление записей из таблицы	python\ncursor.execute(\n \"DELETE FROM users WHERE name = %s\",\n (\"Ivan\",)\n)\nconn.commit()\n	Чтобы случайно не удалить данные массово, нужно использовать условия — WHERE
Откат изменений	Возврат базы данных к состоянию до ошибки	python\nconn.rollback()\n	Используется при ошибках или отмене операций

Закрытие курсора и соединения

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

cursor.close()
conn.close()

Для автоматизации процессов можно использовать контекстные менеджеры. Как выглядит команда:

import psycopg2

# Установка соединения
conn = psycopg2.connect(
    dbname="app_db",
    user="app_user",
    password="strong_password",
    host="localhost"
)

# Использование контекстного менеджера для управления транзакцией
# commit() при успехе, rollback() при ошибке
with conn:
    # Контекстный менеджер для курсора автоматически закроет его
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users;")
        rows = cursor.fetchall()
        for row in rows:
            print(row)
# Соединение все еще открыто, его нужно закрыть явно
conn.close()

Управление пулом соединений

В продакшн-приложениях создавать новое соединение под каждый запрос — неэффективно. Это потребляет ресурсы и может исчерпать лимиты подключений к БД. Решение — использовать пул соединений (connection pool).

Psycopg2 поддерживает три типа пулов через модуль psycopg2.pool:

SimpleConnectionPool — простой пул для однопоточных приложений;
ThreadedConnectionPool — потокобезопасный пул для многопоточных приложений;
PersistentConnectionPool — пул, который назначает постоянные соединения конкретным потокам (обычно используется с Zope или аналогичными фреймворками).

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

from psycopg2 import pool

# Создание пула (минимум 1, максимум 20 соединений)
connection_pool = pool.SimpleConnectionPool(
    1, 20,
    dbname="app_db",
    user="app_user",
    password="strong_password",
    host="localhost"
)

# Получение соединения из пула
conn = connection_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users;")
# ... работа с данными ...

# Возврат соединения в пул (не закрытие!)
connection_pool.putconn(conn)

# Закрытие всех соединений в пуле при завершении приложения
# connection_pool.closeall()

Операции с таблицами

Работа с таблицами в PostgreSQL подразумевает управление структурой данных и обработку записей. Операции можно выполнять через SQL-консоль либо из Python с использованием psycopg2.

Создание и изменение таблиц

Создавать таблицы можно с помощью команды CREATE TABLE. В нее нужно заложить типы данных, первичные ключи для идентификации записей и ограничения для операции. Например, можно применить такой скрипт:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Работать со структурой таблицы можно с помощью команды ALTER TABLE. Она позволяет редактировать, добавлять и удалять столбцы без пересоздания таблицы. Пример команды, которая добавит столбец:

ALTER TABLE users
ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

Вот так можно поменять тип данных в созданной таблице:

ALTER TABLE users
ALTER COLUMN name TYPE VARCHAR(100);

Команда для удаления столбца:

ALTER TABLE users
DROP COLUMN is_active;

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

Обработка данных

После создания таблицы приходится активно работать с данными — добавлять, обновлять и удалять записи. В Python это можно делать через через курсор с параметризованными запросами. Примеры команд:

Действие	Команда
INSERT — добавление	cursor.execute( "INSERT INTO users (name, email) VALUES (%s, %s)", ("Ivan", "ivan@example.com") ) conn.commit()
UPDATE — обновление	cursor.execute( "UPDATE users SET email = %s WHERE name = %s", ("newmail@example.com", "Ivan") ) conn.commit()
DELETE — удаление	cursor.execute( "DELETE FROM users WHERE name = %s", ("Ivan",) ) conn.commit()

В каждой команде по умолчанию работает psycopg2. Применить выполненное изменение можно только после вызова commit().

Работа с транзакциями и типами данных

При работе Python-приложений с PostgreSQL используются транзакции — последовательность операций, которые выполняются как единое целое. Либо все они завершаются успешно, либо не применяется ни одна. Задачу по выполнению транзакций облегчают встроенные механизмы управления и автоматическое сопоставление типов данных.

Создание и завершение транзакций

В psycopg2 транзакции идут по умолчанию. Любое соединение начинает транзакцию, даже при выполнении единичного запроса. Принципы работы:

все операции по удалению, добавлению и обновлению данных выполняются в рамках транзакции;
изменения не получится применить к базе данных до вызова conn.commit();
в случае ошибки можно откатиться назад с помощью conn.rollback().

Пример ручного управления транзакцией:

try:
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        ("Ivan", "ivan@example.com")
    )
    conn.commit()
except Exception:
    conn.rollback()
    raise

Можно управлять транзакциями автоматически с помощью контекстных менеджеров. Пример:

with conn:
    with conn.cursor() as cursor:
        cursor.execute("UPDATE users SET name = %s WHERE id = %s", ("Ivan", 1))

Конструкция with conn: управляет транзакцией, а не соединением. При успешном выполнении всех операций внутри блока автоматически вызывается commit(). Если в блоке возникает исключение, автоматически выполняется rollback(). Важно понимать, что само соединение при этом не закрывается — его нужно закрыть отдельно с помощью conn.close(), когда работа полностью завершена. Для автоматического закрытия курсора используется with conn.cursor():.

Режим autocommit

По умолчанию psycopg2 работает в режиме транзакций, что требует явного вызова commit(). Однако в некоторых ситуациях (например, при выполнении административных команд, таких как CREATE DATABASE, или при работе с данными, где транзакции не нужны) удобно включить режим автоматического подтверждения:

conn = psycopg2.connect(dbname="postgres", user="postgres")
conn.set_session(autocommit=True)  # Включаем autocommit
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydb;")  # Команда выполнится сразу
conn.set_session(autocommit=False)  # Возвращаем обычный режим

Альтернативный способ — установка уровня изоляции при подключении:

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
conn = psycopg2.connect(dbname="postgres", user="postgres", 
                        isolation_level=ISOLATION_LEVEL_AUTOCOMMIT)

Соответствие типов данных Python и PostgreSQL

Для совместимости данных Psycopg2 автоматически преобразует стандартные типы PostgreSQL в нативные типы Python и обратно. Примеры соответствия:

PostgreSQL	Python
INTEGER, BIGINT	int
NUMERIC, DECIMAL	decimal.Decimal
REAL, DOUBLE PRECISION	float
TEXT, VARCHAR	str
BOOLEAN	bool
DATE	datetime.date
TIMESTAMP	datetime.datetime
INTERVAL	datetime.timedelta
BYTEA	bytes

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

from datetime import date, datetime

cursor.execute(
    "INSERT INTO events (event_date, created_at) VALUES (%s, %s)",
    (date.today(), datetime.now())
)
conn.commit()

Пример с числовыми типами, которые во избежание ошибок округления лучше обрабатывать через decimal.Decimal:

from decimal import Decimal

cursor.execute(
    "INSERT INTO payments (amount) VALUES (%s)",
    (Decimal("199.99"),)
)
conn.commit()

Нулевые значения NULL в PostgreSQL Автоматически преобразовываются в None в Python. Такое соответствие по умолчанию обрабатывает psycopg2.

Управлять преобразованием сложных типов данных в специфических бизнес сценариях можно с помощью адаптеров и type casters.

Заключение

PostgreSQL и Python — проверенный технологический стек, которые закроет большинство прикладных задач. Безопасно работать с данными, управлять транзакциями и преобразованием сложных типов позволяет драйвер. Сначала освойте типовые операции с его помощью и только потом переходите к расширенным возможностям СУБД.