PostgreSQL

Модуль psycopg. Подключение к серверу PostgreSQL

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

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

Стандартные библиотеки Python не предоставляют встроенного функционала для работы с PostgreSQL, однако есть большое количество сторонних библиотек. Наиболее популярной из них является Psycopg 2 (официальный сайт Psycopg). Данная библиотека реализована на языке C, благодаря чему обладает сравнительно большой производительностью.

Установка psycopg

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

pip install psycopg2

После этого мы можем импортировать библиотеку в программе на Python:

import psycopg2

Подключение к серверу PostgreSQL

Для подключения к серверу 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()