Autocommit

These examples are meant to highlight best practices and allow users to explore the difference between having AUTOCOMMIT enabled versus disabled.

Throughout our examples, we use engine.begin(). Code inside of a with-block using engine.begin() will behave the same whether AUTOCOMMIT is enabled (y) or disabled (n). This is because engine.begin() automatically issues a COMMIT when the block successfully finished or a ROLLBACK if it fails. For any basic database modifications, the recommended best practice is to use engine.begin(). This is to ensure that the objects are in the desired state before performing further manipulation steps. While it is possible to use engine.connect() instead, when AUTOCOMMIT is disabled, you must manually, inside of the with-block, call commit(). For a more nuanced discussion, see Connection Management in SQLAlchemy.

To explore what changes when AUTOCOMMIT is set to n, you can:

1. Modify your configuration file, as described on Connection Configuration, to set "AUTOCOMMIT":"n".

2. Modify the code in the examples to use engine.connect() instead of engine.begin().

3. Try to execute the examples and see what happens. You should get failures, as engine.connect() without AUTOCOMMIT requires you to manually call conn.commit() as needed.

4. Add conn.commit() to the examples and try to execute them. They should now pass.

Note

The examples on this page are presented in sections, but they are all part of the same module examples/features/specific_focuses/_1_autocommit.py. They are broken up here to provide further information and context. Please ensure that you read and execute the parts in order.

Data Definition Language (DDL)

examples/features/specific_focuses/_1_autocommit.py

from sqlalchemy import text

from examples.config import (
    DEFAULT_SCHEMA_NAME,
    ENGINE,
    SQL_ALCHEMY,
)

# 0. Ensure that the schema exists
SQL_ALCHEMY.create_schema(engine=ENGINE, schema=DEFAULT_SCHEMA_NAME)

# 1. Data Definition Language (DDL)
TABLE_NAME = "HEX_LOOKUP"

create_hex_table = f"""
    CREATE OR REPLACE TABLE {DEFAULT_SCHEMA_NAME}.{TABLE_NAME} (
        id INTEGER IDENTITY PRIMARY KEY,
        hex_code CHAR(6) NOT NULL
    );
"""

with ENGINE.begin() as conn:
    conn.execute(text(create_hex_table))

It is recommended to use engine.begin() for DDL statements.

Note

Note that IDENTITY (an Exasol Database keyword) must be included for the ID to autoincrement. This is not required for the examples given in Basic Operations or ORM, where SQLAlchemy generates the required SQL statement for the Exasol database instance.

Data Query Language (DQL)

examples/features/specific_focuses/_1_autocommit.py

# 2. Data Query Language (DQL)
query = f"""
SELECT *
FROM SYS.EXA_ALL_TABLES
WHERE TABLE_SCHEMA = '{DEFAULT_SCHEMA_NAME}'
AND TABLE_NAME = '{TABLE_NAME}'
"""

with ENGINE.connect() as conn:
    results = conn.execute(text(query)).fetchall()
    # conn.commit() is never needed for DQL

print(f"Table search result: {results}")

Typically, SELECT is considered the only member of DQL. In such use cases, no data manipulations are occurring, so no manual commits need to be made. As such, this code works and should stay the same regardless of whether AUTOCOMMIT is enabled or disabled.

Data Manipulation Language (DML)

examples/features/specific_focuses/_1_autocommit.py

# 3. Data Manipulation Language (DML)
hex_data = [{"hex_code": "FF5733"}, {"hex_code": "33FF57"}, {"hex_code": "3357FF"}]

insert_statement = (
    f"INSERT INTO {DEFAULT_SCHEMA_NAME}.{TABLE_NAME} " f"(hex_code) VALUES (:hex_code)"
)
with ENGINE.begin() as conn:
    conn.execute(text(insert_statement), hex_data)
    # if `ENGINE.connect()` were used instead of `ENGINE.begin()` and
    # "AUTOCOMMIT" were disabled, you MUST include a `conn.commit()`
    # for the change to be persisted; otherwise, no results will be
    # found in #4,

# 4. DQL
select_statement = f"SELECT * FROM {DEFAULT_SCHEMA_NAME}.{TABLE_NAME}"

with ENGINE.connect() as conn:
    result = conn.execute(text(select_statement)).fetchall()

    print(f"Number of entries: {len(result)}")
    for row in result:
        print(row)

Like the Data Definition Language (DDL) example, it is preferred here to use begin() in the with-block. This behaves the same whether AUTOCOMMIT is enabled or disabled. For a more nuanced discussion, see Connection Management in SQLAlchemy.

Connection Management in SQLAlchemy

The begin() Method

The begin() method provides a transactional boundary. It is the safest choice for an operation that modifies the database.

When to use begin()

Scenario	Description
DDL	Use for CREATE, ALTER, or DROP commands to ensure schema changes are committed.
DML	Ideal for INSERT, UPDATE, and DELETE where you want an “all-or-nothing” result.
Auto-Rollback	If your code fails mid-block, SQLAlchemy automatically reverts any partial changes.

The connect() Method

The connect() method provides a raw connection. It offers granular control but places the responsibility of transaction management on the user.

When AUTOCOMMIT is enabled, using connect() ensures that each statement is finalised by the database as soon as it is executed. If AUTOCOMMIT is disabled, then you must manually use commit.

When to use connect()

Scenario	Description
DQL	Best for SELECT statements where no data is being changed.
DML	Use if you need to commit specific parts of a long-running script at different times.
Performance Tuning	Avoids the overhead of starting a transaction block when only reading data.