Flask Biography Tutorial Part V : Implementing Application Model using SQLAlchemy + PostgreSQL 9.2

 

 
Even Batman need a Biography
Even Batman needs a Biography

In this fifth article we are going to liven up our bio application with real data coming from the database. From our previous post, we already have a nice visual representation of our bio application. Now it is time to supply its content with user very own data. It will make your application feels so nice. Alive.

We are going to utilize SQLAlchemy to do :

  • Model definition : let's define a single Users class which will hold all of our registered users.
  • Data population : as we already have the place to store the data, now let's populate it with sample data.
  • Data query : if certain user page is requested, for example http://bio-ekowibowo.rhcloud.com/ekowibowo (I reserved this username, sorry), let's query his/her data and ...
  • Data display : .. let's display it in our bio.html template page.

A sharp eyed reader may notice, "How do you populate those data? I don't see any user signup action there!". Correct! We are going o skip those important feature for now. Here, I am going to concentrate you on the SQLAlchemy part. I think my next article will going to talk about those Signup form. 

 

What is SQLAlchemy ?

It's an Object Relational Mapper/ORM for Python. What is ORM? It is a very simple yet powerful way for your application to define and access Relational Database without the need to use those tedious, error prone and sometimes complicated SQL syntax. Using ORM allows you to focus on data modelling part of your application while at the same time freeing you from the data definition using SQL Data Definition Language.

The SQL purists may said, "What? Learning another layer for my application? I better stick with my old SQL skills!". For a moderate answer, if at certain place in your code you need to have a specific SQL feature from your favorite RDBMS product, you still able to by pass ORM layer and go directly to the core SQL feature. But having another layer in front of your database bring many great advantages. For example, you don't have to worry about your choice of database technology. You can switch it anytime. In this article I am going to use a free to add PostgreSQL 9.2 cartridge in Openshift. But you are free to replace it with another database technology of your choice such as SQLite3 or MySQL. Your code will only differ in its database URL supplied to SQLAlchemy. How cool is that? Cool 

Using SQLAlchemy in Python Application

Let's inspect the code that need to be change if you want to use SQLAlchemy. Here is the snippet or our application code reside in a single file main.py :

1
2
3
4
5
6
7
8
...
from flask_sqlalchemy import SQLAlchemy
import os
application = Flask(__name__)
application.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('OPENSHIFT_POSTGRESQL_DB_URL') if
os.environ.get('OPENSHIFT_POSTGRESQL_DB_URL') else 'postgresql://localhost:5432/bio'
db = SQLAlchemy(application)
...

As always, every Python application that needs an existing features to be added, must always import it. Remember that this SQLAlchemy package become available to our application as the result of our local environment preparation previously. If you push your application to Openshift, don't worry, Openshift will take care of its dependency automatically by inspecting the content of your setup.py, that clearly include SQLAlchemy in its dependencies list.

In order SQLAlchemy to be fully functional for your application, it only need one single entry with the key SQLALCHEMY_DATABASE_URI in application.config variable, which is a Python dictionary. This dictionary entry is given its value according to where you run your application : whether from Openshift runtime environment or from your local database. That's why we need an import os statement. Using this way, your application can run smoothly in Openshift or in your local installation. Lastly, we just instantiate an SQLAlchemy object into a variabel in our application, named db (you can pick any name you want!). You will always refer to this db variable when working with SQLAlchemy in your application.

Preparing Local PostgreSQL Database

Installing PostgreSQL 

From our previous step on how to use SQLAlchemy, we saw this URL : postgresql://localhost:5432/bio.  This means, we have to prepare a database named bio in our local PostgreSQL installation. Now, come the interesting part : how do you do this in your operating system of choice?

First, you have to install PostgreSQL 9.1 into your OS. Why 9.1? It's because this version is the one that we use in Openshift. Ubuntu users will have the easiest way of installing it by just issuing these command:

1
2
sudo apt-get install postgresql-9.1
sudo apt-get install postgresql-server-dev-9.1

For Windows or OSX users, you have to manually download it from http://www.postgresql.org/.

After installation, you have to create a database named bio (or whatever name that you choose, but be sure to change the URL variable). Again, for *nix user (I am not sure with Windows users though), just issue this command:

