Работа с PostgreSQL в Python
17 Ноя. 2018 , Python, 246189 просмотров, 
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).
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 даже если тип подставляемого значения отличается от строки
- Не заключайте плейсходер в кавычки
- Если в запросе используется знак % , он должен быть указан как %%
Продолжайте учиться: На Хекслете есть несколько больших профессий, интенсивов и треков для джуниоров, мидлов и даже сеньоров: они позволят не только узнать новые технологии, но и прокачать уже существующие навыки
Use cases of the DB API for a PostgreSQL database
Python lets you write programs that access, display and update the information in the database with minimal effort.
There are lots of commercial and freeware databases available, and most of them provide Structured Query Language (SQL) for retrieving and adding information. However, while most databases have SQL in common, the details of how to perform an SQL operation vary. The various individuals who wrote the Python database modules invented their own interfaces, and the resulting proliferation of different Python modules caused problems: no two of them were exactly alike, so if you decided to switch to a new database product, you had to rewrite all the code that retrieved and inserted data.
To solve the problem, a Special Interest Group (or SIG) for databases was formed. After some discussion, the Database SIG produced a specification for a consistent interface to relational databases — the DB-API.
Python DB-API
Thanks to DB-API specification, there’s only one interface to learn. Porting code to use a different database product is much simpler, often requiring the change of only a few lines.
This API has been defined to encourage similarity between the Python modules that are used to access databases. By doing this, we hope to achieve a consistency leading to more easily understood modules, code that is generally more portable across databases, and a broader reach of database connectivity from Python.
Current version of DB-API is 2.0 (PEP 249) replaced older DB-API 1.0 version (PEP 248). Modules for most known relational databases now conform to DB-API 2.0 (or at least 1.0)
PostgreSQL
PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems.
It includes most SQL92 and SQL99 data types. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others.
Python interfaces to PostgreSQL
PyGreSQL
PyGreSQL is an open-source Python module that interfaces to a PostgreSQL database. It embeds the PostgreSQL query library to allow easy use of the powerful PostgreSQL features from a Python script.
PyGreSQL homepage
pyPgSQL
pyPgSQL is a package of two modules that provide a Python DB-API 2.0 compliant interface to PostgreSQL databases. The first module, libpq, exports the PostgreSQL C API to Python. This module is written in C and can be compiled into Python or can be dynamically loaded on demand. The second module, PgSQL, provides the DB-API 2.0 compliant interface.
pyPgSQL homepage
psycopg2
psycopg2 is a PostgreSQL database adapter for the Python programming language. Its main advantages are that it supports the full Python DBAPI 2.0 and it is thread safe at level 2. It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a conspicuous number of concurrent INSERTs or UPDATEs.
psycopg homepage
Basic examples
All examples tested on Python 2.4.4 and PostgreSQL 8.2 under Debian GNU/Linux 4.0, but should work on greater Python versions and other operational systems.
This part will take you on a fast tour of the main features of DB-API 2.0, showing howto:
- open a connection to the database
- create cursor
- execute various query
- close connection
All listed examples include SQL and DB-API version of actions.
Getting Started
For the first time we need to run python interpreter, import database module (e.g. psycopg2) and connect to database. Also we need to obtain a cursor object, which acts as a handle for a given SQL query; it allows retrieval of one or more rows of the result, until all the matching rows have been processed.
Python DB-API:
Create database
Usually your system administrator must create databases for you, but if you use your own PostgreSQL server you can do it without assistance.
SQL:
Create table
The created database is empty, so it doesn’t contain any user tables or data. We must create a new table and specify its columns.
SQL:
DB-API 2.0:
Add data
To insert data into the table we can use the execute method of the cursor object. Use the commit() method to commit, i.e. make permanent, the changes to the database.
SQL:
DB-API 2.0:
Retrieve data
Use the execute function to run sql SELECT queries.
SQL:
DB-API 2.0:
Delete data
Use the execute function to run sql DELETE or DROP TABLE. You don’t need to delete all the rows from the table before dropping it.
SQL:
DB-API 2.0:
Close Connection
When you finish work with a cursor or database, closing the cursor and connection is good practice (but isn’t necessary).
DB-API 2.0:
Advanced examples
Advanced querying
You can use all the normal SQL operators like WHERE, GROUP BY, ORDER BY, etc in queries which execute through the execute() method of a cursor object. But be careful when you use database dependent operators, because your code will depends on used database.
SQL:
DB-API 2.0:
Transactions
For databases that support transactions, the Python interface silently starts a transaction when the cursor is created. The commit() method commits the updates made using that cursor, and the rollback() method discards them. Each method then starts a new transaction. Some databases don’t have transactions, but simply apply all changes as they’re executed. On these databases:
- commit() does nothing, but you should still call it in order to be compatible with those databases that do support transactions.
- rollback() should throw an exception or not be implemented.
SQL:
DB-API 2.0:
References
- The Python DB-API interface http://www.amk.ca/python/writing/DB-API.html by Andrew Kuchling
- Python Database API Specification v2.0 http://www.python.org/dev/peps/pep-0249/
- Accessing Databases using the Python DBAPI-2.0 http://www.initd.org/pub/software/psycopg/dbapi20programming.pdf
UsingDbApiWithPostgres (last edited 2010-02-15 14:47:49 by 94-194-202-224 )