Intro to Flask: Creating and Writing to Databases

Eric SchlesEric Schles

In this post, we will complete our understanding of Flask as a model view controller by showing how to add the "model" piece.

This article is a continuation of the Introduction to Flask as a Micro-framework.

The model of an MVC is the database connections that tell the application how to save persistent data. Typically, in MVC's you don't actually write a SQL scheme but rather interact with the database through an object relational model (aka ORM).

An ORM makes writing SQL statements easier and higher level. It also works across many different database languages (each database uses a different version of the SQL language). That means migrating from one database to another is extremely easy with ORM; you'll usually just have to change a few small configuration settings.

Because ORMs are high level, it's possible you won't get all the optimizations you could get if you wrote straight SQL. But this would require you to be an expert SQL user who spends a lot of time making your queries as efficient as possible.

And to do that, you'd need a great deal of understanding of the architecture of a given database, plus an understanding of how that translates into the higher level SQL language. For this and other reasons your best bet is to use ORM.

To show you what I mean, I'll first demonstrate an app written in straight SQL and then compare that with the same app written in an ORM. This first post will focus only on straight SQL. The next article will focus on making use of SQLAlchemy - a very good ORM.

A full application written with SQLite3:

(source)

schema.sql:

    drop table if exists account_holder;
    create table account_holder (
    id integer primary key autoincrement,
    email text not null,
    username text not null,
    phone text not null,
    password text null
);
    drop table if exists contact;
    create table contact (
    id integer primary key autoincrement,
    name text not null,
    phone text not null,
    username text not null,
    email text not null
);

SQLite is a lightweight database that's easy to use and great for small aplications. It comes with its own varient of the SQL language – as do all SQL databases. Most queries in SQLite will work for other databases, so it's great for prototyping any application that uses SQL.

Let's explore the first statement:

drop table if exists account_holder;  

To understand this statement, we must first understand how databases are organized: by tables and columns. Tables are like the data structures of the database, and a table's columns are like the individual values of the data structure. Everything in a database also has a name.

This particular SQLite statement says:

If a table with the name account_holder exists in the database, drop it (AKA delete the table). Notice the semicolon at the end of the statement. All SQL statements need to be ended with a semicolon.

Next we have:

    create table account_holder (

    id integer primary key autoincrement,

    email text not null,

    username text not null,

    phone text not null,

    password text null);

This creates the table 'account_holder' and gives it the following column names: 'id', 'email', 'username', 'phone', and 'password'. The 'create table' statement simply creates the table. What's of real interest is the creation of the variables. Notice we created an 'id' – a variable with two functions:

  1. An index to speed up searching for entries within the database, and
  2. a primary key to be referenced by other tables.

For those of you who haven't learned much about data structures – the reason the 'id' speeds up search is simple. Because we autoincrement the 'id', it will take on successively larger values for each entry increasing by 1 for each entry added. Thus, the rows of our table will be ordered by the natural numbers: 0,1,2,3,4,5,... etc.

This lets us use advanced search algorithms like binary search to quickly find an entry – assuming we can look for it by id.

Now let's talk about this primary key thing. A primary key and a foreign key show the relationship between two tables in SQL. Associating two tables in this way is useful for semantic reasons. For a greater understanding of this check out this example

The remaining fields in our table 'account_holder' should all make sense. Each is of type 'text', and it's required they're not null (or missing) from an insert statement.

Now that we understand our schema, let's create our database:

sqlite3 database.db < schema.sql  

Now let's look at some CRUD operations. These will be stored in models.py.

