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:
| Id | Name | Class | Grade |
|---|---|---|---|
| 1 | Alia | IX | B |
| 2 | Snippy | X | A |
| 3 | Ray | VIII | D |
| 4 | Kim | VI | A |
| 5 | Jennifer | XII | B |
| 6 | Ginny | X | A |
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.
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“
Hit Submit
That’s it now in our PostgreSQL shell, Type:
SELECT * FROM info_table
And the data will be right there!!
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!!