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.

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

/flask-blog
    |-- run.py             
    |-- config.py    
    |__ /env            
    |__ /app            
             |-- __init__.py
             |-- views.py
             |-- models.py         #will contain our sql code           
         |__ /templates
             |-- index.html
         |__ /static
             |--foundation.css

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 https://github.com/mitsuhiko/flask/tarball/master
(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 http://foundation.zurb.com/cdn/releases/foundation-5.5.0.zip
(venv)[leo@flask-blog]$unzip foundation-5.5.0.zip

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 config.py
app.config.from_object('config')
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/config.py
#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 config.py. This way if anyone wants to reuse my app they just need to make changes to’config.py’.

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/models.py
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/db.py
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__':
    manager.run()

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 db.py
#Initialize migrations support
(venv)[leo@flask-blog]$ python db.py db init
#Generate a migration
     (venv)[leo@flask-blog]$ python db.py db migrate

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.
Create
To add posts to your blog, first create an add.html template.

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

<form action="" method="post">
Title
<input type="text" name="title"/>
Body
<textarea type="text" name="body"></textarea>


<button class="button postfix" type="submit">Add</button>

</form>

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

 (venv)[leo@flask-blog]$ vim flask-blog/app/views.py
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'])
		db.session.add(post)
		db.session.commit()
		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/run.py
from app import app
app.debug = True
app.run(host='1.2.3.4')
(venv)[leo@flask-blog]$ python flask-blog/run.py
* Running on http://1.2.3.4:5000/
 * Restarting with reloader

If everything works as planned then you should be able to add posts successfully on resolving “http://1.2.3.4:5000/add”
flask sqlachemy tutorial
Read
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/views.py
@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
<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Flask Blog</title>
<link rel="stylesheet" href="static/css/foundation.css" />
<script src="static/js/vendor/modernizr.js"></script>
</head>
<body>

<!-- Main Page Content and Sidebar -->
{% for post in post %}
<div class="row">
<!-- Main Blog Content -->
<div class="large-9 columns" role="content">
<article>
<h3>{{ post.title }}</h3>

<div class="row">
<div class="large-6 columns">
<p>{{ post.body }}</p>
<p><a href="{{ url_for('edit', id=post.id) }}">Edit</a></p>

</article>

<hr />

{% endfor %}

<!-- End Main Content -->

<script src="static/js/vendor/jquery.js"></script>
<script src="static/js/foundation.min.js"></script>
<script>
$(document).foundation();
</script>

</body>
</html>

Update and Delete

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

(venv)[leo@flask-blog]$ vim flask-blog/app/views.py
@app.route('/edit/' , methods=['POST', 'GET'])
def edit (id):
    #Getting user by primary key:
    post = Post.query.get(id)
    if request.method == 'POST':		
		post.title = request.form['title']
		post.text =  request.form['body']
		db.session.commit()
		return  redirect(url_for('index'))
    return render_template('edit.html', post=post)

@app.route('/delete/' , methods=['POST', 'GET'])
def delete (id):
     post = Post.query.get(id)
     db.session.delete(post)
     db.session.commit()
     flash ('deleted')
	   
     return redirect(url_for('index'))

(venv)[leo@flask-blog]$ vim flask-blog/app/templates/edit.html
<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title> Blog</title>
<link rel="stylesheet" href="static/css/foundation.css" />
<script src="static/js/vendor/modernizr.js"></script>
</head>
<body>
<form action="/edit" method="post">
<div class="row">
<div class="large-12 columns">
<label>Title
<input type="text" name="title" value="{{post.title}}"/>
</label>
</div>
</div>
<div class="row">
<div class="large-12 columns">
<label>Post
<textarea type="text" name="text">{{ post.body}}</textarea>
</label>
</div>
</div>
<div class="small-2 columns">
<button class="button postfix" type="submit">Edit</button>
</div>
</div>
</div>
</div>
</form>
<script src="js/vendor/jquery.js"></script>
<script src="js/foundation.min.js"></script>
<script>
$(document).foundation();
</script>
</body>
</html>

Add edit and delete links in your index.html

<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Flask Blog</title>
<link rel="stylesheet" href="static/css/foundation.css" />
<script src="static/js/vendor/modernizr.js"></script>
</head>
<body>



<!-- Main Page Content and Sidebar -->
{% for post in post %}
<div class="row">

<!-- Main Blog Content -->
<div class="large-9 columns" role="content">

<article>

<h3>{{ post.title }}</h3>


<div class="row">
<div class="large-6 columns">
<p>{{ post.text }}</p>
<p><a href="{{ url_for('edit', id=post.id) }}">Edit</a></p>
<p><a href="{{ url_for('delete', id=post.id) }}">Delete</a></p>


</article>

<hr />

{% endfor %}

<!-- End Main Content -->

<script src="static/js/vendor/jquery.js"></script>
<script src="static/js/foundation.min.js"></script>
<script>
$(document).foundation();
</script>
</body>
</html>

Now run your app

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

flask slqalchemy tutorial

References:

https://pythonhosted.org/Flask-SQLAlchemy/

https://pythonhosted.org/Flask-SQLAlchemy/queries.html

http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html

https://flask-migrate.readthedocs.org/en/latest/

https://github.com/miguelgrinberg/Flask-Migrate

Managing Linux server configs with the SaltStack

Managing Linux server configs with the SaltStackI came across Salt while searching for an alternative to Puppet. I like puppet, but I am falling in love with Salt :). This maybe a personal opinion but I found Salt easier to configure and get started with as compared to Puppet. Another reason I like Salt is that it let’s you manage your... Read More »

