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