В этом руководстве рассмотрим, как выполнять операции 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 id = %sПосмотрим на примере обновления одной строки таблицы:
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 id = %s"""
cursor.execute(sql_select_query, (mobile_id,))
record = cursor.fetchone()
print(record)
# Обновление отдельной записи
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.execute(sql_update_query, (price, mobile_id))
connection.commit()
count = cursor.rowcount
print(count, "Запись успешно обновлена")
print("Таблица после обновления записи")
sql_select_query = """select * from mobile where id = %s"""
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Можно сразу перейти к примеру. Он выглядит следующим образом:
import psycopg2
from psycopg2 import Error
def delete_data(mobile_id):
try:
# Подключиться к существующей базе данных
connection = psycopg2.connect(user="postgres",
# пароль, который указали при установке PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# Удаление записи
sql_delete_query = """Delete from mobile where id = %s"""
cursor.execute(sql_delete_query, (mobile_id,))
connection.commit()
count = cursor.rowcount
print(count, "Запись успешно удалена")
except (Exception, Error) as error:
print("Ошибка при работе с PostgreSQL", error)
finally:
if connection:
cursor.close()
connection.close()
print("Соединение с PostgreSQL закрыто")
delete_data(4)
delete_data(5)
Убедимся, что запись исчезла из таблицы.
1 Запись успешно удалена
Соединение с PostgreSQL закрыто
1 Запись успешно удалена
Соединение с PostgreSQL закрытоCursor.executemany() запросов нескольких строк
Метод cursor.executemany() делает запрос в базу данных со всеми параметрами.
Очень часто нужно выполнить один и тот же запрос с разными данными. Например, обновить информацию о посещаемости студентов. Скорее всего, данные будут разные, но SQL останется неизменным.
Используйте cursor.executemany() для вставки, обновления и удаления нескольких строк в один запрос.
Синтаксис executemany():
executemany(query, vars_list)- В этом случае запросом может быть любая DML-операция (вставка, обновление, удаление).
vars_list— это всего лишь список кортежей, которые передаются в запрос.- Каждый кортеж содержит одну строку для вставки или удаления.
Теперь посмотрим, как использовать этот метод.
Вставка нескольких строк в таблицу PostgreSQL
Можно выполнить вставку нескольких строк с помощью SQL-запроса. Для этого используется запрос с параметрами и метод executemany().
import psycopg2
from psycopg2 import Error
def bulk_insert(records):
try:
# Подключиться к существующей базе данных
connection = psycopg2.connect(user="postgres",
# пароль, который указали при установке PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
sql_insert_query = """ INSERT INTO mobile (id, model, price)
VALUES (%s,%s,%s) """
# executemany() для вставки нескольких строк
result = cursor.executemany(sql_insert_query, records)
connection.commit()
print(cursor.rowcount, "Запись(и) успешно вставлена(ы) в таблицу mobile")
except (Exception, Error) as error:
print("Ошибка при работе с PostgreSQL", error)
finally:
if connection:
cursor.close()
connection.close()
print("Соединение с PostgreSQL закрыто")
records_to_insert = [ (4,'LG', 800) , (5,'One Plus 6', 950)]
bulk_insert(records_to_insert)
Проверим результат, вернув данные из таблицы.
2 Запись(и) успешно вставлена(ы) в таблицу mobile
Соединение с PostgreSQL закрытоПримечание: для этого запроса был создан список записей, включающий два кортежа. Также использовались заменители. Они позволяют передать значения в запрос уже во время работы программы.
Обновление нескольких строк в одном запросе
Чаще всего требуется выполнить один и тот же запрос, но с разными данными. Например, обновить зарплату сотрудников. Сумма будет отличаться, но не запрос.
Обновить несколько колонок таблицы можно с помощью cursor.executemany() и запроса с параметрами (%). Посмотрим на примере.
import psycopg2
from psycopg2 import Error
def update_in_bulk(records):
try:
# Подключиться к существующей базе данных
connection = psycopg2.connect(user="postgres",
# пароль, который указали при установке PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# Обновить несколько записей
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.executemany(sql_update_query, records)
connection.commit()
row_count = cursor.rowcount
print(row_count, "Записи обновлены")
except (Exception, Error) as error:
print("Ошибка при работе с PostgreSQL", error)
finally:
if connection:
cursor.close()
connection.close()
print("Соединение с PostgreSQL закрыто")
update_in_bulk([(750, 4), (950, 5)])
Вывод:
2 Записи обновлены
Соединение с PostgreSQL закрытоПроверим результат.
Используйте cursor.rowcount, чтобы получить общее количество строк, измененных методом executemany().
Удаление нескольких строк из таблицы
В этом примере используем запрос Delete с заменителями, которые подставляют ID записей для удаления. Также есть список записей для удаления. В списке есть кортежи для каждой строки. В примере их два, что значит, что удалены будут две строки.
import psycopg2
from psycopg2 import Error
def delete_in_bulk(records):
try:
# Подключиться к существующей базе данных
connection = psycopg2.connect(user="postgres",
# пароль, который указали при установке PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
delete_query = """Delete from mobile where id = %s"""
cursor.executemany(delete_query, records)
connection.commit()
row_count = cursor.rowcount
print(row_count, "Записи удалены")
except (Exception, Error) as error:
print("Ошибка при работе с PostgreSQL", error)
finally:
if connection:
cursor.close()
connection.close()
print("Соединение с PostgreSQL закрыто")
delete_in_bulk([(5,), (4,), (3,)])
Убедимся, что запрос был выполнен успешно.
3 Записи удалены
Соединение с PostgreSQL закрыто