Creating your first Linux App with Python and Flask

Creating your first  Linux  App with  Python and FlaskWhether playing on Linux or working on Linux there is a good chance you have come across a program written in python. Back in college I wish they thought us Python instead of Java, it’s fun to learn and useful in building practical applications like the yum package manager. In this tutorial I will take... Read More »

How to install an Opensource VPN Server on Linux

How to install an Opensource VPN  Server on LinuxOne of the most concerning thoughts I have while browsing, Is how can I ensure that my data remains private and secure ? In my search for answers, I came cross a number of ways in which you can remain anonymous like using a proxy website. But still using a third party service was not... Read More »

Using Foreman, an Opensource Frontend for Puppet

Using Foreman, an Opensource Frontend for PuppetThe recent vulnerability in bash, got me running to update bash. It’s easy when you have maybe one or two Linux servers, but what do you do if you have 100’s or even thousands or servers? You need to use a server configuration and management tool like puppet. However, instead of using the command line,... Read More »

Using GIT to backup your website files on linux

Using GIT to backup your website files on linuxWell not exactly Git but a software based on Git known as BUP. I generally use rsync to backup my files and that has worked fine so far. The only problem or drawback is that you cannot restore your files to a particular point in time. Hence, I started looking for an alternative and found... Read More »

How to install Ruby, Ruby on Rails and Phusion Passenger on CentOS

Lately, I have been writing apps in ruby instead of PHP, as it has a low learning curve especially if you know bash scripting and an awesome framework called Rails. Ruby on Rails was so easy to work with that I got my app up and running in 48 hours. You can check it out... Read More »

How to configure and secure your Postfix email server

How to configure and secure your Postfix email serverOnce you have your application server up and running, you are going to need a good email server to deliver your emails. I have been using postfix for all my servers and below is the configuration I generally use. Installation of Postfix on CentOS 6 yum install postfix Sendmail is installed by default, so it... Read More »

How to configure and Install Config Server Firewall & Login failure Daemon

How to configure and Install Config Server Firewall & Login failure DaemonWhenever I setup a Linux VPS, the first thing I do is install a firewall. I have noticed an increase in attacks on my servers, especially from China. I use Config Server Firewall (CFG) and Login Failure deamon (LFD) because they are easy to set up and provide additional features like suspicious file reporting and... Read More »

How to create an Init script on Centos 6

How to create an Init script on Centos 6In my  last Shell Scripting tutorial, I used “inotifywait” along with rsync to create a real time file syncing script called backup. This script will run till I manually kill it or till I close my terminal, if I run it as a background process. Now I cannot keep my terminal open forever, but I... Read More »