On May 6, 2014, at 6:09 PM, Michael Costello <[email protected]> wrote:
> Hello. > > Setup: > python 2.7.6 > postgres 9.3.4 > sqlalchemy 0.9.4 (also, 0.8.4) > pg8000 1.9.8 > > I am attempting to create a database using sqlalchemy with the above tools > and the following code: > > from sqlalchemy import create_engine > > dburl = "postgresql+pg8000://user:[email protected]:5432/postgres" > > engine = create_engine(dburl) > > conn = engine.connect() > conn.execute("COMMIT") > conn.execute("CREATE DATABASE qux") > conn.close() > > but I receive the following error: > > sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', > 'CREATE DATABASE cannot run inside a transaction block') 'CREATE DATABASE > qux' () > > However, running the same code against the same database but using sqlalchemy > version 0.8.0 works. > > Is there something I can do to get 0.9.4 to work for me? I can't imagine why that would work differently on 0.8.0 because the transactional behavior is the same on the SQLAlchemy side. Running this test with the latest pg8000 1.9.8: from sqlalchemy import create_engine e = create_engine("postgresql+pg8000://scott:tiger@localhost/test", echo=True) conn = e.connect() conn.execute("COMMIT") conn.execute("create database foo") output on 0.9.4: sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 'CREATE DATABASE cannot run inside a transaction block') 'create database foo' () output on 0.8.0: sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 'CREATE DATABASE cannot run inside a transaction block') 'create database foo' () output on 0.8.4: sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 'CREATE DATABASE cannot run inside a transaction block') 'create database foo' () etc. so i think perhaps your pg8000 version has changed. To achieve this you should use psycopg2 and use psycopg2's "autocommit" mode. See http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#postgresql-isolation-level and http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#psycopg2-transaction-isolation-level; with psycopg2 you can use conn.execution_options("AUTOCOMMIT").execute("CREATE DATABASE qux"). -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
