sqlalchemy with postgres: insert into a table whose columns have parentheses

Question

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

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'

Apparently the sqlalchemy method to bind db parameters is running into trouble with python string interpolation. Any suggestions for a workaround?

SQLA 2.0, modulo some syntactic differences, seems to handle this case correctly. — snakecharmerb
– snakecharmerb, Commented May 22 at 17:15

warownia1 · Accepted Answer · 2018-02-03 18:08:09Z

9

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

answered Feb 3, 2018 at 18:08

warownia1

2,9652 gold badges24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlalchemy with postgres: insert into a table whose columns have parentheses

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related