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-02 12:15:54Z

0

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))

edited Jan 2, 2016 at 12:15

answered Jan 1, 2016 at 16:00

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding table name to SQL query at runtime

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related