dynamic SQLAlchemy table names with foreign keys

Question

Building on my previous post, I am working on converting a SQLite database/database schema to SQLAlchemy.

In this, a series of tables are generated dynamically with names of genomes being analyzed. Each table has a foreign key reference to the parent table (the reference genome). How can I set up the foreign key

class Genome(DynamicName, Base):
    """
    Defines database schema for the reference genome.
    """
    __abstract__ = True
    TranscriptId = Column(String, primary_key=True)
    AnalysisA = Column(Integer)
    child = relationship('')  # how to declare dynamic name?


class AlignedGenome(DynamicName, Base):
    """
    Defines database schema for a target (aligned) genome.
    """
    __abstract__ = True
    AlignmentId = Column(String, primary_key=True)
    TranscriptId = Column(String, ForeignKey('')) # how to declare dynamic name?
    AnalysisZ = Column(Integer)
    parent = relationship('')  # how to declare dynamic name?


def build_genome_table(genome, is_ref=False):
    d = {'__tablename__': genome}
    if is_ref is True:
        table = type(genome, (Genome,), d)
    else:
        table = type(genome, (AlignedGenome,), d)
    return table

The parent and child tables are related via the TranscriptId key, which is a one-to-many relationship: many AlignmentIds are associated with one TranscriptId.

Answer 1

In this case, I think it's much easier to just build the whole class dynamically instead of specific pieces:

def build_genome_table(genome, is_ref=False):
    if is_ref is True:
        table = type(genome, (Base,), {
            "__tablename__": genome,
            "TranscriptId": Column(String, primary_key=True),
            "AnalysisA": Column(Integer),
            "child": relationship("Aligned" + genome),
        })
    else:
        table = type("Aligned" + genome, (Base,), {
            "__tablename__": "Aligned" + genome,
            "AlignmentId": Column(String, primary_key=True),
            "TranscriptId": Column(String, ForeignKey(genome + ".TranscriptId")),
            "AnalysisZ": Column(Integer),
            "parent": relationship(genome),
        })
    return table

You just need to take care to name your tables and classes in a consistent manner.