SqlMagic.autocommit=True is not applied to connection options

[yafimvo]

Running

%load_ext sql
%config SqlMagic.autocommit=True
%sql postgresql://localhost/
%sql CREATE DATABASE new_db

returns the following error:

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

[edublancas]

was this executed in a new session? The error suggests that the CREATE DATABASE statement was executed inside the middle of a transaction

[yafimvo]

@edublancas it happens when executing the session here.

According to docs, setting the autocommit option should fix it but according to the implementation, setting SqlMagic.autocommit doesn't affect the connection settings.

I didn't reference the original issue since I'm not sure we want to support this feature and if SqlMagic.autocommit=True and conn.autocommit = True are really equal.

[idomic]

Reference

[edublancas]

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

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

---

Psycopg2 issues the initial BEGIN command since the native autocommit flag is off (do not confuse it with our config.autocommit flag), as documented in a warning here.

It's best to rely on the driver's autocommit feature rather than manually issuing a COMMIT command. However, since this is driver-dependent, we should only do it selectively. Perhaps something like this:

if config.autocommit:
    try:
        conn.session.execution_options(isolation_level="AUTOCOMMIT")
    except Exception as e:
        # show some warning
        # commit manually
        manual_commit = True
    else:
        manual_commit = False

Then, use the manual_commit flag to issue a COMMIT command after running each query.

[edublancas]

since we closed #117, now we can test this.

the first thing is to check what happens when running:

%load_ext sql
%config SqlMagic.autocommit=True
%sql postgresql://localhost/
%sql CREATE DATABASE new_db

we already know that postgres fails, but we'll see what happens with the others.

then, we must check what happens with @yafimvo's solution: #91

my guess is that the final solution will involve some checking logic as I described in my earlier comment.

since you worked on the integration tests, do you want to take this @tonykploomber ?

[tonykploomber]

I can take this

[idomic]

FYI @jameshowison , this will be on the next release!