dynamic tablename in flask-sqlalchemy models

Question

Is there a possibility to make the __tablename__ in flask-sqlalchemy models dynamic with the declarative base approach?

Usually you set it as this one:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    email = Column(String(120), unique=True)

    def __init__(self, name=None, email=None):
        self.name = name
        self.email = email

    def __repr__(self):
        return '<User %r>' % (self.name)

I would like to change it through a parameter (maybe in the constructor?), so that I can have a table per user.

I found some other approaches in this guide here Approaches but I would like to use the session for that as I am already using it for the other models.

Why exactly do you need a table _per user_ ? that sounds like a busted schema.. — tzaman, Apr 16 '15 at 01:33
I try to build a little leaning platform for maximal 50 users and each user can have own leaningtables (saved in a temporary database) — Marcel Hinderlich, Apr 16 '15 at 19:01

Kyle Anderson · Answer 1 · 2022-04-10T02:28:33.230

You can utilize python's type() function to dynamically build SQLAlchemy models.

Here's a example:

# define columns in an abstract model class
class Log(Base):
    __abstract__ = True  # this line is necessary
    # the columns id, content and user_id are just examples, just ignore it.
    id = Column(BIGINT(64), primary_key=True)
    content = Column(VARCHAR(200), nullable=False)
    user_id = Column(INTEGER(unsigned=True))


# build a model class with a specific table name
def get_log_model(year):
    tablename = 'logs_%s' % year  # dynamic table name
    Model = type('Model', (Log,), {
        '__tablename__': tablename
    })
    return Model

# Log2022 correspond to table "logs_2022"
Log2022 = get_step_model(2022)
# use the dynamically built model in the same way as regular models 
print(session.query(Log2022).count())  # row count of table "logs_2022"

I also wrote an article about it on my website, it may help you too: https://easydevguide.com/posts/dynamic_table

dynamic __tablename__ in flask-sqlalchemy models

1 Answers1

dynamic tablename in flask-sqlalchemy models