How to execute SET statement from sqlalchemy?

[Radha Tanuku]

I am executing the following statements for a connection as below:

statement #1
db_session.execute('SHOW WLM_QUERY_SLOT_COUNT).scalar;

statement#2
db_session.execute('SET WLM_QUERY_SLOT_COUNT=10');

statement #3
db_session.execute('SHOW WLM_QUERY_SLOT_COUNT).scalar;


I could get successful result for the above statement #1.  Statement#2 execution is not setting to new value. Statement # 3 still shows the original value.
Any help on this ?

[Radha Tanuku]

The scope of all 3 statements are under single session.

[Radha Tanuku]

The database used is Redshift in this case. The db_session gets a connection for a Redshift database.

On Friday, June 27, 2014 1:34:32 PM UTC+5:30, Radha Tanuku wrote:

[Jonathan Vanasco]

are you sure that db_session is a Session object?

`Engine` also has an `execute` method, which runs in it's own connection.  

If you're using an `Engine` and not `Session`, then you need to encapsulate it in a transaction 

   * http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#using-transactions

[Mike Bayer]

I don't have redshift available to test.    Keep in mind SET is usually per-"session", that is, Postgresql/redshift "session".  When a new connection starts up, that old setting is gone.  So it depends on how db_session is being set up.

E.g.:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
db_session = Session(e)

print db_session.execute('SHOW statement_timeout').scalar()

db_session.execute('SET statement_timeout=10')

# uncomment this to show it go back to zero
# db_session.connection().invalidate()
# db_session.rollback()

print db_session.execute('SHOW statement_timeout').scalar()

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.