Question 88925 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres truncate command SQL: passing dynamic table name – Postgresql

I am working on a Python web project using a postgres database as backend with the psycopg2 package.

Earlier, I was trying static query for truncate command for demo purpose, after it works, I tried to make it dynamic, thereby passing 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)

On executing I am getting an error. Somehow the table name gets unwanted ' single quotes

psycopg2.errors.SyntaxError: syntax error at or near "'person'"
LINE 2:        TRUNCATE TABLE 'person' RESTART IDENTITY;

How to remove those quotes?

Tags:

2

Answers


  1. 0

    Make the following changes and retry:

    query = "TRUNCATE TABLE {tableName} RESTART IDENTITY;"
cursor.execute(sql.SQL(query).format(tableName=sql.Literal("person"))

    Aside from prevent a whole host of safety issues, this style of formatting prevents SQL Injection attacks. Going forward, it’s better to adopt this style or some of the other possible ones using built in .format().

    For further reading please check documentation.

    Login or Signup to reply.
  2. 0

    This works, and is safe against SQL injection:

    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")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search