How do I write an external dialect for SQLAlchemy?

As of SQLAlchemy 0.8, you can register the dialects in-process without needing to have a separate install.

from sqlalchemy.dialects import registry
registry.register("mysql.foodialect", "myapp.dialect", "MyMySQLDialect")

The above will respond to create_engine("mysql+foodialect://") and load the MyMySQLDialect class from the myapp.dialect module.

See: https://docs.sqlalchemy.org/en/latest/core/connections.html#registering-new-dialects

When SQLAlchemy resolves a database url example://... it will first try to find it in import sqlalchemy.dialects.example. If that doesn't work it falls back to pkg_resources.iter_entry_points('sqlachemy.databases').

Put the new dialect in a package using setuptools, include an entry point named after your dialect, run python setup.py develop or python setup.py install, and SQLAlchemy should be able to find the dialect.

In setup.py:

   entry_points = {
     'sqlalchemy.databases': ['example = example_sa:base.dialect',]
   },

example_sa:base.dialect means import example_sa; return example_sa.base.dialect.

After installing this package, pkg_resources.iter_entry_points(group) yields pkg_resources.EntryPoint instances from group. Call .load() on the EntryPoint with entrypoint.name='example' and you get example_sa.base.dialect.

I was pleasantly surprised at how easy it is to write new dialects for SQLAlchemy 0.6. If your database has just a few quirks compared to standard SQL, chances are you will be able to inherit from the standard (MySQL-like) SQL dialect, define your database's keywords, and copy the implementation of those quirks (like SELECT TOP 10 instead of SELECT ... LIMIT 10) from an existing dialect.