models.py:

    import sqlite3 as sql

    def insert_account_holder(email,username,phone,password):
        con = sql.connect("database.db")
        cur = con.cursor()
        cur.execute("INSERT INTO account_holder (email,username,phone,password) VALUES (?,?,?,?)", (email,username,phone,password))
        con.commit()
        con.close()

    def insert_contact(name,phone,username,email):
        con = sql.connect("database.db")
        cur = con.cursor()
        cur.execute("INSERT INTO contact (name,phone,username,email) VALUES (?,?,?,?)", (name,phone,username,email))
        con.commit()
        con.close()

    def select_account_holder(params=()):
        con = sql.connect("database.db")
        cur = con.cursor()
        if params==():
            cur.execute("select * from account_holder")
        else:
            string = "select"
            for i in xrange(len(params)-1):
                string += "%s,"
            string += "%s"
            string += " from account_holder"

            result = cur.execute(string)
            con.close()
            return result.fetchall()

    def select_contact(params=()):
        con = sql.connect("database.db")
        cur = con.cursor()
        if params==():
            cur.execute("select * from contact")
        else:
            string = "select"
            for i in xrange(len(params)-1):
                string += "%s,"
            string += "%s"
            string += " from contact"

            result = cur.execute(string)
            con.close()
            return result.fetchall()

A closer look at the 'insertaccountholder' function:

    def insert_account_holder(email,username,phone,password):    
        con = sql.connect("database.db")
        cur = con.cursor()
        cur.execute("INSERT INTO account_holder (email,username,phone,password) VALUES (?,?,?,?)", (email,username,phone,password))
        con.commit()
        con.close()

You'll use most of this function every time you work with a database. Both insert functions do the same thing - so you should be able to understand them both by understanding this one:

Setup the connection to the database

con = sql.connect("database.db")  

Create a cursor object

cur = con.cursor()  

Execute an SQL statement to send to the database:

cur.execute("INSERT INTO account_holder (email,username,phone,password) VALUES (?,?,?,?)", (email,username,phone,password))  

If we are sending data to the database, then commit any changes:

con.commit()  

And finally close out the connection:

con.close()  

Now that we have a feel for what these steps mean in English, let's dive a little deeper.

Start with:

  1. con = sql.connect("database.db")
  2. con.commit()
  3. con.close()

These are the standard statements for dealing with any "stream" of data to storage outside of a program. It's the same general idea for dealing with files. To make the point concrete, let's look at files and their database equivalents side by side.

Open the 'stream':

for a file:

f = open("file.txt.","w")  

for a database:

con = sql.connect("database.db")  

write to the 'stream':

for a file:

f.write(stuff)  

for a database:

con.commit()  

close the 'stream':

for a file:

f.close()  

for a database:

con.close()  

One very nice keyword in Python is the "with" statement, which is the best way to keep your files secure. It has a lot of other uses, but we'll cover security (my favorite) in this post.

For files we can use "with" like this:

    with open("file.txt","w") as f:
        f.write("I am going to write a single line here:\
         Syncano is awesome, and so are you :)")

For databases we can use "with" like this:

    with sqlite3.connect("database.db") as con:
        cur = con.cursor()
        cur.execute("INSERT INTO made_up_table (some_text) VALUES (?)" ,\
         ("I am going to write a single line here: Syncano is awesome, \
            and so are you :)" ))

So let's talk about what this is doing:

All this really does is take out the 'f = open("file.txt.","w")' and the 'f.close()' statement (from the example above) and replace it with 'with open("file.txt","w") as f'. Which is pretty nice.

Also - notice we're able to do something very similar with the 'with' statement in the database connection. This means less clean up for our database connections and insures they are always closed after being opened (As long as we use with statements)!

Even though this may seem like a minor thing, it could potentially be a pretty big deal. I won't go through all the technicalities, but let's just say not using 'with' is a huge security vulnerability.

Now we can safely re-write our functions to be more security centric:

    import sqlite3 as sql

    def insert_account_holder(email,username,phone,password):
        with sql.connect("database.db") as con:
            cur = con.cursor()
            cur.execute("INSERT INTO account_holder (email,username,phone,password) VALUES (?,?,?,?)", (email,username,phone,password))
            con.commit()

Yes - I'm only showing you one of the rewritten functions but the rest of them live at the github link above (and also here). You might try rewriting the rest as an exercise. :)

Now let's see how we are going to make use of these functions.

