Как связать python и postgresql

Обновлено: 06.05.2024

В этом материале рассмотрим, как управлять транзакциями PostgreSQL из Python с помощью psycopg2. Транзакция — это единица работы (unit of work), затрагивающая набор операций базы данных.

Подготовка

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

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

Вот о чем дальше пойдет речь:

Включение и выключение режима автокомита (автосохранения).
Транзакции для сохранения изменений в базе данных.
Поддержка свойства ACID транзакции.
Откат всех операций в случае неудачи.
Изменение уровня изоляции транзакции PostgreSQL из Python.

Управление транзакциями psycopg2

Транзакции PostgreSQL обрабатываются объектом соединения. Он отвечает за сохранение изменений или откат в случае неудачи.

С помощью объекта cursor выполняются операции в базе данных. Можно создать неограниченное количество объектов cursor из объекта connection . Если любая из команд объекта cursor завершается неудачно, или же транзакция отменяется, то следующие команды не выполняются вплоть до вызова метода connection.rollback() .

Объект соединения отвечает за остановку транзакции. Это можно сделать с помощью методов commit() ли rollback() .
После использования метода commit() изменения сохраняются в базе данных.
С помощью метода rollback() можно откатить изменения.

Примечание: вызов метода closе() или любого другого, отвечающего за уничтожение объекта соединения, приводит к неявному вызову rollback() , вследствие чего все изменения откатываются.

Connection.autocommit

По умолчанию соединение работает в режиме автоматического сохранения, то есть свойство auto-commit равно True . Это значит, что при успешном выполнении запроса изменения немедленно сохраняются в базу данных, а откат становится невозможным.

Для выполнения запросов в транзакции это свойство нужно отключить. Для этого нужно сделать connection.autocommit=False . В этом случае будет возможность откатить выполненный запрос к оригинальному состоянию в случае неудачи.

Connection.rollback

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

Пример управления транзакциями PostgreSQL из Python

Примечание: транзакция остается открытой до явного вызова commit() или rollback() .

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

С помощью Python вы можете легко установить соединение с вашей базой данных PostgreSQL. Существует множество драйверов Python для PostgreSQL, наиболее популярным из которых является «psycopg». Текущая версия - psycopg2.

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

Модуль psycopg2

Мы можем интегрировать Postgres с Python, используя модуль psycopg2. psycopg2 - это адаптер базы данных Postgres для Python. Чтобы использовать этот модуль, вы должны сначала установить его. Это можно сделать с помощью команды pip , как показано ниже:

Обратите внимание, что я использую Python 3.5, поэтому я использовал pip3 вместо pip .

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

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

Чтобы подключиться к вашей базе данных, вы должны сначала создать объект подключения, представляющий базу данных. Затем вы должны создать объект курсора, чтобы помочь вам в выполнении ваших операторов SQL.

В следующем примере показано, как установить соединение с базой данных с именем «postgres»:

В результате получим:

Ниже приведен список параметров, которые были переданы методу connect() :

database : Имя базы данных, к которой нужно подключиться.
user : Имя пользователя, которое будет использоваться для аутентификации.
password : Пароль базы данных для пользователя.
host : Адрес сервера базы данных. Например, имя домена, «localhost» или IP-адрес.
port : Номер порта. Если вы не предоставите это, будет использоваться значение по умолчанию, а именно 5432.

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

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

Чтобы создать таблицу Postgres в Python, мы используем оператор SQL CREATE TABLE . Этот запрос должен быть выполнен после установления соединения с базой данных. Мы также создаем объект курсора, вызывая метод cursor() , который принадлежит объекту connection . Этот объект cursor используется для фактического выполнения ваших команд.

Затем мы вызываем метод execute() объекта cursor , чтобы помочь нам в создании таблицы. Наконец, нам нужно зафиксировать и закрыть соединение. «Фиксация» соединения говорит драйверу о необходимости посылать команды в базу данных.

Следующий пример демонстрирует это:

