Python: подключение к PostgreSQL
В данной статье мы рассмотрим подключение к базе данных PostgreSQL с помощью Python 3.3.
Было опробовано на ОС Windows 7×32.
Скачаем библиотеку py‑postgresql‑1.1.0.win32‑py3.3.exe с http://www.lfd.uci.edu/~gohlke/pythonlibs/ (Кстати тут можно обнаружить и другие драйвера, можете поэксперементировать).
Вот мой пример выборки из базы:
//Импортируем модуль и делаем псевдоним
import postgresql.driver as pg_driver
//Создаем подключение к базе
db = pg_driver.connect(user=’postgres’, password=’postgres’, host=’192.168.10.234′, database=’ip_acct’, port=5432)
//Запрос и его перебор
for name, in db.prepare(«SELECT name FROM public.docs_types»):
print(name)
//Закрываем подключение
db.close()
Краткое руководство. Использование Python для подключения и запроса данных в База данных Azure для PostgreSQL — гибкий сервер
Область применения: гибкий сервер Базы данных Azure для PostgreSQL
В этом кратком руководстве вы подключаетесь к База данных Azure для PostgreSQL гибкому экземпляру сервера с помощью Python. Также вы узнаете, как использовать инструкции SQL для запроса, вставки, обновления и удаления данных в базе данных на платформах Windows, Mac и Ubuntu Linux.
В этой статье предполагается, что вы знакомы с разработкой с помощью Python, но вы не знакомы с База данных Azure для PostgreSQL гибким сервером.
Необходимые компоненты
- Учетная запись Azure с активной подпиской. Создайте учетную запись бесплатно .
- Гибкий экземпляр сервера База данных Azure для PostgreSQL. Сведения о создании гибкого экземпляра сервера База данных Azure для PostgreSQL см. в статье «Создание База данных Azure для PostgreSQL — гибкий экземпляр сервера с помощью портал Azure».
- Python версии 2.7 или 3.6 и выше.
- Установщик пакета pip последней версии.
Подготовка клиентской рабочей станции
- Если вы создали гибкий экземпляр сервера База данных Azure для PostgreSQL с частным доступом (интеграция с виртуальной сетью), необходимо подключиться к серверу из ресурса в той же виртуальной сети, что и сервер. Вы можете создать виртуальную машину и добавить ее в виртуальную сеть, созданную с помощью База данных Azure для PostgreSQL гибкого экземпляра сервера. Дополнительные сведения см. в статье Создание виртуальной сети для Базы данных Azure для PostgreSQL (Гибкий сервер) и управление ею с помощью Azure CLI.
- Если вы создали гибкий экземпляр сервера База данных Azure для PostgreSQL с общедоступным доступом (разрешенными IP-адресами), вы можете добавить локальный IP-адрес в список правил брандмауэра на сервере. Дополнительные сведения см. в статье Создание правил брандмауэра для Базы данных Azure для PostgreSQL (Гибкий сервер) и управление ими с помощью Azure CLI.
Установка библиотек Python для PostgreSQL
Модуль psycopg2 позволяет подключаться к базе данных PostgreSQL и выполнять запросы к ней. Он доступен в виде пакета Wheel для Linux, macOS или Windows. Установите двоичную версию модуля, включая все зависимости.
Чтобы установить psycopg2 , откройте окно терминала или командную строку и выполните команду pip install psycopg2 .
Получение сведений о подключении к базе данных
Подключение для База данных Azure для PostgreSQL гибкого экземпляра сервера требуется полное имя сервера и учетные данные для входа. Эти сведения можно получить на портале Azure.
- В портал Azure найдите и выберите имя гибкого сервера База данных Azure для PostgreSQL.
- На странице Обзор сервера скопируйте полное имя сервера и имя администратора. Полное имя сервера всегда имеет формат .postgres.database.azure.com. Кроме того, потребуется пароль администратора. Если вы не помните этот пароль, вы можете сбросить его на странице обзора.
Выполнение примеров кода Python
Для каждого примера кода в этой статье сделайте следующее:
- Создайте файл в текстовом редакторе.
- Добавьте пример кода в файл. В коде замените:
- и значениями, скопированными на портале Azure.
- паролем сервера.
- с именем гибкой базы данных сервера База данных Azure для PostgreSQL. При создании сервера автоматически создается база данных по умолчанию с именем postgres. Можно переименовать эту базу данных или создать новую базу данных с помощью команд SQL.
- Сохраните файл в папке проекта с расширением PY, например postgres-insert.py. При сохранении файла в ОС Windows обязательно выберите кодировку UTF-8.
- Чтобы запустить этот файл, перейдите в папку проекта в интерфейсе командной строки и введите python , а затем — имя файла, например python postgres-insert.py .
Создание таблицы и вставка данных
Следующий пример кода подключается к гибкой базе данных сервера База данных Azure для PostgreSQL с помощью функции psycopg2.connect и загружает данные с помощью инструкции SQL INSERT. Функция cursor.execute выполняет SQL-запрос к базе данных.
import psycopg2 # Update connection string information [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] host = "" dbname = "" user = "" password = "" sslmode = "require" # Construct connection string [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn_string = "host= user= dbname= password= sslmode=".format(host, user, dbname, password, sslmode) conn = psycopg2.connect(conn_string) print("Connection established") cursor = conn.cursor() # Drop previous table of same name if one exists [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("DROP TABLE IF EXISTS inventory;") print("Finished dropping table (if existed)") # Create a table [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);") print("Finished creating table") # Insert some data into the table [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150)) cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154)) cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100)) print("Inserted 3 rows of data") # Clean up [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn.commit() cursor.close() conn.close() При успешном выполнении кода возвращаются следующие данные:
Чтение данных
Следующий пример кода подключается к гибкой базе данных сервера База данных Azure для PostgreSQL и использует cursor.execute с инструкцией SQL SELECT для чтения данных. Эта функция принимает запрос и возвращает результирующий набор для итерации с помощью cursor.fetchall()
import psycopg2 # Update connection string information [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] host = "" dbname = "" user = "" password = "" sslmode = "require" # Construct connection string [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn_string = "host= user= dbname= password= sslmode=".format(host, user, dbname, password, sslmode) conn = psycopg2.connect(conn_string) print("Connection established") cursor = conn.cursor() # Fetch all rows from table [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("SELECT * FROM inventory;") rows = cursor.fetchall() # Print all rows [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] for row in rows: print("Data row = (%s, %s, %s)" %(str(row[0]), str(row[1]), str(row[2]))) # Cleanup [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn.commit() cursor.close() conn.close() Обновление данных
Следующий пример кода подключается к гибкой базе данных сервера База данных Azure для PostgreSQL и использует cursor.execute с инструкцией SQL UPDATE для обновления данных.
import psycopg2 # Update connection string information [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] host = "" dbname = "" user = "" password = "" sslmode = "require" # Construct connection string [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn_string = "host= user= dbname= password= sslmode=".format(host, user, dbname, password, sslmode) conn = psycopg2.connect(conn_string) print("Connection established") cursor = conn.cursor() # Update a data row in the table [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("UPDATE inventory SET quantity = %s WHERE name = %s;", (200, "banana")) print("Updated 1 row of data") # Cleanup [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn.commit() cursor.close() conn.close() Удаление данных
Следующий пример кода подключается к гибкой базе данных сервера База данных Azure для PostgreSQL и использует cursor.execute с инструкцией SQL DELETE для удаления элемента инвентаризации, который вы ранее вставили.
import psycopg2 # Update connection string information [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] host = "" dbname = "" user = "" password = "" sslmode = "require" # Construct connection string [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn_string = "host= user= dbname= password= sslmode=".format(host, user, dbname, password, sslmode) conn = psycopg2.connect(conn_string) print("Connection established") cursor = conn.cursor() # Delete data row from table [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] cursor.execute("DELETE FROM inventory WHERE name = %s;", ("orange",)) print("Deleted 1 row of data") # Cleanup [!INCLUDE [applies-to-postgresql-flexible-server](../includes/applies-to-postgresql-flexible-server.md)] conn.commit() cursor.close() conn.close() Работа с PostgreSQL в Python
17 Ноя. 2018 , Python, 263591 просмотров, How to Work with PostgreSQL in Python
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
pip install psycopg2Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:
pip install psycopg2-binary Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.
Начало работы
Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:
import psycopg2 conn = psycopg2.connect(dbname='database', user='db_user', password='mypassword', host='localhost') cursor = conn.cursor() Через курсор происходит дальнейшее общение в базой.
cursor.execute('SELECT * FROM airport LIMIT 10') records = cursor.fetchall() . cursor.close() conn.close() После выполнения запроса, получить результат можно несколькими способами:
- cursor.fetchone() — возвращает 1 строку
- cursor.fetchall() — возвращает список всех строк
- cursor.fetchmany(size=5) — возвращает заданное количество строк
Также курсор является итерируемым объектом, поэтому можно так:
for row in cursor: print(row) Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:
from contextlib import closing with closing(psycopg2.connect(. )) as conn: with conn.cursor() as cursor: cursor.execute('SELECT * FROM airport LIMIT 5') for row in cursor: print(row)
По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor ). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory :
from psycopg2.extras import DictCursor with psycopg2.connect(. ) as conn: with conn.cursor(cursor_factory=DictCursor) as cursor: . Формирование запросов
Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:
cursor.execute('SELECT * FROM airport WHERE city_code = %s', ('ALA', )) for row in cursor: print(row) Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:
- Плейсхолдеры в строке запроса должны быть %s , даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.
- Не нужно обрамлять строки в одинарные кавычки.
- Если в запросе присутствует знак %, то его необходимо писать как %%.
Именованные аргументы можно писать так:
>>> cursor.execute('SELECT * FROM engine_airport WHERE city_code = %(city_code)s', ) . Модуль psycopg2.sql
Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.
>>> cursor.execute('SELECT * FROM %s WHERE city_code = %s', ('airport', 'ALA')) psycopg2.ProgrammingError: ОШИБКА: ошибка синтаксиса (примерное положение: "'airport'") LINE 1: SELECT * FROM 'airport' WHERE city_code = 'ALA' Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2.extensions.quote_ident , но и про неё легко забыть.
from psycopg2 import sql . >>> with conn.cursor() as cursor: columns = ('country_name_ru', 'airport_name_ru', 'city_code') stmt = sql.SQL('SELECT <> FROM <> LIMIT 5').format( sql.SQL(',').join(map(sql.Identifier, columns)), sql.Identifier('airport') ) cursor.execute(stmt) for row in cursor: print(row) ('Французская Полинезия', 'Матайва', 'MVT') ('Индонезия', 'Матак', 'MWK') ('Сенегал', 'Матам', 'MAX') ('Новая Зеландия', 'Матамата', 'MTA') ('Мексика', 'Матаморос', 'MAM') Транзакции
По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:
- закрыв соединение conn.close()
- удалив соединение del conn
- вызвав conn.commit() или conn.rollback()
Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True , каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).
with conn.cursor() as cursor: conn.autocommit = True values = [ ('ALA', 'Almaty', 'Kazakhstan'), ('TSE', 'Astana', 'Kazakhstan'), ('PDX', 'Portland', 'USA'), ] insert = sql.SQL('INSERT INTO city (code, name, country_name) VALUES <>').format( sql.SQL(',').join(map(sql.Literal, values)) ) cursor.execute(insert) Интересные записи:
- Обзор Python 3.9
- Pyenv: удобный менеджер версий python
- Что нового появилось в Django Channels?
- FastAPI, asyncio и multiprocessing
- Введение в logging на Python
- Руководство по работе с HTTP в Python. Библиотека requests
- Django Channels: работа с WebSocket и не только
- Работа с MySQL в Python
- Почему Python?
- Celery: начинаем правильно
- Авторизация через Telegram в Django и Python
- Python-RQ: очередь задач на базе Redis
- Введение в pandas: анализ данных на Python
- Разворачиваем Django приложение в production на примере Telegram бота
- Как написать Telegram бота: практическое руководство
- Django, RQ и FakeRedis
- Итоги первой встречи Python программистов в Алматы
- Обзор Python 3.8
- Интеграция Trix editor в Django
- Участие в подкасте TalkPython
- Строим Data Pipeline на Python и Luigi
- Видео презентации ETL на Python
- Авторизация через Telegram в Django приложении
Базы данных в Python: как подключить PostgreSQL и что это такое
Во время разработки приложений часто нужно подключать и использовать базы данных для хранения информации. Самая распространенная база данных — PostgreSQL, поэтому мы расскажем, как работать в Python именно с ней. Для этого существует множество модулей, например:
- Psycopg2
- py-postgresql
- pg8000
Мы расскажем именно про модуль Psycopg2. И выбрали мы его по таким причинам:
- Распространенность — Psycopg2 использует большинство фреймворков Python
- Поддержка — Psycopg2 активно развивается и поддерживает основные версии Python
- Многопоточность — Psycopg2 позволяет нескольким потокам поддерживать одно и то же соединение
Python-разработчик — с нуля до трудоустройства за 10 месяцев
- Постоянная поддержка от наставника и учебного центра
- Помощь с трудоустройством
- Готовое портфолио к концу обучения
- Практика с первого урока
Вы получите именно те инструменты и навыки, которые позволят вам найти работу
Установка Psycopg2
Для начала работы с модулем достаточно установить пакет при помощи pip:
install psycopg2-binary Если в вашем проекте используется poetry, то при первоначальной настройке проекта нужно добавить psycopg2-binary в зависимости. Для добавления в уже существующий проект воспользуйтесь командой:
Использование Psycopg2
Подключение к БД:
Для подключения к существующей базе данных необходимо знать основную информацию о вашей БД. Если вы не знаете, где ее взять, то пройдите сначала наш большой курс по Основам баз данных:
- Username — имя пользователя, которое вы используете для работы с PostgreSQL
- Password — пароль, который используется пользователем
- Host Name — имя сервера или IP-адрес, на котором работает PostgreSQL
- Database Name — имя базы данных, к которой мы подключаемся.
Для подключения к базе данных мы используем метод connect() , которому в качестве аргументов передаются вышеперечисленные данные:
import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect(dbname='test', user='postgres', password='secret', host='host') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') Также подключение к базе данных может осуществляться с помощью Connection URI :
import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect('postgresql://user:password@host:port/database_name') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') Читайте также: Вышел Python 3.11.0. В два раза быстрее, c детальным описанием ошибок и кучей новых типов
Взаимодействие Python с PostgreSQL
Итак, подключение к базе данных успешно выполнено. Дальше мы будем взаимодействовать с ней через объект cursor , который можно получить через метод cursor() объекта соединения. Он помогает выполнять SQL-запросы из Python.
# получение объекта курсора cursor = conn.cursor() С помощью cursor происходит передача запросов базе данных:
# Получаем список всех пользователей cursor.execute('SELECT * FROM users') all_users = cursor.fetchall() cursor.close() # закрываем курсор conn.close() # закрываем соединение Для получения результата после выполнения запроса используются следующие команды:
- cursor.fetchone() — вернуть одну строку
- cursor.fetchall() — вернуть все строки
- cursor.fetchmany(size=10) — вернуть указанное количество строк
Хорошей практикой при работе с базой данных является закрытие объекта cursor и соединения с базой. Для автоматизации этого процесса удобно взаимодействовать через контекстный менеджер , используя конструкцию with :
with conn.cursor as curs: curs.execute('SELECT * FROM users') all_users = curs.fetchall() В тот момент, когда объект cursor выходит за пределы конструкции with , происходит его закрытие и освобождение связанных с ним ресурсов.
По умолчанию результат возвращается в виде кортежа. Такое поведение возможно изменить, передав параметр cursor_factory в момент открытия объекта cursor , например, использовать NamedTupleCursor. Это вернет данные в виде именованного кортежа:
from psycopg2.extras import NamedTupleCursor # … with conn.cursor(cursor_factory=NamedTupleCursor) as curs: curs.execute('SELECT * FROM users WHERE name=%s', (name='Alfred')) alfred = curs.fetchone() alfred # (id=10, name='Alfred', age='90') # … Выполнение запросов
Psycopg2 преобразует переменные Python в SQL значения с учетом их типа. Все стандартные типы Python адаптированы для правильного представления в SQL.
Передача параметров в SQL-запрос происходит с помощью подстановки плейсхолдеров %s и цепочки значений в качестве второго аргумента функции:
with conn.cursor() as curs: curs.execute('SELECT id, name FROM users WHERE name=%s', ('John',)) curs.fetchall() # … with conn.cursor() as curs: curs.execute(INSERT INTO users (name, age) VALUES (%s, %s), ('John', 19)) # … conn.close() Подстановка значений в SQL-запрос используется для того, чтобы избежать атак типа SQL Injection. Также несколько полезных советов по построению запросов:
- Плейсхолдер должен быть %s даже если тип подставляемого значения отличается от строки
- Не заключайте плейсходер в кавычки
- Если в запросе используется знак % , он должен быть указан как %%
Профессия «Python-разработчик»
- Изучите Python — язык с простым и понятным синтаксисом
- Научитесь создавать полноценные сайты и веб-приложения
- Освойте популярный фреймворк Python — Django
- Разберитесь в базах данных и научитесь управлять ими с помощью SQL