Working with PostgreSQL and flask_sqlalchemy

In this article, we'll be looking at creating and connecting a simple web app built with flask; a python based microframework, to a PostgreSQL database. Here we'll be making use of sqlalchemy which is an object-relational mapper for python. Without further ado, let us get right into it.

PS: I assume you already have python and flask installed so I won't be going into details about that. If you don't, you can visit the python website to download it then run the following command in your terminal to install flask.

$ pip install flask

What is PostgreSQL?

According to its Wikipedia page, "PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance" -Wikipedia

What distinguishes Postgres from other relational databases like MySQL is the fact that it is object-oriented and ships with more use cases and features which is important to run some applications.

I am currently working on a Linux operating system (Parrot to be specific). So we'll be going through these processes the Linux way. Cheers.

Installing Postgres

If like me, you are using the latest version of your Linux distro, then it is very likely that you already have Postgres installed on your machine. Then you can skip to this. If you don't have it installed, however, you can go through the process below to install it.

First, create the file repository configuration

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Then import the repository signing key

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then go ahead to update the package list

$ sudo apt-get update

With all these done, you can then install Postgresql by doing

$ sudo apt-get install postgresql

Starting The Server

With the installation done, we can then proceed to start the Postgres server by running the following in our terminal

$ sudo service postgresql start
[sudo] password for user:
$

Once this runs without any issue, we know our server is running. With this, let us proceed to create our user and then our database. We are creating a new user here because it is bad practice to use the postgres user (personal opinion though). To do this, we enter the following to get into the postgres cli.

$ sudo -i -u postgres

from the above command, we get a prompt similar to this

postgres@parrot:~$

then we proceed by typing in psql

postgres@parrot:~$psql
...
...
postgres=#

this takes us to an interactive postgres environment where we can run general SQL queries and also postgres specific commands.

Creating a user

We proceed to create a user by typing the SQL command for that.

postgres=# CREATE ROLE user WITH CREATEDB LOGIN PASSWORD 'password';

Here, we created a user with the ability to create databases and also gave the user a login password (password).

We can then create a database for this particular user by doing

postgres=# CREATE DATABASE ourdb WITH OWNER user;

with this done, we have created a database named ourdb for user. Exit the environment by typing \q and exit one after the other.

To read more on PostgreSQL, visit the page here

Creating our web app

Next, we'll be creating our simple web app with flask, a microframework for the python programming language. Read more about it here.

We will set up a simple app by doing

from flask import Flask

app = Flask(__name__)

@app.route('/')
def index():
    return 'Hello, World!'

if __name__ == "__main__":
    app.run()

We would be using flask_sqlalchemy to connect to our Postgres database we created earlier. But first,

What is Flask_sqlalchemy?

"Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks." - PyPi

SQLAlchemy?

"SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language." SQLAlchemy

With that, we can proceed to writing our configurations, creating our database tables (also known as models), and connecting to the database.

We make our flask app more workable by adding the following lines

from flask import Flask
from flask_sqlalchemy import SQLAlchemy  # 1

app = Flask(__name__)
app.config['SECRET_KEY'] = "xxxxxxxx"    # 2

db = SQLAlchemy(app)

# our database uri
username = "user"
password = "password"
dbname = "ourdb"

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://{username}:{password}@localhost:5432/{dbname}"

if __name__ == "__main__":
    app.run()

We also create our model by adding the class right after the database configuration

# basic model
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    password = db.Column(db.String(128))

if __name__ == "__main__":
    app.run()

With this done, we can then create our tables for the database by proceeding to the terminal. However, before we do that, we have to install a library to help python connect to the PostgreSQL database. The library is known as psycopg2.

We can install this by running the code below in the terminal

$ pip install psycopg2

If for dependency reasons, we get an error, we can do

$ pip install psycopg2-binary

With that installed, we can proceed to create our table. The connection to the database takes place here. We open a python shell session in the terminal and run the following commands

$ python
...
>>>from ourapp import app, db, User
>>>app.app_context().push()
>>>db.create_all()
>>>

As long as this runs without any errors, we have successfully connected to our database and created the User table. We can then populate the DB either by going to our database and running the SQL commands for that

i.e

$ sudo -i -u postgres

Then we enter the database ourdb owned by user with the following command. (The password would be required here. For us, the password is password)

psql -h <hostname> -d <dbname> -U <username> -p <port>
i.e

postgres@parrot:~$ psql -h localhost -d ourdb -U user -p 5432

This would ask for our password then take us to an interactive command-line environment where we can run SQL commands to populate our database.

We can also do that from our python shell as flask_sqlalchemy allows us to do that.

>>> from ourapp import app, db, User
>>>app.app_context().push()
>>>db.create_all()
>>>
>>>user = User(email="[e-mail]", password="password_hash")
>>>db.session.add(user)
>>>db.session.commit()
>>>

With this, we have successfully created a user and added the details to our database.

Conclusion

In this article, we learned about PostgreSQL, how to install and work with it. We also created a simple flask app and saw how to create tables, connect our flask app to the database and populate the database.

I trust with this you are capable of setting up your flask projects for use with the PostgreSQL database. If you have any questions, you can contact me on Twitter @agba_dr3.

Cheers!

Comments

[Makeem1]

I am getting error while connecting to postgres.