Insert postgres: PostgreSQL : Документация: 9.6: INSERT : Компания Postgres Professional

Содержание

Операции Insert, Update и Delete в базе данных PostgreSQL на Python

В этом руководстве рассмотрим, как выполнять операции Insert, Update и Delete в базе данных PostgreSQL из Python-скриптов. Их еще называют DML-операциями. Также научимся передавать параметры в SQL-запросы.

В итоге разберем, как использовать cursor.executemany() для выполнения вставки, обновления или удаления нескольких строк в один запрос.

Операция Insert

В этом разделе рассмотрим, как выполнять команду Insert для вставки одной или нескольких записей в таблицу PostgreSQL из Python с помощью Psycopg2.

Для выполнения запроса нужно сделать следующее:

Установить psycopg2 с помощью pip.
Установить соединение с базой данных из Python.
Создать запрос Insert. Для этого требуется знать название таблицы и ее колонок.
Выполнить запрос с помощью cursor.execute(). В ответ вы получите количество затронутых строк.
После выполнения запроса нужно закоммитить изменения в базу данных.
Закрыть объект cursor и соединение с базой данных.
Также важно перехватить любые исключения, которые могут возникнуть в процессе.
Наконец, можно проверить результаты, запросив данные из таблицы.

Теперь посмотрим реальный пример.

Копировать Скопировано Use a different Browser

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

Посмотрим на примере обновления одной строки таблицы:

Копировать Скопировано Use a different Browser

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

Можно сразу перейти к примеру. Он выглядит следующим образом:

Копировать Скопировано Use a different Browser

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().

Копировать Скопировано Use a different Browser

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() и запроса с параметрами (%). Посмотрим на примере.

Копировать Скопировано Use a different Browser

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 записей для удаления. Также есть список записей для удаления.

В списке есть кортежи для каждой строки. В примере их два, что значит, что удалены будут две строки.

Копировать Скопировано Use a different Browser

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 закрыто

Как вставить данные в Postgres — Вопросы

kidrulit 9 сентября 2022 г., 13:55

Опишите проблему/ошибку/вопрос

Привет, ребята, я пытаюсь понять, есть ли способ выполнить операцию INSERT в Postgres без написания запроса.
Я пытался следовать документации и заметил, что есть способ сопоставить параметры запроса со значениями таблицы. Однако я не вижу этого в интерфейсе в моей настройке.

В приведенном выше примере я попытался вставить представление JSON.

 {
  "id": 777654140,
  "Страна": {{ $json["запрос"]["ip_country"] }}
}

Что я должен ввести в поле Columns ?
Я ожидаю увидеть здесь схему таблицы данных для вставки значений. Например, Integromat придерживается тех же подходов, которые я нахожу довольно хорошими.

Выполнение запроса не проблема, но я ожидаю, что при выборе режима INSERT не будет кода.

Какое сообщение об ошибке (если есть)?

Поделитесь рабочим процессом

 (выберите узлы и используйте сочетания клавиш CMD+C/CTRL+C и CMD+V/CTRL+V, чтобы скопировать и вставить рабочий процесс соответственно)

Поделитесь выводом, возвращенным последним узлом

Информация о настройке n8n

Версия n8n: 0.193.4
База данных, которую вы используете (по умолчанию: SQLite):
Запуск n8n с процессом выполнения [собственный (по умолчанию), основной]:
Запуск n8n через [Docker, npm, n8n.cloud, настольное приложение]:

БрэмКн 9 сентября 2022 г. , 14:07

Привет @kidrulit
Посмотрите этот пример:

kidrulit 9 сентября 2022 г., 15:32

Есть ли способ передать переменные в Postgres без функционального модуля?
Кажется, нет возможности использовать переменные из предыдущих источников в функции

Мне трудно передавать данные из wbhook в Postgres

Джон 9 сентября 2022 г., 17:04

Эй, @kidrulit,

Вы можете попробовать использовать опцию «Выполнить запрос» и таким образом написать запрос на вставку. В рамках нашего текущего проекта капитального ремонта узла способ определения данных будет изменен и будет намного проще, но это произойдет через несколько месяцев, поэтому на данный момент либо узел function/set, если вы хотите использовать опцию вставки или создание запроса вручную — лучший выбор.