В результате получим:

Этот метод commit() помогает нам применить изменения, которые мы внесли в базу данных, и эти изменения не могут быть отменены, если commit() выполнится успешно. Метод close() закрывает соединение с базой данных.

На данный момент мы создали таблицу с 4 столбцами, каждый из которых имеет различные типы данных. Приведенный выше вывод показывает, что таблица была успешно создана.

Вставка данных

Мы можем вставить одну запись или несколько записей в таблицу базы данных Postgres. Опять же, мы должны сначала установить соединение с сервером базы данных, вызвав функцию connect() . Затем мы должны создать объект курсора, вызвав метод cursor() . Наконец, мы должны выполнить инструкцию INSERT через метод execute() , чтобы добавить данные в таблицу.

Вот пример этого в действии:

В результате получим:

После запуска этого кода мы вставили одну запись в нашу таблицу базы данных. Это было сделано путем указания имени таблицы, а также столбцов, в которые нам нужно вставить данные. Мы также можем вставить несколько записей одной командой. Например:

В результате получим:

Поскольку метод commit() не вызывается до тех пор, пока мы не «выполним» все операторы INSERT , несколько записей вставляются с помощью одного вызова метода commit() .

Извлечение данных

Вы можете выбрать данные из базы данных Postgres и просмотреть записи таблицы. Сначала вы должны установить соединение с базой данных, используя функцию connect() . Затем следует создать новый курсор, вызвав метод cursor() . Созданный объект курсора можно затем использовать для выполнения оператора запроса данных из базы данных SELECT .

В результате получим:

Здесь мы извлекли данные из базы данных, указав таблицу и имена столбцов, которые нам нужно извлечь из таблицы базы данных. Эти данные возвращаются нам в виде списка кортежей, причем список «верхнего уровня» представляет собой строки данных. Тогда каждая строка является кортежем данных столбца. Если строки для запроса не возвращаются, тогда возвращается пустой список fetchall() .

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

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

В результате получим:

В приведенном выше примере мы обновили значение столбца AGE для всех строк, для которых установлено значение ADMISSION 3420. После выполнения обновления мы извлекаем эти данные, чтобы убедиться, что соответствующие строки / столбцы были обновлены.

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

Чтобы удалить запись из таблицы базы данных Postgres, мы должны сначала установить соединение с сервером базы данных. Во-вторых, объект курсора должен быть создан путем вызова функции cursor() . Затем мы запускаем оператор DELETE для выполнения удаления.

В результате получим:

В этом примере мы удалили все записи, в которых регистрационный номер учащегося равен 3420, что в данном случае составляет всего одну строку. После извлечения данных с помощью SELECT мы видим, что эта запись не является частью вышеприведенного вывода, подтверждая, что она была удалена из базы данных.

Заключение

В Python есть разные способы доступа к базе данных PostgreSQL. Существует много драйверов баз данных для Python, которые мы можем использовать для этой цели, но psycopg - самый популярный. В этой статье мы показали, как установить модуль, установить соединение с базой данных PostgreSQL и выполнить распространенные SQL-запросы с использованием кода Python.

В этом руководстве разберем, как выполнять запрос SELECT в базе данных PostgreSQL, используя приложение на Python и Psycopg2. Также разберем, как обрабатывать и использовать результат, возвращаемый запросом.

Получить все строки из базы данных PostgreSQL с помощью fetchall() и ограниченное количество записей, используя fetchmany() и fetchone() .
Использовать переменные Python в операторе where для передачи динамических значений.

Подготовка

Перед началом работы нужно убедиться, что у вас есть следующее:

Имя пользователя и пароль для подключения к PostgreSQL
Название базы данных, из которой требуется получить данные

В этом материале воспользуемся таблицей «mobile», которая была создана в первом руководстве по работе с PostgreSQL в Python. Если таблицы нет, то ее нужно создать.

Шаги для выполнения запроса SELECT из Python-программы

