У меня есть в python коде строки deletedcount = pgcon.execute(text(f'DELETE FROM…
- User
У меня есть в python коде строки
deleted_count = pg_con.execute(text(f'DELETE FROM {schema}."{table}" WHERE filename=:filename;').bindparams(filename=filename))иlogger.info(f'Удалено {deleted_count} строк в таблице{schema}."{table}"'). При выполнении второй строки в логах я получаю записьINFO - Удалено <sqlalchemy.engine.cursor.LegacyCursorResult object at ***x7f1bd631b***9***> строк в таблицеfiles."pbi_rls"`` . Как правильно получить количество удаленных строк при выполнении DELETE sql запроса в postgresql базе данных? Assistant
Классическая засада при работе с SQLAlchemy!
Проблема в том, что
pg_con.execute()возвращает объектLegacyCursorResult(илиCursorResultв новых версиях SQLAlchemy), который представляет собой результат выполнения SQL-запроса, но не количество затронутых строк напрямую.Чтобы получить количество удаленных строк, вам нужно обратиться к атрибуту
rowcountобъектаLegacyCursorResult.Вот исправленная первая строка:
result = pg_con.execute(text(f'DELETE FROM {schema}."{table}" WHERE filename=:filename;').bindparams(filename=filename)) deleted_count = result.rowcountrowcountвозвращает количество строк, затронутых последним SQL-запросом (в данном случае,DELETE). ДляDELETEзапросов это именно то, что вам нужно — количество удаленных строк.Теперь ваша вторая строка отработает правильно:
logger.info(f'Удалено {deleted_count} строк в таблице `{schema}."{table}"`')В логах вы увидите, например:
INFO - Удалено 5 строк в таблицеfiles."pbi_rls"``, если удалено 5 строк.Почему так? Исторически, DB-API 2.0 (стандарт взаимодействия Python с БД) определяет, что метод
execute()курсора возвращает сам курсор (или объект-результат), а не скалярное значение (типа количества строк). Это позволяет получать разные данные из результата запроса:- для
SELECTможно получать строки через итерацию или методыfetch*(); - для
INSERT,UPDATE,DELETEполучать состояние операции через атрибуты типаrowcount,lastrowidи т. п.
SQLAlchemy надстраивается над DB-API 2.0 драйверами (для PostgreSQL это
psycopg2илиasyncpg), поэтому наследует эту логику.Дополнительные советы:
- Всегда проверяйте
rowcountна случай, если запрос не выполнился ожидаемо (например, из-за триггераON DELETEс условием блокировки удаления).
if deleted_count == 0: logger.warning(f'Не найдено строк для удаления по filename={filename} в {schema}."{table}"')- Если работаете асинхронно (с
asyncpgилиaiosqlite), синтаксис будет чуть другим:
result = await pg_con.execute(...) # обратите внимание на await deleted_count = result.rowcount # а тут всё так же- Если вдруг в будущем будете использовать ORM-стиль SQLAlchemy (работа с сессиями и моделями), там удаление выглядит иначе:
from sqlalchemy import delete stmt = delete(MyTable).where(MyTable.filename == filename) result = session.execute(stmt) deleted_count = result.rowcount # и снова наш друг rowcount!Всё, теперь ваши логи точны и информативны!
P.S. И не забывайте про безопасное форматирование запросов: использование
text(f'...{schema}...')с подстановкой переменных из кода рискует открыть дверь SQL-инъекциям. Лучше параметрыschemaиtableтоже передавать через:schemaи.bindparams(), если они могут меняться динамически. Но это тема отдельного разговора :)- для
- User
А как нужно передавать параметры schema и table тоже через :schema и .bindparams(), если там есть особенность с двойными кавычками? И расскажи немного про конструкторы delete() и returning()
Assistant
Отличные вопросы! Давайте погрузимся в детали.
1. Безопасная передача
schemaиtableчерез параметрыВы правы, есть нюанс с идентификаторами (названия таблиц, схем, колонок) vs значениями (конкретные данные типа
filename='мой_файл.txt').Проблема: SQLAlchemy (и вообще DB-API) умеет безопасно подставлять только значения через
:paramиbindparams(param=value). А имена таблиц/схем/колонок (идентификаторы) приходится вставлять в текст SQL-запроса напрямую (черезf-строки или.format()).Почему? Потому что в стандарте SQL различаются:
- литералы (строки, числа, даты...):
WHERE name = :name→name = 'Иван'(экранирование, кавычки) - идентификаторы (имена объектов БД):
FROM :my_tableне может превратиться вFROM 'users'(с кавычками), а должен статьFROM users(без кавычек) илиFROM "Users"(с двойными кавычками, если регистрозависимое имя).
Если просто сделать
pg_con.execute(text('DELETE FROM :schema.:table ...').bindparams(schema='public', table='users'))PostgreSQL (и любой другой SQL-сервер) завернёт запрос с ошибкой:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "$1" LINE 1: DELETE FROM $1.$2 WHERE... ^Потому что
:schemaи:tableпытаются подставиться как строковые литералы, а не как имена объектов БД.Что делать? Использовать специальный класс
Tableиз SQLAlchemy и конструировать запрос безtext():from sqlalchemy import Table, MetaData metadata = MetaData() my_table = Table(f'"{table}"', metadata, schema=schema, quote=True) stmt = my_table.delete().where(my_table.c.filename == bindparam('filename')) result = pg_con.execute(stmt, {'filename': filename}) deleted_count = result.rowcountЗдесь:
Table(..., quote=True)заставляет SQLAlchemy сохранять точный регистр и добавлять двойные кавычки при необходимости ("pbi_rls").my_table.c.filenameссылается на колонкуfilenameв этой таблице.bindparam('filename')говорит, что значениеfilenameпридёт отдельным параметром (безопасно).
Полученный SQL будет корректным:
DELETE FROM "files"."pbi_rls" WHERE filename = $1где
$1(или:1, зависит от драйвера) — подставленное безопасно значениеfilename.Если очень хочется через
text(), то минимально-безопасный вариант (но я не рекомендую) — двойные кавычки вручную и валидация имён:import re def sanitize_identifier(name): if not re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', name): raise ValueError(f'Bad identifier: {name}') return f'"{name}"' # оборачиваем в двойные кавычки schema = sanitize_identifier(schema) table = sanitize_identifier(table) sql = text(f'DELETE FROM {schema}.{table} WHERE filename = :filename') result = pg_con.execute(sql.bindparams(filename=filename))Так вы хотя бы защититесь от SQL-инъекций в именах, но лучше всё-таки через
Table.2. Конструктор
delete()иreturning()Теперь о красивых и мощных возможностях SQLAlchemy Core (не путайте с ORM!).
Базовый
delete()from sqlalchemy import delete stmt = delete(my_table).where(my_table.c.filename == 'data.csv') # либо, как выше, через bindparam stmt = delete(my_table).where(my_table.c.filename == bindparam('filename'))Генерирует SQL:
DELETE FROM "files"."pbi_rls" WHERE filename = 'data.csv'либо (с параметром)
DELETE FROM "files"."pbi_rls" WHERE filename = $1Магия
returning()В PostgreSQL (и некоторых других СУБД)
DELETEподдерживает конструкциюRETURNING *(какSELECTпосле удаления). SQLAlchemy позволяет это сделать элегантно:from sqlalchemy import delete stmt = delete(my_table).where(my_table.c.filename == bindparam('filename'))\ .returning(my_table.c.id, my_table.c.deleted_at) result = pg_con.execute(stmt, {'filename': 'old_file.txt'})Теперь:
- Удалятся строки
WHERE filename = 'old_file.txt' - Вернутся значения колонок
idиdeleted_atименно тех строк, которые удалили.
SQL, который сгенерируется:
DELETE FROM "files"."pbi_rls" WHERE filename = $1 RETURNING "pbi_rls".id, "pbi_rls".deleted_atЧто получаем в
result?Это будет всё тот же
CursorResult, но теперь:result.rowcountпо-прежнему вернёт количество удалённых строк.result.fetchall()(илиresult.all()в асинхронном режиме) вернёт список кортежей сidиdeleted_at.
Пример:
deleted_rows = result.fetchall() for row in deleted_rows: logger.info(f'Удалён ID={row.id} в {row.deleted_at}')Скажем, удалили 2 строки — получим:
[ (123, datetime.datetime(2023, 10, 18, 12, 30, 1)), (124, datetime.datetime(2023, 10, 18, 12, 30, 1)) ]Это удобнее, чем:
- Отдельный
SELECT id, deleted_at FROM ... WHERE filename = :filename DELETE- Сопоставлять «что нашли» и «что стёрли».
Можно даже
returning(my_table)(аналогRETURNING *) — тогда придут все колонки удалённых строк.Асинхронная версия (если
pg_conотasyncpg):from sqlalchemy.ext.asyncio import AsyncConnection async def delete_with_returning(pg_con: AsyncConnection, filename: str): stmt = delete(my_table).where(my_table.c.filename == bindparam('filename'))\ .returning(my_table.c.id) result = await pg_con.execute(stmt, {'filename': filename}) deleted_ids = [row.id for row in await result.all()] # ждём асинхронно logger.info(f'Удалены ID: {deleted_ids}')В сухом остатке:
text()+f'строка'+.bindparams()для простых случаев и когда точно уверены в источникеschema/table.Table(..., schema=..., quote=True)+delete()+bindparam()для безопасной динамической сборки..returning()→ одним запросом удалили и получили подтверждение, какие именно строки затронуты.
Пользуйтесь на здоровье! :)
- литералы (строки, числа, даты...):
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.