кидрулит 10 сентября 2022 г., 19:16

Спасибо!
Можно ли указать более одного свойства в поле параметра запроса?

Мой следующий запрос INSERT завершается ошибкой, когда я передаю все 14 свойств в поле Query Parameters .

 INSERT INTO public."page-events" (id, created_at, isp_provider, country, path, hostname, http_referrer, language, hash, utm_medium, utm_source, utm_campaign, utm_content, utm_term)
ЗНАЧЕНИЯ ($1, $2, $3, $4, $5, $6, $7, $8, $9, 10 долларов, 11 долларов, 12 долларов, 13 долларов, 14 долларов)

Кажется, что параметры просто не загружаются в исходном запросе, так как я получаю сообщение об ошибке ОШИБКА: нулевое значение в столбце «id» отношения «события страницы» нарушает ненулевое ограничение

Вот как параметры перечислены:

Я что-то упустил? Могу ли я передавать параметры непосредственно в запрос?

Джон 11 сентября 2022 г. , 5:33

Эй, @kidrulit,

Все должно быть в порядке, похоже, ошибка Postgres говорит, что идентификатор пуст. Однако введенный вами запрос сильно отличается от того, что вы указали в поле выражения.

Как только вы выберете выполнить запрос, вы можете просто ввести первый запрос и использовать выражения для значений.

кидрулит 11 сентября 2022 г., 12:51

Привет @Jon

Спасибо за ответ.
Не могли бы вы помочь мне понять, чем оно отличается от поля «Выражение»? Я не совсем понял.

Вставка работает нормально, когда я выполняю в pg_admin
Я просто скопировал результат поля Выражение и передал его вместо значений.

 INSERT INTO public. "page-events" (id, created_at, isp_provider, country, path, hostname, http_referrer, language, uid_hash, utm_medium, utm_source, utm_campaign, utm_content, utm_term)
