create and update table dynamically in declarative base

[Nana Okyere]

I'm new to flask, python and sql alchemy. I'm using flask for a small application. Hence I'm using flask-sqlalchemy. I know it uses the declarative base. I have model.py as:

from app import db

def _create_db_sequence(name):

    # if db is Oracle create the sequence

    if db.engine.name.upper() == 'ORACLE':

        query = "SELECT * FROM user_sequences WHERE sequence_name = '{}'".format(name)

        result = db.session.execute(query)

        if not result.scalar():

            db.session.execute('CREATE SEQUENCE {}'.format(name))

            db.session.commit()

class FieldType(db.Model):

    __tablename__ = 'field_types'

    id = db.Column(db.Integer, db.Sequence('FIELD_TYPES_SEQ'), primary_key=True)

    name = db.Column(db.String(120), unique=True, nullable=False)  # a nice name, like Date

    # one-to-many relationship (many Fields can use the same FieldType)

    fields = db.relationship('Field', backref=db.backref('type', lazy='joined'))

    # python type for the field (int, double, str, date, etc)

    python_type = db.Column(db.String(120))

    # for date will be 'from datetime import date as Date', for int, is empty string

    python_import = db.Column(db.String(120))

    _create_db_sequence('FIELD_TYPES_SEQ')

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')

class Department(db.Model):

    __tablename__ = 'departments'

    id = db.Column(db.Integer, db.Sequence('DEPARTMENTS_SEQ'), primary_key=True)

    name = db.Column(db.String(120), unique=True, nullable=False)

    # one-to-many relationship (a Department can have several associated TableInfos)

    tables_info = db.relationship(TableInfo, backref=db.backref('department', lazy='joined'))

    _create_db_sequence('DEPARTMENTS_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? 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, how do I create the table and columns in runtime in a way that the application can interact with it? Can I create tables on the fly in a flask-sqlalchemy environment? Thanks.

[Nana Okyere]

Can anyone help me with this?

[Mike Bayer]

On 9/17/15 5:11 PM, Nana Okyere wrote:

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?

I'm not sure what this is asking.   An "empty" table is this:

t = Table('mytable', metadata)


however, this is only a Python construct.   It isn't in your database schema.

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.

Column objects can be added using append_column: http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=append_column#sqlalchemy.schema.Table.append_column

t.append_column(Column(...))

but again, this does nothing to the database itself, we're still just making an object in Python only and adding things to it.

Since this table and its columns won't be based on predefined models, how do I create the table and columns in runtime in a way that the application can interact with it?

once you have the fully formed Table object ready to go, you'd emit Table.create(my_engine_or_connection).   That will emit CREATE TABLE statements to the database.

Background on these features and behaviors is at:

http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=append_column#

Can I create tables on the fly in a flask-sqlalchemy environment? Thanks.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Nana Okyere]

Thanks Mike. I didn't know this would be that simple. Like I said, I'm new to this so I'm learning. I'll try your suggestions and let you know.