SqlMagic.autocommit=True vs conn.autocommit = True

[jameshowison]

Using ipython-sql 0.3.9 installed via conda together with postgres and I'm not sure if the SqlMagic.autocommit=True option is working.

This code

%load_ext sql

%config SqlMagic.autocommit=True

%sql postgresql://localhost/

%sql CREATE DATABASE my_test_db

Produces a InternalError: (psycopg2.errors.ActiveSqlTransaction) CREATE DATABASE cannot run inside a transaction block error.

Conversely, this works fine

import psycopg2 as pg

conn = pg.connect(host='localhost')
conn.autocommit = True
cur = conn.cursor()
cur.execute('CREATE DATABASE class_music_festival')
conn.close()

Anyone know if %config SqlMagic.autocommit=True is supposed to do the same as conn.autocommit = True

[edublancas]

I looked at the code to understand why this is failing.

It turns out, ipython-sql isn't using the psycopg2's autocommit feature. %config SqlMagic.autocommit=True sets a flag that causes ipython-sql to run COMMIT after each command; so, if we turn it on, running this:

CREATE DATABASE my_test_db

is the same as running this:

BEGIN; -- executed since psycopg2's autocommit is off
CREATE DATABASE my_test_db; -- executed by the user
COMMIT; -- executed by ipython-sql due to the autocommit=True

Which causes the error:

CREATE DATABASE cannot run inside a transaction block

@jameshowison my team maintains a fork of this project and we're tackling this issue already so feel free to share your feedback! https://github.com/ploomber/jupysql/issues/90