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 https://github.com/Leo-g/Flask-Scaffold
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.
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 |__ /venv |-- db.py |__ /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
(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
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 (venv)[leo@flask-blog]$ python db.py 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