Psycopg2插入到带有占位符的表中 [英] Psycopg2 Insert Into Table with Placeholders

问题描述

这可能是一个愚蠢的问题，但是我在这里做错了什么?它创建了表，但是INSERT INTO不起作用，我猜我在占位符上做错了什么?

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

INSERT INTO调用不起作用，它给了我

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!

推荐答案

您正在使用Python字符串格式，这是一个非常糟糕的主意(TM).考虑一下SQL注入.正确的方法是使用绑定变量:

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

其中参数的元组作为execute()的第二个参数给出.另外，您不需要转义任何值，psycopg2将为您完成转义.在这种特殊情况下，建议不要将表名传递到变量(escaped_name)中，而是将其嵌入查询字符串中:psycopg2不知道如何仅引用值来引用表名和列名.

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.

请参阅psycopg2文档:

See psycopg2 documentation:

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

如果要以编程方式生成SQL语句，通常的方法是对语句使用Python格式，对参数使用变量绑定.例如，如果您在escaped_name中具有表名，则可以执行以下操作:

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.

请注意，当且仅当 escaped_name是由代码生成的，而忽略了任何外部输入(例如，表基本名称和计数器)时，这是安全的，但存在注入SQL的风险如果您使用用户提供的数据.

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.

这篇关于Psycopg2插入到带有占位符的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！