Как получить id созданных записей

Question

Как получить id созданных записей

Рейтинг: 1Ответов: 2Опубликовано: 08.08.2023

Есть таблица postgresql payments_approval_history
В таблицу вносятся сразу несколько записей, необходимо получить id этих записей без доп запроса чтобы не увеличивать впустую счетчик id. Сейчас я умею такое реализовывать только с использованием цикла (пробегаемся по каждой записи и вносим отдельно:

query_a_h = """
    INSERT INTO payments_approval_history (
        payment_id,
        status_id,
        user_id,
        approval_sum
    )
    VALUES (%s, %s, %s, %s)
    RETURNING payment_id, confirm_id;"""

values_p_a_h = [
    (26, 2, 2, 2),
    (27, 2, 2, 3),
    (80, 2, 2, 4)
]

tmp = []
for i in range(len(values_p_a_h)):
    cursor.execute(query_a_h, 
                   [
                    values_p_a_h[i][0], 
                    values_p_a_h[i][1], 
                    values_p_a_h[i][2], 
                    values_p_a_h[i][3]
                    ])
    results = cursor.fetchall()
    tmp.append(results)
conn.commit()
cursor.close()
conn.close()
print(tmp)

В интернетах я нашёл executemany, но он не может возвращать ничего psycopg2.ProgrammingError: no results to fetch и ничего не записывает, пока из запроса не удалишь RETURNING payment_id, confirm_id
В документации psycopg2 говорится, что лучше использовать execute_batch, но на выходе я получаю только последнее значение из всего списка.

Мой вопрос: Какая практика лучшая в случае, когда необходимо внести строк 10-30 и получить их id обратно?

python-3.x postgresql psycopg2

Источник: Stack Overflow на русском

Answer 1

▲ -1Принят

import psycopg2 as pg

from psycopg2.extras import execute_values

values_p_a_h = [
    (26, 2, 2, 2),
    (27, 2, 2, 3),
    (80, 2, 2, 4)
]

with pg.connect('<connection string>') as conn:
    with conn.cursor() as cur:
        try:
            a_h_id = execute_values(
                cur,
                """
                INSERT INTO payments_approval_history (
                    payment_id,
                    status_id,
                    user_id,
                    approval_sum
                )
                VALUES %s on conflict do nothing 
                RETURNING payment_id, confirm_id;
                """,
                values_p_a_h,
                template="""(%s::int, %s::int, %s::int, %s::int)""",
                page_size=len(values_p_a_h), # вот тут возможны варианты. По умолчанию 500
                fetch=True
            )
        except Exception:
            conn.rollback()
            raise
        else:
            conn.commit()

Answer 2

▲ -1

Для решения моей проблемы с отправкой множества значений нужно было использовать execute_values (from psycopg2.extras import execute_values)
Ссылка на документацию
Большое спасибо Акину за подсказки
Создал функцию, которая помогает мне создать запрос

def get_db_dml_query(action, table, columns, subquery=";"):
    query = None
    if action == 'UPDATE':
        # Список столбцов в SET
        expr_set = ', '.join([f"{col} = c.{col}" for col in columns[1:]])
        # Список столбцов для таблицы "с"
        expr_s_tab = str(columns).replace('\'', '').replace('"', '')
        # Выражение для WHERE
        expr_where = result = f"c.{columns[0]} = t.{columns[0]}"
        # Конструктор запроса
        query = f"{action} {table} AS t SET {expr_set} FROM (VALUES %s) AS c {expr_s_tab} WHERE {expr_where} {subquery}"

    elif action == 'INSERT INTO':
        # Кортеж колонок переводим в строки и удаляем кавычки
        expr_cols = str(columns).replace('\'', '').replace('"', '')
        # Конструктор запроса
        query = f"{action} {table} {expr_cols} VALUES  %s {subquery}"

    return query

Сама запись в БД выглядит вот так:

# Запись в payments_approval_history
action_p_a_h = 'INSERT INTO'
table_p_a_h = 'payments_approval_history'
columns_p_a_h = ('payment_id', 'status_id', 'user_id', 'approval_sum')
subquery = " RETURNING payment_id, confirm_id;"
query_a_h = get_db_dml_query(action_p_a_h, table_p_a_h, columns_p_a_h, subquery)
values_p_a_h = [
    (26, 2, 2, 2),
    (27, 2, 2, 3),
    (80, 2, 2, 4)
]
a_h_id = execute_values(cursor, query_a_h, values_p_a_h, fetch=True)
conn.commit()

a_h_id - возвращает двумерный список с нужной информацией

Как получить id созданных записей

Ответы