How to build CRUD app with Python, Flask, SQLAlchemy and MySQL

In this post I will briefly describe, how you can you build a database driven CRUD (Create, Read, Update, Delete) app on Linux with Python, Flask, SQLAlchemy and MySQL. I used this process to create a blog and hence the examples below will describe how to store and modify posts in a MySQL database.

Update: You can scaffold a database driven CRUD app with

Software Versions
Python 2.7
Flask 0.11
Flask-SQLAlchemy 2.0
Flask-Migrate 1.3
MySQL-python 1.2
Foundation 5
Mariadb 10

Before you continue if you have not built an application on Linux with Flask or Python then I recommend you read Creating your first Linux App with Python and Flask.

Directory Creation

mkdir flask-blog
mkdir flask-blog/app
#HTML files will reside in the templates folder.
mkdir flask-blog/app/templates
#CSS/JS and other static files go in the static folder
mkdir flask-blog/app/static

Here is the directory structure and the files that they will contain

    |__ /venv 
    |__ /app            
             |--         #will contain our sql code           
         |__ /templates
             |-- index.html
         |__ /static

Install and Setup your MySQL server.
I used Mariadb 10 which is a fork of MySQL, you can use MySQL 5.6 if you prefer and install the relevant development packages.

[leo@flask-blog]$ yum install MariaDB-server MariaDB-devel
#Start the server
[leo@flask-blog]$ sudo systemctl start mysql
#Set the root password
[leo@flask-blog]$  mysqladmin -u root -p  ‘’ password ‘newpassword’

Remember to install ‘ MariaDB-devel’, else you will get an error when you install MysSQL Python packages later.
Setup and activate the virtual environment

[leo@flask-blog]$ pip install virtualenv 
[leo@flask-blog]$ virtualenv flask-blog/venv
[leo@flask-blog]$ source flask-blog/venv/bin/activate

Flask installation

(venv)[leo@flask-blog]$ pip install
(venv)[leo@flask-blog]$ pip install  Flask-SQLAlchemy mysql-python Flask-Migrate 

I have installed the latest version of Flask via git, you can install it directly as well via ‘pip install Flask’. I also installed Flask-SQLAlchemy, This extension gives us all the benefits of SQLAlchemy which we need to use for database operations.

Optional Download and Install Foundation 5
This is an optional step, Foundation 5 takes care of the HTML/CSS framework and has ready templates which you can use to design your blog or application. You can use bootstrap if you like.

(venv)[leo@flask-blog]$ cd flask-blog/app/static
(venv)[leo@flask-blog]$ wget

Database Migrations
Migrations keep track of any changes made to the database, they are beneficial especially when you need to migrate your database. Flask-Migrate, which we installed earlier will help us take care of this.

#Create an initialization file for your App
(venv)[leo@flask-blog]$ vim app/__init.py__
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
#Create an Instance of Flask
app = Flask(__name__)
#Include config from
app.secret_key = 'some_secret'
#Create an instance of SQLAclhemy
db = SQLAlchemy(app)
from app import views, models
(venv)[leo@flask-blog]$ vim flask-blog/
#Add your connection string
SQLALCHEMY_DATABASE_URI = 'mysql://root:password@localhost/blog'

The initialization file is where we need to declare our objects, for example the ‘app’ object will create an instance of Flask and the ‘db’ object will create an instance of SQLALchemy with the configuration of the app object as shown in the code above. I have stored the database connection string which contains the database login details in a global configurations file called This way if anyone wants to reuse my app they just need to make changes to’’.

I have also imported two modules called views and models, views will contain our main code where as models will contain our database related code.

(venv)[leo@flask-blog]$ vim flask-blog/app/
from app import db
class Post(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  title = db.Column(db.String(128))
  body = db.Column(db.Text)

  def __init__(self, title, body):
        self.title = title
        self.body = body

The db SQLAlchemy object contains a ‘db.Model’ and a ‘db.Column’ method to map tables and columns to classes and objects respectively. The types of the column can be passed as an argument as shown above.

In order to create these tables and columns you need to use Flask-Migrate.

(venv)[leo@flask-blog]$ vim flask-blog/
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand
from config import SQLALCHEMY_DATABASE_URI
from app import app, db

migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':

Flask-Migrate has two methods ‘Migrate’ and ‘MigrateCommand’. Migrate is used to initialize the extension, while Manager gives you access to command line options.
The application will now have a db command line option with several sub-commands.

(venv)[leo@flask-blog]$ chmod +x
#Initialize migrations support
(venv)[leo@flask-blog]$ python db init
#Generate a migration
(venv)[leo@flask-blog]$ python db migrate
(venv)[leo@flask-blog]$ python db upgrade

Once you have successfully connected and mapped your tables with SQLAlchemy you can begin to code the CRUD(Create,Read,Update, Delete) part of your app.
To add posts to your blog, first create an add.html template.

(venv)[leo@flask-blog]$ vim flask-blog/app/templates/add.html

Title Body

Now define a function that will handle your ‘add’ requests.

 (venv)[leo@flask-blog]$ vim flask-blog/app/
from flask import render_template, request,flash, redirect, url_for
from app import app, db
from app.models import Post

@app.route('/add' , methods=['POST', 'GET'])
def add():
	if request.method == 'POST':
		post=Post(request.form['title'], request.form['body'])
		flash('New entry was successfully posted')     

	return render_template('add.html')

To add or delete entries we need to use ‘session’. ‘db.session.add(post)’ will store data that needs to be entered but a change will not be made in the database until you call commit.

Running your app

(venv)[leo@flask-blog]$ vim flask-blog/
from app import app
app.debug = True'')
(venv)[leo@flask-blog]$ python flask-blog/
* Running on
 * Restarting with reloader

