Работа с PostgreSQL в Python
17 Ноя. 2018 , Python, 258307 просмотров, How to Work with PostgreSQL in Python
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
pip install psycopg2Для тех, кто не хочет ставить пакет прямо в системный 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) Интересные записи:
- Руководство по работе с HTTP в Python. Библиотека requests
- Обзор Python 3.9
- Что нового появилось в Django Channels?
- Работа с MySQL в Python
- Почему Python?
- Celery: начинаем правильно
- Введение в logging на Python
- Django Channels: работа с WebSocket и не только
- FastAPI, asyncio и multiprocessing
- Pyenv: удобный менеджер версий python
- Авторизация через Telegram в Django и Python
- Разворачиваем Django приложение в production на примере Telegram бота
- Python-RQ: очередь задач на базе Redis
- Введение в pandas: анализ данных на Python
- Как написать Telegram бота: практическое руководство
- Django, RQ и FakeRedis
- Итоги первой встречи Python программистов в Алматы
- Обзор Python 3.8
- Интеграция Trix editor в Django
- Участие в подкасте TalkPython
- Строим Data Pipeline на Python и Luigi
- Авторизация через Telegram в Django приложении
- Видео презентации ETL на Python
Как создать postgreSQL на основе json?
пытаюсь добавить данные из json в postgreSQL, выдаёт такую ошибку. В чём может быть дело, подскажите пожалуйста.
> Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/ninja/operation.py", line 95, in run result = self.view_func(request, **values) File "/Users/abra/Desktop/WorkAndArt/abra/abra/ServerFF/FFServer/FFServer/urls.py", line 102, in createDataBase cur.execute(sql_string) psycopg2.errors.SyntaxError: syntax error at or near "[" LINE 2: . 437\u043e\u0442\u0438\u043a, 120 \u043c\u043b.)', [ @api.get("/createDataBase") def createDataBase(request): with open('/Users/abra/Desktop/WorkAndArt/abra/abra/ServerFF/FFServer/FFServer/FFArchive.json') as json_data: productJson = json.load(json_data) # use JSON loads to create a list of records # create a nested list of the records' values values = [list(x.values()) for x in productJson] # get the column names columns = [list(x.keys()) for x in productJson][0] # value string for the SQL string values_str = "" # enumerate over the records' values for i, record in enumerate(values): # declare empty list for values val_list = [] # append each value to a new list of values for v, val in enumerate(record): if type(val) == str: val = str(Json(val)).replace('"', '') val_list += [str(val)] # put parenthesis around each record string values_str += "(" + ', '.join(val_list) + "),\n" # remove the last comma and end SQL with a semicolon values_str = values_str[:-2] + ";" # concatenate the SQL string table_name = "PRODUCTS" sql_string = "INSERT INTO %s (%s)\nVALUES %s" % ( table_name, ', '.join(columns), values_str ) con = psycopg2.connect( database="postgres", user="abra", password="abra", host="localhost", port="5432" ) print("Database opened successfully") cur = con.cursor() #УДАЛЯЕМ ПРЕДИДУЩИЮ cur.execute('''DROP TABLE PRODUCTS''') # СОЗДАЁМ ТАБЛИЦУ cur.execute('''CREATE TABLE PRODUCTS (code TEXT NOT NULL, name TEXT NOT NULL, details json[], articul TEXT NOT NULL, price TEXT NOT NULL, quantity TEXT NOT NULL, stores json[], brand json, description TEXT NOT NULL, image_url json[], groups json[], certificates TEXT NOT NULL, portion TEXT NOT NULL, SubjectToCertification boolean NOT NULL );''') print("Table created successfully") cur.execute(sql_string) return "Server was creates with table: " + str(columns) [ < "code": "8308b6d2-d9cc-11e9-802f-001e582bf58c#6ddfd039-1b97-11ea-b8c5-b42e99659391", "name": "SHAABOOM PUMP SHOT, 120 мл (Экзотик, 120 мл.)", "details": [ < "value": "Экзотик", "detail_type": < "name": "Вкус" >>, < "value": "120 мл.", "detail_type": < "name": "Упаковка" >> ], "articul": "018189", "price": 160, "quantity": 79, "stores": [ < "store": "ТренажерыСпорттовары", "quantity": "48" >, < "store": "ИП Проспект", "quantity": "31" >], "brand": < "name": "Kevin Levrone" >, "description": "Надоело бороться с усталостью на каждой тренировке? Тогда воспользуйтесь инновационным предтренировочным комплексом SHAABOOM PUMP от компании KEVIN LEVRONE. \n Уже с первой порции вы прочувствуете невероятный прилив сил и энергии, который позволит в разы поднять продуктивность тренировок. При регулярном использовании атлеты также отмечают, что предтреник положительно влияет на выносливость, силовые показатели и рост мышечной массы. \n\nИнгредиенты
\n\n\n \n\n\n\n\n\n\n\nПорция 30 мл \n \n\nКоличество порций в ампуле - 4 \n \n\n \n \n\n \n\nБета-Аланин \n3500 мг \n \n\n, < "osPath": "productImage/018189_0.png" >, < "osPath": "productImage/018189_2.png" >, < "osPath": "productImage/018189_3.png" >], "groups": [ < "name": "СПОРТИВНОЕ ПИТАНИЕ" >, < "name": "Предтренировочные комплексы" >], "certificates": "", "portion": 4, "SubjectToCertification": true >]
column names: ['code', 'name', 'details', 'articul', 'price', 'quantity', 'stores', 'brand', 'description', 'image_url', 'groups', 'certificates', 'portion', 'SubjectToCertification'] INSERT INTO PRODUCTS (code, name, details, articul, price, quantity, stores, brand, description, image_url, groups, certificates, portion, SubjectToCertification) VALUES ('8308b6d2-d9cc-11e9-802f-001e582bf58c#6ddfd039-1b97-11ea-b8c5-b42e99659391', 'SHAABOOM PUMP SHOT, 120 мл (Экзотик, 120 мл.)', [>, >], '018189', 160, 79, [, ], , 'Надоело бороться с усталостью на каждой тренировке? Тогда воспользуйтесь инновационным предтренировочным комплексом SHAABOOM PUMP от компании KEVIN LEVRONE. Уже с первой порции вы прочувствуете невероятный прилив сил и энергии, который позволит в разы поднять продуктивность тренировок. При регулярном использовании атлеты также отмечают, что предтреник положительно влияет на выносливость, силовые показатели и рост мышечной массы. Ингредиенты
120 мл Порция 30 мл Количество порций в ампуле - 4 Бета-Аланин 3500 мг , , , ], [, ], '', 4, True);
Как записать в БД в формате json определённые индексы и значения из списка на python?
Суть вопроса в следующем,
на php достаточно просто записать такой массив:
$data = [
'0' => 1,
'4' => 2,
'7' => 3
];
json_encode($data); и пишем в БД.
Каким образом можно записать такого же формата только на питоне? То есть с сохранением порядка ключей массива.
- Вопрос задан более трёх лет назад
- 436 просмотров
Комментировать
Решения вопроса 1
Python backend-developer
Полагаю, что в БД записать даже на php недостаточно записи $data = ['0'=>1, '4'=>2]
В python делается примерно так:
import json data = instance.field_name = json.dumps(data) instance.save()
Также можно использовать поле JSONField, тогда чтение/запись json будет прозрачным:
from django.contrib.postgres.fields import JSONField from django.db import models class CustomModel(models.Model): json_field_name = JSONField() instance = CustomModel() instance.field_name = instance.save()
Сохранить порядок ключей можно только, если использовать OrderedDict, однако это не гарантирует, что при сохранении в JSON и чтении порядок сохранится. Лучше для таких целей немного изменить формат данных, например, использовать список:
Using Python to insert JSON into PostgreSQL [closed]
This question does not appear to belong here. Either it's not database-related or it otherwise conflicts with the scope of our site. See What topics can I ask about here?, What types of questions should I avoid asking? or this blog post for more info.
Closed 3 years ago .
I have the following table:
create table json_table ( p_id int primary key, first_name varchar(20), last_name varchar(20), p_attribute json, quote_content text )
Now I basically want to load a json object with the help of a python script and let the python script insert the json into the table. I have achieved inserting a JSON via psql, but its not really inserting a JSON-File, it's more of inserting a string equivalent to a JSON file and PostgreSQL just treats it as json. What I've done with psql to achieve inserting JSON-File: Reading the file and loading the contents into a variable
\set content type C:\PATH\data.json
Inserting the JSON using json_populate_recordset() and predefined variable 'content':
insert into json_table select * from json_populate_recordset(NULL:: json_table, :'content');
This works well but I want my python script to do the same. In the following Code, the connection is already established:
connection = psycopg2.connect(connection_string) cursor = connection.cursor() cursor.execute("set search_path to public") with open('data.json') as file: data = json.load(file) query_sql = """ insert into json_table select * from json_populate_recordset(NULL::json_table, '<>'); """.format(data) cursor.execute(query_sql)
I get the following error:
Traceback (most recent call last): File "C:/PATH", line 24, in main() File "C:/PATH", line 20, in main cursor.execute(query_sql) psycopg2.errors.SyntaxError: syntax error at or near "p_id" LINE 3: json_populate_recordset(NULL::json_table, '[
If I paste the JSON content in pgAdmin4 and use the string inside json_populate_recordset() it works. I assume im handling the JSON file wrong. My data.json looks like this:
[ < "p_id": 1, "first_name": "Jane", "last_name": "Doe", "p_attribute": < "age": "37", "hair_color": "blue", "profession": "example", "favourite_quote": "I am the classic example" >, "quote_content": "'am':2 'classic':4 'example':5 'i':1 'the':3" >, < "p_id": 2, "first_name": "Gordon", "last_name": "Ramsay", "p_attribute": < "age": "53", "hair_color": "blonde", "profession": "chef", "favourite_quote": "Where is the lamb sauce?!" >, "quote_content": "'is':2 'lamb':4 'sauce':5 'the':3 'where':1" > ]