Как работать с PostgreSQL в Python

Введение

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

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

Немного о Python Database API Specification v2.0

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

Модули для работы с БД

Существует несколько модулей для работы с СУБД PostgreSQL в Python, которые соответствуют PEP 249. 

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

pg8000 — модуль, реализованный полностью на Python, а потому уступает psycopg2 в производительности. Есть и функциональные ограничения: не поддерживаются некоторые расширенные функции PostgreSQL, что может стать препятствием для использования в сложных приложениях.

PyGreSQL — один из первых драйверов для PostgreSQL, который тем не менее продолжает развиваться. Модуль написан на C и Python, что обеспечивает ему достаточную производительность, хотя и меньшую чем дает psycopg2. Начинающим пользователям синтаксис PyGreSQL может показаться сложнее по сравнению с psycopg2.

py-postgreql — реализован в основном на Python, но включает расширения на C, ответственные за производительность. Модуль менее популярен, чем psycopg2, и поддерживает только Python 3.

SQLAlchemy — объектно-реляционное отображение (ORM, object–relational mapping), позволяющее работать с базой данных на уровне объектов. SQLAlchemy упрощает разработку и сопровождение кода, поддерживает сложные запросы, кэширование и управление сессиями. Для работы SQLAlchemy требуется один из вышеупомянутых драйверов для подключения к базе данных.

Модуль psycopg2 активно развивается, работает с высокой скоростью, обладает внушительным сообществом пользователей, что позволяет даже новичкам рассчитывать на быструю помощь при затруднении. Все эти преимущества перед аналогичными решениями и сподвигли нас к выбору psycopg2 в качестве драйвера к PostgreSQL.

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

Требования psycopg2 к версиям Python и PostgreSQL

Перед началом работы убедитесь, что у вас установлены следующие компоненты.

1. Python 2.7 или Python 3.4 и выше.

Чтобы проверить наличие Python 3, введите в терминале:

    python3 --version

Если был получен вывод вида Python 3.10.6, значит Python 3 установлен и можно продолжать.

В случае получения ошибки, проверьте наличие версии Python 2.7:

    python2.7 --version

Если в системе нет ни одной из версий Python, установите актуальную:

    sudo apt-get update
sudo apt-get install python3

2. Сервер PostgreSQL 7.4 и выше (поддерживается и последняя на сегодняшний день версия 16.4).

Проверить установленную версию PostgreSQL можно с помощью следующей команды:

    pg_config --version

При наличии какой-либо версии данной СУБД вывод будет похож на:

    postgres (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)

Если PostgreSQL в системе отсутствует, его надо установить:

    sudo apt update
sudo apt install postgresql postgresql-contrib

psycopg2 для системного Python

Системный Python — это версия, которая установлена в ОС по умолчанию. Она используется для выполнения системных задач, а также автоматизации процессов, необходимых для работы системы. Именно эта версия Python будет использоваться командой pip по умолчанию.

Библиотеки в Python устанавливаются с помощью пакетного менеджера pip, который идет отдельно от самого Python. Заметьте, если используется третья версия Python, то мы при установке pip мы указываем ее явно:

    sudo apt-get install python3-pip

Установка PostgreSQL требует заголовочных файлов, которые содержат объявления функций и типов данных, необходимых для компиляции, поэтому библиотеку psycopg2 получаем вместе с libpq-dev:

    sudo apt-get install libpq-dev psycopg2

Если в процессе установки возникает ошибка вида connection error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598), необходимо сделать домены PyPI доверенными:

    python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2

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

Если требуется установить определенную версию psycopg2, используйте команду:

    pip install psycopg2==2.9.3

psycopg2-binary для установки без сборки

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

Для установки psycopg2-binary необходимо использовать команду:

    pip install psycopg2-binary

pyenv для отдельного окружения

У Python есть собственная система управления версиями — pyenv, которая позволяет легко переключаться между ними и создавать изолированные виртуальные окружения. Такая гибкость очень востребована в разработке, где иногда требуются разные версии Python для разных проектов.

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

    sudo apt-get update
sudo apt-get install -y make build-essential libssl-dev zlib1g-dev libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev

После установки необходимых зависимостей можно приступать к установке pyenv. Для этого необходимо выполнить следующие шаги.

1. Перейти в терминал и запустить установку:

    curl https://pyenv.run | bash

2. Добавить pyenv в PATH:

    export PATH="$HOME/.pyenv/bin:$PATH"
eval "$(pyenv init -)"
eval "$(pyenv virtualenv-init -)"

3. Установить необходимую версию Python и создать виртуальное окружение:

    pyenv install 3.10.6
pyenv virtualenv 3.10.6 myenv
pyenv activate myenv

Проверка установки psycopg2