views.py:

    from app import app
    from flask import render_template,redirect, request, flash,g,session,url_for
    from models import *

    @app.route("/",methods=["GET","POST"])
    @app.route("/index",methods=["GET","POST"])
    def index():
        return render_template("index.html")

    @app.route("/signup", methods=["GET","POST"])
    def signup():
        return render_template("signup.html")

    @app.route("/signedup", methods=["GET","POST"])
    def signedup():

        email = request.form['email']
        username = request.form['username']
        password = request.form['password']
        phone = request.form.get('phone')

        if not session.get("logged_in"):
            insert_account_holder(email,username,phone,password)
        return render_template("homepage.html",username=username) 

    @app.route("/login")
    def login():
        return render_template("login.html")

    @app.route("/directory/<username>")
    def directory(username):
        contacts = select_by_username_contact(username)
        return render_template("directory.html",username=username,contacts=contacts)

There is a lot to digest here, unless you read my last post. But, since I'll assume all of you are now familar enough with Flask OR you read my last post, I'm going to just going to explain the function calls that have to do with the database.

    @app.route("/signedup", methods=["GET","POST"])
    def signedup():

        email = request.form['email']
        username = request.form['username']
        password = request.form['password']
        phone = request.form.get('phone')

        if not session.get("logged_in"):
            insert_account_holder(email,username,phone,password)
        return render_template("homepage.html",username=username) 

Here we are getting data from the user. If you recall from earlier in this post, this method "writes" to the database - so after this function is called, there will be a new row in the 'account_holder' table with any information you stored in 'email','username','phone','password'.

Now let's look at querying the database for information:

    @app.route("/directory/<username>")
    def directory(username):
        contacts = select_by_username_contact(username)
        return render_template("directory.html",username=username,contacts=contacts)

What's nice here is that we can look up data by 'username'. Notice that the 'username' is the 'username' of the 'accountholder', and not the 'username' of the 'contact'. The 'username' here acts as a foreign key for the 'accountholder', since this field also appears in the 'account_holder' table. Let's go ahead and change our schema and re-instantiate our database.

schema.sql:

    drop table if exists account_holder;
    create table account_holder (
        email text not null,
        username text primary key not null,
        phone text not null,
        password text null
    );

    drop table if exists contact;
    create table contact (
        name text not null,
        phone text not null,
        username text foriegn key not null,
        email text not null
    );

how to make the database:

sqlite3 database.db < schema.sql  

This concludes our basic introduction into SQL in Flask. But what happens when you want to send a new type of data to the database? Or when you want to query for a new type of information? When working with SQL, you'll either need to write a new method to do this - or you'll have to insert straight SQL into your code. Either way, it's likely you'll be writing something that requires a lot of testing and won't be nearly as efficient as code someone else spent more time on. It's not a pretty solution.

I tried my best so far to convince you that straight SQL is a bad idea, and in the next article, we'll look at an ORM that will make all of this much, much easier. But there are just a few instances in which you should still use SQL straight up.

An ORM will always be potentially less optimized than straight SQL - because the SQL queries you can write for a specific database will have their own optimizations. In two different SQL databases, the same ORM query might be slower or faster depending on the underlying algorithms being used.

For instance - say you have two versions of the same database. Let's also assume our database is indexed. And in one version, it's implemented with binary search - while in the other, it's implemented with linear search.

The writer of the ORM may not know this or may not have implemented high level queries to account for this - either because the change was recent or because the query is not used often. In this case, your ORM would perform very differently. To drive that point home - let's look at the running time of linear search versus binary search.

Binary search:

  1. average time complexity: O(log(n))
  2. worst time complexity: O(log(n))
  3. worst space complexity: O(1)

Linear search:

  1. average time complexity: O(n)
  2. worst time complexity: O(n)
  3. worst space complexity: O(1)

So - in terms of space complexity - no big deal. You'll run out of memory just as fast with one as the other. However, in terms of time complexity - you are almost always much better off using binary search.

(By the way, if you don't understand Big-Oh notation, I'd say it's worth googling.)

Build powerful apps in half the time

Use our serverless platform to set up your backend in minutes.

Learn more
Eric Schles
Author

Eric Schles