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
On Sun, Apr 4, 2021, at 1:47 PM, Soumaya Mauthoor wrote:
> 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
>
>
>
>
>
>
> Le jeu. 1 avr. 2021 à 18:33, Soumaya Mauthoor <[email protected]> a
> écrit :
>> Ooooh I see thanks for clarifying :-)
>>
>> Soumaya
>>
>> On Thu, 1 Apr 2021, 17:34 Mike Bayer, <[email protected]> wrote:
>>> __
>>>
>>>
>>> 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 ! :)
>>>
>>>
>>>
>>>>
>>>>
>>>>
>>>>
>>>> :
>>>>
>>>> >>> 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
>>>>
>>>> Le jeu. 1 avr. 2021 à 15:27, Mike Bayer <[email protected]> a écrit
>>>> :
>>>>> __
>>>>>
>>>>>
>>>>> 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 [email protected].
>>>>>> To view this discussion on the web visit
>>>>>> https://groups.google.com/d/msgid/sqlalchemy/5d474102-1212-4e8e-b26e-a78815ed5829n%40googlegroups.com
>>>>>>
>>>>>> <https://groups.google.com/d/msgid/sqlalchemy/5d474102-1212-4e8e-b26e-a78815ed5829n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>
>>>>>
>>>>> --
>>>>> 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 [email protected].
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/sqlalchemy/4cbda2cc-09ef-4a5c-be20-3bc710898fe0%40www.fastmail.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/sqlalchemy/4cbda2cc-09ef-4a5c-be20-3bc710898fe0%40www.fastmail.com?utm_medium=email&utm_source=footer>.
>>>>
>>>> --
>>>> 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 [email protected].
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/sqlalchemy/CAN14jWQsMN66PBeh0Vg7xu4UPHecMqZut-bGccwdAvLLoZCHNQ%40mail.gmail.com
>>>>
>>>> <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWQsMN66PBeh0Vg7xu4UPHecMqZut-bGccwdAvLLoZCHNQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>>>
>>>
>>> --
>>> 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 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
>>> [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/2a6c2fcc-7bbe-44cf-a232-e9131cc7f436%40www.fastmail.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/2a6c2fcc-7bbe-44cf-a232-e9131cc7f436%40www.fastmail.com?utm_medium=email&utm_source=footer>
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAN14jWSQyFAL_iOVV%3DvsyBhOB86di__HiP2odq9cxoZ%2BzzscoQ%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWSQyFAL_iOVV%3DvsyBhOB86di__HiP2odq9cxoZ%2BzzscoQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/db166aad-37ca-46a1-b728-c652a74b05e2%40www.fastmail.com.
