Peter Hoffmann Director Data Engineering at Blue Yonder. Python Developer, Conference Speaker, Mountaineer

exasol dialect 0.9.0 for python sqlalchemy released

SQLAlchemy dialect for the EXASOL database.

Today we have released sqlalchemy_exasol 0.9.0.

Besides several bug fixes, this is the first version with full sqlalchemy test coverage/support for EXASOL 5.0.

This release should speedup database reflection a lot. Instead of querying table and constraint information per table/row it caches the results per schema.

Reflecting an example schema with 12 tables went down from 14 seconds and 84 database queries to 1,4 seconds and 5 database queries.

The caching only works if you use the same inspector for all reflections:

from sqlalchemy import *
from sqlalchemy.engine import reflection

dsn = "exa+pyodbc://XXX" 
engine = create_engine(dsn)
engine.echo = True
insp = reflection.Inspector.from_engine(engine)
md = MetaData(bind=engine)

schemaname = "testschema"

for tablename in insp.get_table_names(schema=schemaname, order_by="foreign_key"):
    tb = Table(tablename, md, schema=schemaname)
    insp.reflecttable(tb, None)

Using metadata.reflect(..) does not benefit that much from the caching, because sqlalchemy internally creates a new inspector instance for each table.