Работа с PostgreSQL на Python с помощью psycopg2
5 ноября 2019
Недавно мне понадобилось сходить в PostgreSQL из скрипта на Python. Была предпринята попытка воспользоваться для этого библиотекой py-postgresql, поскольку я успешно использовал ее в прошлом. Но оказалось, что py-postgresql не работает с последними версиями постгреса. В моем случае использовался PostgreSQL 11. Ну что же, тогда не будем выпендриваться, и возьмем используемый всеми psycopg2. Поскольку интерфейс psycopg2 заметно отличается от интерфейса py-postgresql, было решено написать небольшую памятку по использованию данной библиотеки.
Как обычно в мире Python, библиотека устанавливается через pip и при желании изолируется от других проектов с помощью virtualenv:
В MacOS вполне достаточно этой одной команды. В Linux может потребоваться вручную подтянуть библиотеку libpq, поскольку psycopg2 построен на ее базе:
Соединение с СУБД осуществляется так:
conn = psycopg2.connect(database="mydb", user="afiskon",
password="s3cr3t", host="localhost", port=5432)
Иногда бывает полезно узнать используемую версию libpq. Она доступна через переменную psycopg2.__libpq_version__. Переменная содержит число вроде 11002 (libpq 11.2) или 90613 (libpq 9.6.13).
Взаимодействие с базой осуществляется при помощи отдельного класса, cursor:
Пример создания таблицы:
"login VARCHAR(64), password VARCHAR(64))")
conn.commit()
Заметьте, что коммит транзакции осуществляется через класс connection, а не cursor.
Пример заполнения таблицы:
("afiskon", "123"))
cur.execute("INSERT INTO users (login, password) VALUES (%s, %s)",
("eax", "456"))
conn.commit()
То же самое, но через prepared statements:
"INSERT INTO users (login, password) VALUES ($1, $2)")
cur.execute("EXECUTE insuser(%s, %s)", ("afiskon", "123"))
cur.execute("EXECUTE insuser(%s, %s)", ("eax", "456"))
conn.commit()
Пример SELECT-запроса:
cur.fetchall()
# [(1, 'afiskon', '123'), (2, 'eax', '456')]
Можно читать и по одному картежу:
cur.fetchone()
# (1, 'afiskon', '123')
cur.fetchone()
# (2, 'eax', '456')
cur.fetchone() is None
# True
Также класс cursor может быть использован в цилке for:
for row in cur:
print(row)
# (1, 'afiskon', '123')
# (2, 'eax', '456')
Параметрам запроса можно присваивать имена. Например, как в этом UPDATE-запросе:
"login = %(login)s", {"login":"eax", "password":"789"})
conn.commit()
На плейсхолдерах значений всегда должно стоять %s, даже если передается целое число или иной тип. Например, как в следующем DELETE-запросе:
conn.commit()
Заметьте, что методу execute передается картеж из одного элемента. Если передать просто один аргумент без картежа, метод взорвется с ошибкой:
Пример вызова хранимки:
cur.fetchall()
# [('PostgreSQL 11.5 (Ubuntu ...трали-вали три педали',)]
По завершении работы с курсором его следует закрыть:
Чтобы постоянно не думать про все эти conn.commit() и cur.close(), лучше выполнять транзакции в with-блоках:
with conn.cursor() as cur:
cur.execute("INSERT INTO users (login, password) " +
"VALUES (%s, %s)", ("r2auk", "789"))
Заметьте, что conn также должен быть взят в with. Если во время исполнения кода не будет брошено исключение, транзакция закоммитится. Иначе она откатится. Так или иначе, все ресурсы, выделенные под cursor, будут освобождены. Соединение с СУБД остается открытым.
Чтобы явно откатить транзакцию, используйте метод rollback класса connection:
with conn.cursor() as cur:
cur.execute("DELETE FROM users")
conn.rollback()
Чтобы закрыть соединение с СУБД, используйте метод close:
try:
# работаем с базой
finally:
conn.close()
Конечно же, описать абсолютно все нюансы работы c psycopg2 в рамках одного поста не представляется возможным. Дополнительные сведения вы найдете в официальной документации.
Метки: PostgreSQL, Python, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.