SQLAlchemy has a nice reflection facility which takes for example a database table name as argument and produces a Table object out of it for manipulation. However, those objects do not behave like the objects produced by declarative classes, which are easier to work with. Here’s a little class that helps to bridge that gap by reflecting proper declarative classes from database tables. It has only been tested with PostgreSQL, but it may work with other databases as well.

The class SQLReflector requires the preconfigured SQLAlchemy database engine as argument. Example (try in IPython):

The instantiated SQLReflector object can be used to reflect database objects using the following methods:

• reflect_table(schema_name, table_name): reflects a single table into a class
• reflect_tables(schema_name, table_name_list): reflects multiple tables at once from one schema
• reflect_schema(schema_name): reflects all tables and sequences in the given schema
• reflect_database(): reflects everything from a database

So to reflect the table “my_table” in schema “public”, write:

Now you can query it like any declarative class:

The reflect_table method accepts a column_definitions argument. You can use that to define missing stuff, such as primary keys for views, or foreign key relationships not defined in the database.

For example PostgreSQL views don’t have primary keys, which causes problems with SQLAlchemy. To define a primary key while reflecting the class, you can write something like this:

To define an explicit foreign key, you can write something like:

You can find all the tables you have reflected in the SQLReflector instance, under the classes property. Every schema you have used is there, and under each schema every table you have reflected. So if you run reflector.reflect_database(), you should find all you schemas under reflector.classes, and every table like this:

• reflector.classes.<schema_name>.<class_name>

But remember, if you have any views defined in a PostgreSQL database, the reflect_database() call will fail, because you must explicitly add a primary key constraint to every view to please SQLAlchemy.

There are a couple of helper methods to snoop around the database and see what’s there. The method get_schema_names() will give a list of schema names in the database. The method get_table_names(schema_name) will give a list of table names in a given schema. There is a full sqlalchemy.engine.reflection.Inspector object in the instantiated SQLReflector object (the class member name is, not surprisingly, “inspector”). You can use that to further inspect the database.

SQL objects can have names containing for example accented letters and special characters. Python 2 allows only letters, numbers and underscores in identifier names. Python 3 allows some unicode characters in identifiers. For compatibility’s sake, SQLReflector mangles SQL identifiers so that the resulting Python identifiers contain only letters, numbers and underscores.

By default, table names are converted into CamelCase in such a way, that the name is converted into lowercase, and after that, underscore characters in table names are removed, and the character following the underscore character is changed into uppercase. Also first character is uppercased.

For example:

• Table called “my_data” becomes a class called “MyData”
• Table called “MyData” becomes a class called “Mydata”

If you don’t like this behaviour, you can disable it by giving a “camelcase=False” argument to the constructor. In this case, the schema name sanitizer is used for table names.

Schema names are not converted into CamelCase, but are sanitized from unwanted characters.

You can disable all sanitization by giving the class constructor a “sanitize_names=False” argument. In this case the SQL name is used as the class name whatever it is, possibly resulting in exceptions if the class name contains characters Python does not accept.

Here’s the code (sqlreflector.py):