Pip install postgresql windows

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

Psycopg is the most popular PostgreSQL database adapter for the Python
programming language. Its main features are the complete implementation of
the Python DB API 2.0 specification and the thread safety (several threads can
share the same connection). It was designed for heavily multi-threaded
applications that create and destroy lots of cursors and make a large number
of concurrent “INSERT”s or “UPDATE”s.

Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being
both efficient and secure. It features client-side and server-side cursors,
asynchronous communication and notifications, “COPY TO/COPY FROM” support.
Many Python types are supported out-of-the-box and adapted to matching
PostgreSQL data types; adaptation can be extended and customized thanks to a
flexible objects adaptation system.

Psycopg 2 is both Unicode and Python 3 friendly.

$ pip install psycopg2

$ python setup.py build
$ sudo python setup.py install

$ pip install psycopg2-binary

Last Updated :
02 Dec, 2022

Psycopg is the most popular PostgreSQL adapter used in  Python.  Its works on the principle of the whole implementation of Python DB API 2.0 along with the thread safety (the same connection is shared by multiple threads). It is designed to perform heavily multi-threaded applications that usually create and destroy lots of cursors and make a large number of simultaneous INSERTS or UPDATES. Psycopg features client-side and server-side cursors, asynchronous communication, and notification. Psycopg 2 is both Unicode and Python 3 friendly.

Installation:

The current psycopg2 implementation supports:

• Python versions from 3.6 to 3.10
• PostgreSQL server versions from 7.4 to 14
• PostgreSQL client library version from 9.1
• pgAdmin 4

For most of the available Operating Systems, the quickest way to install this package is through the wheel package available in the PyPI library. We must make sure that we use the latest version of pip, which can be updated using the following command in the terminal.

$ pip install -U pip
$ pip install psycopg2-binary

This will install the pre-compiled binary version of the module which doesn’t require the built or runtime prerequisites. Then we can import the psycopg2 package in the usual manner:

Basic module usage:

The basic use of Psycopg is in implementing the DB API 2.0 protocol to all the database adapters. Here is the basic interactive session of the basic commands.

Example 1: Program to establish a connection between python program and a PostgreSQL database.

Картинка с сайта: www.geeksforgeeks.org

Example 2: Creating a table using python

Картинка с сайта: www.geeksforgeeks.org

Картинка с сайта: www.geeksforgeeks.org

Example 3: Inserting data into the table:

Картинка с сайта: www.geeksforgeeks.org

Картинка с сайта: www.geeksforgeeks.org

Passing parameters to SQL queries

Python variables are converted to SQL values with Psycopg, Python determines the function used to convert the object into a string representation suitable for PostgreSQL.Passing parameters to an SQL statement happens in functions such as cursor.execute() by using %s as the placeholder into the SQL statement.

Example 4: Fetching the data from the database and displaying it into the terminal.

Картинка с сайта: www.geeksforgeeks.org

Example 5: Updating the data in the database.

Картинка с сайта: www.geeksforgeeks.org

Картинка с сайта: www.geeksforgeeks.org

Example 6: Deleting data from the database.

Картинка с сайта: www.geeksforgeeks.org

Картинка с сайта: www.geeksforgeeks.org

The main entry points of Psycopg are:

• The connect() function creates a new database session and returns a new instance of connection.
• The class connection encloses a database session. It allows to :
  • create new cursor instance
  • terminate transaction using commit() or rollback() methods.
• The cursor allows interaction with the database:
  • send commands to the database using execute() and executemany() methods.
  • retrieve data from the database using methods such as fetchone(), fetchmany(), fetchall(), or by iteration.

Введение

PostgreSQL — одна из самых мощных реляционных баз данных с открытым исходным кодом. Она обладает высокой надежностью и производительностью, что делает ее самой популярной среди разработчиков. Если отталкиваться от активности на StackOverflow, то в 2023 году на PostgreSQL приходилась половина всех используемых СУБД. 

Python — высокоуровневый язык программирования, широко используется для разработки, в частности — веб-приложений. Одна из ведущих библиотек в Python для взаимодействия с СУБД PostgreSQL — psycopg2.

На официальном сайте psycopg представлена обновленная третья версия. Однако psycopg2 — все еще самая популярная среди разработчиков, поэтому в статье рассмотрим именно вторую версию.

