Flask - How to connect MySql/SQLite Database

This article explains how to connect a MySQL or SQLite database to a Flask-based project using SQLAlchemy, a popular Python SQL toolkit that empowers clean, object-oriented access to the database.

For newcomers, Flask is a lightweight web application framework written in Python. Sometimes classified as a microframework, Flask provides a lightweight codebase that can be easily extended to become an API, a simple web app, or a complex eCommerce platform.

,

Flask is a lightweight web framework written in Python programming language. It is designed to make getting started quick and easy, with the ability to scale up to complex applications. Flask does not require particular tools or libraries and has no database provided by default, form validation helpers, or other hard dependencies.

We can start coding in Flask in less than one minute by installing Flask and writing a new web app with a few lines of code.

Install Flask via PIP, the official Python package manager:

$ pip install Flask

During Flask installation a small core with critical libraries is installed:

• Werkzeug implements WSGI, the standard Python interface between applications and servers.
• Jinja is a template language that renders the pages your application serves.
• MarkupSafe comes with Jinja. It escapes untrusted input when rendering templates to avoid injection attacks.
• ItsDangerous securely signs data to ensure its integrity. This is used to protect Flask’s session cookie.
• Click is a framework for writing command line applications. It provides the flask command and allows adding custom management commands.

Once the installation is complete, we can code a new, minimal app by saving and executing the code:

from flask import Flask
app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello, from Flask!'

This sample code will display classic "Hello World" in the browser.

SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper (ORM) that provides a clean, object-oriented access to the information saved in the database. This toolkit can be used with Python2 and Python3 and covers all popular SQL dialects and engines: SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, and others.

Is quite important to know that Flask accesses SQLAlchemy features via a Flask-SQLAlchemy Extension, the "glue" between our application and SQLAlchemy core.in

Using Flask-SQLAlchemy definestaking a new Flask application should be easy if we spent a few minutes to take a look at the official documentation. A simple app that uses SQLAlchemy should perform three things:

• Create Flask application
• Define 'SQLALCHEMY_DATABASE_URI' in configuration
• Create the SQLAlchemy object and bind it to the Flask object

from flask import Flask
from flask_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, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

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

To table can be created with ease via Flask CLI:

$ # Open the Flask shell
$ flask shell
>>> 
>>> # Import the DB (SQLAlchemy object)
>>> from app import db
>>>
>>> # Create User table
>>> db.create_all()

Once the table is created, we can add content using the console

>>> # get User object into the context
>>> from app import User
>>>
>>> Create a new User object
>>> user1 = User(username='user1', email='admin@example.com')
>>>
>>> # Add the new user to the SqlAlchemy session
>>> db.session.add(user1)
>>>
>>> # Save changes in the database
>>> db.session.commit()

Using SQLite in our development is great and we can code quite fast all application features and logic without limitations but in production where we might have high traffic probably is better for our project and our users to migrate the database layer to MySql or PostgreSQL.

MySql Set Up

To effectively use MySql or PostgreSQL in our application, we need to have a few things already installed on the workstation:

• The Mysql Server - that manage and save the database
• The Mysql client-level C++ libraries

A common mistake for beginners is to assume that the usage of MySql requires only the Flask-SQLAlchemy library. This installation phase will not be covered in detail here, but curious minds can access a few links and read more about MySql installation and setup on different platforms:

• How To Install MySQL on Ubuntu
• How To Install MySQL on CentOS
• How to Install MySQL on Windows

Usually, the MySql client libraries are shipped along with the server and there is no extra setup regarding the client libraries.

To check if Mysql is properly installed, we can open a terminal window and type:

$ mysql -u root -p

Anything is not an error, is usually a good sign.

Connect to MySql from Flask

Just to recap, the full setup to connect to MySql from Flask is presented below:

1# - Install the software - Flask and SQLAlchemy

$ pip3 install flask
$ pip3 install flask_sqlalchemy

2# - Create the database

This step can be done via MySql console or using a visual tool like phpmyadmin or MySQL Workbench (both free). Using the MySql console is the fastest way and the code is:

$ mysql -h hostname -u test -p
mysql > CREATE DATABASE flasktest;

3# - Configure Flask/SQLAlchemy to connect

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

db = SQLAlchemy(app)

# Connection credentials
db_user = 'test'
db_pass = 'SuperPass12'
db_name = 'flasktest'

# configuring our database uri
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://{username}:{password}@localhost/{dbname}".format(db_user, db_pass, db_name)

# basic model
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    password = db.Column(db.String(128))

if __name__ == "__main__":
    app.run(debug=True)

The only change in our Flask application is basically the connection string:

...
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://{username}:{password}@localhost/{dbname}".format(db_user, db_pass, db_name)
...

After injecting the variables, the SqlAlchemy connection string will looks like:

"SQLALCHEMY_DATABASE_URI" = "mysql://test:SuperPass12@localhost/flasktest"

From this point, we can use the Flask CLI to manage the tables and information in the same way as for SQLite connection. The whole, abstraction is coming from SQLAlchemy and we can start coding something useful and enjoy life on Earth.

The above content can be used to start a new app from scratch but sometimes we can use a minimal starter already configured with database, ORM, helpers,of and deployment scripts to help us deliver faster new projects. Here is a short list with open-source Flask starters provided by the AppSeed platform for free:

✅ Datta Able Flask

Datta Able Lite (free version) designed by CodedThemes now available in Flask Framework. Datta Able rendering is fast in all major browsers. It is passed through a good score in Google Page Speed, Pingdom, GT Metrix. Code passed via w3 validators with all w3 standards. This admin panel is fully responsive and tested on all retina devices.

• 👉 Datta Able Flask - Product page
• 👉 Datta Able Flask - LIVE demo

✅ Flask Template Argon

Argon Dashboard designed by Creative-Tim coded in Flask with authentication, ORM, Blueprints, and deployment scripts for Docker, HEROKU, and Gunicorn/Nginx stack. Argon Dashboard is built with over 100 individual components, giving you the freedom of choosing and combining. Every component has multiple states for colors, styles, hover, and focus, that you can easily access and use.

• 👉 Flask Argon Dashboard - product page
• 👉 Flask Argon Dashboard - LIVE deployment

✅ Flask Pixel UI Kit

Pixel UI Kit (Free version) designed by Themesberg and coded in Flask Framework with SQLite database, SqlAlchemy ORM, and authentication. Pixel is a free, fully responsive, and modern Bootstrap 4 UI Kit that will help you build creative and professional websites. Use our components and sections, and switch some Sass variables to build and arrange pages to best suit your needs.

• 👉 Flask Pixel Lite - product page
• 👉 Flask Pixel Lite - LIVE Deployment

✅ Flask Bootstrap 5 Volt

Open-source Flask Bootstrap 5 seed project crafted with authentication, ORM, and deployment scripts on top of a modern jQuery-free design - Volt Dashboard. This Dashboard Template is designed on top of Bootstrap, the world’s most popular framework for building responsive, mobile-first sites.

• 👉 Volt Dashboard Flask - Product page
• 👉 Volt Dashboard Flask - LIVE Demo

Thanks for reading! For more resources, feel free to access:

• 👉 Access AppSeed for more starters and support
• 👉 Deploy Projects on Aws, Azure, and DO via DeployPRO