CODEWITHSUNDEEP
pythonmysqldatabasesqliteflask
Andy
Andy

Reputation: 541

Migrate Flask models.py to MySQL

I'm new. Bear with me.

I'm developing a Flask application using SQLAlchemy as an ORM and up until today I have been using SQLite for convenience. I'm now putting the application live on Digital Ocean and want to use MySQL instead of SQLite.

I have MySQL installed on my ubuntu VPS on Digital Ocean and it seems like it is configured properly. However, obviously I have to create the database tables, so I can save the data inside.

Question: Is there a way for me to migrate my models.py, so the database tables are created from what I have written in models.py or do I have to create all the database tables myself manually in MySQL?

You can see the application live here: http://workflowforum.dk/ and I have made a small test to see if there is a database connection here: http://workflowforum.dk/testdb

Models.py (Only user model):

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy 
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand
from datetime import datetime, date
from hashlib import md5
from bcrypt import hashpw, gensalt

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:password@localhost/database'
db = SQLAlchemy(app)

migrate = Migrate(app, db)

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

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    slug = db.Column(db.String(80))
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(80))
    password = db.Column(db.String(80), unique=False)
    admin = db.Column(db.Boolean(), default=False)
    join_date = db.Column(db.DateTime)
    last_seen = db.Column(db.DateTime)
    topics = db.relationship('Topic')
    posts = db.relationship('Post')
    picture = db.Column(db.Boolean(), default=False)
    title = db.Column(db.String(80))
    company = db.Column(db.String(80))
    summary = db.Column(db.String(80))

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(180), unique=False)
    topics = db.relationship('Topic', backref="category")

class Topic(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    slug = db.Column(db.String(255), unique=True)
    title = db.Column(db.String(80), unique=False)
    description = db.Column(db.Text, unique=False)
    pub_date = db.Column(db.DateTime)
    last_update = db.Column(db.DateTime)
    user_id = db.Column(db.String(80), db.ForeignKey('user.id'))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    views = db.Column(db.Integer, default=0)
    locked = db.Column(db.Boolean(), default=False)
    pinned = db.Column(db.Boolean(), default=False)
    user = db.relationship('User')
    posts = db.relationship('Post')

Views.py (Only database test):

@app.route('/testdb')
    def testdb():
    if db.session.query("1").from_statement("SELECT 1").all():
        return 'It works.'
    else:
        return 'Something is broken.'

UPDATE after Lukas comment:

When trying to db.create_all() I get this traceback:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1005, "Can't create table 'pwforum.topic' (errno: 150)") [SQL: u'\nCREATE TABLE topic (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tslug VARCHAR(255), \n\ttitle VARCHAR(80), \n\tdescription TEXT, \n\tpub_date DATETIME, \n\tlast_update DATETIME, \n\tuser_id VARCHAR(80), \n\tcategory_id INTEGER, \n\tviews INTEGER, \n\tlocked BOOL, \n\tpinned BOOL, \n\tPRIMARY KEY (id), \n\tUNIQUE (slug), \n\tFOREIGN KEY(user_id) REFERENCES user (id), \n\tFOREIGN KEY(category_id) REFERENCES category (id), \n\tCHECK (locked IN (0, 1)), \n\tCHECK (pinned IN (0, 1))\n)\n\n']

Upvotes: 4

Views: 2758

Answers (1)

Miguel Grinberg
Miguel Grinberg

Reputation: 67489

The db.create_all suggestion in the comments is usually what you do when you don't use a migration framework. But it looks like you are using Flask-Migrate as a database migration framework here. The problem with doing create_all is that your migration scripts are skipped, so any fixes that you need to make to convert your sqlite models to MySQL will not be reflected in the migration scripts.

My recommendation is that you create an empty MySQL database, configure it in your Flask app, and then generate the MySQL tables simply by calling:

$ ./manage.py db upgrade

When you do this, Flask-Migrate will start running the migration scripts one by one and in order.

If you get failures, that it is probably because some of your migration scripts have changes that are compatible with sqlite but not with MySQL. You will need to fix all these problems in your migration scripts, until you get all of them to run cleanly, at which point you will have a complete MySQL database to use in your application.

Upvotes: 1

Related Questions