1
sudo createdb bio

If you want a GUI approach, you can use PgAdmin III that shipped with PostgreSQL installation in Windows/OSX. Ubuntu users can also use PgAdmin III by issuing command:

1
sudo apt-get install pgadmin3

This is way I love developing application in Ubuntu... 

Making PostgreSQL Trust Your Connection

If you don't want to supply username and password when accessing PostgreSQL, in your *nix installation you have to open /etc/postgresql/9.1/main/pg_hba.conf and find these lines below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5

You have to change md5 to trust.

Restart your PostgreSQL server. 

But should you want to have username and password for your database, you can use a complete URL format: postgres://username:password@localhost:port/databasename. For example: postgres://john:secret@localhost:5432/bio.

You are free to choose what method you like the most!

Data Modelling of Our Registered Users

In its simplest definition, data modelling is the process of constructing what you want to store by your application to the Relational Database. The term relational database is very significant in this definition, as it uniquely define how you construct your database. Relational database is just one type of database existed. There are others, such as Flat Database or NoSQL. Relational database is widely popular as it allows complex application data to be stored in this so called 3rd normal form,  that eliminates data redundancy and enabled a very complex query of the application data stored in logically related tables.

Before ORM era, you have to use SQL to define your application data in Relational Database. It's quite simple actually. But, if you starting to work with different database at the same time, this SQL definition approach starting to bring a maintenance nightmare.  This is because each RDBMS product may bring different syntax for the same feature. Or even having a complete different feature with the rest of the RDBMS in the market. Thanks to ORM technology, and especially to those who work for SQLAlchemy, that days are over. Now you can model your application data using your programming language of choice, by completely forgetting that you use a specific RDBMS product.

How do we define our application data for this bio application? Have a quick inspection of the currently running bio application in your local environment. What is its core data? Registered users, right? Good. Now, let just inspect this new code that intuitively define our Users class: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
class Users(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(60), unique=True)
   firstname = db.Column(db.String(20))
   lastname = db.Column(db.String(20))
   password = db.Column(db.String)
   email = db.Column(db.String(100), unique=True)
   time_registered = db.Column(db.DateTime)
   tagline = db.Column(db.String(255))
   bio = db.Column(db.Text)
   avatar = db.Column(db.String(255))
   def __init__(self, username = None, password = None,
email = None, firstname = None, lastname = None,
      tagline = None, bio = None, avatar = None):
      self.username = username
      self.email = email
      self.firstname = firstname
      self.lastname = lastname
      self.password = password
      self.tagline = tagline
      self.bio = bio
      self.avatar = avatar

Remember from my first post in this blog when I said that Python didn't have a notion of data type for a primary data type? Hence, it is very well suited for first timer in programming? Well, now that we are grown up, we are not working with a primary data type anymore. Those variable in Users class is an instance of SQLAlchemy Column class. Enabling us to use a full potential of ORM technology : completely neglecting our RDBMS product working behind this ORM layer. 

"Is it that simple? Isn't that you missed product portofolio?", you may wondering. Correct. In this article we just going to work with a single class, hence a single table. In another part of this article series, we will implement product portofolio feature using relations concept in SQLAlchemy.

Using your Application Model Definition

Tables Creation

For those SQL veteran, you are going to miss your SQL Data Definition Language. Because, this is how you create tables for the already defined Users class. Sorry.

1
 db.create_all() 

If your tables aren't created yet, it will create it for you. With just a single line of code.

But, with one caveat though: sometimes it isn't able to modify the already existing tables. We need to talk about data migration for that feature. For the current state of the application, let just drop all of our current tables and then re-create it for each application startup. We do this in a function named dbinit().

1
2
3
def dbinit(): 
     db.drop_all()
     db.create_all()

 

Data Population

We already have the application along with its tables. I am sure you really want to start the application and experience yourself its new feature. But, as we don't have a database backend application (which can populate your data) like the one created automatically in Django,  we must use SQLAlchemy code data population code for that. Let us modify our previous dbinit() method.

