Работа с PostgreSQL в Python
В этой статье мы обсудим, как получить доступ к базе данных PostgreSQL в Python с помощью драйвера psycopg2.
Модуль psycopg2
После установки модуля вы можете использовать его для подключения к вашей базе данных в вашем приложении.
Подключение к базе данных
Чтобы подключиться к вашей базе данных, вы должны сначала создать объект подключения, представляющий базу данных. Затем вы должны создать объект курсора, чтобы помочь вам в выполнении ваших операторов SQL.
В следующем примере показано, как установить соединение с базой данных с именем «postgres»:
В результате получим:
Ниже приведен список параметров, которые были переданы методу connect() :
Обратите внимание, что значения для вышеуказанных параметров должны быть правильными, чтобы соединение было успешным. Если нет, то будет сгенерировано исключение. Вывод в приведенном выше коде показывает, что соединение с базой данных установлено успешно.
Создание таблицы
Следующий пример демонстрирует это:
В результате получим:
Этот метод commit() помогает нам применить изменения, которые мы внесли в базу данных, и эти изменения не могут быть отменены, если commit() выполнится успешно. Метод close() закрывает соединение с базой данных.
На данный момент мы создали таблицу с 4 столбцами, каждый из которых имеет различные типы данных. Приведенный выше вывод показывает, что таблица была успешно создана.
Вставка данных
Вот пример этого в действии:
В результате получим:
После запуска этого кода мы вставили одну запись в нашу таблицу базы данных. Это было сделано путем указания имени таблицы, а также столбцов, в которые нам нужно вставить данные. Мы также можем вставить несколько записей одной командой. Например:
В результате получим:
Извлечение данных
В результате получим:
Обновление таблиц
В результате получим:
В приведенном выше примере мы обновили значение столбца AGE для всех строк, для которых установлено значение ADMISSION 3420. После выполнения обновления мы извлекаем эти данные, чтобы убедиться, что соответствующие строки / столбцы были обновлены.
Удаление строк
В результате получим:
В этом примере мы удалили все записи, в которых регистрационный номер учащегося равен 3420, что в данном случае составляет всего одну строку. После извлечения данных с помощью SELECT мы видим, что эта запись не является частью вышеприведенного вывода, подтверждая, что она была удалена из базы данных.
Заключение
Python и PostgreSQL: как получить доступ к базе данных PostgreSQL
PostgreSQL является удивительным и современным R elational D atabase М ПРАВЛЕНИЕ S ystem (СУБД). Он также является базой данных с открытым исходным кодом. PostgreSQL — кроссплатформенный. Вы можете очень легко установить PostgreSQL в Windows, Mac OS и Linux.
Вы можете легко взаимодействовать с базой данных PostgreSQL с помощью языка программирования Python. Все, что вам нужно сделать, это установить модуль Python psycopg2 с PIP, и все готово.
В этой статье я покажу вам, как получить доступ к базе данных PostgreSQL с помощью Python в Linux. Я собираюсь использовать Debian 9 Stretch в качестве операционной системы и языка программирования Python 3. Версия базы данных PostgreSQL, которую я собираюсь использовать, — PostgreSQL 9.6. Итак, приступим.
Предварительные условия:
Вы можете найти множество статей на linuxhint.com, которые могут помочь вам настроить PostgreSQL и установить Python + PIP в ваш любимый дистрибутив Linux. Просто ищи это.
Создание базы данных и пользователя PostgreSQL
В этом разделе я покажу вам, как создать базу данных PostgreSQL и пользователя в Linux. Мы будем подключаться к этой базе данных из Python позже в этой статье.
Сначала узнайте свое имя пользователя для входа с помощью следующей команды:
Как видите, мое имя пользователя для входа — shovon. Ваш будет другим. Обязательно запишите это, так как оно вам понадобится позже.
Теперь запустите интерактивный терминал PostgreSQL с помощью следующей команды:
Должен запуститься интерактивный терминал PostgreSQL.
Теперь создайте базу данных PostgreSQL pyapp с помощью следующей команды SQL:
postgres = # CREATE DATABASE pyapp;
Pyapp база данных должна быть создана.
Теперь вам нужно создать нового пользователя PostgreSQL. Также убедитесь, что имя пользователя совпадает с вашим логином.
Создайте нового пользователя PostgreSQL с помощью следующей команды SQL:
postgres = # CREATE USER your_login_username WITH ENCRYPTED PASSWORD ‘your_password’ ;
ПРИМЕЧАНИЕ: замените your_login_username и your_password своим собственным именем пользователя и паролем для входа.
Должен быть создан пользователь PostgreSQL.
Теперь предоставьте вновь созданному пользователю все привилегии для только что созданной базы данных pyapp с помощью следующей команды SQL:
postgres = # GRANT ALL ON DATABASE pyapp TO your_login_username;
Все привилегии для базы данных pyapp предоставлены вашему пользователю, входящему в систему.
Теперь выйдите из терминала PostgreSQL с помощью следующей команды:
Теперь давайте посмотрим, сможем ли мы войти в нашу недавно созданную базу данных pyapp, используя наше имя пользователя, с помощью следующей команды:
Вы должны войти в систему.
Установка psycopg2 с PIP и PIP3
Пришло время установить модуль Python psycopg2.
Если вы используете Python 3, выполните следующую команду для установки psycopg2 :
Если вы используете Python 2, выполните следующую команду для установки psycopg2 :
Должен быть установлен модуль psycopg2-binary PIP.
Создание каталога проекта
Теперь создайте каталог проекта pyapp с помощью следующей команды:
И перейдите в каталог с помощью следующей команды:
Здесь я создам весь сценарий Python для доступа к базе данных PostgreSQL.
Подключение к базе данных PostgreSQL
Сначала создайте программу на Python connect.py в каталоге вашего проекта.
Теперь введите следующие строки и сохраните файл.
Теперь запустите сценарий connect.py с помощью одной из следующих команд:
Как видите, я подключен к базе данных.
Здесь, в строке 1, импортируется модуль psycopg2. В строке 4 для подключения к базе данных PostgreSQL используется метод psycopg2.connect (). Блок try-except используется для обнаружения ошибок, если что-то пойдет не так и соединение с базой данных не удастся.
Выполнение команд SQL
В этом разделе я создам простую таблицу пользователей с помощью Python psycopg2.
Введите следующий код в новый скрипт Python create_table.py и сохраните его.
Теперь запустим скрипт:
Как видите, таблица users создана.
С psycopg2, если вы хотите выполнить команду SQL, сначала вам нужно создать курсор.
В строке 9 я создал курсор с помощью метода conn.cursor () и сохранил его в переменной cur. Здесь conn — это переменная, в которой я сохранил соединение с базой данных из метода psycopg2.connect ().
Затем вы выполняете команду SQL с курсором как cur.exec («YOUR_SQL_GOES_HERE»), что я сделал в строке 12-17 для создания таблицы пользователей.
Если ваша команда SQL вносит изменения в базу данных, к которой вы подключены, вам нужно вызвать метод conn.commit (), чтобы сделать изменения постоянными, как я сделал в строке 19.
Вставка данных в базу данных PostgreSQL
Теперь, когда у вас есть таблица пользователей, давайте вставим в нее некоторые данные.
Создайте новый файл insert.py в каталоге вашего проекта, введите следующие коды и сохраните файл.
Теперь запустите скрипт Python insert.py следующим образом:
Данные должны быть вставлены.
Как вы можете видеть в терминале PostgreSQL.
В сценарии insert.py строка 12 метод cur.execute () запускает SQL-запрос для вставки в таблицу пользователей. % S «ы заменяется строками из кортежа, второй параметр cur.execute () методы.
Первое вхождение % s заменяется первым элементом кортежа, второе % s заменяется вторым элементом кортежа и так далее. Вы также можете смешивать типы данных, если хотите. Например, % d представляет собой целое число.
Получение данных из базы данных PostgreSQL
Теперь вы можете получить данные, которые вы вставили в базу данных PostgreSQL.
Сначала создайте новый скрипт Python fetch.py и введите следующие строки кода. Затем сохраните файл.
Теперь запустите скрипт fetch.py с помощью следующей команды:
Как видите, данные, которые я вставил, были извлечены. Он возвращается как кортеж, что-то вроде массива.
Краткое руководство. Подключение к Базе данных Azure для PostgreSQL (Гибкий сервер) и запрашивание данных с помощью Python
Из этого краткого руководства вы узнаете, как подключиться к Базе данных Azure для PostgreSQL (Гибкий сервер) с использованием Python. Также вы узнаете, как использовать инструкции SQL для запроса, вставки, обновления и удаления данных в базе данных на платформах Windows, Mac и Ubuntu Linux.
В этой статье предполагается, что у вас уже есть опыт разработки на языке Python, но вы только начали работу с Базой данных Azure для PostgreSQL (Гибкий сервер).
Предварительные требования
Подготовка клиентской рабочей станции
Установка библиотек Python для PostgreSQL
Модуль psycopg2 позволяет подключаться к базе данных PostgreSQL и выполнять запросы к ней. Он доступен в виде пакета Wheel для Linux, macOS или Windows. Установите двоичную версию модуля, включая все зависимости. Дополнительные сведения об установке psycopg2 и о предварительных требованиях см. на этой странице.
Получение сведений о подключении к базе данных
Чтобы подключиться к Базе данных Azure для PostgreSQL (Гибкий сервер), требуется полное имя сервера и учетные данные для входа. Эти сведения можно получить на портале Azure.
На портале Azure найдите и выберите имя гибкого сервера.
На странице Обзор сервера скопируйте полное имя сервера и имя администратора. Полное имя сервера всегда имеет формат .postgres.database.azure.com.
Кроме того, потребуется пароль администратора. Если вы не помните этот пароль, вы можете сбросить его на странице обзора.
Выполнение примеров кода Python
Для каждого примера кода в этой статье сделайте следующее:
Создайте файл в текстовом редакторе.
Добавьте пример кода в файл. В коде замените:
Сохраните файл в папке проекта с расширением PY, например postgres-insert.py. При сохранении файла в ОС Windows обязательно выберите кодировку UTF-8.
Создание таблицы и вставка данных
В приведенном ниже примере кода устанавливается подключение к базе данных в службе «База данных Azure для PostgreSQL (Гибкий сервер)» с помощью функции psycopg2.connect и загружаются данные с помощью инструкции SQL INSERT. Функция cursor.execute выполняет SQL-запрос к базе данных.
При успешном выполнении кода возвращаются следующие данные:
Работа с PostgreSQL в Python
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:
Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.
Начало работы
Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:
Через курсор происходит дальнейшее общение в базой.
После выполнения запроса, получить результат можно несколькими способами:
Также курсор является итерируемым объектом, поэтому можно так:
Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:
Формирование запросов
Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:
Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:
Именованные аргументы можно писать так:
Модуль psycopg2.sql
Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.
Транзакции
По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:
💌 Присоединяйтесь к рассылке
Понравился контент? Пожалуйста, подпишись на рассылку.
Как подружить Python и базы данных SQL. Подробное руководство
Leo Matyushkin
Все приложения взаимодействуют с данными, чаще всего через систему управления базами данных (СУБД). Одни языки программирования поставляются с модулями для работы с СУБД, другие требуют использования сторонних пакетов. Из этого подробного руководства вы узнаете о различных библиотеках Python для работы с SQL-базами данных. Мы разработаем простое приложение для взаимодействия с БД SQLite, MySQL и PostgreSQL.
Примечание. Если вы не разбираетесь в базах данных, советуем обратить внимание на следующие публикации Библиотеки программиста: 11 типов современных баз данных, SQL за 20 минут, Подборка материалов для изучения баз данных и SQL.
Из этого пособия вы узнаете:
Чтобы получить максимальную отдачу от этого учебного пособия, необходимо знать основы Python, SQL и работы с СУБД. Вы также должны иметь возможность загружать и импортировать пакеты в Python и знать, как устанавливать и запускать серверы БД локально или удаленно.
В каждом разделе по три подраздела: SQLite, MySQL и PostgreSQL.
1. Схема базы данных для обучения
В этом уроке мы разработаем очень маленькую базу данных приложения для социальных сетей. База данных будет состоять из четырех таблиц:
Схема базы данных показана на рисунке ниже.
2. Подключение к базам данных
Примечание. Для выполнения разделов о MySQL и PostgreSQL необходимо самостоятельно запустить соответствующие серверы. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL в публикации Запуск проекта Django (англ.). Чтобы узнать, как создать базу данных в PostgreSQL, перейдите к разделу Setting Up a Database в публикации Предотвращение атак SQL-инъекций с помощью Python (англ.).
SQLite
SQLite, вероятно, является самой простой базой данных, к которой можно подключиться с помощью Python, поскольку для этого не требуется устанавливать какие-либо внешние модули. По умолчанию стандартная библиотека Python уже содержит модуль sqlite3.
Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:
Вот как работает этот код:
MySQL
Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:
Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:
Вот как выглядит эта функция:
Создадим базу данных sm_app для нашего приложения на сервере MySQL:
PostgreSQL
Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2 :
Определим функцию create_connection() для подключения к базе данных PostgreSQL:
Запустив вышеприведенный скрипт, мы увидим базу данных sm_app на своем сервере PostgreSQL. Подключимся к ней:
Здесь 127.0.0.1 и 5432 это соответственно IP-адресу и порт хоста сервера.
3. Создание таблиц
В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные библиотеки Python. Мы создали базу данных sm_app на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.
Как обсуждалось ранее, нам нужно получить и связать четыре таблицы:
SQLite
Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.
Итак, начнем с определения функции execute_query() :
Теперь напишем передаваемый запрос ( query ):
В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:
Следующий запрос используется для создания таблицы posts:
Наконец, формируем следующим скриптом таблицы comments и likes :
MySQL
Описываем таблицу users :
Запрос для реализации отношения внешнего ключа в MySQL немного отличается от SQLite. Более того, MySQL использует ключевое слово AUTO_INCREMENT для указания столбцов, значения которых автоматически увеличиваются при вставке новых записей.
PostgreSQL
Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor :
Мы можем использовать эту функцию для организации таблиц, вставки, изменения и удаления записей в вашей базе данных PostgreSQL.
Создадим внутри базы данных sm_app таблицу users :
4. Вставка записей
В предыдущем разделе мы разобрали, как развертывать таблицы в базах данных SQLite, MySQL и PostgreSQL с использованием различных модулей Python. В этом разделе узнаем, как вставлять записи.
SQLite
Вставим в таблицу posts шесть записей:
Следующий скрипт вставляет записи в таблицы comments и likes :
MySQL
Посмотрите на следующий пример, который вставляет две записи в таблицу likes :
PostgreSQL
Следующий скрипт вставляет записи в таблицу posts :
5. Извлечение данных из записей
SQLite
SELECT
Давайте выберем все записи из таблицы users :
Примечание. Не рекомендуется использовать SELECT * для больших таблиц, так как это может привести к большому числу операций ввода-вывода, которые увеличивают сетевой трафик.
Результат вышеприведенного запроса выглядит следующим образом:
Таким же образом вы можете извлечь все записи из таблицы posts :
Вывод выглядит так:
Вы также можете выполнять более сложные запросы, включающие операции типа JOIN для извлечения данных из двух связанных таблиц. Например, следующий скрипт возвращает идентификаторы и имена пользователей, а также описание сообщений, опубликованных этими пользователями:
Следующий скрипт возвращает все сообщения вместе с комментариями к сообщениям и именами пользователей, которые разместили комментарии:
Вывод выглядит так:
Вывод выглядит так:
WHERE
MySQL
Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite:
Теперь выберем все записи из таблицы users :
Вывод будет похож на то, что мы видели с SQLite.
PostgreSQL
Опять же, результат будет похож на то, что мы видели раньше.
6. Обновление записей таблицы
SQLite
Увидим следующий вывод:
Следующий скрипт обновит описание:
То есть запись была обновлена.
MySQL
Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3 :
PostgreSQL
Запрос на обновление PostgreSQL аналогичен SQLite и MySQL.
7. Удаление записей таблицы
SQLite
В качестве примера удалим комментарий с id равным 5:
Заключение
Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.
Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.