What is the difference between engine.begin() and engine.connect()?

Question

i go first straight for my questions: Why would one rather use engine.connect() instead of engine.begin(), if the second is more reliable? Then, why is it still on the tutorial page of SQLAlchemy and everywhere in stackoverflow? Performance?

Why does engine.connect() work so inconsistently? Is the problem withing the autocommit?

My backgroundstory to this is, that i just resolved an issue. Normal SQL-queries like SELECT, CREATE TABLE and DELETE would work flawlessly when using engine.connect(). Though, using MERGE would work very inconsistently. Sometimes committing, sometimes blocking other queries, sometimes nothing. Here it is recommended to use engine.begin() for MERGE queries.

So i substituted the following code:

with engine.connect() as connection:
    connection.execute('MERGE Table1 USING Table2 ON .....')

by

with engine.begin() as connection:
    connection.execute('MERGE Table1 USING Table2 ON .....')

and now everything works perfectly. Inlcuding the queries of SELECT,CREATE TABLE and DELETE. In the SQLAlchemy docs it says the second option uses transactions with a transaction-commit, but the scope of with engine.connect() does an autocommit aswell. Sorry i am a complete newbie to SQL.

Answer 1

the scope of with engine.connect() does an autocommit as well

No, it doesn't. That's the most striking difference between with engine.connect() and with engine.begin()

with engine.connect() as conn:
    # do stuff
# on exit, the transaction is automatically rolled back

with engine.begin() as conn:
    # do stuff
# on exit, the transaction is automatically committed if no errors occurred

As mentioned in the tutorial, engine.connect() is used with the "[explicitly] commit as you go" style of code, while engine.begin() represents the "begin once" style.

Transactions are used in both cases. However, engine.begin() begins the transaction immediately, while engine.connect() waits until a statement is executed before beginning the transaction. That permits us to alter the characteristics of the transaction that will eventually be started. A common use of this engine.connect() feature is to use non-default transaction isolation:

# default isolation level
with engine.connect() as conn:
    print(conn.get_isolation_level())  # REPEATABLE READ

# using another isolation level
with engine.connect().execution_options(
    isolation_level="SERIALIZABLE"
) as conn:
    print(conn.get_isolation_level())  # SERIALIZABLE