Работа с PostgreSQL в Python
17 Ноя. 2018 , Python, 238222 просмотров, How to Work with PostgreSQL in Python
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:
Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.
Начало работы
Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:
Через курсор происходит дальнейшее общение в базой.
После выполнения запроса, получить результат можно несколькими способами:
- cursor.fetchone() — возвращает 1 строку
- cursor.fetchall() — возвращает список всех строк
- cursor.fetchmany(size=5) — возвращает заданное количество строк
Также курсор является итерируемым объектом, поэтому можно так:
Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:
По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor ). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory :
Формирование запросов
Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:
Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:
- Плейсхолдеры в строке запроса должны быть %s , даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.
- Не нужно обрамлять строки в одинарные кавычки.
- Если в запросе присутствует знак %, то его необходимо писать как %%.
Именованные аргументы можно писать так:
Модуль psycopg2.sql
Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.
Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2.extensions.quote_ident , но и про неё легко забыть.
Транзакции
По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:
- закрыв соединение conn.close()
- удалив соединение del conn
- вызвав conn.commit() или conn.rollback()
Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True , каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).
Introduction
Today I am going to show you how to create and modify a PostgreSQL database in Python, with the help of the psycopg2 library.
Unlike SQLAlchemy that generates SQL queries while mapping the database schema to Python objects, psycopg2 takes your hand-crafted SQL queries and executes them against the database. In other words, SQLAlchemy is an ORM (Object-Relational Mapper) and psycopg2 is a database driver for PostgreSQL.
Create the database and its tables
We’ll be creating a dead simple housing database that consists of two tables: “person” and “house”.
Each house has an address and multiple people can live in the same house (1 to many relationship/many people can live in a single house). This relationship is realized by using id as the primary key of “house” and house_id as the foreign key of “person”.
Now let’s see the complete script for the creation of the database and its tables, followed by an explanation of the code.
(in case you are not familiar with the if __name__ == "__main__" condition, it checks that the script being executed is the current script)
In essence, this script does the following:
- Loads database connection information from a .ini file;
- Connects to PostgreSQL;
- Creates the “houses” database;
- Connects to the newly-created database;
- Creates the “house” table; and
- Creates the “person” table.
The contents of the .ini file are just a set of variables for the database connection.
Think of it as storing API keys and other sensitive information in environment variables instead of hard-coding it in the script. Though since this is a local database it’s fine to show you my credentials.
After loading this information with the load_connection_info function as a dictionary (line 58 of the code gist), we connect to PostgreSQL. Because the database does not exist yet, we connect to the engine itself. The creation is handled by the create_db function (lines 16 to 36). psycopg2.connect returns a connection between Python and PostgreSQL, from which we create a cursor. Cursors are created to execute the code in the PostgreSQL.
After that, still in create_db , we execute the database creation query by passing it a string with the proper SQL code. This is wrapped in a try/except/else block in case something goes wrong. Usually we first execute the query and afterwards commit it to the database, but “CREATE DATABASE” statements require the commit to be automatic, hence using conn.autocommit in create_db .
Okay, the “houses” database is created, the next step is to create the “house” and “person” tables. First connect to the newly-created database on line 64 and create a new cursor on line 65. Instead of passing each argument separately (host, database, user and password), we use the ** operator to unpack each key-value pair on its own.
Then, we create the “house” and “person” tables. We write the necessary SQL queries and call the create_table function on lines 63 to 74 and 77 to 85, respectively. create_table simply executes and commits the queries, wrapped in a try/except/else block. If nothing bad happens, the changes are committed to the database inside the else block, otherwise the exception and query are output in the except block. In case an exception is raised we also rollback any changes that were not committed.
At the very end of the script we close all active connections and cursors.
Insert data into the database
At this point all the infrastructure is setup and we can move on to insert data.
Our goal is to insert pandas DataFrames in the database. However, even if we are only inserting a handful of rows in each table, the way the insert_data function is written allows for inserting DataFrames with hundreds of rows in the database. This is achieved by using execute_values instead of the basic execute function, allowing for insertion in batches intead of a single gigantic query. No matter the number of rows in the DataFrame, execute_values will only insert 100 rows at a time, the page_size value specified.
Okay, but let me give you a bullet point summary of the steps taken:
- Connect to the database;
- Create a DataFrame for the “house” data;
- Insert the “house” data in its table;
- Create a DataFrame for the “person” data; and
- Insert the “person” data in its table.
And there’s not much more to it. The basic logic when working with psycopg2 lies in the execute/commit/rollback trio of actions. You first execute a SQL query and if everything goes well you commit the changes, otherwise you rollback the changes. Whatever you’re trying to do, your go-to actions are execute, commit and rollback.
Just two notes to take in consideration for insertions:
- execute_values only accepts data as tuples, hence the transformation on line 15; and
- The query string must have a %s so that execute_values can replace the tuple of data into that query string (lines 54 and 63). If you are not familiar with this syntax for string replacement, check out this article to learn more.
Data extraction
Finally, we can write some SELECT queries to extract data!
I mentioned execute, commit and rollback, but there is another common function used when working with psycopg2: fetch. When the query executed returns some data, fetch is how we get that data. And just like execute has variations, fetch has some too. We can use fetchone to get the next row of data, fetchall to get all rows at once or fetchmany to get a batch of rows at a time.
In this case we are using fetchmany . Again, each table has only a handful of rows, but I wrote the code in a way that is easy for you to refer back to in the future if needed. Just like with execute_values , by using fetchmany you can balance the memory costs of working with large amounts of data and performance of the code.
And just as we inserted data from DataFrames, now we want to extract data into DataFrames. For that, we start by creating an empty DataFrame with only its column names specified. This is important because these names must match the names of the columns in the query results.
Inside of get_data_from_db (lines 28 to 58), we execute the SQL query and keep fetching the next 100 rows as long as there are rows left. Of course, in this case we only fetch once per query. For each row of the batch fetched, we save it in a list of dictionaries that map columns to the values, that is, each dictionary represents one of the rows fetched. Then, this list of dictionaries is appended to the DataFrame. This way the data returned is always appended to the DataFrame. Once fetch has reached the last row of data, it will return an empty list. At that point we stop fetching by breaking out of the loop.
At this point the complexity depends on your SQL code rather than Python and psycopg2. For instance, the first two SELECT queries simply return all data from the two tables available, but the third query joins both tables to return the names of the people and the address of their house. Still an easy query, but slightly more complicated than a SELECT * .
Lastly, here are the DataFrames of extracted data:
Conclusion
Overall, I think the code in this demo shows a good degree of separation between the SQL and the Python code, while staying flexible enough. Plus, this code should be more than enough to get you started in integrating PostgreSQL operations in your Python code.
To recap, this demo went through the following database operations in Python (using the psycopg2 driver):
- Creating a database;
- Creating tables;
- Inserting data (from pandas DataFrames);
- Extracting data (into pandas DataFrames); and
- Batch insertion and extraction.
If you also want to look into using an ORM as an alternative to a database driver, I recommend starting with this introductory article about SQLAlchemy. You can also read the official documentation for more about SQLAlchemy and PostgreSQL here.
Connect Python and PostgreSQL using psycopg2
By Thomas Gumbricht January 06, 2018 January 31, 2020 Tweet Like +1
Introduction
This post on how to Install psycopg2, create connection to Postgres and createdb is not needed if you clone or download Karttur´s GeoImagine Framework from the GitHub.com. This post, however, contains the details on how to create a more secure database connection. And it also covers both general and detailed instructions that are useful for other setups with psycopg2 as well as when updates are required.
In earlier posts I described how to install Eclipse IDE for Python development after installing Anaconda Python as the Python interpreter, and then I installed PostgreSQL and PostGIS. This post describes how to connect Python with the Postgres server and create a new database in Postgres using Python.
Environments and packages
The Anaconda Python distribution contains a lot of Python packages (a package is a self contained collection of python modules [ .py files] that performs given tasks). When working with Python you can find packages for almost all your needs, either that can be used out of the box, or after some modification. There is of course a package for connecting Python to Postgres server — psycopg2 .
Psycopg2 as a package in a virtual environments
If you followed the post on Conda virtual environments you should have a virtual environment for Python (e.g. geoimagine001) setup on your local machine. And it should include psycopg2 as this package was added to the list of default packages to install with any new virtual environment.
Add psycopg2 to a virtual environment
if you did setup a virtual python environment as described in the previous section, you can just skip this section.
You can install new packages into your environment in the usual way that conda packages are installed. Make sure that the environment into which you want to install a package (psycopg2 in this case) is the active environment:
$ conda activate geoimagine001
The terminal prompt should now point at your environment. and you can enter the install command:
(geoimagine001) … $ conda install -c anaconda psycopg2
or tell conda under which environment to install the packages:
(base) …$ conda install –name geoimagine001 -c anaconda psycopg2
Once the installation is finished you should see the installed packages under the site-packages path. On my machine that is:
Add psycopg2 to the Anaconda base
This section describes how to add psycopg2 to your conda base environment. If you have a virtual environment as describes above, you can also skip this section.
If you installed Anaconda and set up Eclipse as described in the earlier posts, the Python distribution that Eclipse uses is under:
where ‘path’ is the path you choose for installing Anaconda (if you use another python version then 3.7, the version number is different). Your Python path contains a folder called site-packages . The packages in that folder are available for direct use in the Eclipse IDE.
In my Anaconda installation, psycopg2 was not installed under site-packages , but included as an .egg file — a kind of package installer. To install psycopg2 , with Anaconda set up as described in the earlier post, all you have to do to add psycopg2 to your site-packages is to execute the Terminal command:
$ conda install psycopg2
To check if psycopg2 is in place, list the package content in the Terminal :
You can also open the folder in Finder . Copy the full path to the site-packages folder, and when in Finder , simultaneously press the keys for ‘command(cmd)’+’Shift’+’G’, in the Go to the Folder that opens, just paste the full path and click Go .
Security
If you are only going to use your Postgres database as localhost (on your own machine), security is less important. But if you want to protect your data you must set some level of security. The solution I use is primarily for macOS and UNIX/Linux systems, and is not very advanced. I use a combination of storing my password in my home directory (
) combined with a simple encryption.
Create a file in your home directory (
) called .netrc that defines your credentials. An earlier post describes how to use the Terminal for creating and editing files in detail. In the Terminal go to your home directory:
Then start the Terminal text editor pico for editing/creating the file:
Enter the two lines below (but with your role/user and password), one for the default user (if you installed Postgres with Homebrew the default user is the same as your user on the local machine — ‘yourUser’), and one for the production user (‘prodUser’) if you followed my suggestions in the previous post. If you only have the default user, enter the same login and password in both lines.
Exit pico (ctrl-X) and save the file (click Y when asked to save). You probably have to change the read and write permissions for .netrc , which you do by executing the following Terminal command:
$ chmod og-rw .netrc
With this solution your credentials will only be explicitly written out in a hidden file.
Set Postgres connection in Python (Eclipse)
Start Eclipse , and you should come back to the workbench that you created in a previous post. Repeat the steps outlined in that post to create a new PyDev project, with a PyDev package and a sub-package.
Create a project (call it what you want):
File : New : Project : PyDev project
Create a new PyDev Package and call it ‘geoimagine’:
File : New : PyDev Package
Create a new sub-package and call it ‘db_setup’:
File : New : PyDev Package
In the dialog window you should see the main package (‘geoimagine’) already filled ( geoimagine ). Use Python syntax and add a dot (.) followed by the name of the sub-package (‘db_setup’) ( geoimagine.db_setup ).
PyDev Class module
In the sub-package create a Python module called ‘db_setup_class’ (set type to Class in the Template ) window that appears after you click click Finish :
File : New : PyDev Module
In the db_setup_class.py module enter (or copy and paste) the following code (replace the default Class that was created with the module):
The import psycopg2 you installed above, whereas the package base64 is a Python core package. You will use it to send your password in encrypted form. When you call the module class PGsession it expects a variabe called ‘query’. This variable should be in the form of a dictionary, with pairs of keys and values:
You are going to use the query dictionary for sending the user and (encrypted) password to PGsession . But you must first create the Main PyDev module from which you will do that.
Connect and create a new DB
Create a second PyDev module, called db_setup_main in the db_setup package. Below are two versions, but the second version is just in case your system can not handle the default .netrc file using the netrc package (the second version instead includes an explicit file reader, but you should not need to worry about that).
The alternative version (with an explicit reader for the .netrc ) is only a backup if the above version does not work properly. Click the button to see the code.
Show/Hide alternative code
Run your package
With the main module ( db_setup_main.py ) open in the Eclipse main pane, kick off your code from the Eclipse menu:
If everything worked out, the Console pane should return:
If you run it again, the database ‘prodDB’ will be listed in ‘Databases []’ in the Console , and not be created again.
You can look at the details for ‘prodDB’ in pgAdmin (installing and setting up pgAdmin is described in this post). Connect pgAdmin to your Postgres server and expand the Browser menu by clicking on the small ‘+’ signs:
Servers : postgres : Databases : ‘prodDB’
If you click ‘prodDB’ (you might also need to click the tab SQL in the right pane of pgAdmin) you will see how ‘prodDB’ was defined:
Encoding, collation, and Ctype are set to UTF8 (the most universal set of characters), and tablespace is the to pg_default. The ‘-1’ for connection limit means unlimited. You can set all this parameters in the SQL for CREATEDB if you want, but I prefer the most universal, which is also the postgres default.
Базы данных в Python: как подключить PostgreSQL и что это такое
Во время разработки приложений часто нужно подключать и использовать базы данных для хранения информации. Самая распространенная база данных — PostgreSQL, поэтому мы расскажем, как работать в Python именно с ней. Для этого существует множество модулей, например:
Мы расскажем именно про модуль Psycopg2. И выбрали мы его по таким причинам:
- Распространенность — Psycopg2 использует большинство фреймворков Python
- Поддержка — Psycopg2 активно развивается и поддерживает основные версии Python
- Многопоточность — Psycopg2 позволяет нескольким потокам поддерживать одно и то же соединение
Установка Psycopg2
Для начала работы с модулем достаточно установить пакет при помощи pip:
Если в вашем проекте используется poetry, то при первоначальной настройке проекта нужно добавить psycopg2-binary в зависимости. Для добавления в уже существующий проект воспользуйтесь командой:
Использование Psycopg2
Подключение к БД:
Для подключения к существующей базе данных необходимо знать основную информацию о вашей БД. Если вы не знаете, где ее взять, то пройдите сначала наш большой курс по Основам баз данных:
- Username — имя пользователя, которое вы используете для работы с PostgreSQL
- Password — пароль, который используется пользователем
- Host Name — имя сервера или IP-адрес, на котором работает PostgreSQL
- Database Name — имя базы данных, к которой мы подключаемся.
Для подключения к базе данных мы используем метод connect() , которому в качестве аргументов передаются вышеперечисленные данные:
Также подключение к базе данных может осуществляться с помощью Connection URI:
Читайте также: Вышел Python 3.11.0. В два раза быстрее, c детальным описанием ошибок и кучей новых типов
Взаимодействие Python с PostgreSQL
Итак, подключение к базе данных успешно выполнено. Дальше мы будем взаимодействовать с ней через объект cursor , который можно получить через метод cursor() объекта соединения. Он помогает выполнять SQL-запросы из Python.
С помощью cursor происходит передача запросов базе данных:
Для получения результата после выполнения запроса используются следующие команды:
- cursor.fetchone() — вернуть одну строку
- cursor.fetchall() — вернуть все строки
- cursor.fetchmany(size=10) — вернуть указанное количество строк
Хорошей практикой при работе с базой данных является закрытие объекта cursor и соединения с базой. Для автоматизации этого процесса удобно взаимодействовать через контекстный менеджер, используя конструкцию with :
В тот момент, когда объект cursor выходит за пределы конструкции with , происходит его закрытие и освобождение связанных с ним ресурсов.
По умолчанию результат возвращается в виде кортежа. Такое поведение возможно изменить, передав параметр cursor_factory в момент открытия объекта cursor , например, использовать NamedTupleCursor. Это вернет данные в виде именованного кортежа:
Выполнение запросов
Psycopg2 преобразует переменные Python в SQL значения с учетом их типа. Все стандартные типы Python адаптированы для правильного представления в SQL.
Передача параметров в SQL-запрос происходит с помощью подстановки плейсхолдеров %s и цепочки значений в качестве второго аргумента функции:
Подстановка значений в SQL-запрос используется для того, чтобы избежать атак типа SQL Injection. Также несколько полезных советов по построению запросов:
- Плейсхолдер должен быть %s даже если тип подставляемого значения отличается от строки
- Не заключайте плейсходер в кавычки
- Если в запросе используется знак % , он должен быть указан как %%
Продолжайте учиться: На Хекслете есть несколько больших профессий, интенсивов и треков для джуниоров, мидлов и даже сеньоров: они позволят не только узнать новые технологии, но и прокачать уже существующие навыки