Немного о Python Database API Specification v2.0

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

Модули для работы с БД

Существует несколько модулей для работы с СУБД PostgreSQL в Python, которые соответствуют PEP 249. 

psycopg2 — один из самых популярных и широко используемых драйверов Python для PostgreSQL. Реализация на языке C обеспечивает высокую производительность, а поддержка параметризованных запросов помогает предотвратить SQL-инъекции. 

pg8000 — модуль, реализованный полностью на Python, а потому уступает psycopg2 в производительности. Есть и функциональные ограничения: не поддерживаются некоторые расширенные функции PostgreSQL, что может стать препятствием для использования в сложных приложениях.

PyGreSQL — один из первых драйверов для PostgreSQL, который тем не менее продолжает развиваться. Модуль написан на C и Python, что обеспечивает ему достаточную производительность, хотя и меньшую чем дает psycopg2. Начинающим пользователям синтаксис PyGreSQL может показаться сложнее по сравнению с psycopg2.

py-postgreql — реализован в основном на Python, но включает расширения на C, ответственные за производительность. Модуль менее популярен, чем psycopg2, и поддерживает только Python 3.

SQLAlchemy — объектно-реляционное отображение (ORM, object–relational mapping), позволяющее работать с базой данных на уровне объектов. SQLAlchemy упрощает разработку и сопровождение кода, поддерживает сложные запросы, кэширование и управление сессиями. Для работы SQLAlchemy требуется один из вышеупомянутых драйверов для подключения к базе данных.

Модуль psycopg2 активно развивается, работает с высокой скоростью, обладает внушительным сообществом пользователей, что позволяет даже новичкам рассчитывать на быструю помощь при затруднении. Все эти преимущества перед аналогичными решениями и сподвигли нас к выбору psycopg2 в качестве драйвера к PostgreSQL.

Установка библиотеки psycopg2

Требования psycopg2 к версиям Python и PostgreSQL

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

1. Python 2.7 или Python 3.4 и выше.

Чтобы проверить наличие Python 3, введите в терминале:

    python3 --version

Если был получен вывод вида Python 3.10.6, значит Python 3 установлен и можно продолжать.

В случае получения ошибки, проверьте наличие версии Python 2.7:

    python2.7 --version

Для наших примеров мы взяли наиболее популярную ОС Ubuntu, поэтому работаем с пакетным менеджером apt. В других дистрибутивах, не основанных на Debian, могут использоваться альтернативные пакетные менеджеры и команда для установки пакетов будет другой.

Если в системе нет ни одной из версий Python, установите актуальную:

    sudo apt-get update
sudo apt-get install python3

2. Сервер PostgreSQL 7.4 и выше (поддерживается и последняя на сегодняшний день версия 16.4).

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

    pg_config --version

При наличии какой-либо версии данной СУБД вывод будет похож на:

    postgres (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)

Если PostgreSQL в системе отсутствует, его надо установить:

    sudo apt update
sudo apt install postgresql postgresql-contrib

psycopg2 для системного Python

Системный Python — это версия, которая установлена в ОС по умолчанию. Она используется для выполнения системных задач, а также автоматизации процессов, необходимых для работы системы. Именно эта версия Python будет использоваться командой pip по умолчанию.

Библиотеки в Python устанавливаются с помощью пакетного менеджера pip, который идет отдельно от самого Python. Заметьте, если используется третья версия Python, то мы при установке pip мы указываем ее явно:

    sudo apt-get install python3-pip

Установка PostgreSQL требует заголовочных файлов, которые содержат объявления функций и типов данных, необходимых для компиляции, поэтому библиотеку psycopg2 получаем вместе с libpq-dev:

    sudo apt-get install libpq-dev psycopg2

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

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

Установка конкретной версии psycopg2

Если требуется установить определенную версию psycopg2, используйте команду:

    pip install psycopg2==2.9.3

psycopg2-binary для установки без сборки

psycopg2-binary — это предварительно скомпилированная версия библиотеки psycopg2. Она включает все необходимые бинарные файлы, поэтому не нужно собирать библиотеку вручную. Это избавляет от необходимости устанавливать и настраивать дополнительные зависимости, например, компиляторы или заголовочные файлы для PostgreSQL.