Чтобы проверить наличие psycopg2, можно использовать команду pip list, которая выводит список всех установленных пакетов в окружении Python:

    pip list | grep psycopg2

Если psycopg2 установлен, вы увидите его в списке с указанием версии:

    psycopg2     2.9.3

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

Перед тем как подключиться к базе данных PostgreSQL, ее необходимо создать. Это можно сделать с помощью утилиты psql или графического клиента, например, pgAdmin. Однако базу данных также можно создать напрямую из Python с использованием psycopg2.

Создание базы данных с использованием psql

1. Подключение к серверу PostgreSQL под пользователем postgres:

    psql -h localhost -p 5432 -U postgres

При подключении к БД запрашивается пароль, указанный для пользователя postgres при установке PostgreSQL. Если при установке пароль не задавался, следует ввести пароль по умолчанию: postgres. Допускается использование и других пользователей, postgres указан в качестве примера.

2. Создание новой базы данных с именем selecteldb:

    CREATE DATABASE selecteldb;

3. Выход из psql:

    \q

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

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

В PostgreSQL по умолчанию создается БД postgres, ее мы и будем использовать для подключения. Также можно подключиться к собственной БД при ее наличии, изменив значение параметра dbname.

    import psycopg2

# Подключение к базе данных postgres
conn = psycopg2.connect(
dbname="postgres",
user="postgres",
password="password",
host="localhost",
port="5432"
)

cur = conn.cursor()

