Python work with postgresql

Python PostgreSQL Tutorial Using Psycopg2

Updated on: March 9, 2021 | 41 Comments

This Python PostgreSQL tutorial demonstrates how to use the Psycopg2 module to connect to PostgreSQL and perform SQL queries, database operations. There are many ways we can connect to a PostgreSQL database from Python, and in this tutorial, we’re going to explore several options to see how to achieve this.

Below is the list of available Python modules to work with the PostgreSQL database server.

  • Psycopg2
  • pg8000
  • py-postgresql
  • PyGreSQL
  • ocpgdb
  • bpgsql
  • SQLAlchemy . SQLAlchemy needs any of the above to be installed separately.

Note: Above all modules adhere to Python Database API Specification v2.0 (PEP 249). This API is designed to encourage and maintain the similarity between the Python database modules to access databases. In other words, the syntax, method, and way of access the database are the same in all the above modules.

We stick to the Psycopg2 because it is arguably the most popular and stable module to work with PostgreSQL. Also, We are using Psycopg2 to work with PostgreSQL because of the following reasons.

  • It is used in most of the Python and Postgres frameworks.
  • It is also actively maintained and supports Python’s primary version, i.e., Python 3 and Python 2.
  • It is thread-safe and designed for heavily multi-threaded applications. Note, threads can share the connections.

This Python PostgreSQL guide mainly focuses on the followings

  • Installing Psycopg2 and use its API to access the PostgreSQL database
  • Perform data insertion, data retrieval, data update, and data deletion through Python application.
  • Next, it will cover PostgreSQL transaction management, connection pooling, and error-handling techniques to develop robust Python programs with PostgreSQL.

Let’s dive right in.

Table of contents

Install Psycopg2 using the pip command

You need to install the current version of Psycopg2 (2.8.6) on your machine to use PostgreSQL from Python. This module is available on pypi.org.

Using the following pip command, you can install Psycopg2 on any operating system, including Windows, macOS, Linux, Unix, and Ubuntu.

You can also install a specific version using the following command.

If you are facing pip install error like “connection error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)”. You can resolve this error by setting pypi.org and files.pythonhosted.org as trusted hosts. If you are facing a pip install error Please try following the command.

The current psycopg2 module supports:

  • Python version 2.7, and Python 3 versions from 3.4 to 3.8
  • PostgreSQL server versions from 7.4 to 12
  • PostgreSQL client library version from 9.1

Verify Psycopg2 installation

You should get the following messages after running the above command.

  • Collecting psycopg2
  • Downloading psycopg2-2.8.6
  • Installing collected packages: psycopg2
  • Successfully installed psycopg2-2.8.6

Please use the following command to install Psycopg2 If you are using anaconda.

Python PostgreSQL database connection

In this section, we will learn how to connect to PostgreSQL through Python using Psycopg2.

Arguments required to connect PostgreSQL database from Python

You need to know the following detail of the PostgreSQL server to perform the connection.

  • Username: The username you use to work with PostgreSQL, The default username for the PostgreSQL database is Postgres.
  • Password: Password is given by the user at the time of installing the PostgreSQL.
  • Host Name: This is the server name or Ip address on which PostgreSQL is running. if you are running on localhost, then you can use localhost, or its IP, i.e., 127.0.0.0
  • Database Name: Database name to which you want to connect. Here we are using Database named “postgres_db“.
Читайте также:  0xc0000011b windows не удается подключиться к принтеру

How to Connect to PostgreSQL in Python

    Install Psycopg2 module

Install and import psycopg2 module. Import using a import psycopg2 statement so you can use this module’s methods to communicate with the PostgreSQL database.

Use the connect() method

Use the psycopg2.connect() method with the required arguments to connect MySQL. It would return an Connection object if the connection established successfully

Use the cursor() method

Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python.

Use the execute() method

The execute() methods run the SQL query and return the result.

Extract result using fetchall()

Use cursor.fetchall() or fetchone() or fetchmany() to read query result.

Close cursor and connection objects

use cursor.clsoe() and connection.clsoe() method to close PostgreSQL connections after your work completes

Python example to connect PostgreSQL database

To connect the PostgreSQL database and perform SQL queries, you must know the database name you want to connect to, and if you have not created any database, I advise you to create one before proceeding further.

You should get the following output after connecting to PostgreSQL from Python

Important points

  • In our example, we are executing a SELECT version(); query to fetch the PostgreSQL version.
  • Using the Error class of Psycopg2, we can handle any database error and exception while working with PostgreSQL from Python. Using this approach, we can make our application robust.
  • The error class helps us to understand the error in detail. It returns an error message and error code if any.
  • We can create as many cursors as we want from a single connection object. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors.
  • Cursors are not thread-safe.
  • We can retrieve query result using cursor methods such as fetchone() , fetchmany() , fetcthall() .

