sqlalchemy with postgres:插入到列有括号的表中 [英] sqlalchemy with postgres: insert into a table whose columns have parentheses
问题描述
假设您在 postgres 中有一个表 "foo"
,列名为 "col (parens) name"
.psql 命令
Suppose you have a table "foo"
in postgres with column name "col (parens) name"
. The psql command
INSERT INTO "foo" ("col (parens) name") VALUES ('bar');
工作正常.但是,如果我尝试使用 sqlalchemy(0.9.7 版)执行相同操作,则生成的 Python 代码将失败:
works just fine. However, if I try to do the same using sqlalchemy (version 0.9.7), the resulting python code fails:
conn = sqlalchemy.create_engine('postgresql://name:password@host:port/database')
meta = sqlalchemy.schema.MetaData()
meta.reflect(bind=conn)
foo = meta.tables['foo']
vals = [{'col (parens) name': 'hi'}, {'col (parens) name': 'bye'}]
conn.execute(foo.insert(values=vals))
这不起作用,出现以下错误:
This does not work, giving the following error:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "sqlalchemy/engine/base.py", line 729, in execute
return meth(self, multiparams, params)
File "sqlalchemy/sql/elements.py", line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
compiled_sql, distilled_params
File "sqlalchemy/engine/base.py", line 957, in _execute_context
context)
File "sqlalchemy/engine/base.py", line 1162, in _handle_dbapi_exception
util.reraise(*exc_info)
File "sqlalchemy/engine/base.py", line 950, in _execute_context
context)
File "sqlalchemy/engine/default.py", line 436, in do_execute
cursor.execute(statement, parameters)
KeyError: 'col (parens'
显然,用于绑定 db 参数的 sqlalchemy 方法在使用 python 字符串插值时遇到了麻烦.有什么解决方法的建议吗?
Apparently the sqlalchemy method to bind db parameters is running into trouble with python string interpolation. Any suggestions for a workaround?
推荐答案
将 paramstyle="format"
添加到 create_engine
调用.它将改变查询值插入查询的方式,使其不会在结束括号时崩溃.
Add paramstyle="format"
to the create_engine
call. It will change the way the query values are inserted to the query in a way that it won't crash on closing brackets.
conn = sqlalchemy.create_engine(
'postgresql://name:password@host:port/database',
paramstyle="format"
)
这篇关于sqlalchemy with postgres:插入到列有括号的表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!