ЗНАЧЕНИЯ (10000100, '2022-9-11 12:42', 'Datacamp Limited', 'Япония', '/pricing-page', 'www.spatial.chat', 'www.spatial.chat/pricing', ' jp", "jsjdjsjdsjdjsj", "seo", "google", "mark", "none", "oooo")

 ВСТАВКА 0 1
Запрос успешно возвращен через 894 мс.

кидрулит 11 сентября 2022 г., 13:19

Думаю, я принципиально не понял, как работает сопоставление параметров запроса в n8n.
Давайте проверим на более простом случае.

Ввод в поле запроса:
SELECT * FROM public."page-events" WHERE id = $1;

Ввод в поле Query Parameters
{{ $json["id"] }}, , результат 10000100,

Вывод n8n недействителен.

Когда я запускаю SELECT * FROM public. "события целевой страницы" WHERE id = 10000100; в поле запроса я получаю результат строки, соответствующей идентификатору. Я предполагаю, что запрос игнорирует поле параметров запроса.

кидрулит 11 сентября 2022 г., 13:36

#10

Хорошо, я понял. Спасибо, нашел похожий вопрос на форуме.
Перетаскивание значения из предыдущего шага в поле Query Parameters не работает; Мне нужно указать имя.
Когда я помещаю id , но не значение в параметры запроса, я получаю результат.

Очень неудобно, ребята, буду рад увидеть более подробную инструкцию в официальном документе. Является ли ваш документ открытым исходным кодом, могу ли я внести свой вклад?

1 Нравится

Джон 11 сентября 2022 г. , 15:08

#11

Привет, @kidrulit,

Документацию может внести кто угодно, я думаю, мы делаем некоторые предположения, так как большинство полей ввода работают одинаково.

Когда вы используете запрос на выполнение, вы должны использовать тот же запрос, что и при обычном использовании базы данных, но для ваших значений вы можете использовать переменные, которые будут заменены при запуске.

1 Нравится

Эффективно INSERT MULTIPLE с Postgres

Предисловие

Прошу прощения за отсутствие обложки. В свою защиту я искал подходящее изображение для иллюстрации «множественной вставки» и считаю важным предупредить кого-либо еще и никогда делать то же самое.

Выглядит знакомо?

У вас есть код, похожий на этот?

 new_id = db.query ("""
  ВСТАВЬТЕ В Foo(бла, вибл)
  ЗНАЧЕНИЯ($1, $2)
  ВОЗВРАЩАЕМ идентификатор """,
  "бла", "блин")
БД. запрос("""
  ВСТАВИТЬ В другое (foo_id, foogle)
  ЗНАЧЕНИЯ($1, $2)""",
  новый_id, 42)

Войти в полноэкранный режимВыйти из полноэкранного режима

Может быть, это сложнее, может быть, в нем есть операторы SELECT и все такое.

Но это работает. Так почему же я собираюсь предложить изменить его?

Круглые поездки

В тот момент, когда мы вводим сетевое соединение, мы получаем два ключевых параметра производительности. Есть «Сколько времени что-то занимает для выполнения» и «Как далеко это находится». Над первым у нас есть некоторый контроль; мы можем писать вещи более эффективно, добавлять индексирование базы данных, увеличивать размеры экземпляров и делать все, что нужно.

Последнее, однако, мы мало контролируем. Если время обращения к базе данных составляет 5 мс, то приведенный выше код займет не менее 10 мс, независимо от того, насколько быстры база данных и служба приложений.

Есть и другие посты, которые проведут вас через прелести оптимизации запросов — надеюсь, а если нет, то пилите меня, и я напишу один.

Что мы собираемся сделать, так это убрать круговые поездки и, возможно, немного ускорить работу.

ВСТАВЬТЕ НЕСКОЛЬКО

К сожалению, не существует такой вещи, как INSERT MULTIPLE . Вы можете поместить несколько строк в одну таблицу ( INSERT ... SELECT ... ), но не строку в несколько таблиц.

Здесь можно использовать триггер — это абсолютно сработает, но я считаю, что триггеры, выходящие за рамки действительно тривиальных случаев, чрезвычайно сложно тестировать и отлаживать.

Вместо этого нам нужна техника под названием «Изменение общего табличного выражения» или «Изменение CTE».

Общие табличные выражения

Обычно любой признак С в запросе вызывает у меня раздражение. Как правило, это признак чрезмерно сложного запроса к схеме, не предназначенной для его обслуживания.

В простейшем случае они выглядят как перефразировка подвыборки JOIN:

 WITH thing AS (
  ВЫБЕРИТЕ id, бла, FROM foo
  ГДЕ покачиваться КАК '%ibble'
)
ВЫБЕРИТЕ foogle ИЗ другого
ПРИСОЕДИНЯЙТЕСЬ к вещи на other. foo_id=foo.id

Войти в полноэкранный режимВыйти из полноэкранного режима

Но магия в том, что С приводит к тому, что запрос является таблицей с полным именем — общим табличным выражением — поэтому вы можете использовать его несколько раз или даже во втором общем табличном выражении.

 С вещью КАК (
  ВЫБЕРИТЕ id, бла, FROM foo
  ГДЕ покачиваться КАК '%ibble'
),
другое_вещь КАК (
  ВЫБЕРИТЕ id, foo_id, foogle, бла ИЗ другого
  ПРИСОЕДИНЯЙТЕСЬ к вещи на other.foo_id=foo.id
)
ВЫБЕРИТЕ id, foo_id ОТ other_thing
ПРИСОЕДИНЯЙТЕСЬ к table_i_havent_упомянутому_до t ON t.id=other_thing.id

Войти в полноэкранный режимВыйти из полноэкранного режима

Оптимизатор запросов будет здесь хитрым и знает, что ему нужно thing до того, как будет готово other_thing . Но на практике он также будет обрабатывать весь запрос как один SELECT, так что это не так уж интересно.

Но что, если мы используем не SELECT в CTE, а INSERT, UPDATE или DELETE?

Изменение CTE

 С step_one AS (
  ВСТАВЬТЕ В Foo(бла, вибл)
  ЗНАЧЕНИЯ($1, $2)
  ВОЗВРАЩАЕМЫЙ идентификатор
)
ВСТАВИТЬ В другое (foo_id, foogle)
ВЫБЕРИТЕ идентификатор, $3 ОТ step_one

Войти в полноэкранный режимВыйти из полноэкранного режима

Это делает то же самое, что и (псевдо) Python в начале.

Postgres сначала выполнит step_one, так как от него зависит внешний оператор, а затем запустит внешний оператор с результатом — точно так же, как это делал Python. Только на этот раз все за один раз.

Давайте попробуем что-нибудь посложнее — как насчет четырех утверждений?

MOAR Изменение CTE

 new_id = db.query("""
  ВСТАВЬТЕ В Foo(бла, вибл)
  ЗНАЧЕНИЯ($1, $2)
  ВОЗВРАЩАЕМ идентификатор """,
  "бла", "блин")
other_new_id = db.query ("""
  ВСТАВЬТЕ В БАР (блок)
  ЗНАЧЕНИЯ($1)
  ВОЗВРАЩАЕМ идентификатор """,
  "бла", "блин")
БД.запрос("""
  ВСТАВИТЬ В другое (foo_id, foogle, bar_id)
  ЗНАЧЕНИЯ($1, $2, $3)""",
  новый_идентификатор, 42, другой_новый_идентификатор)

Войти в полноэкранный режимВыйти из полноэкранного режима

Здесь у нас есть Python, который выполняет два оператора один за другим и вставляет новые идентификаторы из обоих во вторую таблицу.

В Python мы должны запускать одно за другим — вы не можете запускать операторы одновременно в одной транзакции — так что 3 цикла туда и обратно, 3 выполнения операторов. (На самом деле еще хуже, так как транзакция стоила дополнительных 2 циклов, всего 5.)

0015 С step_one КАК ( ВСТАВЬТЕ В Foo(бла, вибл) ЗНАЧЕНИЯ($1, $2) ВОЗВРАЩАЕМЫЙ идентификатор ), С step_two КАК ( ВСТАВЬТЕ В БАР (блок) ЦЕННОСТИ ($3) ВОЗВРАЩАЕМЫЙ идентификатор ), ВСТАВИТЬ В другое (foo_id, foogle, bar_id) ВЫБЕРИТЕ s1.id, $4, s2.id ИЗ step_one s1, step_two s2 Войти в полноэкранный режимВыйти из полноэкранного режима

При этом используется только один круговой обход, но кроме того, step_one и step_two выполняются одновременно, потому что они не зависят друг от друга… так что только два времени выполнения инструкции.

Кроме того, это один оператор, поэтому вам больше не нужна транзакция для изоляции.

Однако я признаю, что параметры становится трудно отслеживать. Эй, я знаю, что решит эту проблему:

ДАЖЕ МОАР КТР!

 С my_data(blah, wibble, blook, floogle) КАК (
 ЗНАЧЕНИЯ ($1, $2, $3, $4::BIGINT)
),
С step_one КАК (
 ВСТАВЬТЕ В Foo(бла, вибл)
 ВЫБЕРИТЕ m.

Операции Insert, Update и Delete в базе данных PostgreSQL на Python

Операция Insert

Операция Update

Удаление строк и колонок

Cursor.executemany() запросов нескольких строк

Вставка нескольких строк в таблицу PostgreSQL

Обновление нескольких строк в одном запросе

Удаление нескольких строк из таблицы

Как вставить данные в Postgres — Вопросы

Опишите проблему/ошибку/вопрос

Какое сообщение об ошибке (если есть)?

Поделитесь рабочим процессом

Поделитесь выводом, возвращенным последним узлом

Информация о настройке n8n

Эффективно INSERT MULTIPLE с Postgres

Предисловие

Выглядит знакомо?

Круглые поездки

ВСТАВЬТЕ НЕСКОЛЬКО

Общие табличные выражения

Изменение CTE

MOAR Изменение CTE

ДАЖЕ МОАР КТР!

Добавить комментарий Отменить ответ