Предварительно скомпилированная версия — это версия программного обеспечения, которая уже была преобразована в машинный код.

Для установки psycopg2-binary необходимо использовать команду:

    pip install psycopg2-binary

pyenv для отдельного окружения

У Python есть собственная система управления версиями — pyenv, которая позволяет легко переключаться между ними и создавать изолированные виртуальные окружения. Такая гибкость очень востребована в разработке, где иногда требуются разные версии Python для разных проектов.

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

    sudo apt-get update
sudo apt-get install -y make build-essential libssl-dev zlib1g-dev libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev

После установки необходимых зависимостей можно приступать к установке pyenv. Для этого необходимо выполнить следующие шаги.

1. Перейти в терминал и запустить установку:

    curl https://pyenv.run | bash

2. Добавить pyenv в PATH:

    export PATH="$HOME/.pyenv/bin:$PATH"
eval "$(pyenv init -)"
eval "$(pyenv virtualenv-init -)"

3. Установить необходимую версию Python и создать виртуальное окружение:

    pyenv install 3.10.6
pyenv virtualenv 3.10.6 myenv
pyenv activate myenv

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

Чтобы проверить наличие psycopg2, можно использовать команду pip list, которая выводит список всех установленных пакетов в окружении Python:

    pip list | grep psycopg2

Если psycopg2 установлен, вы увидите его в списке с указанием версии:

    psycopg2     2.9.3

Создание базы данных и пользователя PostgreSQL

Перед тем как подключиться к базе данных PostgreSQL, ее необходимо создать. Это можно сделать с помощью утилиты psql или графического клиента, например, pgAdmin. Однако базу данных также можно создать напрямую из Python с использованием psycopg2.

Создание базы данных с использованием psql

1. Подключение к серверу PostgreSQL под пользователем postgres:

    psql -h localhost -p 5432 -U postgres

При подключении к БД запрашивается пароль, указанный для пользователя postgres при установке PostgreSQL. Если при установке пароль не задавался, следует ввести пароль по умолчанию: postgres. Допускается использование и других пользователей, postgres указан в качестве примера.

2. Создание новой базы данных с именем selecteldb:

    CREATE DATABASE selecteldb;

3. Выход из psql:

    \q

Создание базы данных с использованием psycopg2

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

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

    import psycopg2

# Подключение к базе данных postgres
conn = psycopg2.connect(
dbname="postgres",
user="postgres",
password="password",
host="localhost",
port="5432"
)

cur = conn.cursor()

# Создание новой базы данных
cur.execute("CREATE DATABASE selecteldb;)
conn.commit()

# Закрытие курсора и соединения
cur.close()
conn.close()

В данном примере для общения с PostgreSQL мы использовали курсор (cursor). Что это такое — рассказываем далее.

Подключение к базе данных PostgreSQL и получение курсора

Курсор — это объект, который используется для взаимодействия с базой данных. Он позволяет выполнять SQL-запросы, получать результаты и управлять транзакциями. Курсор можно сравнить с указателем на текущую позицию в наборе данных. Он позволяет перемещаться по данным, извлекая строки по мере необходимости.

Соединение с БД и получение курсора

Для получения курсора потребуется создать соединение с базой данных с помощью функции psycopg2.connect(), а затем вызвать метод cursor() объекта соединения.

psycopg2.connect() может принимать значения в формате:

    conn = psycopg2.connect(
dbname="database_name",
user="user",
password="password",
host="localhost",
port="port"
)

Или в виде URI:

    conn = psycopg2.connect('postgresql://user:password@host:port/database_name')

Один из самых популярных методов курсора — execute(). Этот метод используется для выполнения SQL-запросов, таких как SELECT, INSERT, UPDATE, и других команд.

Следующий скрипт подключается к базе данных selecteldb, создает курсор, выполняет запрос SELECT version() для получения версии PostgreSQL и выводит результат. 

    import psycopg2

conn = psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
)

cur = conn.cursor()

# Выполнение SQL-запроса
cur.execute("SELECT version();")

# Получение результата запроса
version = cur.fetchone()
print(version)
cur.close()
conn.close()

