Введение в PostgreSQL с Python +Psycopg2

Это руководство по PostgreSQL в Python описывает, как использовать модуль Psycopg2 для подключения к PostgreSQL, выполнения SQL-запросов и других операций с базой данных.

Здесь не инструкции по установки локального сервера, так как это не касается python. Скачайте и установите PostgreSQL с официального сайта https://www.postgresql.org/download/. Подойдут версии 10+, 11+, 12+.

Вот список разных модулей Python для работы с сервером базы данных PostgreSQL:

  • Psycopg2,
  • pg8000,
  • py-postgreql,
  • PyGreSQL,
  • ocpgdb,
  • bpsql,
  • SQLAlchemy. Для работы SQLAlchemy нужно, чтобы хотя бы одно из перечисленных выше решений было установлено.

Примечание: все модули придерживаются спецификации Python Database API Specification v2.0 (PEP 249). Этот API разработан с целью обеспечить сходство разных модулей для доступа к базам данных из Python. Другими словами, синтаксис, методы и прочее очень похожи во всех этих модулях.

В этом руководстве будем использовать Psycopg2, потому что это один из самых популярных и стабильных модулей для работы с PostgreSQL:

  • Он используется в большинстве фреймворков Python и Postgres;
  • Он активно поддерживается и работает как с Python 3, так и с Python 2;
  • Он потокобезопасен и спроектирован для работы в многопоточных приложениях. Несколько потоков могут работать с одним подключением.

В этом руководстве пройдемся по следующим пунктам:

  • Установка Psycopg2 и использование его API для доступа к базе данных PostgreSQL;
  • Вставка, получение, обновление и удаление данных в базе данных из приложения Python;
  • Дальше рассмотрим управление транзакциями PostgreSQL, пул соединений и методы обработки исключений, что понадобится для разработки сложных программ на Python с помощью PostgreSQL.

Установка Psycopg2 с помощью pip

Для начала нужно установить текущую версию Psycopg2 для использования PostgreSQL в Python. С помощью команды pip можно установить модуль в любую операцию систему: Windows, macOS, Linux:

pip install psycopg2

Также можно установить конкретную версию программы с помощью такой команды:

pip install psycopg2=2.8.6

Если возникает ошибка установки, например «connection error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)», то ее можно решить, сделав files.pythonhosted.org доверенным хостом:

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2

Модуль psycopg2 поддерживает:

  • Python 2.7 и Python 3, начиная с версии 3.4.
  • Сервер PostgreSQL от 7.4 до 12.
  • Клиентскую библиотеку PostgreSQL от 9.1.

Проверка установки Psycopg2

После запуска команды должны появиться следующие сообщения:

  • Collecting psycopg2
  • Downloading psycopg2-2.8.6
  • Installing collected packages: psycopg2
  • Successfully installed psycopg2-2.8.6

При использовании anaconda подойдет следующая команда.

conda install -c anaconda psycopg2

Подключение к базе данных PostgreSQL из Python

В этом разделе рассмотрим, как подключиться к PostgreSQL из Python с помощью модуля Psycopg2.

Вот какие аргументы потребуются для подключения:

  • Имя пользователя: значение по умолчанию для базы данных PostgreSQL – postgres.
  • Пароль: пользователь получает пароль при установке PostgreSQL.
  • Имя хоста: имя сервера или IP-адрес, на котором работает база данных. Если она запущена локально, то нужно использовать localhost или 127.0.0.0.
  • Имя базы данных: в этом руководстве будем использовать базу postgres_db.

Шаги для подключения:

  • Использовать метод connect() с обязательными параметрами для подключения базы данных.
  • Создать объект cursor с помощью объекта соединения, который возвращает метод connect. Он нужен для выполнения запросов.
  • Закрыть объект cursor и соединение с базой данных после завершения работы.
  • Перехватить исключения, которые могут возникнуть в процессе.

Создание базы данных PostgreSQL с Psycopg2

Для начала создадим базу данных на сервере. Во время установки PostgreSQL вы указывали пароль, его нужно использовать при подключении.

Копировать 


import psycopg2

from psycopg2 import Error

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
try:

    # Подключение к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432")

    connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

    # Курсор для выполнения операций с базой данных

    cursor = connection.cursor()

    sql_create_database = 'create database postgres_db'

    cursor.execute(sql_create_database)
except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

Пример кода для подключения к базе данных PostgreSQL из Python

Для подключения к базе данных PostgreSQL и выполнения SQL-запросов нужно знать название базы данных. Ее нужно создать прежде чем пытаться выполнить подключение.

Копировать 


import psycopg2

from psycopg2 import Error



try:

    # Подключение к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432",

                                  database="postgres_db")



    # Курсор для выполнения операций с базой данных

    cursor = connection.cursor()

    # Распечатать сведения о PostgreSQL

    print("Информация о сервере PostgreSQL")

    print(connection.get_dsn_parameters(), "\n")

    # Выполнение SQL-запроса

    cursor.execute("SELECT version();")

    # Получить результат

    record = cursor.fetchone()

    print("Вы подключены к - ", record, "\n")



