autocommit Transaction Isolation Level

[sumau]

Hello

Following advice from this article:

https://www.oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

and because we run large, consecutive queries, we set our transaction isolation level to 'autocommit' when connecting to our postgres DB. This means we cannot create explicit transactions and use begin() and commit():

https://www.oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

 

This has not a problem so far because we can create another (temporary) connection when we do want to have a roll-backable transaction. However since postgres DB is set to autocommit by default, I was wondering whether setting the transaction isolation level to 'autocommit' was needlessly complicated? 

For example in the example explained here: the https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#getting-a-connection

the query will still be committed, even if don't explicitly commit the transaction or the transaction isolation level to 'autocommit'.

Regards

Soumaya

[Mike Bayer]

On Thu, Apr 1, 2021, at 10:06 AM, sumau wrote:

Hello

Following advice from this article:

https://www.oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

and because we run large, consecutive queries, we set our transaction isolation level to 'autocommit' when connecting to our postgres DB. This means we cannot create explicit transactions and use begin() and commit():

https://www.oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

 

This has not a problem so far because we can create another (temporary) connection when we do want to have a roll-backable transaction. However since postgres DB is set to autocommit by default, I was wondering whether setting the transaction isolation level to 'autocommit' was needlessly complicated? 

the Python DBAPI is transactional by default and the "autocommit" feature of many DBAPIs is not part of the specification:  https://www.python.org/dev/peps/pep-0249/ .   the psycopg2 driver, as well as all other DBAPI drivers, are not in autocommit by default.

For example in the example explained here: the https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#getting-a-connection

the query will still be committed, even if don't explicitly commit the transaction or the transaction isolation level to 'autocommit'.

respectfully, that is incorrect for the DBAPI drivers included in SQLAlchemy, provided you aren't using MySQL with MyISAM tables. try it out

Regards

Soumaya

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5d474102-1212-4e8e-b26e-a78815ed5829n%40googlegroups.com.

[Soumaya Mauthoor]

Hello Mike

Thanks for quick response! I don't have postgres installed right now so I tried with sqlite, which is also autocommit by default. Example:

>>> from sqlalchemy import text, create_engine
>>> e = create_engine('sqlite:///foo.db')
>>> with e.connect() as conn: conn.execute(text('create table test(int col1)'))
...
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x7f6257fba7c0>
>>> e.execute('select * from test').fetchall()
[]

I didn't commit the transaction, but it seems to have autocommited since I can query it in the following query? 

Soumaya 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4cbda2cc-09ef-4a5c-be20-3bc710898fe0%40www.fastmail.com.

[Mike Bayer]

On Thu, Apr 1, 2021, at 11:20 AM, Soumaya Mauthoor wrote:

Hello Mike

Thanks for quick response! I don't have postgres installed right now so I tried with sqlite, which is also autocommit by default. Example

ahha - that's again a very specific quirk of the pysqlite driver that it does not autobegin the transaction if DDL is encountered, which is a bug in the driver, see https://bugs.python.org/issue9924

This autocommit situation has been mostly unchanged since I first started SQLAlchemy in 2005.     So I've been working with this behavior literally every day for sixteen years with a dozen or more DBAPI drivers.    It's safe to trust I know what i am speaking of !  :)

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN14jWQsMN66PBeh0Vg7xu4UPHecMqZut-bGccwdAvLLoZCHNQ%40mail.gmail.com.

[Soumaya Mauthoor]

Ooooh I see thanks for clarifying :-)

Soumaya

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/sfAPI9_vwaE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2a6c2fcc-7bbe-44cf-a232-e9131cc7f436%40www.fastmail.com

[Soumaya Mauthoor]

Hey Mike

Sorry to be a pain but I finally got postgresql working on my laptop and I'm getting the same behaviour as for sqlite (see below). I'm obviously doing something wrong, any idea where? I'm writing an in-house tutorial on sqlalchemy for colleagues, including an explanation of why we set the Transaction Isolation Level to autocommit, so it would be great to have an example :-)

>>> from sqlalchemy import text,create_engine
>>> c = create_engine('postgresql://***:***@localhost:5432/postgres').connect()
>>> c.execute(text('create table test(col1 int)'))
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x7ffc412b91f0>
>>> del c
>>> c = create_engine('postgresql://***:***@localhost:5432/postgres').connect()

>>> c.execute(text('select * from test')).fetchall()

[]

Thanks

Soumaya

[Mike Bayer]

If you are using the legacy engine, that is, without "future=True" in create_engine(), SQLAlchemy autocommits statements that have strings like "CREATE TABLE", "INSERT", etc.  This is called "library level autocommit", it's deprecated and the whole thing is documented at https://docs.sqlalchemy.org/en/14/core/connections.html#library-level-e-g-emulated-autocommit

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN14jWSQyFAL_iOVV%3DvsyBhOB86di__HiP2odq9cxoZ%2BzzscoQ%40mail.gmail.com.

[Soumaya Mauthoor]

That makes complete sense now, thanks Mike:-)

Soumaya

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/db166aad-37ca-46a1-b728-c652a74b05e2%40www.fastmail.com.