Features

Autocommit

By default, SQLAlchemy-Exasol has AUTOCOMMIT turned on, as mentioned in Specific Parameters for Dialect SQLAlchemy-Exasol. This parameter is only relevant when using engine.connect(). When using engine.begin(), the transaction, if the inner steps are successful, is always automatically committed at the end of the with-block.

Committing or using autocommit ensures that any changes made during a transaction — such as inserts or updates — are permanently saved to the database. For more information on these patterns, please see Connection Management in SQLAlchemy and these other resources:

Autoincremented Columns

In SQLAlchemy-Exasol, the autoincrement feature leverages Exasol’s native IDENTITY columns to automatically generate unique, sequential primary key values. To enable this behavior when defining a table, set primary_key=True as shown in the following examples:

Once configured, Exasol generates a new ID on the server side whenever a new row is inserted.

Automatic Indexes

Exasol is a self-tuning database designed to eliminate the manual effort of performance optimization. Instead of requiring users to design and maintain indexes, the database engine intelligently generates and updates them on the fly during query execution to ensure optimal join and filter performance.

For SQLALchemy-Exasol users, this means:

  • Simplified Development: You are free from the burden of manual index management; the database handles it all for you.

  • Automatic Performance: Efficient indexes are created precisely when needed and are automatically maintained or discarded based on usage.

  • Optimized Storage: Indexes are only persisted upon a successful transaction commit, keeping your database clean and efficient.

Because Exasol provides this built-in auto-tuning, manual index creation is not only unnecessary but intentionally unsupported to prevent interference with the engine’s optimization algorithms. For more in-depth information, explore the Exasol documentation on indexes.

Caching

Since version 1.4, SQLAlchemy features a “SQL compilation caching” facility designed to significantly reduce Python interpreter overhead during query construction. This system works by generating a unique cache key that represents the structural state of a Core or an ORM SQL construct, including its columns, tables, and JOIN conditions. Once a specific query structure is compiled into a string for the first time, SQLAlchemy stores the result in an internal cache; subsequent executions with the same structure skip the expensive string compilation process and reuse the existing SQL. This is particularly beneficial for ORM-heavy applications, as it streamlines the logic for lazy loaders and relationship lookups.

For more information, see these pages from SQLAlchemy:

Since version 1.4, SQLAlchemy has also streamlined its “Reflection API” by implementing an internal metadata cache within the Inspector object. This system is designed to minimize the performance cost of querying database system catalogs—such as those in Exasol—by ensuring that schema details like columns, foreign keys, and constraints are only fetched once per inspector instance. When a method like get_foreign_keys() is called, the result is stored in an internal dictionary; subsequent requests for the same table metadata skip the network round-trip and return the cached data immediately. This is particularly advantageous for applications that perform heavy reflection operations.

For more details, see:

Foreign Keys

By default, Exasol does not enforce foreign keys or primary keys. Instead, they are primarily used as metadata to help the query optimizer create faster execution plans:

  • Default State: By default, constraints are created in a DISABLE state. This means you can insert data that violates referential integrity without the database stopping you.

  • Enforcement Setting: To prevent invalid data from being inserted, you can explicitly set the constraint to ENABLE.

  • Performance Impact: Exasol leaves them disabled by default because strict

    enforcement adds overhead during high-speed data loading (DML operations).

To see the status of your foreign key columns, check table EXA_ALL_CONSTRAINTS.

To check what your system settings are, use this SQL statement:

SELECT * FROM EXA_PARAMETERS
WHERE PARAMETER_NAME = 'CONSTRAINT_STATE_DEFAULT';

To check a foreign key constraint without switching the constraint to ENABLE, see Verification of the Foreign Key Property.

To switch a constraint to ENABLE, choose which SQL statement suits your purposes best:

-- For a specific constraint
ALTER TABLE <table_name> MODIFY CONSTRAINT <constraint_name> ENABLE;

-- For global enforcement, which will degrade performance
ALTER SYSTEM SET DEFAULT_CONSTRAINT_STATE = 'ENABLE';

Object Name Handling

Exasol interprets all case-insensitive (unquoted) object names as uppercase text. In contrast, SQLAlchemy considers all lowercase object names to be case-insensitive.

  • The sqlalchemy-exasol dialect converts all unquoted object names during schema-level communication (e.g., during table and index reflection) to what is required for SQLAlchemy. Note that this conversion does not occur when you provide a fully prepared SQL statement (e.g., via a raw string or text() construct) or are selecting rows based on values, e.g. in a where clause.

  • Therefore, whenever you have unquoted table names, you should use all lowercase names when working with SQLAlchemy metadata objects, but ensure you use all uppercase names when writing strings to match Exasol’s internal storage.

  • Quoted object names should not have their cases altered.

For an example, see Object Name Handling.

Object-Relational Mapping

SQLAlchemy-Exasol utilizes the Object-Relational Mapping (ORM) interface provided by SQLAlchemy. ORM is a powerful tool that maps database tables to Python classes, allowing developers to interact with relational databases using object-oriented code instead of raw SQL. It is highly useful because it abstracts away complex database interactions, such as connection management and transaction handling, into a “Unit of Work” pattern that ensures data consistency.

Note

Query Method Chaining

Query method chaining in SQLAlchemy utilizes a generative interface where successive method calls like .filter(), .join(), and .order_by() return a new Query object containing the additional criteria. This allows you to build complex, multi-clause SQL statements through a fluent Pythonic syntax that remains readable and maintainable. The chain is ultimately executed when a final “execution” method such as .all(), .first(), .scalar(), etc. is called to fetch the results from the database. For more details, check out: