Команда Postgres truncate SQL: передача имени динамической таблицы
Я работаю над веб-проектом Python, используя базу данных postgres в качестве бэкэнда с пакетом psycopg2.
Раньше я пытался static запросить команду truncate для демонстрационных целей, после того, как она заработала, я попытался сделать это dynamic, тем самым пропустив table name.
def clear_tables(query: str, vars_list: list[Any]):
# psycog 2 connection engine code
cursor.executemany(query=query, vars_list=vars_list)
conn.commit()
def foo():
vars_list: list[Any] = list()
table_name: str = "person"
record: tuple[Any] = (table_name,)
vars_list.append(record)
query = """
TRUNCATE TABLE %s RESTART IDENTITY;
"""
clear_tables(query=query, vars_list=vars_list)
При выполнении получаю ошибку. Каким-то образом table name получает нежелательные ' одинарные кавычки
psycopg2.errors.SyntaxError: syntax error at or near "'person'"
LINE 2: TRUNCATE TABLE 'person' RESTART IDENTITY;
Как убрать эти цитаты?
Я на много лет отстал от PostgreSQL, но обратите внимание, что в базах данных SQL обычно не все поля могут быть параметризованы в первую очередь. Частью смысла подготовленных операторов является построение плана запроса перед его выполнением; но если вы не знаете, какая таблица, вы не сможете найти, какие индексы применяются, и в противном случае у вас не будет достаточно, чтобы начать планирование. (Да, это обоснование не применимо к TRUNCATE, но в целом вы не позволите ненадежному коду решать, какое имя таблицы усекать в первую очередь, поэтому многие доводы в пользу подготовленных операторов не учитываются. не применяется)
Привет Belayer, Спасибо. Я пытался использовать его, но как-то это не сработало. При дальнейшем поиске я получил AsIS расширение от psycog2, сейчас попробую.
Привет, Чарльз, спасибо. Хорошо сказано и я с вами согласен. Я новичок в sql, мне нравится учиться и читать о том, как писать хорошие postgres/sql запросы. У вас есть или вы хотите предложить некоторые open source/free resources or code snippets. Как вы упомянули, некоторые компоненты/части запросов sql не должны быть параметризованы, в этом есть смысл.
@Belayer %I и %L — это заполнители, используемые функцией format(), доступной в Postgres. Они не работают с Psycopg2.
Ответы 2
Внесите следующие изменения и повторите попытку:
query = "TRUNCATE TABLE {tableName} RESTART IDENTITY;"
cursor.execute(sql.SQL(query).format(tableName=sql.Literal("person"))
Помимо предотвращения целого ряда проблем с безопасностью, этот стиль форматирования предотвращает атаки SQL Injection. В будущем лучше использовать этот стиль или некоторые другие возможные, используя встроенный .format().
Для дальнейшего чтения, пожалуйста, проверьте документацию.
Это работает и защищено от SQL-инъекций:
import psycopg2
from psycopg2 import sql
def truncate(conn: psycopg2.extensions.connection, schema: str, table: str):
query = sql.SQL("TRUNCATE {schema}.{table} RESTART IDENTITY").format(
schema=sql.Identifier(schema), table=sql.Identifier(table)
)
with conn.cursor() as cur:
cur.execute(query)
conn = psycopg2.connect()
truncate(conn, "public", "foo")
Используйте
%I(идентификатор) вместо%s(строка) в своем заявлении. Итак: TRUNCATE TABLE %I RESTART IDENTITY;