Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages
wb_sunny

Flask PostgreSQL – SQLAlchemy

Flask Postgresql

In this article, we will learn to connect our Flask Application with PostgreSQL Database systems using an ORM – Object Relational Mapper, called Flask SQLAlchemy.

What is PostgreSQL?

Similar to the MySQL Database management system, PostgreSQL is another type of RDBMS used for accessing, storing, and handling the data in the form of database tables.

PostgreSQL also uses SQL- Structured Query Language to access and handle databases and also perform various Tasks in PostgreSQL

The Basic Structure of PostgreSQL

Data is stored inside Postgres DB in the form of Table. A typical Postgres Table looks like this:

IdNameClassGrade
1AliaIXB
2SnippyXA
3RayVIIID
4KimVIA
5JenniferXIIB
6GinnyXA
PostgreSQL Table

The rows are called records and the columns are called fields. Therefore, in the above table we have 6 records and 4 fields.

Difference between MySQL and PostgreSQL

Though both MySQL and PostgreSQL belong to RDBMS, there are some key differences between both.

  • One of the basic differences between MySQL and PostgreSQL is that PostgreSQL is an ORDBMS (Object Relational Database Management System) while MySQL is a community-driven RDBMS.
  • Another advantage of PostgreSQL over MySQL is that it can support modern applications feature like JSON, XML, etc. while MySQL can only support JSON.

Why SQLAlchemy to Connect PostgreSQL to a Flask Application?

SQLAlchemy is an ORM-Objects Relational Mapper written in Python. It gives away around to interact with the Databases without using SQL statements.

It provides an extra layer on top of SQL which allows us to use Databases and Tables just like Python Class Objects. We just have to create a Class Object and SQLAlchemy will take care of the rest!

In Flask, unlike Django, which comes with a pre-built ORM in the form of Django Models, it does not have a pre-built ORM.

Hence we need to install the Flask-SQLAlchemy ORM library to use models in this web framework

Setting-up PostgreSQL in your system

In this section, we will download and set-up all the necessary packages for our Flask- SQLAlchemy-Postgres project.

1. Installing PostgreSQL shell

To install PostgreSQL, visit the link here. Once PostgreSQL is installed, open the SQL shell and set up your DB connection Username and password.

Run the below command to open shell in Terminal:

sudo su postgres pqsl

In case of Windows, directly search for SQL shell in the search menu.

I have kept my Connection details default (shown in bracket).

  • Server: localhost
  • Database: postgres
  • Port: 5433
  • Username: postgres

After you enter the password, you will be prompted into the PostgreSQL DB by default.

Now let’s create a new DB with the name Flask to store our data.

CREATE DATABASE <db_name>;

Here we are using SQL syntax only. Do check out our SQL tutorial on the JournalDev website to gain more knowledge about the query language.

To change the current Db to Flask DB use the command:

\c <db_name>;

That’s it now you are in the new Flask DB.

Postgres
Postgres

2. Installing psycopg2 adapter tool

We also need pyscopg2, which is the PostgreSQL database adapter for Python. Let’s run the pip command:

pip install psycopg2-binary

3. Installing ORM packages for Flask

First we need to install Flask-SQLAlchemy ORM.

To install the package, simply run the code:

pip install flask-sqlalchemy

Also we need to install Flask-Migrate.

Flask-Migrate, uses Alembic which is a light Database migration tool. It helps us to Create/Update Databases and Tables. It also allows us to update an existing Table incase you delete or create new Table Fields.

To install Flask-Migrate, run:

pip install Flask-Migrate

That is we need !! Now let’s get our hands dirty !!

Implementing a PostgreSQL database connection in Flask with SQLAlchemy

In this section, we will create a simple Flask application that stores user information in the Database.

1. Creating a Flask Model

A Model is a Python Class that represents a table in the Database. It contains information regarding the Table structure.

In Flask, it is more systematic to save all the DB information and the models in a separate file called – models.py situated right beside our main application file.

A typical models.py file looks like:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Model_name(db.Model):
    __tablename__ = 'table_name'

    field1_name = db.Column(db.Field1Type, primary_key...)
    field2_name = db.Column(db.Field2Type)
    field3_name = db.Column(db.Field3Type)

    def __init__(self, Field1_name,Field1_name,Field1_name):
        self.field1_name = field1_name
        self.field2_name = field2_name
        self.field3_name = field3_name

    def __repr__(self):
        return f"<statement>"

This is similar to a classic Python Class. These indicate the Fields of the Table and their representation.

Therefore, let us build a small InfoModel Table to store user information:

models.py:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class InfoModel(db.Model):
    __tablename__ = 'info_table'

    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String())
    age = db.Column(db.Integer())

    def __init__(self, name,age):
        self.name = name
        self.age = age

    def __repr__(self):
        return f"{self.name}:{self.age}"

2. Coding our Main Flask Application

Now we will connect Postgres with our Flask Application. The syntax is :

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from models import db, InfoModel

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://<username>:<password>@<server>:5432/<db_name>"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db.init_app(app)
migrate = Migrate(app, db)

#general Flask Code
@app.route('')
# Your code

if __name__ == '__main__':
    app.run(debug=True)

Here,

  • We create a Flask object – app
  • Then configure the PostgreSQL Connection
  • I have kept SQL_TRACK_MODIFICATIONS to False just for simplicity.
  • Then pass on app object to the SQLAlchemy object db
  • Create a Migrate Object for migrations.

That’s it!

Also add the below Views in the app.py file.

apps.py:

from flask import Flask,render_template,request
from flask_migrate import Migrate
from models import db, InfoModel

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://postgres:1148@localhost:5432/flask"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)
migrate = Migrate(app, db)

@app.route('/form')
def form():
    return render_template('form.html')


@app.route('/login', methods = ['POST', 'GET'])
def login():
    if request.method == 'GET':
        return "Login via the login Form"
    
    if request.method == 'POST':
        name = request.form['name']
        age = request.form['age']
        new_user = InfoModel(name=name, age=age)
        db.session.add(new_user)
        db.session.commit()
        return f"Done!!"


if __name__ == '__main__':
    app.run(debug=True)

We can interact with the Table just like a Class Object. We use:

  • db.session.add() to add new data
  • db.session.comit() to save the changes

3. Implementing the Flask Code

The last thing left is to run the migrations. Hence run the commands:

python db init
python db migrate
python db upgrade

That’s it,

Now run the server

python app.py

And lets check the Browser. Go to “/form

Form
Form

Hit Submit

Success
Success \

That’s it now in our PostgreSQL shell, Type:

SELECT * FROM info_table

And the data will be right there!!

PostgreSQL
PostgreSQL

Perfect!

Conclusion

That’s it, guys!! This was all about setting up Flask PostgreSQL and SQLAlchemy connections. See you the next time !! Till then, Happy Coding!!