Adding table name to SQL query at runtime

Question

I write a method like below:

def add(self,table_name, tc_no, file_no):
    self._cursor.execute("select HastaId from {}".format(table_name)," where TC=%s and FileNo=%s",(tc_no,file_no))
    row = self._cursor.fetchone()
    return row

and I got an error

TypeError: execute() takes at most 2 positional arguments (3 given)

I know the mistake in the format() . How can i use that?

Gord Thompson · Accepted Answer · 2016-01-02T12:15:54.930

You have the right idea. Query parameters can only represent column values, not column or table names.

Therefore you do need to use string formatting to insert the table name into the SQL command text and then use query parameters to supply the values for the WHERE clause.

So, a construct like this will not work:

crsr.execute(
        "select HastaId from %s where TC=%s and FileNo=%s",
        (table_name, tc_no, file_no))

but this will work

crsr.execute(
        "select HastaId from [{}] where TC=%s and FileNo=%s".format(table_name),
        (tc_no, file_no))

Adding table name to SQL query at runtime

1 Answers1