dynamic table names with SQLalchemy
Question: I am trying to convert old sqlite3 code to sql alchemy. I am trying to make
sense of how best to handle my use case. I am new to the ORM method of
database access.

I am trying to dynamically generate unique table names based on a common
definition. I have read the [mixins
guide](http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#declarative-
mixins) as well as the post on how to use `type` [to dynamically declare
classes](http://stackoverflow.com/questions/2768607/dynamic-class-creation-in-
sqlalchemy), but I am still unsure how of I would go about this. Here is what
I have so far:

    
    
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
    from sqlalchemy.ext.declarative import declared_attr
    
    Base = declarative_base()
    
    
    class DynamicName(object):
        @declared_attr
        def __tablename__(cls):
            return cls.__name__.lower()
    
    class Genome(DynamicName, Base):
        __tablename__ = 'AbstractGenome'
        AlignmentId = Column(Integer, primary_key=True)
        StartOutOfFrame = Column(Integer)
        BadFrame = Column(Integer)
    
    def build_genome_table(genome):
        d = {'__tablename__': genome}
        table = type(genome, (Genome,), d)
        return table
    

If I try to use this, it doesn't work:

    
    
    >>> from sqlalchemy import create_engine
    >>> engine = create_engine('sqlite:///:memory:', echo=True)
    >>> genomes = ["A", "B"]
    >>> tables = {x: build_genome_table(x) for x in genomes}
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "<stdin>", line 1, in <dictcomp>
      File "<stdin>", line 3, in build_genome_table
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py", line 55, in __init__
        _as_declarative(cls, classname, cls.__dict__)
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 88, in _as_declarative
        _MapperConfig.setup_mapping(cls, classname, dict_)
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 103, in setup_mapping
        cfg_cls(cls_, classname, dict_)
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 135, in __init__
        self._early_mapping()
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 138, in _early_mapping
        self.map()
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 529, in map
        **self.mapper_args
      File "<string>", line 2, in mapper
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 623, in __init__
        self._configure_inheritance()
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 930, in _configure_inheritance
        self.local_table)
      File "<string>", line 2, in join_condition
      File "/cluster/home/ifiddes/python2.7/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 839, in _join_condition
        (a.description, b.description, hint))
    sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'AbstractGenome' and 'A'.
    

How do I go about dynamically generating a `Genome` table based on a passed
name? I also would ideally like a setup where I can have hierarchical
inheritance, so that I can declare different subclasses like `ReferenceGenome`
or `TargetGenome` which have additional columns but also can have dynamic
names.


Answer: See: `sqlalchemy.orm.mapper`:
<http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#class-mapping-api>.
My understanding (as I've only modified code using this function in the past)
is that it directly maps a model class to a Table object, which itself is
connected to a database table.

This use-case actually sounds pretty similar to the recipe history_meta:
<http://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_history/history_meta.html>.
It might take some time to sort through, but a Table object is being created
here dynamically based on an existing model (any subclass of Versioned), and
then directly mapped to the database table when the class is created.

Here's the issue though: you do need an actual database table to map to. It's
an ORM after all. You have a few options here:

  1. If you want to create a table on the fly that will persist in the database, you can use Table.create() as per here: <http://docs.sqlalchemy.org/en/latest/core/metadata.html#creating-and-dropping-database-tables>

  2. If you only need to create tables every now and then, you can integrate with alembic: <https://pypi.python.org/pypi/alembic>

  3. If you just need it for one process, and never again, you can create temporary tables, though I'm not sure if SQLAlchemy directly supports it. The few resources I checked seem to be using create() and drop() anyway. I haven't used SQLAlchemy 1.0+, so it may have some support somewhere that I haven't seen.

Let me know if anything here isn't clear. It's been a while since I've played
with history_meta.py, so I may be rusty.