Установить psycopg2 с помощью pip.
Создать соединение с базой данных PostgreSQL.
Создать инструкцию с запросом SELECT для получения данных из таблицы PostgreSQL.
Выполнить запрос с помощью cursor.execute() и получить результат.
Выполнить итерацию по объекту с помощью цикла и получить значения всех полей (колонок) базы данных для каждой строки.
Закрыть объекты cursor и connection.
Перехватить любые SQL-исключения, которые могут произойти в процессе.

Запить тестовых данных

Запишем несколько строк в базу данных, что бы потренироваться их получать.

Пример получения данных с помощью fetchall()

В этом примере рассмотрим, как получить все строки из таблицы:

Примечание: в этом примере использовалась команда cursor.fetchall() для получения всех строк из базы данных.

Используйте cursor.execute() для выполнения запроса:

cursor.fetchall() — для всех строк.
cursor.fetchone() — для одной.
cursor.fetchmany(SIZE) — для определенного количества.

Передача переменных Python в качестве параметров запроса

В большинстве случаев требуется передавать переменные Python в запросы SQL для получения нужного результата. Например, приложение может передать ID пользователя для получения подробностей о нем из базы данных. Для этого требуется использовать запрос с параметрами.

Запрос с параметрами — это такой запрос, где применяются заполнители ( %s ) на месте параметров, а значения подставляются уже во время работы программы. Таким образом эти запросы компилируются всего один раз.

Получение определенного количества строк

Если таблица содержит тысячи строк, то получение всех из них может занять много времени. Но существует альтернатива в виде cursor.fetchmany() .

Вот ее синтаксис:

size — это количество строк, которые требуется получить.
этот метод делает запрос на определенное количество строк из результата запроса. fetchmany() возвращает список кортежей, содержащих строки.
fetchmany() возвращает пустой список, если строки не были найдены. Количество строк зависит от аргумента SIZE. Ошибка ProgrammingError возникает в том случае, если предыдущий вызов execute() не дал никаких результатов.

fetchmany() вернет меньше строк, если в таблице их меньше, чем было указано в аргументе SIZE.

Пример получения ограниченного количества строк из таблицы PostgreSQL с помощью cursor.fetchmany() :

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

Вот список разных модулей 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:

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

Модуль 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 подойдет следующая команда.

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

4 Jan. 2021 , Python, 13675 views, Работа с PostgreSQL в Python

PostgreSQL is one of the most popular open source database. If you are building a web application you need a database. Python community likes PostgreSQL as well as PHP community likes MySQL. In order "to speak" with a PostgreSQL database pythonistas usually use psycopg2 library. It is written in C programming language using libpq. Let's dive into it.

Installation

If you install the psycopg2 you have to have additional source files and compiler (gcc):

But you can install the precompiled binary, in this case you need to execute:

Getting started

In order to query a database first we need to connect to it and get a cursor:

Now you have to use cursor to make queries:

When a query is sent you can get the results using the following methods:

cursor.fetchone() — returns a single row
cursor.fetchall() — returns a list of rows
cursor.fetchmany(size=5) — returns the provided number of rows

Cursor is an iterable object, so you can use the for loop:

If you want to follow best practices you need to close a cursor and a connection. Let's do it in a pythonic way using the context manager:

By default when you iterate over a cursor (or using the methods mentioned above) you will get a tuple, each column corresponds to its index. If you want to get a value by column name you can use NamedTupleCursor or DictCursor :

Building dynamic queries

Did you know that you can form a query using psycopg2? You can do it using format-like placeholders:

You should keep in mind the following rules while working with placeholders:

Placeholder should be %s for all data types
Do not use single quotes for string values
If you need to use the character % you have to write it as %%

You can use named arguments as well.

Also psycopg2 provides the module called sql which can be used to securely form an SQL query. It was introduced in the version 2.7

Transactions

💌 Join the mailing list

If you like the content I produce, please join my mailing list to stay tuned.

Читайте также: