Стандартный workflow — Python: SQL

• Установка зависимостей
• Подключение к БД
• Полный пример
  • Cursor
  • Connection

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

1. Установка зависимостей
2. Подключение к базе данных
3. Подготовка запроса
4. Выполнение запроса
5. Формирование результата

В этом курсе мы будем работать с базой данных Postgres.

Установка зависимостей

Для начала работы достаточно установить пакет при помощи uv:

uv add psycopg2-binary

Подключение к БД

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

• Username — имя пользователя, которое вы используете для работы с PostgreSQL.
• Password — пароль, который используется пользователем.
• Host Name — имя сервера или IP-адрес, на котором работает PostgreSQL.
• Database Name — имя базы данных, к которой мы подключаемся.

Для подключения к базе данных мы используем метод connect(), которому в качестве аргументов передаются вышеперечисленные данные:

import psycopg2

try:
    # пытаемся подключиться к базе данных
    conn = psycopg2.connect(
        dbname="test", user="postgres", password="secret", host="host"
    )
except:
    # в случае сбоя подключения будет выведено сообщение в STDOUT
    print("Can`t establish connection to database")

Также подключение к базе данных может осуществляться с помощью Connection URI:

import psycopg2

try:
    # пытаемся подключиться к базе данных
    conn = psycopg2.connect("postgresql://user:password@host:port/database_name")
except:
    # в случае сбоя подключения будет выведено сообщение  в STDOUT
    print("Can`t establish connection to database")

Полный пример

Ниже показан основной принцип работы с Psycopg2 на примере работы с таблицей пользователя. В коде создается таблица, заполняется и затем ее данные выводятся в консоль.

import psycopg2

# Создаем соединение с базой
# hexlet_test - Имя базы данных
try:
    conn = psycopg2.connect("postgresql://user:password@host:port/hexlet_test")
except:
    print("Can`t establish connection to database")

sql = "CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(255), phone VARCHAR(255));"
# Запрос выполняется через создание объекта курсора
cursor = conn.cursor()
cursor.execute(sql) # Коммитим, т.е. сохраняем изменения в БД
cursor.close()  # в конце закрывается

sql2 = "INSERT INTO users (username, phone) VALUES ('tommy', '123456789');"
cursor = conn.cursor()
cursor.execute(sql2)
conn.commit()
cursor.close()

sql3 = "SELECT * FROM users;"
cursor = conn.cursor()
# Указатель на набор данных в памяти СУБД
cursor.execute(sql3)
for row in cursor:
    print(row)
cursor.close()
conn.close()  # Соединение нужно закрыть

Cursor

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

cursor = conn.cursor()
cursor.execute(sql)
cursor.close()

Дело в том, что выполнение запроса в базу данных, гораздо более сложная операция чем может показаться на первый взгляд. Например, если мы делаем запрос на выборку данных, то СУБД не пересылает ее автоматически из базы в наше приложение. Это нужно будет сделать самостоятельно из кода. Почему? Выборка данных может быть огромной, что приведет к резкому скачку использования оперативной памяти. Поэтому база данных хранит данные у себя и передает их по запросу.

С другой стороны, это порождает проблему того, что дополнительная память начинает активно использоваться внутри самой базы данных. Сколько времени хранить данные, которые запросил клиент? Именно поэтому нам приходится закрывать курсор. Когда мы это делаем, то посылается сигнал базе данных, что данные больше не нужны и ресурсы можно освободить.

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

Что будет если не закрыть курсор? Несмотря на то, что курсоры легковесные объекты и множество открытых курсоров не представляет большой проблемы, все же они держат ресурсы системы. Поэтому если забывать их закрывать, то в конце концов это приведет к сбоям в работе приложения.

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

Connection

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

Самостоятельная работа

По инструкции поднимите локально базу Postgres и подключитесь к ней. Выполните все шаги из этого урока локально у себя.

Дополнительные материалы

1. Основы
2. Инструкция по установке Postgres