使用cx_Oracle将变量绑定到表名 [英] Binding variable to table name with cx_Oracle
问题描述
我在Python中使用cx_Oracle,无法将变量用作表名,例如以下简单示例:
I'm using cx_Oracle in Python and can't get a variable be used as table name, like in this simple example:
query = "select * from some.:usertable.userinfo"
bindvars = {'usertable':usertable}
cursor.execute(query, bindvars)
正确的语法是什么?当我使用 WHERE
…等但不是与表名一起使用时,变量替换可以很好地工作。我想我必须以某种方式分隔:usertable ...
What is the correct syntax? Variable substition works fine when I use WHERE
… etc. but not with table names. I guess I have to separate ":usertable" somehow…
推荐答案
数据库适配器很少支持将参数用于不是价值(需要引用的内容)。要么使用字符串格式设置(狡猾,就会冒着注入SQL的危险),要么使用像SQLAlchemy这样的库,让您使用Python代码生成有效的SQL。
Database adapters rarely support using parameters for anything that isn't a 'value' (something that needs quoting). Either use string formatting (dodgy, you run the risk of a sql injection) or use a library like SQLAlchemy that let's you produce valid SQL using Python code.
如果确定您的 usertable
值是合理的(例如,对照现有表名列表进行检查),则可以执行以下操作:
If you are certain your usertable
value is sane (checked against a list of existing table names, for example), the following would work:
query = 'select * from some.{usertable}.userinfo'.format(usertable=usertable)
这篇关于使用cx_Oracle将变量绑定到表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!