I'm doing an SQL insterface, and I'm having a problem with string formatting. What I want to do is an SQL INSERT INTO table_name statement. When I do it now, using parameterized instertion, I get unwanted quotes around my table name. This is the code:
query = "INSERT INTO %(table)s VALUES %(vals)s;"
self.cur.execute(query, params)
What happens is this:
ERROR: syntax error at or near "'books'"
LINE 1: INSERT INTO 'books' VALUES 'hej, hej, hej, hej';
As you can see, there are quotation marks around books, causing a syntax error.
I have tried to find info on this but with no luck. How can I fix this?
You cannot use execute parameters as table names or column names. They are only allowed for values.
You have to format the SQL-statement first:
query = "INSERT INTO {} VALUES ({})".format(table_name, ','.join(['%s']*number_of_columns))
self.cur.execute(query, params)
Or better, use a package, that already provide some database-abstraction, like SQLAlchemy.