Switching from SQLite to MySQL with Flask SQLAlchemy

Question

I had built a site with Flask SQLAlchemy and SQLite and want to switch to MySQL. I had migrated the database itself and have it running under MySQL, but

1. I Cannot find out how to connect to the MySQL database (that is, what the SQLALCHEMY_DATABASE_URI should be) and
2. I'm unclear if any of my existing SQLAlchemy SQLite code will work with the MySQL.

I presume that (1) is moderately simple and just a matter of being shown how to map, for example, the contents of the connection dialogue I use in my MySQL database tool to an appropriately formatted URL. But I am really worried about (2), I had assumed that SQLAlchemy provided an abstraction layer so that simple SQLAlchemy code such as

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

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
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.session.add(admin)

User.query.all()

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

would work without any of the changes rather than a proper modification to the database URI, though the examples I had found for using the SQLAlchemy with the MySQL appear to be using a totally different API.

Can I (2) migrate my Flask SQLAlchemy code to work with the MySQL database by just modifying the database URI and if so (1) what should be that URI?

Answer 1

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

Below is your code with some little changes:

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 was found that the default driver used by the SQLAlchemy (mqsqldb), didn't get compiled for me in the virtual environment. So I had opted for the MySQL driver with full python implementation pymysql. When you install it using pip install pymysql, the SQLALCHEMY_DATABASE_URI will be changing 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 a different database with limited or no change in most cases. So, my answer is yes. You could be able to use your SQLite code to work with MySQL with the URI mapped as in above code.

Interested in SQL ? Check out this SQL Certification by Intellipaat.

If you want to know more about MySQL, refer to the below MySQL tutorial video that will help you out in a better way: