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.

Using Python 2.7 and

In [150]: psycopg2.version
Out[150]: ‘2.4.2 (dt dec pq3 ext)’

I have a simple python scripts that processing transactions and writes data to a database. Occasionally there is an insert that violates my primary key. This is fine, i just want it to ignore that record and continue on it merry way. The problem I am having is that psycopg2 primary key error is aborting the entire transaction block and all inserts after the error fail. Here is an example error

ERROR: duplicate key value violates unique constraint "encounter_id_pkey"
DETAIL:  Key (encounter_id)=(9012235) already exists.

This is on the next insert. not a violation.

Inserting: 0163168~9024065
ERROR: current transaction is aborted, commands ignored until end of transaction block

The Second error repeats itself for every insert. Here is a simplified loop. I am looping through a pandas data frame, but it could be any loop.

conn = psycopg2.connect("dbname='XXXX' user='XXXXX' host='XXXX' password='XXXXX'")

cur = conn.cursor()

for i, val in df2.iteritems():
    try:
        cur = conn.cursor()
        cur.execute("""insert into encounter_id_table (
        encounter_id,current_date  )
        values       
        (%(create_date)s, %(encounter_id)s ) ;""",
        'encounter_id':i.split('~')[1],  
        'create_date': datetime.date.today() })           
        cur.commit()
        cur.close() 
    except Exception , e:
        print 'ERROR:', e[0]
        cur.close()
 conn.close()   

Again the basic idea is to gracefully handle the Error. In the dictum of Admiral Nelson of the Royal Navy: «Damn the maneuvers go straight at them». Or in our case damn the Errors go straight at them.» I thought by opening a cursor on every insert that I would be resetting the transaction block. I do not want to have to reset the connection just because of a primary key error. Is there something i am just missing?

Thanks before hand for your time.

John

Это руководство по 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 закрыто