try-except-finally block

  • We placed all our code in the try-except block to catch the database exceptions and errors that may occur during this process.

cursor.close() and connection.close()

  • It is always good practice to close the cursor and connection object once your work gets completed to avoid database issues.

Create a PostgreSQL table from Python

This section will learn how to create a table in PostgreSQL from Python. In this example, we will create a “Mobile” table in PostgreSQL.

Output

Note: Note: In the end, we are committing our changes to the database using the commit() method.

The mapping between Python and PostgreSQL types

There is default mapping specified to convert Python types into PostgreSQL equivalent, and vice versa. Whenever you execute a PostgreSQL query using Python following table is used by psycopg2 to return the result in the form of Python objects.

Python PostgreSQL
None NULL
bool bool
float real or double
int smallint
integer
bigint
Decimal numeric
str varchar
text
date date
time time
timetz
datetime timestamp
timestamptz
timedelta interval
list ARRAY
tuple Composite types
IN syntax
dict hstore

Constants and numeric conversion

When you try to insert Python None and boolean values such as True and False into PostgreSQL, it gets converted into the proper SQL literals. The same case is with Python numerical types. It gets converted into equivalent PostgreSQL types.

For example, When you execute an insert query, Python numeric objects such as int , long , float , Decimal are converted into a PostgreSQL numerical representation. When you read from the PostgreSQL table, integer types are converted into an int , floating-point types are converted into a float , numeric/Decimal are converted into Decimal .

Perform PostgreSQL CRUD operations from Python

Now, we created a “mobile” table. Now let’ see how to perform insert, select, update, and delete PostgreSQL queries from Python.

Now, Let’s see the example.

Output:

Please refer to the following tutorials to have more information on insert, update, and delete data from the PostgreSQL table using Python.

In the following tutorial, we will teach you how to pass parameters to SQL queries. We will learn how to use a parameterized query to pass Python variables and dynamic data into SQL queries.

  • Insert data into the PostgreSQL Table from Python: Learn how to execute the SQL insert query from a Python application to add a record to the PostgreSQL table.
  • Select data from PostgreSQL Table from Python: Learn how to execute a SQL select query from a Python application to fetch rows from the database table. We will also learn how to use fetchall(), fetchmany() and fetchone() methods to read a limited number of rows from the table.
  • Update data of PostgreSQL table from Python: Learn how to execute SQL update query from Python application to modify a PostgreSQL table’s records.
  • Delete data from PostgreSQL table from Python: Learn how to execute SQL delete query from Python application to delete records from a PostgreSQL table.

Working with PostgreSQL date and time in Python

This section will demonstrate how to work with PostgreSQL date and timestamp data types in Python and vice-versa. Most of the time, we work with date and time data. We insert date and time into the table and also read from it in our application whenever required.

In a usual scenario, when you execute the insert query with the datetime object, the Python psycopg2 module converts it into a PostgreSQL timestamp format to insert it in the table.

And when you execute a SELECT query from Python to read timestamp values from the PostgreSQL table, the psycopg2 module converts it into a datetime object.

We are using the “Item” table for this demo. Please copy and execute the below query on your PostgreSQL query tool to have adequate data for this operation.

Let’s understand this scenario with a simple example. Here we will read purchase_time column from the PostgreSQL table and convert it into a Python datetime object.

Output:

Call PostgreSQL Function and Stored Procedure from Python

PostgreSQL function and the Stored procedure can perform different operations, such as data manipulation or data retrieval. We can execute such functions from Python.

Python PostgreSQL Transaction management

In this article, we will see how to manage PostgreSQL transactions from Python using psycopg2.

  • Learn how to use the commit() and the rollback() method of a connection class to manage database transactions and maintain the ACID properties.
  • Also, learn how to change the PostgreSQL transaction isolation level from Python.

Python PostgreSQL Connection Pooling

This section will let you know what a connection pool is and how to implement a PostgreSQL database connection pool using Psycopg2 in Python. Using Psycopg2, we can implement a connection pool for simple as well as multithreaded applications.

Use the Connection pool to increase the speed and performance of database-centric applications.

Python PostgreSQL Exercise Project

Solve our free Python database exercise project to practice and master the PostgreSQL database operations using Python.

In this exercise project, We will implement the Hospital Information System, which covers all database operations. In this Python database exercise, we will do database CRUD operations From Python. This practice exercise also covers transaction management and error-handling techniques.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

Источник

Работа с PostgreSQL в 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).

💌 Присоединяйтесь к рассылке

Понравился контент? Пожалуйста, подпишись на рассылку.

Источник

Поделиться с друзьями
КомпСовет
Adblock
detector