If everything works as planned then you should be able to add posts successfully on resolving “”
flask sqlachemy tutorial
In order to display the blog post, create an index page with a corresponding function which will display all posts.

(venv)[leo@flask-blog]$ vim flask-blog/app/
@app.route('/' )
def index():
  post = Post.query.all()
  return render_template('index.html', post=post)

SQLAlchemy provides a ‘query’ attribute to all instances of a class. You can use it to get data from the database, if you need to access data of the Post table then you can use ‘Post.query.all’ as shown above. I have used ‘all()’ to get all entries but you can use ‘filter_by(title=title).first’ to get selective data.

To display the data add the following code in your index.html file.

(venv)[leo@flask-blog]$ vim flask-blog/app/templates/index.html

    Flask Blog
     {% for post in post %}

{{ post.title }}

{{ post.body }}


{% endfor %}

Update and Delete

For update and delete add the following code and the corresponding template for .

(venv)[leo@flask-blog]$ vim flask-blog/app/

You can access this code in the Gist here, There was some problem displaying it.

Corresponding templates.

(venv)[leo@flask-blog]$ vim flask-blog/app/templates/edit.html


Add edit and delete links in your index.html

    Flask Blog
     {% for post in post %}

{{ post.title }}

{{ post.text }}



{% endfor %}

Now run your app

(venv)[leo@flask-blog]$ python flask-blog/

flask slqalchemy tutorial

Other Tutorials you must Read to build Database applications

One to Many Relationships with SQLAlchemy

Many to Many Relationships with SQLAlchemy

Building Database Driven REST API’s with Flask


You can also quickly Scaffold a CRUD app with



Follow me

Leo G

Is a Linux Hobbyist and Enthusiast. He Strongly believes in OpenSource Software and would like you to view and download his software at
Follow me

  • Pingback: Links 23/1/2015: Red Hat on IBM Power, Meizu Leaks With Ubuntu | Techrights()

  • Pingback: How to build a database driven app on Linux | Linux Admins()

  • Simas

    For me tutorial fails at the step:

    python db migrate

    I get error:

    Traceback (most recent call last):
    File “”, line 4, in
    from app import app, db
    ImportError: No module named app

    • The error is clear, you need to give the path to your directory, check the directory structure, needs to be in the same directory where your app code is defined

  • Pingback: How to build a database driven app on Linux | Zbyněk Svoboda()

  • Pingback: How to install Selenium on Linux and automate your web tests -()

  • Vandan Revanur

    After finishing all the steps and running my app , i get the following error.
    OperationalError: (OperationalError) (1049, “Unknown database ‘blog'”) None None

    • Leo G

      create a database with the name “blog” and then run your script

      • Vandan Revanur

        everything working fine ,but when i press the edit button it gives a 404 error. please help me out.

        • Leo G

          Can you share your edit.html and edit function code on pastebin or something?

          • Vandan Revanur

            Suddenly I’m not able to add a new post too. I’m getting a value error when i visit ‘/add’ .i have added all the required files on pastebin. here’s the link please help me out.

          • Leo G

            Value error means you are filling in incorrect value like instead of int, you are posting a string, with regards to edit, I cannot see any issues, but sometimes flask gives a 404 due to some syntax error, trying removing all the code in the edit function and then resolve the url. Then add the code back one statement at time till you get the 404, this way you can figure out the issue

          • Vandan Revanur

            filling in an incorrect value where ? i just typed /add to my root address and it gives me a value error

          • Leo G

            That’s bcoz your add function does not return anything for get request, Your return is inside your if loop, here try this

          • Vandan Revanur

            The add function works properly after the correction. In the edit.html we have used “/edit ” in the form action, but in we dont have a route for /edit , so when i press the edit button on the website it says ” The requested url is not found”. Can you help me with a route for “/edit” ?

          • Leo G

            Leave the form action blank as follows

          • Vandan Revanur

            Im getting a bad request error.The browser (or proxy) sent a request that this server could not understand.

          • Vinicius

            I’m getting the very same error, already tried the answer below and many other changes but still get the error when editing. Did you figure out what to change?

    • spouk

      Use u stupid mind. Don’t asking. Use head. Example really very simple.
      The example in the article really simple. If you can not “read” the code in this example, then think about a change of occupation. Programming is not your vocation. Find her and takes them.

  • Pingback: How to install Selenium on Linux and test your web applications | Linux Info()

  • Pingback: How to install Selenium on Linux and test your web applications | Linux Admins()

  • Pingback: How to build a database driven app on Linux | TutorTechs()

  • Sreenu Sasubilli

    Anyone solved Edit functionality with this tutorial? It is basically returning 400 bad request with form action as “”

  • Ghazi

    Hi, I would like to know how this line works:
    Line 6: url_for(‘users.user_add’) in index.html file present in the app/templates/users directory.
    Similar usages have also been done in the file also
    success_url = ‘users.user_index’

    How does the “users” variable get the key pairs as “user_add” and “user_index” ?

  • Pingback: JSON web token authentication with Flask and Angularjs -()

  • Pingback: Creating your first Linux App with Python 3 and Flask -()

  • malar

    how can i select the required column from postgresql database using python

  • Julian Maya

    Leo, thank you for your time.
    It was clear and fun!!