Полученный вывод:

    ('PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)',)

Возвращение строк курсором

Курсор предоставляет несколько методов для получения данных из результатов запроса:

• fetchone(): возвращает одну строку результата;
• fetchall(): возвращает все строки результата;
• fetchmany(size): возвращает указанное количество строк результата.

Следующий скрипт подключается к базе данных selecteldb, создает курсор и выполняет несколько запросов: генерирует таблицу servers, записывает в нее три строки, после чего извлекает данные.

Подробнее о создании таблицы и ее наполнении рассказываем ниже.

    import psycopg2

conn = psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
)

cur = conn.cursor()

# Создание таблицы servers с тремя столбцами: id, server и costcur.execute('''
    CREATE TABLE IF NOT EXISTS servers (
        id SERIAL PRIMARY KEY,
        server VARCHAR(100),
        cost INTEGER
    );
''')
# Подтверждение транзакцииconn.commit()
# Вставка данных
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server1", 100))
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server2", 200))
cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server3", 300))
# Подтверждение транзакцииconn.commit()
# Извлечение данных из таблицы servers
cur.execute("SELECT id, server, cost FROM servers;")
rows = cur.fetchall()
print(rows)

cur.close()
conn.close()

Закрытие курсора

Важный шаг при работе с БД — закрытие курсора, чтобы высвободить ресурсы и предотвратить утечки памяти. В psycopg2 курсор можно закрыть вручную или автоматически с помощью контекстного менеджера.

Ручное закрытие курсора

Закрытие курсора и соединения осуществляется вызовом методов close() для каждого из них:

    import psycopg2

conn = psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
)

cur = conn.cursor()

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

version = cur.fetchone()
print(version)

cur.close()
conn.close()

Закрытие курсора с использованием контекстного менеджера

Для автоматического закрытия курсора и соединения удобно использовать конструкцию with, которая обеспечивает освобождение ресурсов по завершении блока:

    import psycopg2

# Здесь и далее для удобства будем пользоваться контекстным менеджером Python# Соединение и выполнение запроса:
with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    cur.execute("SELECT id, server, cost FROM servers;")
    rows = cur.fetchall()
    print(rows)

Работа с таблицами

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

Для создания таблицы используется метод execute курсора и SQL-команды CREATE TABLE. 

Хорошей практикой считается удостовериться перед вызовом CREATE TABLE, что мы не пытаемся сконструировать таблицу повторно.

    import psycopg2

with psycopg2.connect(
	dbname="selecteldb",
	user="postgres",
	password="password",
	host="localhost",
	port="5432"
) as conn:
	with conn.cursor() as cur:
    	# Создание таблицы servers с тремя столбцами: id, server и cost
    	cur.execute('''
        	CREATE TABLE IF NOT EXISTS servers (
            	id SERIAL PRIMARY KEY,
            	server VARCHAR(100),
            	cost INTEGER
        	);
    	''')
    	conn.commit()

Запись данных в таблицу

Вставка данных в таблицу осуществляется с помощью SQL-команды INSERT INTO.

В SQL-запросах, выполненных через psycopg2, можно использовать %s как поле подстановки для параметров. Это означает, что вместо непосредственного встраивания значений в SQL-запрос, вы указываете %s в тех местах, где должны быть вставлены параметры.

В следующем примере:

• %s — поле подстановки для значений, которые будут переданы в запросе;
• ("server1", 100) — кортеж содержит значения, которые заменят %s в порядке их появления.

Обратите внимание, что в одном из вышеописанных запросах мы добавляли в таблицу servers некоторые значения. Если в вашей таблице они уже присутствуют, повторное выполнение запроса не требуется.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Вставка данных
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server1", 100))
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server2", 200))
    cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server3", 300))
    conn.commit()

Обновление таблиц

Чтобы обновить данные в таблице, применяется SQL-команда UPDATE, которая позволяет изменить существующие записи.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Обновление данных
    cur.execute("UPDATE servers SET cost = %s WHERE server = %s;", (150, "server1"))
    conn.commit()

Удаление строк и таблиц

Для удаления строк таблицы используется SQL-команда DELETE, а для самой таблицы — DROP TABLE.

    import psycopg2

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Удаление строки
    cur.execute("DELETE FROM servers WHERE server = %s;", ("server1",))
 
    # Удаление таблицы
    cur.execute("DROP TABLE servers;")
    conn.commit()

Формирование SQL-запросов курсором

В ситуациях, когда запросы простые, можно использовать статические строки запросов. Однако для динамических запросов рекомендуется использовать psycopg2.sql. Он полезен для предотвращения SQL-инъекций и упрощения работы со сложными SQL-запросами.

    import psycopg2from psycopg2 import sql

with psycopg2.connect(
dbname="selecteldb",
user="postgres",
password="password",
host="localhost",
port="5432"
) as conn:
with conn.cursor() as cur:
    # Формирование SQL-запроса
    query = sql.SQL("SELECT {fields} FROM {table} WHERE {pkey} = %s;").format(
        fields=sql.SQL(', ').join([
            sql.Identifier('id'),
            sql.Identifier('server'),
            sql.Identifier('cost')
        ]),
        table=sql.Identifier('servers'),
        pkey=sql.Identifier('id')
    )
    cur.execute(query, (1,))
    row = cur.fetchone()
    print(row)

Создание и закрытие транзакций

Транзакция — это группа операций, которые выполняются как единое целое. Транзакция должна быть либо полностью завершена, либо полностью отменена, что гарантирует целостность данных в БД.

Метод commit() завершает текущую транзакцию и фиксирует все изменения в базе данных. При закрытии соединения без вызова commit() все изменения будут отменены.

Чтобы начать транзакцию, следует выполнить SQL-запросы после создания соединения и курсора. Операции INSERT, UPDATE, DELETE и другие будут частью транзакции до тех пор, пока не будет выполнена команда commit() или rollback().

В следующих примерах мы дополнительно используем класс Error из библиотеки psycopg2, который позволяет обрабатывать возникающие ошибки и исключения, а также получать подробную информацию о них.

    import psycopg2
from psycopg2 import Error

try:
    with psycopg2.connect(
        dbname="selecteldb",
        user="postgres",
        password="password",
        host="localhost",
        port="5432"
    ) as conn:
        with conn.cursor() as cur:
            try:
                # Выполнение SQL-запросов
                cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server4", 400))
                cur.execute("UPDATE servers SET cost = %s WHERE server = %s;", (450, "server2"))
               
                # Подтверждение транзакции
                conn.commit()
            except Error as e:
                # Откат изменений в случае ошибки
                conn.rollback()
                # Выводим сообщение об ошибке и ее код
                print(f"Transaction failed: {e.pgcode} - {e.pgerror}")
except Exception as e:
    # Обработка ошибок подключения к базе данных
    print(f"The error has occurred. Details: {e}")

В этом примере транзакция начинается автоматически при выполнении первого SQL-запроса. Если все операции успешны, транзакция подтверждается вызовом commit(). Если возникает ошибка, транзакция откатывается с помощью rollback().

Использование контекстного менеджера with для подключения и вызова курсора, а также блок try-except автоматически управляет началом и завершением транзакции. Конструкция try-except используется в Python для обработки исключительных ситуаций (сокращенно «исключений») — неожиданных ошибок и сбоев, которые могут возникнуть во время выполнения программы.

Конструкции try-except и with можно комбинировать различными способами. В следующем примере блок with помещен внутрь try:

    import psycopg2
from psycopg2 import Error

try:
    with psycopg2.connect(
        dbname="selecteldb",
        user="postgres",
        password="password",
        host="localhost",
        port="5432"
    ) as conn:
        with conn.cursor() as cur:
            try:
                # Выполнение SQL-запросов
                cur.execute("INSERT INTO servers (server, cost) VALUES (%s, %s);", ("server5", 500))
                cur.execute("DELETE FROM servers WHERE server = %s;", ("server3",))
               
                # Подтверждение транзакции
                conn.commit()
            except Error as e:
                # Откат изменений в случае ошибки
                conn.rollback()
                # Выводим сообщение об ошибке и ее код
                print(f"Transaction failed: {e.pgcode} - {e.pgerror}")
except Exception as e:
    # Обработка ошибок взаимодействия с базой данных
    print(f"Failed to perform operation: {e}")

Использование конструкции try-except при выполнении транзакций считается хорошей практикой, хоть и не является обязательной. Она не предотвращает блокировки напрямую, но помогает избежать их, позволяя откатывать транзакции и правильно обрабатывать ошибки. Это снижает риск ситуаций, когда база данных остается в заблокированном состоянии из-за неудачного SQL-запроса или незавершенной транзакции.

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

При работе с БД следует правильно сопоставлять типы данных языка программирования и СУБД. Библиотеке psycopg2 автоматически преобразует многие типы данных Python в соответствующие типы PostgreSQL и обратно.

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

При работе с psycopg2 значения None и булевы значения (True, False) из Python автоматически конвертируются в соответствующие SQL-литералы при вставке в PostgreSQL.

При выполнении SQL-запросов с числовыми объектами, например int, long, float и Decimal, они преобразовываются в представления PostgreSQL.

    import psycopg2

with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы с числовыми типами данных
        cur.execute('''
            CREATE TABLE IF NOT EXISTS numbers (
                id SERIAL PRIMARY KEY,
                int_value INTEGER,
                float_value FLOAT,
                numeric_value NUMERIC;
            )
        ''')
        conn.commit()
       
        # Вставка данных
        cur.execute("INSERT INTO numbers (int_value, float_value, numeric_value) VALUES (%s, %s, %s)", (42, 3.14, 123.45))
        conn.commit()

        # Получение данных
        cur.execute("SELECT int_value, float_value, numeric_value FROM numbers;")
        row = cur.fetchone()
        print(row)

Полученный вывод:

    (42, 3.14, Decimal('123.45'))

Работа с date и timestamp

Для работы с типами данных date и timestamp в Python можно использовать строки в формате даты и времени. Рассмотрим пример работы с этими типами данных:

    import psycopg2

with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы, если она не существует
        cur.execute('''
            CREATE TABLE IF NOT EXISTS date_example (
                id SERIAL PRIMARY KEY,
                a_date DATE,
                a_timestamp TIMESTAMP
            );
        ''')
        conn.commit()

        # Вставка данных с типами date и timestamp
        cur.execute('''
            INSERT INTO date_example (a_date, a_timestamp)
            VALUES (%s, %s);
        ''', ('2024-07-15', '2024-07-15 12:34:56'))
        conn.commit()

        # Извлечение данных и вывод в консоль
        cur.execute("SELECT * FROM date_example;")
        row = cur.fetchone()
        print(row)

Полученный вывод:

    (1, datetime.date(2024, 7, 15), datetime.datetime(2024, 7, 15, 12, 34, 56))

Для удобства во встроенной библиотеке Python есть модуль datetime, который позволяет упростить работу с датой и временем. Объекты datetime удобно использовать для работы с динамическими данными, поскольку они позволяют выполнять с датой и временем такие операции, как подсчет и сравнение.

Следующий код вычислит количество дней между двумя датами:

    import psycopg2
from datetime import date, datetime

# Установим дату для работы
date1 = date(2024, 7, 15)
date2 = date.today()  # Текущая дата

# Соединение с базой данных
with psycopg2.connect(
    dbname="selecteldb",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
) as conn:
    with conn.cursor() as cur:
        # Создание таблицы, если она не существует
        cur.execute('''
            CREATE TABLE IF NOT EXISTS date_example2 (
                id SERIAL PRIMARY KEY,
                a_date DATE
            );
        ''')
        conn.commit()

        # Вставка первой даты
        cur.execute('''
            INSERT INTO date_example2 (a_date)
            VALUES (%s);
        ''', (date1,))
        conn.commit()

        # Извлечение даты из базы данных
        cur.execute("SELECT a_date FROM date_example2 WHERE id = 1;")
        stored_date = cur.fetchone()[0]

        # Вычисление разности между датами
        date_difference = date2 - stored_date
        print(f"Количество дней между {stored_date} и {date2}: {date_difference.days} дней")

Полученный вывод:

    Количество дней между 2024-07-15 и 2024-08-29: 45 дней

Заключение

В статье рассмотрели работу с PostgreSQL с помощью модуля psycopg2 в Python. Благодаря psycopg2 можно легко интегрировать PostgreSQL в свои приложения, упрощая процесс разработки и управления данными.