Psycopg2 Insert Into Table with Placeholders

Question

This might be a rather silly question but what am I doing wrong here? It creates the table but the INSERT INTO doesn't work, I guess I'm doing something wrong with the placeholders?

conn = psycopg2.connect("dbname=postgres user=postgres")
cur = conn.cursor()
escaped_name = "TOUR_2"
cur.execute('CREATE TABLE %s(id serial PRIMARY KEY, day date, elapsed_time varchar,    net_time varchar, length float, average_speed float, geometry GEOMETRY);' % escaped_name)

cur.execute('INSERT INTO %s (day,elapsed_time, net_time, length, average_speed, geometry) VALUES (%s, %s, %s, %s, %s, %s)', (escaped_name, day ,time_length,  time_length_net, length_km, avg_speed,  myLine_ppy))

conn.commit()
cur.close()
conn.close()

The INSERT INTO call doesn't work, it gives me

cur.execute('INSERT INTO %s (day,elapsed_time, net_time, length, average_speed,  
geometry) VALUES (%s, %s, %s, %s, %s, %s)'% (escaped_name, day ,time_length,  
time_length_net, length_km, avg_speed,  myLine_ppy))
psycopg2.ProgrammingError: syntax error at or near ":"
LINE 1: ...h, average_speed, geometry) VALUES (2013/09/01 , 2:56:59, 02...

Can someone help me on this one? Thanks a bunch!

Childlike

Answer 1

You are using Python string formatting and this is a Very Bad Idea (TM). Think SQL-injection. The right way to do it is to use bound variables:

cur.execute('INSERT INTO %s (day, elapsed_time, net_time, length, average_speed, geometry) VALUES (%s, %s, %s, %s, %s, %s)', (escaped_name, day, time_length, time_length_net, length_km, avg_speed, myLine_ppy))

where the tuple of parameters is given as second argument to execute(). Also you don't need to escape any value, psycopg2 will do the escaping for you. In this particular case is also suggested to not pass the table name in a variable (escaped_name) but to embed it in the query string: psycopg2 doesn't know how to quote table and column names, only values.

See psycopg2 documentation:

https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

If you want to programmatically generate the SQL statement, the customary way is to use Python formatting for the statement and variable binding for the arguments. For example, if you have the table name in escaped_name you can do:

query = "INSERT INTO %s (col1, ...) VALUES (%%s, ...)" % escaped_name
curs.execute(query, args_tuple)

Obviously, to use placeholders in your query you need to quote any % that introduce a bound argument in the first format.

Note that this is safe if and only if escaped_name is generated by your code ignoring any external input (for example a table base name and a counter) but it is at risk of SQL injection if you use data provided by the user.

Aldehyde

+1 To quote the doc: "Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." – Worser

Thank you fog, I understand now. I was looking for a solution because I want to automate the process of creating a table with an consecutive number, i. e. TOUR+'i' where 'i' would be a number and not always write a new SQL statement. Would you have any idea how to do this? – Childlike

@TimothyDalton The customary way to "automatically" generate SQL statements is to use Python formatting to build the statement and variable binding to pass the arguments. See my updated answer. – Aldehyde

Thanks again fog, it works now using the %% for my values and not solely the %. I couldn't find anything regarding this on the web. The second % directs the statement to grab the values from my second parameters, in this case the args_tuple, right? – Childlike

@TimothyDalton Hidden in the docs: When parameters are used, in order to include a literal % in the query you can use the %% string – Hasp

Why do the sentences "The right way to do it.." and "In this particular case.." contradict each other, with an example in the former sentence of how to do something with the latter sentence points out is impossible? -1 – Grosgrain

Answer 2

To expand on @Matt's answer, placeholders do not work for identifiers like table names because the name will be quoted as a string value and result in invalid syntax.

If you want to generate such a query dynamically, you can use the referred to pyscopg2.sql module:

from psycopg2.sql import Identifier, SQL

cur.execute(SQL("INSERT INTO {} VALUES (%s)").format(Identifier('my_table')), (10,))

Reflux