Python Psycopg2 使用占位符插入表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19235686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Psycopg2 Insert Into Table with Placeholders
提问by Timothy Dalton
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?
这可能是一个相当愚蠢的问题，但我在这里做错了什么？它创建了表格，但 INSERT INTO 不起作用，我想我在占位符上做错了什么？
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
INSERT INTO 调用不起作用，它给了我
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!
有人可以帮我解决这个问题吗？谢谢一堆！
采纳答案by fog
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:
您正在使用 Python 字符串格式，这是一个非常糟糕的主意 (TM)。想想 SQL 注入。正确的做法是使用绑定变量：
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.
其中参数元组作为第二个参数给出execute()。此外，您不需要转义任何值，psycopg2 将为您进行转义。在这种特殊情况下，还建议不要在变量 ( escaped_name) 中传递表名，而是将其嵌入到查询字符串中：psycopg2 不知道如何引用表名和列名，只知道如何引用值。
See psycopg2 documentation:
请参阅 psycopg2 文档：
https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
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_nameyou can do:
如果要以编程方式生成 SQL 语句，通常的方法是使用 Python 格式的语句和变量绑定的参数。例如，如果您有表名，则escaped_name可以执行以下操作：
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 ifescaped_nameis 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.
请注意，当且仅当escaped_name由忽略任何外部输入（例如表基名和计数器）的代码生成时，这是安全的，但如果您使用用户提供的数据，则存在 SQL 注入的风险。
回答by Matt
As of psycopg2 v2.7there is a supported way to do this: see the psycopg2.sqldocs.
截至目前，psycopg2 v2.7有一种受支持的方法可以做到这一点：请参阅psycopg2.sqldocs。
