I've had a look at this and submitted a patch that should make autocommit
work for the pg8000 Sqlalchemy dialect in the same way as it does for the
psycopg2 dialect https://github.com/zzzeek/sqlalchemy/pull/88.
On Friday, 9 May 2014 17:50:43 UTC+1, Tony Locke wrote:
>
> 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 <michael7...@gmail.com>
>> 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:pas...@db.foo.com: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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
