Why is it useful to have a MetaData object which is not bind to an engine in SQLAlchemy?

Question

I am trying to understand the behavior of MySQL regarding the MetaData object and the engine object. This SO-Answer describes MetaData as

a collection of table definitions

and engine as

the dialect and connection details of a specific database

So far so good. But when is it useful to have these two separated? Aren't table definitions linked to a specific database?

Answer 1

SQLAlchemy 0.1 didn't have a "metadata" object - the Engine was bound directly to each Table. That idea got old very quick, partially because folks wanted to declare their Table object before they connected, and "bound metadata" came along. Then, a long period of mass confusion occurred. People were in the super hard habit (since I told them to do it this way) of saying things like:

table.insert().execute()

result = table.select().execute()

I.e., no transaction, using a new connection each time. Then we'd say, "oh well you should be using a transaction, you should be more efficient about connections", and then we'd basically tell them they had to rewrite their app, and the "sqlalchemy has too many ways" meme blew up like a balloon.

At the same time, Pylons and other early WSGI frameworks were pushing hard to have multiple "apps" running at once - in some cases, different "users" would have their own set of tables each in different databases, kind of thing. More common are horizontal scaling approaches where the same tables are in many databases. I have an app here that has a "replication" system built in where records are copied from the "primary" database to the "history" database periodically, and the table metadata there is shared too.

The point is for all those use cases, users would come to SQLA and their understanding of things began with "bound metadata". Blogs and tutorials all over the place used it. And a good chunk of these users would need to break out of that system, and become completely confused that there was this whole "other way" of working. So it became clear that the "bound metadata" system was just too rigid as a default. Ideally I wish that I never implemented it at all, and I never use it myself. This is why the docs for it are pushed into just one section now, new users who only skim the docs, who by their nature add a huge support burden to the mailing list, don't find it and don't get confused. The section itself has plenty of bullets explaining exactly when and why it is confusing. I'm assuming you read it, at http://www.sqlalchemy.org/docs/core/schema.html#binding-metadata-to-an-engine-or-connection .