Python postgresql обработка ошибок

This is not an answer to the question but merely my thoughts that don’t fit in a comment. This is a scenario in which I think setting pgcode in the exception object would be helpful but, unfortunately, it is not the case.

I’m implementing a pg_isready-like Python script to test if a Postgres service is running on the given address and port (e.g., localhost and 49136). The address and port may or may not be used by any other program.

pg_isready internally calls internal_ping(). Pay attention to the comment of Here begins the interesting part of "ping": determine the cause...:

     /*
      * Here begins the interesting part of "ping": determine the cause of the
      * failure in sufficient detail to decide what to return.  We do not want
      * to report that the server is not up just because we didn't have a valid
      * password, for example.  In fact, any sort of authentication request
      * implies the server is up.  (We need this check since the libpq side of
      * things might have pulled the plug on the connection before getting an
      * error as such from the postmaster.)
      */
     if (conn->auth_req_received)
         return PQPING_OK;

So pg_isready also uses the fact that an error of invalid authentication on connection means the connection itself is already successful, so the service is up, too. Therefore, I can implement it as follows:

        ready = True

        try:
            psycopg2.connect(
                host=address,
                port=port,
                password=password,
                connect_timeout=timeout,
            )
        except psycopg2.OperationalError as ex:
            ready = ("fe_sendauth: no password supplied" in except_msg)

However, when the exception psycopg2.OperationalError is caught, ex.pgcode is None. Therefore, I can’t use the error codes to compare and see if the exception is about authentication/authorization. I’ll have to check if the exception has a certain message (as @dhke pointed out in the comment), which I think is kind of fragile because the error message may be changed in the future release but the error codes are much less likely to be changed, I think.

I discovered this behavior while throwing together a quick and dirty python program and database schema that generates test cases for pgaudit to catch. In short, psycopg2 does not throw an exception if an INSERT or UPDATE «works» but the plpgsql trigger fired by the statement fails with an error.

I’ve tried all manner of searching here and in Google. Everything I find talks about handling exceptions in python or in the statements executed by psycopg2, but not errors in the triggers fired by statements executed by python.

Versions of everything involved are:

• OS = CentOS 7
• python = 3.6
• postgresql = 9.6
• psycopg2 = 2.8.6

Example:

                                         Table "audit_test.visits"
  Column  |           Type           | Collation | Nullable |                    Default
----------+--------------------------+-----------+----------+-----------------------------------------------
 id       | integer                  |           | not null | nextval('audit_test.visits_id_seq'::regclass)
 tstamp   | timestamp with time zone |           |          |
 patient  | integer                  |           |          |
 facility | integer                  |           |          |
 treated  | boolean                  |           |          |
Triggers:
    pat_health BEFORE INSERT OR UPDATE ON audit_test.visits FOR EACH ROW EXECUTE PROCEDURE audit_test.update_health()

                                 Table "audit_test.patients"
  Column   |  Type   | Collation | Nullable |                     Default
-----------+---------+-----------+----------+-------------------------------------------------
 id        | integer |           | not null | nextval('audit_test.patients_id_seq'::regclass)
 name      | text    |           |          |
 ishealthy | boolean |           |          |

create function update_health() returns trigger as $body$
begin
  if new.treated is false then
    update patients set ishealthy = false where patients.id = new.patient;
  elsif new.treated is true then
    update patients set ishealthy = true where patients.id = new.patient;
  end if;
  return null;
end;
$body$ language plpgsql;

Running the following statement throws an error.

mydb=# insert into audit_test.visits (tstamp, patient, treated, facility) values (CURRENT_TIMESTAMP, 18, FALSE, 5);  ERROR:  relation "patients" does not exist
LINE 1: update patients set ishealthy = false where patients.id = ne...
               ^
QUERY:  update patients set ishealthy = false where patients.id = new.patient
CONTEXT:  PL/pgSQL function audit_test.update_health() line 4 at SQL statement
mydb=# select * from audit_test.visits;
 id | tstamp | patient | facility | treated
----+--------+---------+----------+---------
(0 rows)

mydb=# q

I’m aware I could fix my problem by using the schema name in the trigger’s logic, but that’s not what I’m worried about. If I run this same statement from a python program with psycopg2, no exception is thrown to make the program aware the trigger failed.

#!/usr/bin/python3
import psycopg2

conn = psycopg2.connect(
dbname='mydb',
user='fakeadminlol',
host='myserver',
port=5432,
options='-c search_path=audit_test',)
cursor = conn.cursor()

sql = '''insert into visits (tstamp, patient, treated, facility) values (CURRENT_TIMESTAMP, {}, FALSE, {});'''.format(1,1)
try:
    cursor.execute(sql)
except (Exception, psycopg2.DatabaseError) as ex:
    print(str(ex))
conn.commit()
conn.close()

The above code doesn’t give ANY indication of an error in pdb, and the program acts like nothing went wrong. Despite no python errors, the visits table is still empty just like the above example with psql.

<<< pdb output omitted for brevity >>>
> /root/testme.py(14)<module>()
-> sql = '''insert into visits (tstamp, patient, treated, facility) values (CURRENT_TIMESTAMP, {}, FALSE, {});'''.format(1,1)
(Pdb)
> /root/testme.py(15)<module>()
-> try:
(Pdb)
> /root/testme.py(16)<module>()
-> cursor.execute(sql)
(Pdb)
> /root/testme.py(21)<module>()
-> conn.commit()
(Pdb)
> /root/testme.py(22)<module>()
-> conn.close()
(Pdb)
--Return--
> /root/testme.py(22)<module>()->None
-> conn.close()
(Pdb)
--Return--
> <string>(1)<module>()->None
(Pdb)

I’ve tried looking at documentation for postgresql, python, and psycopg2. Nothing has led me to a solution. How can I detect and handle an error in a postgresql trigger from python?

Это руководство по 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 с помощью модуля 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.

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

При попытке вставить значения 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 закрыто