How to INSERT INTO with psycopg2
The most famous library to connect PostgreSQL from Python might be psycopg2. psycopg2 is a simple and easy library for the people who want to manipulate SQL simply. The usage is basically pretty straightforward but I got stuck when I tried to a record into PostgreSQL via psycogpg2. Here I wrote a query like this.
import psycopg2 conn = psycopg2.connect(host=pg_credential.hostname, port=pg_credential.port, user=pg_credential.username, password=pg_credential.password, database=pg_credential.path[1:]) # To remove slash cursor = conn.cursor() cursor.execute("INSERT INTO a_table (c1, c2, c3) VALUES(%s, %s, %s)", (v1, v2, v3)) cursor.close() conn.close() But I cannot see any data in a_table . The query seems fine itself because we can insert into with the query. Why the data was not inserted properly? I took some time to search for an answer and finally find a solution.
We need to call commit of cursor object. In short, we need to write code like below.
import psycopg2 conn = psycopg2.connect(host=pg_credential.hostname, port=pg_credential.port, user=pg_credential.username, password=pg_credential.password, database=pg_credential.path[1:]) # To remove slash cursor = conn.cursor() cursor.execute("INSERT INTO a_table (c1, c2, c3) VALUES(%s, %s, %s)", (v1, v2, v3)) conn.commit() # cursor.close() conn.close() Since I usually use commit with the only transactional operation, I have thought it won’t necessary to call it to simply inserting data when I don’t use transaction. So that was my bad. We should check the documentation carefully especially when we use a new library for us. psycopg2 documentation is here.
CRUD-функции в базе данных PostgreSQL на Python
В этом руководстве рассмотрим, как выполнять операции Insert, Update и Delete в базе данных PostgreSQL из Python-скриптов. Их еще называют DML-операциями. Также научимся передавать параметры в SQL-запросы.
В итоге разберем, как использовать cursor.executemany() для выполнения вставки, обновления или удаления нескольких строк в один запрос.
Операция Insert
В этом разделе рассмотрим, как выполнять команду Insert для вставки одной или нескольких записей в таблицу PostgreSQL из Python с помощью Psycopg2.
Для выполнения запроса нужно сделать следующее:
- Установить psycopg2 с помощью pip.
- Установить соединение с базой данных из Python.
- Создать запрос Insert. Для этого требуется знать название таблицы и ее колонок.
- Выполнить запрос с помощью cursor.execute() . В ответ вы получите количество затронутых строк.
- После выполнения запроса нужно закоммитить изменения в базу данных.
- Закрыть объект cursor и соединение с базой данных.
- Также важно перехватить любые исключения, которые могут возникнуть в процессе.
- Наконец, можно проверить результаты, запросив данные из таблицы.
Теперь посмотрим реальный пример.
import psycopg2 from psycopg2 import Error try: # Подключиться к существующей базе данных connection = psycopg2.connect(user="postgres", # пароль, который указали при установке PostgreSQL password="1111", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (%s,%s,%s)""" record_to_insert = (5, 'One Plus 6', 950) cursor.execute(postgres_insert_query, record_to_insert) connection.commit() count = cursor.rowcount print (count, "Запись успешно добавлена в таблицу mobile") except (Exception, Error) as error: print("Ошибка при работе с PostgreSQL", error) finally: if connection: cursor.close() connection.close() print("Соединение с PostgreSQL закрыто")1 Запись успешно добавлена в таблицу mobile Соединение с PostgreSQL закрыто
- В этом примере использовался запрос с параметрами для передачи значений во время работы программы. А в конце изменения сохранились с помощью cursor.commit .
- С помощью запроса с параметрами можно передавать переменные python в качестве параметров на месте %s .
Операция Update
В этом разделе вы узнаете, как обновлять значение в одной или нескольких колонках для одной или нескольких строк таблицы. Для этого нужно изменить запрос к базе данных.
# Задать новое значение price в строке с id для таблицы mobile Update mobile set price = %s where >Посмотрим на примере обновления одной строки таблицы:
import psycopg2 from psycopg2 import Error def update_table(mobile_id, price): try: # Подключиться к существующей базе данных connection = psycopg2.connect(user="postgres", # пароль, который указали при установке PostgreSQL password="1111", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() print("Таблица до обновления записи") sql_select_query = """select * from mobile where cursor.execute(sql_select_query, (mobile_id,)) record = cursor.fetchone() print(record) # Обновление отдельной записи sql_update_query = """Update mobile set price = %s where cursor.execute(sql_update_query, (price, mobile_id)) connection.commit() count = cursor.rowcount print(count, "Запись успешно обновлена") print("Таблица после обновления записи") sql_select_query = """select * from mobile where cursor.execute(sql_select_query, (mobile_id,)) record = cursor.fetchone() print(record) except (Exception, Error) as error: print("Ошибка при работе с PostgreSQL", error) finally: if connection: cursor.close() connection.close() print("Соединение с PostgreSQL закрыто") update_table(3, 970)Убедимся, что обновление сработало. Вывод:
Таблица до обновления записи (3, 'Samsung Galaxy S21', 900.0) 1 Запись успешно обновлена Таблица после обновления записи (3, 'Samsung Galaxy S21', 970.0) Соединение с PostgreSQL закрытоУдаление строк и колонок
В этом разделе рассмотрим, как выполнять операцию удаления данных из таблицы с помощью программы на Python и Psycopg2.
# Удалить из таблицы . в строке с id . Delete from mobile where id = %sМожно сразу перейти к примеру. Он выглядит следующим образом:
Python sql insert psycopg2
Рассмотрим добавление в базу данных PostgreSQL на примере следующей таблицы:
CREATE TABLE people ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INTEGER)Добавление данных
Для добавления данных применяется SQL-инструкция INSERT . Для добавления одной строки используем метод execute() :
import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # добавляем строку в таблицу people cursor.execute("INSERT INTO people (name, age) VALUES ('Tom', 38)") # выполняем транзакцию conn.commit() print("Данные добавлены") cursor.close() conn.close()Здесь добавляется одна строка, где name = "Tom", а age = 38. Перед выполнением команды INSERT открывается транзакция, для завершения которой необходимо вызвать метод commit() текущего объекта Connection.
Установка параметров
С помощью второго параметра в метод execute() можно передать значения для параметров SQL-запроса:
import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления bob = ("Bob", 42) cursor.execute("INSERT INTO people (name, age) VALUES (%s, %s)", bob) conn.commit() print("Данные добавлены") cursor.close() conn.close()В данном случае добавляемые в БД значения представляют кортеж bob. В SQL-запросе вместо конкретных значений используются знаки подстановки %s . Вместо этих символов при выполнении запроса будут вставляться данные из кортежа data. Так, первый элемент кортежа - строка "Bob" передается на место первого плейсхолдера %s, второй элемент - число 42 передается на место второго плейсхолдера %s. То есть в итоге команды SQL будет выглядеть следующим образом:
INSERT INTO people (name, age) VALUES ('Bob', 42)Также обратите внимание, что НЕ надо помещать плейсхолдер %s в кавычки - psycopg2 делает это автоматически.
И если мы посмотрим на содержимое базы данных, то найдем там все добавленные объекты:
Множественная вставка
Метод executemany() позволяет вставить набор строк:
import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления people = [("Sam", 28), ("Alice", 33), ("Kate", 25)] cursor.executemany("INSERT INTO people (name, age) VALUES (%s, %s)", people) conn.commit() print("Данные добавлены") cursor.close() conn.close()В метод cursor.executemany() по сути передается то же самое выражение SQL, только теперь данные определены в виде списка кортежей people. Фактически каждый кортеж в этом списке представляет отдельную строку - данные отдельного пользователя, и при выполнении метода для каждого кортежа будет создаваться свое выражение INSERT INTO
