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 as well as the post on how to use type to dynamically declare classes, 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 1

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.