Switching from SQLite to MySQL with Flask SQLAlchemy

Solution 1:

The tutorial pointed by you shows the right way of connecting to MySQL using SQLAlchemy. Below is your code with very little changes:

My assumptions are your MySQL server is running on the same machine where Flask is running and the database name is db_name. In case your server is not same machine, put the server IP in place of localhost.

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

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


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

admin = User('admin', '[email protected]')

db.create_all() # In case user table doesn't exists already. Else remove it.    

db.session.add(admin)

db.session.commit() # This is needed to write the changes to database

User.query.all()

User.query.filter_by(username='admin').first()

It happened to me that the default driver used by SQLAlchemy (mqsqldb), doesn't get compiled for me in my virtual environments. So I have opted for a MySQL driver with full python implementation pymysql. Once you install it using pip install pymysql, the SQLALCHEMY_DATABASE_URI will change to:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'

The purpose of using ORM like SQLAlchemy is that , you can use different database with little or no change in most cases. So, my answer is yes. You should be able to use your sqlite code to work with MySQL with the URI mapped as in above code.

Solution 2:

The accepted answer was correct at the time, but the syntax in the import statement has been deprecated.

This:

from flask.ext.sqlalchemy import SQLAlchemy

Should be replaced with:

import flask_sqlalchemy

Since questions regarding database connections tend to get traffic and stay relevant for a long time, it's worth having on the record.

The deprecation is in the Flask Version 1.0 Changelog, which actually uses this module in the example:

flask.ext - import extensions directly by their name instead of through the flask.ext namespace. For example, import flask.ext.sqlalchemy becomes import flask_sqlalchemy.