Python connect to psql

PostgreSQL – Connect To PostgreSQL Database Server in Python

The psycopg database adapter is used to connect with PostgreSQL database server through python.

First, use the following command line from the terminal:

If you have downloaded the source package into your computer, you can use the setup.py as follows:

python setup.py build sudo python setup.py install

Create a new database

First, log in to the PostgreSQL database server using any client tool such as pgAdmin or psql.

Second, use the following statement to create a new database named suppliers in the PostgreSQL database server.

CREATE DATABASE suppliers;

Connect to the PostgreSQL database using the psycopg2

To connect to the suppliers database, you use the connect() function of the psycopg2 module.

The connect() function creates a new database session and returns a new instance of the connection class. By using the connection object, you can create a new cursor to execute any SQL statements.

To call the connect() function, you specify the PostgreSQL database parameters as a connection string and pass it to the function like this:

conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")

Or you can use a list of keyword arguments:

conn = psycopg2.connect( host="localhost", database="suppliers", user="postgres", password="Abcd1234")

The following is the list of the connection parameters:

database: the name of the database that you want to connect.

user: the username used to authenticate.

password: password used to authenticate.

host: database server address e.g., localhost or an IP address.

port: the port number that defaults to 5432 if it is not provided.

To make it more convenient, you can use a configuration file to store all connection parameters.

The following shows the contents of the database.ini file:

[postgresql] host=localhost database=suppliers user=postgres password=SecurePas$1

By using the database.ini, you can change the PostgreSQL connection parameters when you move the code to the production environment without modifying the code.

Notice that if you git, you need to add the database.ini to the .gitignore file to not committing the sensitive information to the public repo like github. The .gitignore file will be like this:

The following config() function read the database.ini file and returns connection parameters. The config() function is placed in the config.py file:

#!/usr/bin/python from configparser import ConfigParser def config(filename='database.ini', section='postgresql'): # create a parser parser = ConfigParser() # read config file parser.read(filename) # get section, default to postgresql db = <> if parser.has_section(section): params = parser.items(section) for param in params: db[param[0]] = param[1] else: raise Exception('Section not found in the file'.format(section, filename)) return db

The following connect() function connects to the suppliers database and prints out the PostgreSQL database version.

#!/usr/bin/python import psycopg2 from config import config def connect(): """ Connect to the PostgreSQL database server """ conn = None try: # read connection parameters params = config() # connect to the PostgreSQL server print('Connecting to the PostgreSQL database. ') conn = psycopg2.connect(**params) # create a cursor cur = conn.cursor() # execute a statement print('PostgreSQL database version:') cur.execute('SELECT version()') # display the PostgreSQL database server version db_version = cur.fetchone() print(db_version) # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() print('Database connection closed.') if __name__ == '__main__': connect()

First, read database connection parameters from the database.ini file.

Next, create a new database connection by calling the connect() function.

Then, create a new cursor and execute an SQL statement to get the PostgreSQL database version.

After that, read the result set by calling the fetchone() method of the cursor object.

Finally, close the communication with the database server by calling the close() method of the cursor and connection objects.

Execute the connect.py file

To execute the connect.py file, you use the following command:

You will see the following output:

Connecting to the PostgreSQL database. PostgreSQL database version: ('PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit', ) Database connection closed.

Troubleshooting

The connect() function raises the DatabaseError exception if an error occurred. To see how it works, you can change the connection parameters in the database.ini file.

For example, if you change the host to localhosts, the program will output the following message:

Connecting to the PostgreSQL database. could not translate host name "localhosts" to address: Unknown host

The following displays error message when you change the database to a database that does not exist e.g., supplier:

Connecting to the PostgreSQL database. FATAL: database "supplier" does not exist

If you change the user to postgress, it will not be authenticated successfully as follows:

Connecting to the PostgreSQL database. FATAL: password authentication failed for user "postgress"

Источник

Работа с PostgreSQL в Python

17 Ноя. 2018 , Python, 249487 просмотров, How to Work with PostgreSQL in Python

PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.

Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.

Установка

Тут всё просто, выполняем команду:

Для тех, кто не хочет ставить пакет прямо в системный 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) 

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

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

Интересные записи:

• Pyenv: удобный менеджер версий python
• Работа с MySQL в Python
• Почему Python?
• Обзор Python 3.9
• FastAPI, asyncio и multiprocessing
• Celery: начинаем правильно
• Что нового появилось в Django Channels?
• Django Channels: работа с WebSocket и не только
• Введение в logging на Python
• Руководство по работе с HTTP в Python. Библиотека requests
• Как написать Telegram бота: практическое руководство
• Python-RQ: очередь задач на базе Redis
• Участие в подкасте TalkPython
• Введение в pandas: анализ данных на Python
• Разворачиваем Django приложение в production на примере Telegram бота
• Авторизация через Telegram в Django и Python
• Итоги первой встречи Python программистов в Алматы
• Обзор Python 3.8
• Строим Data Pipeline на Python и Luigi
• Видео презентации ETL на Python

Источник