Create/Update/Delete Tables Dynamically With Flask-Sqlalchemy

Question

New to python, flask, and sqlalchemy. Please help.

I'm writing a small application with flask. I have defined some models as:

class Field(db.Model):
    __tablename__ = 'fields'

    id = db.Column(db.Integer, db.Sequence('FIELDS_SEQ'), primary_key=True)
    name = db.Column(db.String(120), nullable=False)
    position = db.Column(db.Integer)

    # a field has only one type
    type_id = db.Column(db.Integer, db.ForeignKey(FieldType.id))

    # a field only belongs to one table
    table_info_id = db.Column(db.Integer, db.ForeignKey('tables_info.id'), unique=True)

    _create_db_sequence('FIELDS_SEQ')


class TableInfo(db.Model):
    __tablename__ = 'tables_info'

    id = db.Column(db.Integer, db.Sequence('TABLES_INFO_SEQ'), primary_key=True)
    name = db.Column(db.String(120), unique=True, nullable=False)

    # a table can have a lot of fields
    fields = db.relationship(Field, backref='table_info', lazy='joined')

    # a table only belongs to one department
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))

    _create_db_sequence('TABLES_INFO_SEQ')

So I have a form that will collect the name attribute of TableInfo. Now, how do I dynamically - in runtime- create an empty table with the name attribute given with only one column definition (id). Once table is created, I will use another form to collect field/column names. Again, I'll like to dynamically add these columns to the table. Since this table and its columns won't be based on predefined models as above, how do I create the table and columns in runtime in a way that the application can interact with it? I read that flask-sqlalchemy uses the declarative base which requires models to be defined before mapped tables are created. Can I create tables on the fly in a flask-sqlalchemy environment? Thanks.

It looks like your table structure may be overly complex. While it's understandable you may have a use case where you need more columns on the fly, and while I'm sure this will be possible via SQL alchemy, it's going to end up a hacky or non ORM solution. You may have an easier time investigating a no-sql solution which allows dynamic creation of columns on the fly. — pmccallum
@F Boucaut unfortunately, I have only Oracle to work with now. Can you elaborate on how this may be possible via sqlalchemy? — okyere

baptiste chéné baptiste chéné · Accepted Answer · 2017-01-18T10:37:34

I have a similar problem. I change my model.py and want to upgrade my db.

Here is what i do for upgrade it. I don't know if it was the right way to do it, but it's work.

I create a file manage.py

import os
from flask import Flask
from dictiofquestions import dico
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from core import db
from Models import Questions

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = 'True'

db.init_app(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)


if __name__ == "__main__":
    manager.run()

Don't forget this ligne app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = 'True'

Here is my file core.py

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

And my models.py

from core import db

class Questions(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    question = db.Column(db.String(200), unique=True)
    resp1 = db.Column(db.String(80), unique=False)
    resp2 = db.Column(db.String(80), unique=False)
    resp3 = db.Column(db.String(80), unique=False)
    resp4 = db.Column(db.String(80), unique=False)
    valideresp = db.Column(db.String(5), unique=False)
    categorie = db.Column(db.String(50), unique=False)

    def __init__(self, question, resp1, resp2, resp3, resp4, valideresp, categorie):
        self.question = question
        self.resp1 = resp1
        self.resp2 = resp2
        self.resp3 = resp3
        self.resp4 = resp4
        self.valideresp = valideresp
        self.categorie = categorie

    def __repr__(self):
        return '<Questions %r>' % self.question

In my console i enter the commands:

python manage.py db init
python manage.py db migrate
python manage.py db upgrade

Like that i can upgrade my db when i want with this 3 commands. If you need more informations i think you can go to this page. https://flask-migrate.readthedocs.io/en/latest/

Create/Update/Delete Tables Dynamically With Flask-Sqlalchemy

1 Answers