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.
Операция Insert

Операция 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 закрыто
Максим
Я создал этот блог в 2018 году, чтобы распространять полезные учебные материалы, документации и уроки на русском. На сайте опубликовано множество статей по основам python и библиотекам, уроков для начинающих и примеров написания программ.
Мои контакты: Почта
admin@pythonru.comAlex Zabrodin2018-10-26OnlinePython, Programming, HTML, CSS, JavaScript