Best way to organize the folders containing the SQLAlchemy models

Question

I use SQLAlchemy at work and it does the job really fine. Now I am thinking about best practices.

For now, I create a module holding all the SQLA stuff :

my_model
        |__  __init__.py 
        |__  _config.py <<<<< contains LOGIN, HOST, and a MetaData instance
        |__  table1.py <<<<< contains the class, the model and the mapper for table1
        |__  table2.py <<<<< contains the class, the model and the mapper for table2
        [...]

Now, I really don't know if it's the best way to do it. I would like to load the classes with a fine granularity and be sure to create one connection only with the db, etc.

Here, all the classes are separated, but all import _config and I am wondering if it's a good thing.

What's more, I would like to be able to create subclasses of the model classes that could be stored independently without messing up with the mapper every time. How can I do that ?

For now I just put them in the same file and I have to create another mapper, but the first mapper is still called every time. The same would go if I'd have to import the parent class because the mapper is triggered at import. If I don't use the class to access data, isn't it overheat to map it each time ?

I'd like to avoid using Elixir too, please.

Answer 1

Personally I like to keep the database / ORM logic out of the model classes. It makes them easier to test. I typically have something like a types.py which defines the types used in my application, but independent of the database.

Then typically there is a db.py or something similar which has the Session class and the rest of the code required to set up the database, including all the mappers etc.

None of the other modules except those which perform database operations need to import the db module and the existence of the database is totally hidden from most of the application classes.

As far as I know, you can't easily make subclasses without changing the mapper. SQLAlchemy will have no way to know which subclass to retrieve from the database when you perform a query and you need to be able to indicate the subclass when you are storing the data anyway.

I haven't really seen any problems arise from calling all the mappers at once from the main db module, so I don't think initializing them all the time is really a concern unless you actually identify it as a bottleneck. In my experience, other processing is a far larger factor than the minor mapper overhead.