如何在Python中从SQL查询的字符串中删除引号? [英] How to remove the quotes from a string for SQL query in Python?
问题描述
我有一个数据库名称字典。我从字典中取一个名字
I have a dictionary of database names. I take a name from the dictionary
database_name = database_dict[i]
说出database_name的值为'foo'
lets say the value for database_name is 'foo'
使用Psycopg2我正在执行一条语句:
Using Psycopg2 I am executing a statement:
cur.execute("INSERT INTO %s VALUES(...);", database_name)
我在foo上遇到语法错误,因为它应该是 INSERT INTO foo VALUES而不是 INSERT INTO'foo'VALUES
I get A syntax error at foo, because it should be "INSERT INTO foo VALUES" not "INSERT INTO 'foo' VALUES"
任何建议如何传递表名的字符串值并删除单引号?我应该在我的数据库字典值中放置一个转义符吗?
Any advice how to pass in a string value for the name of the table and removing the single quotes? Should I place an escape character inside my database dictionary values?
编辑:更近的地方在这里:如何从postgresql中的表中删除单引号?
Something closer is here: How do I remove single quotes from a table in postgresql?
,但是我无法使用REMOVE使它工作。
but I could not get it to work using REMOVE. It gave a syntax error at the single quote inside the remove statement.
推荐答案
SQL查询的结构组件,例如表和在尝试使用 cursor.execute(query,params)
的第二个参数时,无法对字段名称进行参数化。仅数字/文字数据值可以参数化。
The structural components of an SQL query such as table and field names cannot be parameterized as you attempt in second argument of cursor.execute(query, params)
. Only numeric/literal data values can be parameterized.
考虑将 database_name 变量插值到SQL查询字符串中,但使用psycopg2的 sqlIdentifier()
和 str.format
:
Consider interpolating the database_name variable into the SQL query string but do so safely with psycopg2's sqlIdentifier()
with str.format
:
from psycopg2 import sql
...
cur.execute(sql.SQL('INSERT INTO {} VALUES(...)').format(sql.Identifier(database_name)))
有效参数化在您的情况下将绑定附加查询中 VALUES(...)
中传递的数据值,例如 VALUES(%s,% s,%s)
。或者在其他查询中:
Valid parameterizaiton in your case would be to bind the data values passed in the VALUES(...)
in append query such as VALUES(%s, %s, %s)
. Alternatively in other queries:
"SELECT %s AS NewColumn..."
"...WHERE fieldname = %s OR otherfield IN (%s, %s, %s)"
"...HAVING Max(NumColumn) >= %s"
这篇关于如何在Python中从SQL查询的字符串中删除引号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!