Одной из наиболее популярных реляционных систем баз данных является PostgreSQL. Рассмотрим, как работать с базами данных PostgreSQL в приложении на языке Python.
Перед началом работы естественно должна быть установлена сама PostgreSQL. Про установку PostgreSQL можно прочитать в соответствующей статье Установка сервера PostgreSQL.
Стандартные библиотеки Python не предоставляют встроенного функционала для работы с PostgreSQL, однако есть большое количество сторонних библиотек. Наиболее популярной из них является Psycopg 2 (официальный сайт Psycopg). Данная библиотека реализована на языке C, благодаря чему обладает сравнительно большой производительностью.
Для установки выполним в терминале следующую команду:
pip install psycopg2
После этого мы можем импортировать библиотеку в программе на Python:
import psycopg2
Для подключения к серверу PostgreSQL применяется функция connect(). Она принимает настройки подключения:
psycopg2.connect(dbname="db_name", host="db_host", user="db_user", password="db_pass", port="db_port")
Функция принимает следующие параметры:
dbname: имя базы данных
user: имя пользователя
password: пароль пользователя
host: хост/адрес сервера
port: порт (если не указано, то используется порт по умолчанию - 5432)
При удачном подключении функция connect создает новую сессию базы данных и возвращает объект connection
Класс connection предоставляет ряд методов для работы с подключением к БД:
close(): закрывает подключение
cursor(): возвращает объект cursor для осуществления запросов к бд
commit(): поддверждает транзакцию
rollback(): откатывает транзакцию
Например, покдлючимся к стандартной базе данных "postgres" на локальном сервере PostgreSQL:
import psycopg2
conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1", port="5432")
print("Подключение установлено")
conn.close()
В данном случае подключение идет для встроенного пользователя по умолчанию "postgres".
Метод cursor() объекта connection возвращает курсор - объект cursor, через который можно отправлять запросы к базе данных. Для этого класс cursor предоставляет ряд методов:
execute(query, vars=None): выполняет одну SQL-инструкцию. Через второй параметр в код SQL можно передать набор параметров в виде списка или словаря
executemany(query, vars_list): выполняет параметризованное SQL-инструкцию. Через второй параметр принимает наборы значений, которые передаются в выполняемый код SQL.
callproc(procname[, parameters]): выполняет хранимую функцию. Через второй параметр можно передать набор параметров в виде списка или словаря
mogrify(operation[, parameters]): возвращает код запроса SQL после привязки параметров
fetchone(): возвращает следующую строку из полученного из БД набора строк в виде кортежа. Если строк в наборе нет, то возвращает None
fetchmany([size=cursor.arraysize]): возвращает набор строк в виде списка. количество возвращаемых строк передается через параметр.
Если больше строк нет в наборе, то возвращается пустой список.
fetchall(): возвращает все (оставшиеся) строки в виде списка. При отсутствии строк возвращается пустой список.
scroll(value[, mode='relative']): перемещает курсор в наборе на позицию value в соответствии с режимом mode.
Определение курсора:
import psycopg2 conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() cursor.close() # закрываем курсор conn.close() # закрываем подключение
Стоит отметить, что оба объекта - connection и cursor могут использоваться как менеджеры контекста. То есть с помощью выражения with определить контекста. Однако если объект cursor по завершению закрывается, то объект connection НЕ закрывается:
import psycopg2
conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1", port="5432")
with conn:
with conn.cursor() as cursor:
print("Подключение установлено")
print(cursor.closed) # True - курсор закрыт
# cursor.close() # нет смысла - объект cursor уже закрыт
conn.close() # объект conn не закрыт, надо закрывать
Перед выполнением первой команды SQL автоматически создается транзакция, в процессе которой можно выполнять различные выражения SQL с помощью методов execute/executemany курсора, но для
подтверждения их выполнения необходимо вызывать метод commit() объекта connection. Условно это может выглядеть так:
import psycopg2 conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() cursor.execute(sql1) conn.commit() # реальное выполнение команд sql1 cursor.close() conn.close()
Здесь реальное выполнение условной команды sql1 производится только при выполнении метода conn.commit().
Если же надо, чтобы выражения sql автоматически выполнялись при каждом вызове метода cursor.execute(), то можно установить автокоммит с помощью свойства connection.autocommit:
import psycopg2 conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1") conn.autocommit = True # устанавливаем актокоммит cursor = conn.cursor() cursor.execute(sql1) # непосредственное выполнение команды sql1 cursor.close() conn.close()