Sqlalchemy with postgres: insert into table whose columns have parentheses
Suppose you have a table "foo"
in postgres with a column name "col (parens) name"
. Psql command
INSERT INTO "foo" ("col (parens) name") VALUES ('bar');
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))
It doesn't work, resulting in 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'
Apparently the sqlalchemy method to bind db parameters is having an issue with python string interpolation. Any suggestions for a workaround?
+3
source to share