1
2
3
4
5
6
7
8
9
def dbinit(): 
     db.drop_all()
     db.create_all()
 db.session.add(Users(username='ekowibowo', firstname='Eko',
                         lastname='Suprapto Wibowo', password='rahasia',
                         email='swdev.bali@gmail.com',
                         tagline='A cool coder and an even cooler Capoeirista',
                         bio = 'I love Python very much!',
                         avatar = '/static/avatar.png'))

We use SQLAlchemy session object, calling its add method with an instance of Users class. Session in its simplest term is a connection to your RDBMS working in the background. You will call it often in your application life cycle to do the usual create, read, update and delete action to the underlying tables.

We already have a functional initdb() method. But when do you call this method? Simple: before our application start, whether it is run locally or on Openshift.  This is the snippet of our main.py application:

1
2
3
if __name__ == '__main__':
    dbinit()
    application.run(debug=True, host="0.0.0.0", port=8888) 

We call dbinit() before application.run() was called.

For the wsgi/application file that being use by Openshift to start your application, the modification is nearly identical.

1
2
3
...
from main import *
dbinit() 

In the previous wsgi/application file we only issue from main import application, which import application object from main.py. Now that we need to call dbinit() method, it's easier to import all methods and objects/variabels from main.py. Hence, we issue import *. If you are a picky programmer, you can also issue each import individually, shown below:

1
2
3
from main import application
from main import dbinit
dbinit() 

If you have reach this your code progress until this point, now you can safely run your application. But still, nothing change yet visually. For that we need to query those data.

Querying Data 

Our application already have code to initialize its database with a single user data (or more if you add another users in dbinit() method). It's time to display those data. First we have to query it. Guess where we have to do it? Right, in def index() of our one and only application code in main.py. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@application.route('/')
@application.route('/')
def index(username = None):
   if username is None:
      return render_template('themes/water/index.html', page_title = 'Biography just     
for you!')
   user = Users.query.filter_by(username=username).first()
   if user is None:
      user = Users()
      user.username = username
      user.firstname = 'Batman, is that you?'
      user.lastname = ''
      user.tagline = 'Tagline of how special you are'
      user.bio = 'Explain to the rest of the world, why you are the very most unique     
person to look at'
      user.avatar = '/static/batman.jpeg'
      return render_template('themes/water/bio.html', page_title = 'Claim this name : '
+ username, user = user)
   return render_template('themes/water/bio.html', page_title = user.firstname + ' ' +
user.lastname, user = user)

I hope the code rather self explanatory (as it is in every Python code!). The most important line is the line user = Users.query.filter_by(username=username).first(). We can filter by anything and returning any data matching our filter criteria. But knowing that username is unique, we can be sure that it will only return one single user. SQLAlchemy have others method to query your data, step by step we are going to use in the later part of this article series.

In this route/view method, if the user is not already registered, we present a new blank User showing that this username is available to take. We also assume that every unknown user is, well, Batman! Laughing 

Data Presentation

Data presentation is the responsibility of the bio.html. Have a look at previous post of how we display variable that passed to our template using a simple Jinja2 tag. The snippet of bio.html is shown below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
...
<div class="page-header">
<h1>{{ user.firstname }} {{ user.lastname }}</h1>
 
{{ user.tagline }}
 
</div>
...
<div class="col-sm-4">
<div class="starter-template">
<img src="/user.avatar" />
<div align="justify">
{{ user.bio }}
</div>
</div>
...

Conclusion 

In this part, you have understand how a Flask application define its application by modelling it using SQLAlchemy which is a very simple yet powerful ORM tool. You have also know how to create tables, query data and presenting it to users. But, we still populate data using Python code. This is not how a real world application behave!  

Our next article will be dealing with how every visitors can eventually register to our service. We will use Flask-WTF to help us dealing with web forms.  We will also explore Flask-Login package which will gives our application production quality login feature.  

You can test our live application using this url :

 

 

You can download the fifth part of this application here : bio-part-5.zip 

Stay tuned! 




Leave comments

authimage
  • TYPO: we just instantiate an SQLAlchemy object into a variabel ==> variable, of course

    • bossip
  • How do I use onupdate and default values in Column class to generate a migration?

    • Leo

Copyright(c) 2014 - PythonBlogs.com
By using this website, you signify your acceptance of Terms and Conditions and Privacy Policy
All rights reserved