CODEWITHSUNDEEP
pythonflaskflask-sqlalchemy
HFX
HFX

Reputation: 592

In Flask-SQLAlchemy, Should i use create_all() to create tables in production environment?

I am new to Flask. I am working on Flask-SQLAlchemy,and i also tried Flask-Migrate.

It's handy to create production and test environments quickly.

But i think it's not as convenient as Django-Migration.

When i tried to create many-to-many data model. I got following error sometimes:

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: u'
    CREATE TABLE ad_accounts (
    access_token_id INTEGER, 
    ad_account_id INTEGER, 
    FOREIGN KEY(access_token_id) REFERENCES fb_access_token (id), 
    FOREIGN KEY(ad_account_id) REFERENCES ad_account (id)
    )
']

My models:

from sqlalchemy.dialects.mysql import BIGINT

ad_accounts = db.Table('ad_accounts',
    db.Column('access_token_id', db.Integer, db.ForeignKey('fb_access_token.id')),
    db.Column('ad_account_id', db.Integer, db.ForeignKey('ad_account.id'))
)


class AdAccount(db.Model):
    __bind_key__ = 'ads'
    __tablename__ = 'ad_account'

    id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(BIGINT(unsigned=True), default=None)
    account_status = db.Column(db.Integer, default=None)
    business_name = db.Column(db.String(255), default='')
    owner = db.Column(db.String(255), default='')
    timezone_name = db.Column(db.String(255), default='')
    created_time = db.Column(db.Integer, default=0)
    activities = db.relationship('Activity', backref='ad_account', lazy='dynamic')


class FbAccessToken(db.Model):
    __bind_key__ = 'ads'
    __tablename__ = 'fb_access_token'

    id            = db.Column(db.Integer, primary_key=True)
    admin_id      = db.Column(db.Integer, db.ForeignKey('admin_user.admin_id'))
    # fb_account_id = db.Column(db.String(32), default='')
    ad_accounts   = db.relationship('AdAccount', secondary=ad_accounts, backref='access_token_list', lazy='dynamic')
    update_time   = db.Column(db.Integer, default=0)
    page_id       = db.Column(BIGINT(unsigned=True), default=0)
    current_account_id = db.Column(BIGINT(unsigned=True), nullable=True)

When every time i was running 'python app.py db upgrade' in product envirment,i was afraid to break it down.

And someone told me that i should alter table by manual SQL.

I am confused now,i want to do it in a convenient way,i used to do it in django-migration.

Suppose I have already created database and tables in product envirment, do I still have to execute create_all or 'python app.py db upgrade'(In Flask-Migrate)?

And how to add a comment on a column in Flask-Migrate?

Upvotes: 2

Views: 2417

Answers (2)

Espoir Murhabazi
Espoir Murhabazi

Reputation: 6376

I am not sure if I got well what you want. I think flask Migrate is a good tool but it have some limitations as stated Here.

Basically , since alembic doesn't detect every migration you have to edit the migration script by adding foreign keys names when using them as it says in alembic documentation here . I face the same problem with my migration and I resolve it by editing the migration script in folder '/migration/version' by manually adding foreign keys name! with this line of code

sa.ForeignKeyConstraint(['access_token_id'], ['fb_access_token.id'],use_alter=True, name='fk_fb_access_token.id_id' ),

or if you don't like to edit migration script you can use the object

ForeignKeyConstraint

instead of

db.ForeignKey()

and give it all the parameter specially name when defining your foreign keys

But In general, it's because you need to name the foreign keys and don't use the default names

Upvotes: -1

alecxe
alecxe

Reputation: 473853

On an existing database, in production, you obviously don't want to recreate your database schema and lose any existing data. Look into database migrations, here are the relevant packages:

Upvotes: 0

Related Questions