title: dynamic table names with SQLalchemy
body: <p>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.</p>
  
  <p>I am trying to dynamically generate unique table names based on a common definition. I have read the <a href="http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#declarative-mixins" rel="nofollow noreferrer">mixins guide</a> as well as the post on how to use <code>type</code> <a href="https://stackoverflow.com/questions/2768607/dynamic-class-creation-in-sqlalchemy">to dynamically declare classes</a>, but I am still unsure how of I would go about this. Here is what I have so far:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If I try to use this, it doesn't work:</p>
  
  <pre><code>&gt;&gt;&gt; from sqlalchemy import create_engine
  &gt;&gt;&gt; engine = create_engine('sqlite:///:memory:', echo=True)
  &gt;&gt;&gt; genomes = ["A", "B"]
  &gt;&gt;&gt; tables = {x: build_genome_table(x) for x in genomes}
  Traceback (most recent call last):
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;
    File "&lt;stdin&gt;", line 1, in &lt;dictcomp&gt;
    File "&lt;stdin&gt;", 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 "&lt;string&gt;", 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 "&lt;string&gt;", 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'.
  </code></pre>
  
  <p>How do I go about dynamically generating a <code>Genome</code> 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 <code>ReferenceGenome</code> or <code>TargetGenome</code> which have additional columns but also can have dynamic names.</p>