How to execute SET statement from sqlalchemy?

Question

I am trying to execute SET statement from sqlalchemy.

>>> from sqlalchemy import create_engine
>>> c = create_engine('postgres://myuser@myremoteserver/mydb?keepalives_idle=4&keepalives_interval=1&keepalives_count=5')
>>> c.execute('SET statement_timeout=1000')
<sqlalchemy.engine.base.ResultProxy object at 0xb7b250>

It will return ResultProxy object. But value for statement_timeout is not set.

>>> c.execute('SHOW statement_timeout').scalar()
'0'

If I will try ResultProxy.scalar then its give error.

>>> c.execute('SET statement_timeout=1000').scalar()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1676, in scalar
    self.connection._handle_dbapi_exception(e, None, None, self.cursor, self.context)
  File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 923, in _handle_dbapi_exception
    self.invalidate(e)
  File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 633, in invalidate
    raise exc.InvalidRequestError("This Connection is closed")
sqlalchemy.exc.InvalidRequestError: This Connection is closed

How to set the value for this variable?

Are you, by any chance, execute those in different sessions? — Milen A. Radev
– Milen A. Radev, Commented Feb 26, 2013 at 9:25

Chris Travers · Accepted Answer · 2013-04-30 15:23:37Z

1

Two observations.

First, your error suggests trying to use a closed connection. Is it possible there is some sort of weird connection pooling problems going on in the background or that the connection was closed in between?

Secondly, your second query does not return a value.

Reading closely your first approach appears to work, except that ResultProxy is not set to a value because the statement didn't return anything. You probably just want to discard it and run the SHOW command instead to get the value for the application.

answered Apr 30, 2013 at 15:23

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

Collectives™ on Stack Overflow

How to execute SET statement from sqlalchemy?

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