Flask-SQLAlchemy中的多个相同表 [英] Multiple Identical Tables in Flask-SQLAlchemy

问题描述

我正在创建一个类似REST的API，以将数据输入和输出旧数据库.目前，我没有选择更改db结构的选项.数据库中几乎每个表都具有完全相同的结构.这是我目前使用Flask-SQLAlchemy处理它的方式:

I'm creating a REST-like API to get data in and out of a legacy database. At the moment I don't have the option to alter the db structure. Nearly every table in the database has the exact same structure. Here's how I'm currently handling it using Flask-SQLAlchemy:

class MyDatasetBase(object):
    timestamp = db.Column('colname', db.Float, primary_key=True)
    val1 = db.Column(db.Float)
    val2 = db.Column(db.Float)
    val3 = db.Column(db.Float)
    fkeyid = db.Column(db.Integer)

    @declared_attr
    def foreigntable(self):
        return db.relationship('ForeignTableClass', uselist=False)

class Table1(MyDatasetBase, db.Model):
    __tablename__ = 'table1'

class Table2(MyDatasetBase, db.Model):
    __tablename__ = 'table2'

class ForeignTableClass(db.Model):
    __tablename__ = 'ForeignTable'
    id = db.Column(db.Integer, db.ForeignKey('table1.fkeyid'), db.ForeignKey('table2.fkeyid'), primary_key=True)
    name = db.Column(db.String)

这一切都可行，但是其中一些数据集包含很多表，我觉得必须有一种更有效的方式来完成其中的一些事情.

This all works, but some of these datasets contain a lot of tables and I feel like there has to be a more efficient way to do a few of these things.

是否有一种方法可以为从MyDatasetBase派生的每个表显式定义一个类?如果有的话，这会导致外键问题吗?在ForeignTableClass中，我必须将id定义为上面列出的每个表的外键吗?

Is there a way to get around explicitly defining a class for each of the tables derived from MyDatasetBase? If there is, will that cause problems with the foreign key stuff, where in ForeignTableClass I have to define id as being a foreign key for every table listed above?

推荐答案

一种方法是使用类型方法:

names = ['table1', 'table2', 'table3']

for name in names:
    type(name.title(), (MyDatasetBase, db.Model), { '__tablename__' : name })
# generate <class 'flask_sqlalchemy.Table1'> ... 

使用元数据和 查看全文