except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

После подключения появится следующий вывод:

Информация о сервере PostgreSQL
{'user': 'postgres', 'dbname': 'postgres_db', 'host': '127.0.0.1', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} 

Вы подключены к -  ('PostgreSQL 10.13, compiled by Visual C++ build 1800, 64-bit',) 

Соединение с PostgreSQL закрыто

Разбор процесса подключения в деталях

import psycopg2 — Эта строка импортирует модуль Psycopg2 в программу. С помощью классов и методов модуля можно взаимодействовать с базой.

from psycopg2 import Error — С помощью класса Error можно обрабатывать любые ошибки и исключения базы данных. Это сделает приложение более отказоустойчивым. Этот класс также поможет понять ошибку в подробностях. Он возвращает сообщение об ошибке и ее код.

psycopg2.connect() — С помощью метода connect() создается подключение к экземпляру базы данных PostgreSQL. Он возвращает объект подключения. Этот объект является потокобезопасным и может быть использован на разных потоках.

Метод connect() принимает разные аргументы, рассмотренные выше. В этом примере в метод были переданы следующие аргументы: user = "postgres", password = "1111", host = "127.0.0.1", port = "5432", database = "postgres_db".

cursor = connection.cursor() — С базой данных можно взаимодействовать с помощью класса cursor. Его можно получить из метода cursor(), который есть у объекта соединения. Он поможет выполнять SQL-команды из Python.

Из одного объекта соединения можно создавать неограниченное количество объектов cursor. Они не изолированы, поэтому любые изменения, сделанные в базе данных с помощью одного объекта, будут видны остальным. Объекты cursor не являются потокобезопасными.

После этого выведем свойства соединения с помощью connection.get_dsn_parameters().

cursor.execute() — С помощью метода execute объекта cursor можно выполнить любую операцию или запрос к базе данных. В качестве параметра этот метод принимает SQL-запрос. Результаты запроса можно получить с помощью fetchone(), fetchmany(), fetchall().

В этом примере выполняем SELECT version(); для получения сведений о версии PosgreSQL.

Блок try-except-finally — Разместим код в блоке try-except для перехвата исключений и ошибок базы данных.


cursor.close() и connection.close() — Правильно всегда закрывать объекты cursor и connection после завершения работы, чтобы избежать проблем с базой данных.

Создание таблицы PostgreSQL из Python

В этом разделе разберем, как создавать таблицу в PostgreSQL из Python. В качестве примера создадим таблицу Mobile.

Выполним следующие шаги:

  • Подготовим запрос для базы данных
  • Подключимся к PosgreSQL с помощью psycopg2.connect().
  • Выполним запрос с помощью cursor.execute().
  • Закроем соединение с базой данных и объект cursor.

Теперь рассмотрим пример.

Копировать 


import psycopg2

from psycopg2 import Error



try:

    # Подключиться к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432",

                                  database="postgres_db")



    # Создайте курсор для выполнения операций с базой данных

    cursor = connection.cursor()

    # SQL-запрос для создания новой таблицы

    create_table_query = '''CREATE TABLE mobile

                          (ID INT PRIMARY KEY     NOT NULL,

                          MODEL           TEXT    NOT NULL,

                          PRICE         REAL); '''

    # Выполнение команды: это создает новую таблицу

    cursor.execute(create_table_query)

    connection.commit()

    print("Таблица успешно создана в PostgreSQL")



except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

Вывод:

Таблица успешно создана в PostgreSQL
Соединение с PostgreSQL закрыто

Примечание: наконец, коммитим изменения с помощью метода commit().

Соответствие типов данных Python и PostgreSQL

Есть стандартный маппер для конвертации типов Python в их эквиваленты в PosgreSQL и наоборот. Каждый раз при выполнении запроса PostgreSQL из Python с помощью psycopg2 результат возвращается в виде объектов Python.

PythonPostgreSQL
NoneNULL
boolbool
floatreal
double
int
long		smallint
integer
bigint
Decimalnumeric
str
unicode		varchar
text
datedate
timetime
timetz
datetimetimestamp
timestamptz
timedeltainterval
listARRAY
tuple
namedtuple		Composite types
IN syntax
dicthstore

Константы и числовые преобразования

При попытке вставить значения None и boolean (True, False) из Python в PostgreSQL, они конвертируются в соответствующие литералы SQL. То же происходит и с числовыми типами. Они конвертируются в соответствующие типы PostgreSQL.

Например, при выполнении запроса на вставку числовые объекты, такие как int, long, float и Decimal, конвертируются в числовые представления из PostgreSQL. При чтении из таблицы целые числа конвертируются в int, числа с плавающей точкой — во float, а десятичные — в Decimal.

Выполнение CRUD-операций из Python

