Dialect-specific sqlalchemy configuration

Question

When using sqlalchemy with multiple dialects, just adding dialect-specific configuration such as sqlite's foreign key support or postgres' server_side_cursors poses a problem, because all other dialects will not understand the configuration or events. For instance:

# applying postgres configuration to a sqlite3 database fails
>>> sqlalchemy.create_engine("sqlite3:///test.sqlite3", server_side_cursors=True)
...
TypeError: Invalid argument(s) 'server_side_cursors' sent to create_engine(), using configuration SQLiteDialect_pysqlite/NullPool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

However, sqlite does not need this configuration, because it automatically streams results. Similarly postgres does not need to enable foreign key support, because that's the default.

How can I apply this dialect-specific configuration in a way, that does not break other dialects? Is there some sqlalchemy facilitating this branching? Is there something better than isinstance tests?

score 5 · Accepted Answer · answered Mar 08 '14 at 12:25

Given a created engine, one should branch on either engine.dialect.name (being sqlite or postgresql here) or engine.dialect.driver (being e.g. pysqlite or psycopg2). So the foreign key support should branch on engine.dialect.name == "sqlite" since it works with all drivers, but the server_side_cursors setting should branch on engine.dialect.driver == "psycopg2", because other drivers for Postgres do not support this setting.

Thanks to nosklo on Freenode#sqlalchemy for the pointer.

Dialect-specific sqlalchemy configuration

1 Answers1