CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Neel
Neel

Reputation: 21297

How to execute SET statement from sqlalchemy?

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?

Upvotes: 1

Views: 1684

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 1

Related Questions