Таблица mobile уже есть. Теперь рассмотрим, как выполнять запросы для вставки, обновления, удаления или получения данных из таблицы в Python.

Копировать 


import psycopg2

from psycopg2 import Error



try:

    # Подключиться к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432",

                                  database="postgres_db")



    cursor = connection.cursor()

    # Выполнение SQL-запроса для вставки данных в таблицу

    insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (1, 'Iphone12', 1100)"""

    cursor.execute(insert_query)

    connection.commit()

    print("1 запись успешно вставлена")

    # Получить результат

    cursor.execute("SELECT * from mobile")

    record = cursor.fetchall()

    print("Результат", record)



    # Выполнение SQL-запроса для обновления таблицы

    update_query = """Update mobile set price = 1500 where id = 1"""

    cursor.execute(update_query)

    connection.commit()

    count = cursor.rowcount

    print(count, "Запись успешно удалена")

    # Получить результат

    cursor.execute("SELECT * from mobile")

    print("Результат", cursor.fetchall())



    # Выполнение SQL-запроса для удаления таблицы

    delete_query = """Delete from mobile where id = 1"""

    cursor.execute(delete_query)

    connection.commit()

    count = cursor.rowcount

    print(count, "Запись успешно удалена")

    # Получить результат

    cursor.execute("SELECT * from mobile")

    print("Результат", cursor.fetchall())



except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

Вывод:

1 запись успешно вставлена
Результат [(1, 'Iphone12', 1100.0)]
1 Запись успешно удалена
Результат [(1, 'Iphone12', 1500.0)]
1 Запись успешно удалена
Результат []
Соединение с PostgreSQL закрыто

Примечание: не забывайте сохранять изменения в базу данных с помощью connection.commit() после успешного выполнения операции базы данных.

Работа с датой и временем из PostgreSQL

В этом разделе рассмотрим, как работать с типами date и timestamp из PostgreSQL в Python и наоборот.

Обычно при выполнении вставки объекта datetime модуль psycopg2 конвертирует его в формат timestamp PostgreSQL.

По аналогии при чтении значений timestamp из таблицы PostgreSQL модуль psycopg2 конвертирует их в объекты datetime Python.

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

Копировать 


import psycopg2

from psycopg2 import Error



try:

    # Подключиться к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432",

                                  database="postgres_db")



    # Создайте курсор для выполнения операций с базой данных

    cursor = connection.cursor()

    # SQL-запрос для создания новой таблицы

    create_table_query = '''CREATE TABLE item (

	    item_id serial NOT NULL PRIMARY KEY,

	    item_name VARCHAR (100) NOT NULL,

    	    purchase_time timestamp NOT NULL,

	    price INTEGER NOT NULL

    );'''

    # Выполнение команды: это создает новую таблицу

    cursor.execute(create_table_query)

    connection.commit()

    print("Таблица успешно создана в PostgreSQL")



except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

Рассмотрим сценарий на простом примере. Здесь мы читаем колонку purchase_time из таблицы и конвертируем значение в объект datetime Python.


Копировать 


import psycopg2

import datetime

from psycopg2 import Error



try:

    # Подключиться к существующей базе данных

    connection = psycopg2.connect(user="postgres",

                                  # пароль, который указали при установке PostgreSQL

                                  password="1111",

                                  host="127.0.0.1",

                                  port="5432",

                                  database="postgres_db")



    cursor = connection.cursor()

    # Выполнение SQL-запроса для вставки даты и времени в таблицу

    insert_query = """ INSERT INTO item (item_Id, item_name, purchase_time, price)

                              VALUES (%s, %s, %s, %s)"""

    item_purchase_time = datetime.datetime.now()

    item_tuple = (12, "Keyboard", item_purchase_time, 150)

    cursor.execute(insert_query, item_tuple)

    connection.commit()

    print("1 элемент успешно добавлен")



    # Считать значение времени покупки PostgreSQL в Python datetime

    cursor.execute("SELECT purchase_time from item where item_id = 12")

    purchase_datetime = cursor.fetchone()

    print("Дата покупки товара", purchase_datetime[0].date())

    print("Время покупки товара", purchase_datetime[0].time())



except (Exception, Error) as error:

    print("Ошибка при работе с PostgreSQL", error)

finally:

    if connection:

        cursor.close()

        connection.close()

        print("Соединение с PostgreSQL закрыто")

Вывод:

1 элемент успешно добавлен
Дата покупки товара 2021-01-16
Время покупки товара 20:16:23.166867
Соединение с PostgreSQL закрыто

Максим
Я создал этот блог в 2018 году, чтобы распространять полезные учебные материалы, документации и уроки на русском. На сайте опубликовано множество статей по основам python и библиотекам, уроков для начинающих и примеров написания программ.
Мои контакты: Почта
admin@pythonru.comAlex Zabrodin2018-10-26OnlinePython, Programming, HTML, CSS, JavaScript

Статьи по теме