Exasol Dialect 0.9.0 for Python SQLAlchemy Released

Posted on January 26, 2015 in python exasol

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 and support for EXASOL 5.0.

This release should speed up database reflection significantly. Instead of querying table and constraint information for each 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 much from caching, because SQLAlchemy internally creates a new inspector instance for each table.