Hi, the pg8000 driver has an autocommit mode, activated using the boolean
'autocommit' attribute of the DBAPI connection. For example:
import pg8000conn = pg8000.connect(user="postgres", password="C.P.Snow")
conn.autocommit = Truecur = conn.cursor()cur.execute("CREATE DATABASE
qux")conn.autocommit = Falsecursor.close()conn.close()
I'm not sure if the SQLAlchemy driver for pg8000 supports using:
conn.execution_options(“AUTOCOMMIT”)
I'll investigate...
Cheers,
Tony.
On Wednesday, 7 May 2014 01:08:00 UTC+1, Michael Bayer wrote:
>
>
> On May 6, 2014, at 6:09 PM, Michael Costello
> <[email protected]<javascript:>>
> 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-leveland
>
> 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.
