Switching from SQLite to MySQL with Flask SQLAlchemy

Question

I have a site that I've built with Flask SQLAlchemy and SQLite, and need to switch to MySQL. I have migrated the database itself and have it running under MySQL, but

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

I suspect that (1) is fairly simple and just a matter of being shown how to map, for example, the contents of the connection dialog I use in my MySQL database tool to an appropriately formatted URL. But I'm 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()

wold work without any modifications other than an appropriate change to the database URI; but the examples I've found for using SQLAlchemy with MySQL seem to use a completely different API.

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

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.

Comments

[Faisal Julaidan]

For some reasons i got connection refused. I made sure username and pass for mysql are correct. The weried thig is that your method worked for me locally in my machine but when i built a droplet in Degital Ocean for deployment it did not work and i got connection refused. Mysql is properly installed and i can login to it successfully but from flask no idk why!! Any help?

[skjoshi]

Are the flask app and DB on same droplet? If not you might want to check if you can access DB remotely. Also, above example assumes the username@localhost is allowed to access. There is username@% which allow this user to access DB from anywhere.

[Faisal Julaidan]

I made it work by including the ...mysqlId/mysql.sock in the path. ;)

[Mandava Geethabhargava]

which one is best method to access database , sqlalchemy or direct accessing to mysql through other packages in flask

[Sameera K]

I spent two days configuring this in a AWS Lambda with Mysqldb, error, "ImportError: libmysqlclient.so.20: cannot open shared object file: No such file or directory", but replacing it with pymysql solved the problem.