# Создание новой базы данных
cur.execute("CREATE DATABASE selecteldb;)
conn.commit()

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

В данном примере для общения с PostgreSQL мы использовали курсор (cursor). Что это такое — рассказываем далее.

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

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

Соединение с БД и получение курсора

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

psycopg2.connect() может принимать значения в формате:

    conn = psycopg2.connect(
dbname="database_name",
user="user",
password="password",
host="localhost",
port="port"
)

Или в виде URI:

    conn = psycopg2.connect('postgresql://user:password@host:port/database_name')

Один из самых популярных методов курсора — execute(). Этот метод используется для выполнения SQL-запросов, таких как SELECT, INSERT, UPDATE, и других команд.

Следующий скрипт подключается к базе данных selecteldb, создает курсор, выполняет запрос SELECT version() для получения версии PostgreSQL и выводит результат. 

    import psycopg2

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

cur = conn.cursor()

# Выполнение SQL-запроса
cur.execute("SELECT version();")

# Получение результата запроса
version = cur.fetchone()
print(version)
cur.close()
conn.close()

Полученный вывод:

    ('PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)',)

Возвращение строк курсором

Курсор предоставляет несколько методов для получения данных из результатов запроса:

• fetchone(): возвращает одну строку результата;
• fetchall(): возвращает все строки результата;
• fetchmany(size): возвращает указанное количество строк результата.

Следующий скрипт подключается к базе данных selecteldb, создает курсор и выполняет несколько запросов: генерирует таблицу servers, записывает в нее три строки, после чего извлекает данные.

Подробнее о создании таблицы и ее наполнении рассказываем ниже.

    import psycopg2

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

cur = conn.cursor()

# Создание таблицы servers с тремя столбцами: id, server и costcur.execute('''
    CREATE TABLE IF NOT EXISTS servers (
        id SERIAL PRIMARY KEY,
        server VARCHAR(100),
        cost INTEGER
    );
''')
# Подтверждение транзакцииconn.commit()
# Вставка данных
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server1", 100))
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server2", 200))
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server3", 300))
# Подтверждение транзакцииconn.commit()
# Извлечение данных из таблицы servers
cur.execute("SELECT id, server, cost FROM servers;")
rows = cur.fetchall()
print(rows)

cur.close()
conn.close()

Закрытие курсора

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

Ручное закрытие курсора

Закрытие курсора и соединения осуществляется вызовом методов close() для каждого из них:

    import psycopg2

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

cur = conn.cursor()

cur.execute("SELECT version();")

version = cur.fetchone()
print(version)

cur.close()
conn.close()

Закрытие курсора с использованием контекстного менеджера

Для автоматического закрытия курсора и соединения удобно использовать конструкцию with, которая обеспечивает освобождение ресурсов по завершении блока:

    import psycopg2

# Здесь и далее для удобства будем пользоваться контекстным менеджером Python# Соединение и выполнение запроса:
with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    cur.execute("SELECT id, server, cost FROM servers;")
    rows = cur.fetchall()
    print(rows)

Работа с таблицами

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

Для создания таблицы используется метод execute курсора и SQL-команды CREATE TABLE. 

Хорошей практикой считается удостовериться перед вызовом CREATE TABLE, что мы не пытаемся сконструировать таблицу повторно.

    import psycopg2

with psycopg2.connect(
	dbname="selecteldb",
	user="postgres",
	password="password",
	host="localhost",
	port="5432"
) as conn:
	with conn.cursor() as cur:
    	# Создание таблицы servers с тремя столбцами: id, server и cost
    	cur.execute('''
        	CREATE TABLE IF NOT EXISTS servers (
            	id SERIAL PRIMARY KEY,
            	server VARCHAR(100),
            	cost INTEGER
        	);
    	''')
    	conn.commit()

Запись данных в таблицу

Вставка данных в таблицу осуществляется с помощью SQL-команды INSERT INTO.

В SQL-запросах, выполненных через psycopg2, можно использовать %s как поле подстановки для параметров. Это означает, что вместо непосредственного встраивания значений в SQL-запрос, вы указываете %s в тех местах, где должны быть вставлены параметры.

В следующем примере:

• %s — поле подстановки для значений, которые будут переданы в запросе;
• ("server1", 100) — кортеж содержит значения, которые заменят %s в порядке их появления.

Обратите внимание, что в одном из вышеописанных запросах мы добавляли в таблицу servers некоторые значения. Если в вашей таблице они уже присутствуют, повторное выполнение запроса не требуется.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Вставка данных
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server1", 100))
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server2", 200))
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server3", 300))
    conn.commit()

Обновление таблиц

Чтобы обновить данные в таблице, применяется SQL-команда UPDATE, которая позволяет изменить существующие записи.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Обновление данных
    cur.execute("UPDATE servers SET cost = %s WHERE server = %s;", (150, "server1"))
    conn.commit()

Удаление строк и таблиц

Для удаления строк таблицы используется SQL-команда DELETE, а для самой таблицы — DROP TABLE.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Удаление строки
    cur.execute("DELETE FROM servers WHERE server = %s;", ("server1",))
 
    # Удаление таблицы
    cur.execute("DROP TABLE servers;")
    conn.commit()

Формирование SQL-запросов курсором

В ситуациях, когда запросы простые, можно использовать статические строки запросов. Однако для динамических запросов рекомендуется использовать psycopg2.sql. Он полезен для предотвращения SQL-инъекций и упрощения работы со сложными SQL-запросами.

    import psycopg2from psycopg2 import sql

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Формирование SQL-запроса
    query = sql.SQL("SELECT {fields} FROM {table} WHERE {pkey} = %s;").format(
        fields=sql.SQL(', ').join([
            sql.Identifier('id'),
            sql.Identifier('server'),
            sql.Identifier('cost')
        ]),
        table=sql.Identifier('servers'),
        pkey=sql.Identifier('id')
    )
    cur.execute(query, (1,))
    row = cur.fetchone()
    print(row)

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

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

Метод commit() завершает текущую транзакцию и фиксирует все изменения в базе данных. При закрытии соединения без вызова commit() все изменения будут отменены.

Чтобы начать транзакцию, следует выполнить SQL-запросы после создания соединения и курсора. Операции INSERT, UPDATE, DELETE и другие будут частью транзакции до тех пор, пока не будет выполнена команда commit() или rollback().

В следующих примерах мы дополнительно используем класс Error из библиотеки psycopg2, который позволяет обрабатывать возникающие ошибки и исключения, а также получать подробную информацию о них.

    import psycopg2
from psycopg2 import Error

try:
    with psycopg2.connect(
        dbname="selecteldb",
        user="postgres",
        password="password",
        host="localhost",
        port="5432"
    ) as conn:
        with conn.cursor() as cur:
            try:
                # Выполнение SQL-запросов
                cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server4", 400))
                cur.execute("UPDATE servers SET cost = %s WHERE server = %s;", (450, "server2"))
               
                # Подтверждение транзакции
                conn.commit()
            except Error as e:
                # Откат изменений в случае ошибки
                conn.rollback()
                # Выводим сообщение об ошибке и ее код
                print(f"Transaction failed: {e.pgcode} - {e.pgerror}")
except Exception as e:
    # Обработка ошибок подключения к базе данных
    print(f"The error has occurred. Details: {e}")

В этом примере транзакция начинается автоматически при выполнении первого SQL-запроса. Если все операции успешны, транзакция подтверждается вызовом commit(). Если возникает ошибка, транзакция откатывается с помощью rollback().

Использование контекстного менеджера with для подключения и вызова курсора, а также блок try-except автоматически управляет началом и завершением транзакции. Конструкция try-except используется в Python для обработки исключительных ситуаций (сокращенно «исключений») — неожиданных ошибок и сбоев, которые могут возникнуть во время выполнения программы.

Конструкции try-except и with можно комбинировать различными способами. В следующем примере блок with помещен внутрь try:

    import psycopg2
from psycopg2 import Error

try:
    with psycopg2.connect(
        dbname="selecteldb",
        user="postgres",
        password="password",
        host="localhost",
        port="5432"
    ) as conn:
        with conn.cursor() as cur:
            try:
                # Выполнение SQL-запросов
                cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server5", 500))
                cur.execute("DELETE FROM servers WHERE server = %s;", ("server3",))
               
                # Подтверждение транзакции
                conn.commit()
            except Error as e:
                # Откат изменений в случае ошибки
                conn.rollback()
                # Выводим сообщение об ошибке и ее код
                print(f"Transaction failed: {e.pgcode} - {e.pgerror}")
except Exception as e:
    # Обработка ошибок взаимодействия с базой данных
    print(f"Failed to perform operation: {e}")

Использование конструкции try-except при выполнении транзакций считается хорошей практикой, хоть и не является обязательной. Она не предотвращает блокировки напрямую, но помогает избежать их, позволяя откатывать транзакции и правильно обрабатывать ошибки. Это снижает риск ситуаций, когда база данных остается в заблокированном состоянии из-за неудачного SQL-запроса или незавершенной транзакции.

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

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

Константы и числовые преобразования

При работе с psycopg2 значения None и булевы значения (True, False) из Python автоматически конвертируются в соответствующие SQL-литералы при вставке в PostgreSQL.

При выполнении SQL-запросов с числовыми объектами, например int, long, float и Decimal, они преобразовываются в представления PostgreSQL.

    import psycopg2

with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы с числовыми типами данных
        cur.execute('''
            CREATE TABLE IF NOT EXISTS numbers (
                id SERIAL PRIMARY KEY,
                int_value INTEGER,
                float_value FLOAT,
                numeric_value NUMERIC;
            )
        ''')
        conn.commit()
       
        # Вставка данных
        cur.execute("INSERT INTO numbers (int_value, float_value, numeric_value) VALUES (%s, %s, %s)", (42, 3.14, 123.45))
        conn.commit()

        # Получение данных
        cur.execute("SELECT int_value, float_value, numeric_value FROM numbers;")
        row = cur.fetchone()
        print(row)

Полученный вывод:

    (42, 3.14, Decimal('123.45'))

Работа с date и timestamp

Для работы с типами данных date и timestamp в Python можно использовать строки в формате даты и времени. Рассмотрим пример работы с этими типами данных:

    import psycopg2

with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы, если она не существует
        cur.execute('''
            CREATE TABLE IF NOT EXISTS date_example (
                id SERIAL PRIMARY KEY,
                a_date DATE,
                a_timestamp TIMESTAMP
            );
        ''')
        conn.commit()

        # Вставка данных с типами date и timestamp
        cur.execute('''
            INSERT INTO date_example (a_date, a_timestamp)
            VALUES (%s, %s);
        ''', ('2024-07-15', '2024-07-15 12:34:56'))
        conn.commit()

        # Извлечение данных и вывод в консоль
        cur.execute("SELECT * FROM date_example;")
        row = cur.fetchone()
        print(row)

Полученный вывод:

    (1, datetime.date(2024, 7, 15), datetime.datetime(2024, 7, 15, 12, 34, 56))

Для удобства во встроенной библиотеке Python есть модуль datetime, который позволяет упростить работу с датой и временем. Объекты datetime удобно использовать для работы с динамическими данными, поскольку они позволяют выполнять с датой и временем такие операции, как подсчет и сравнение.

Следующий код вычислит количество дней между двумя датами:

    import psycopg2
from datetime import date, datetime

# Установим дату для работы
date1 = date(2024, 7, 15)
date2 = date.today()  # Текущая дата

# Соединение с базой данных
with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы, если она не существует
        cur.execute('''
            CREATE TABLE IF NOT EXISTS date_example2 (
                id SERIAL PRIMARY KEY,
                a_date DATE
            );
        ''')
        conn.commit()

        # Вставка первой даты
        cur.execute('''
            INSERT INTO date_example2 (a_date)
            VALUES (%s);
        ''', (date1,))
        conn.commit()

        # Извлечение даты из базы данных
        cur.execute("SELECT a_date FROM date_example2 WHERE id = 1;")
        stored_date = cur.fetchone()[0]

        # Вычисление разности между датами
        date_difference = date2 - stored_date
        print(f"Количество дней между {stored_date} и {date2}: {date_difference.days} дней")

Полученный вывод:

    Количество дней между 2024-07-15 и 2024-08-29: 45 дней

Заключение

В статье рассмотрели работу с PostgreSQL с помощью модуля psycopg2 в Python. Благодаря psycopg2 можно легко интегрировать PostgreSQL в свои приложения, упрощая процесс разработки и управления данными.