Записки программиста
Недавно мне понадобилось сходить в PostgreSQL из скрипта на Python. Была предпринята попытка воспользоваться для этого библиотекой py-postgresql, поскольку я успешно использовал ее в прошлом. Но оказалось, что py-postgresql не работает с последними версиями постгреса. В моем случае использовался PostgreSQL 11. Ну что же, тогда не будем выпендриваться, и возьмем используемый всеми psycopg2. Поскольку интерфейс psycopg2 заметно отличается от интерфейса py-postgresql, было решено написать небольшую памятку по использованию данной библиотеки.
Как обычно в мире Python, библиотека устанавливается через pip и при желании изолируется от других проектов с помощью virtualenv:
В MacOS вполне достаточно этой одной команды. В Linux может потребоваться вручную подтянуть библиотеку libpq, поскольку psycopg2 построен на ее базе:
Соединение с СУБД осуществляется так:
import 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:
cur. execute ( «CREATE TABLE users (id SERIAL PRIMARY KEY, » +
«login VARCHAR(64), password VARCHAR(64))» )
conn. commit ( )
Заметьте, что коммит транзакции осуществляется через класс connection, а не cursor.
Пример заполнения таблицы:
cur. execute ( «INSERT INTO users (login, password) VALUES (%s, %s)» ,
( «afiskon» , «123» ) )
cur. execute ( «INSERT INTO users (login, password) VALUES (%s, %s)» ,
( «eax» , «456» ) )
conn. commit ( )
cur. execute ( «PREPARE insuser AS » +
«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 ( )
cur. execute ( «SELECT id, login, password FROM users» )
cur. fetchall ( )
# [(1, ‘afiskon’, ‘123’), (2, ‘eax’, ‘456’)]
Можно читать и по одному картежу:
cur. execute ( «SELECT id, login, password FROM users» )
cur. fetchone ( )
# (1, ‘afiskon’, ‘123’)
cur. fetchone ( )
# (2, ‘eax’, ‘456’)
cur. fetchone ( ) is None
# True
Также класс cursor может быть использован в цилке for:
cur. execute ( «SELECT id, login, password FROM users» )
for row in cur:
print ( row )
# (1, ‘afiskon’, ‘123’)
# (2, ‘eax’, ‘456’)
Параметрам запроса можно присваивать имена. Например, как в этом UPDATE-запросе:
cur. execute ( «UPDATE users SET password = %(password)s WHERE » +
«login = %(login)s» , { «login» : «eax» , «password» : «789» } )
conn. commit ( )
На плейсхолдерах значений всегда должно стоять %s , даже если передается целое число или иной тип. Например, как в следующем DELETE-запросе:
Заметьте, что методу execute передается картеж из одного элемента. Если передать просто один аргумент без картежа, метод взорвется с ошибкой:
cur. execute ( «SELECT version()» )
cur. fetchall ( )
# [(‘PostgreSQL 11.5 (Ubuntu . трали-вали три педали’,)]
По завершении работы с курсором его следует закрыть:
Чтобы постоянно не думать про все эти conn.commit() и cur.close() , лучше выполнять транзакции в with-блоках:
with conn:
with conn. cursor ( ) as cur:
cur. execute ( «INSERT INTO users (login, password) » +
«VALUES (%s, %s)» , ( «r2auk» , «789» ) )
Заметьте, что conn также должен быть взят в with. Если во время исполнения кода не будет брошено исключение, транзакция закоммитится. Иначе она откатится. Так или иначе, все ресурсы, выделенные под cursor, будут освобождены. Соединение с СУБД остается открытым.
Чтобы явно откатить транзакцию, используйте метод rollback класса connection:
Чтобы закрыть соединение с СУБД, используйте метод close:
Конечно же, описать абсолютно все нюансы работы c psycopg2 в рамках одного поста не представляется возможным. Дополнительные сведения вы найдете в официальной документации.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.
Коротко о себе
Меня зовут Александр, позывной любительского радио R2AUK. Здесь я пишу об интересующих меня вещах и временами — просто о жизни.
Вы можете следить за обновлениями блога с помощью RSS, ВКонтакте, Telegram или Twitter. Также я являюсь одним из ведущих подкаста DevZen и иногда выкладываю видео на YouTube.
Мой e-mail — af is kon @gmail.com. Если вы хотите мне написать, прошу предварительно ознакомиться с этим FAQ.
Работа с PostgreSQL в Python
17 Ноя. 2018 , Python, 249646 просмотров, How to Work with PostgreSQL in Python
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:
pip install psycopg2-binary Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.
Начало работы
Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:
import psycopg2 conn = psycopg2.connect(dbname='database', user='db_user', password='mypassword', host='localhost') cursor = conn.cursor() Через курсор происходит дальнейшее общение в базой.
cursor.execute('SELECT * FROM airport LIMIT 10') records = cursor.fetchall() . cursor.close() conn.close() После выполнения запроса, получить результат можно несколькими способами:
- cursor.fetchone() — возвращает 1 строку
- cursor.fetchall() — возвращает список всех строк
- cursor.fetchmany(size=5) — возвращает заданное количество строк
Также курсор является итерируемым объектом, поэтому можно так:
for row in cursor: print(row) Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:
from contextlib import closing with closing(psycopg2.connect(. )) as conn: with conn.cursor() as cursor: cursor.execute('SELECT * FROM airport LIMIT 5') for row in cursor: print(row) По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor ). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory :
from psycopg2.extras import DictCursor with psycopg2.connect(. ) as conn: with conn.cursor(cursor_factory=DictCursor) as cursor: . Формирование запросов
Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:
cursor.execute('SELECT * FROM airport WHERE city_code = %s', ('ALA', )) for row in cursor: print(row) Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:
- Плейсхолдеры в строке запроса должны быть %s , даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.
- Не нужно обрамлять строки в одинарные кавычки.
- Если в запросе присутствует знак %, то его необходимо писать как %%.
Именованные аргументы можно писать так:
>>> cursor.execute('SELECT * FROM engine_airport WHERE city_code = %(city_code)s', ) . Модуль psycopg2.sql
Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.
>>> cursor.execute('SELECT * FROM %s WHERE city_code = %s', ('airport', 'ALA')) psycopg2.ProgrammingError: ОШИБКА: ошибка синтаксиса (примерное положение: "'airport'") LINE 1: SELECT * FROM 'airport' WHERE city_code = 'ALA' Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2.extensions.quote_ident , но и про неё легко забыть.
from psycopg2 import sql . >>> with conn.cursor() as cursor: columns = ('country_name_ru', 'airport_name_ru', 'city_code') stmt = sql.SQL('SELECT <> FROM <> LIMIT 5').format( sql.SQL(',').join(map(sql.Identifier, columns)), sql.Identifier('airport') ) cursor.execute(stmt) for row in cursor: print(row) ('Французская Полинезия', 'Матайва', 'MVT') ('Индонезия', 'Матак', 'MWK') ('Сенегал', 'Матам', 'MAX') ('Новая Зеландия', 'Матамата', 'MTA') ('Мексика', 'Матаморос', 'MAM') Транзакции
По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:
- закрыв соединение conn.close()
- удалив соединение del conn
- вызвав conn.commit() или conn.rollback()
Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True , каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).
with conn.cursor() as cursor: conn.autocommit = True values = [ ('ALA', 'Almaty', 'Kazakhstan'), ('TSE', 'Astana', 'Kazakhstan'), ('PDX', 'Portland', 'USA'), ] insert = sql.SQL('INSERT INTO city (code, name, country_name) VALUES <>').format( sql.SQL(',').join(map(sql.Literal, values)) ) cursor.execute(insert) 💌 Присоединяйтесь к рассылке
Понравился контент? Пожалуйста, подпишись на рассылку.
Интересные записи:
- Почему Python?
- Работа с MySQL в Python
- Что нового появилось в Django Channels?
- FastAPI, asyncio и multiprocessing
- Celery: начинаем правильно
- Обзор Python 3.9
- Django Channels: работа с WebSocket и не только
- Введение в logging на Python
- Pyenv: удобный менеджер версий python
- Руководство по работе с HTTP в Python. Библиотека requests
- Авторизация через Telegram в Django и Python
- Итоги первой встречи Python программистов в Алматы
- Python-RQ: очередь задач на базе Redis
- Как написать Telegram бота: практическое руководство
- Разворачиваем Django приложение в production на примере Telegram бота
- Введение в pandas: анализ данных на Python
- Участие в подкасте TalkPython
- Обзор Python 3.8
- Строим Data Pipeline на Python и Luigi
- Видео презентации ETL на Python