Using Flask SQLAlchemy with PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It is a very useful database software just like MySQL and SQLite.

By the end of this tutorial you will learn how to setup postgresql on your ubuntu system and how to connect it to your flask web application via Flask SQLAlchemy.

Let us start

Installing PostgreSQL on Ubuntu

To install postgresql on your system you can use the following commands as below

sudo apt-get update
sudo apt-get -y install postgresql postgresql-contrib

To check if postgresql was successfully installed you can use systemctl to check it. This is used to also verify the status of postgresql.

sudo systemctl status postgresql.service
#Alternate Method
sudo service postgresql status

You can automatically launch PostgreSQL upon system boot-up,  

systemctl.sudo systemctl enable postgresql.service

To start or restart PostgreSQL you can replace the status with startand restart accordingly

sudo systemctl start postgresql.service

or with this which will show active if the service starts

sudo service postgresql start

Setting Up a New User For PostgreSQL

By default when you install postgresql, you can access the database using the postgres root user also called superuser. This user has admin/root access and permission hence you will need to create a new user to set some restrictions to your database.
Let us see how to access the service using ‘postgres’. You can do so using the following commands

sudo su postgres

or with this

sudo su - postgres

You can choose to change the password for the postgres user via this command if you want

sudo passwd postgres

Creating a New User

Let us create a new user and assign it some new roles.

CREATE USER newuser WITH PASSWORD 'password';

You can now assign a new role to this user. We will assign an admin/superuser role to this new user.

ALTER USER newuser WITH SUPERUSER;

Alternatively you can also create a new user who has the permission to create a database together. This is very useful and simplifies the task

CREATE ROLE newuser WITH CREATEDB LOGIN PASSWORD 'password'

Creating a Database

There are two ways to create a database with PostgreSQL.Either with the shorthand or with the Query Mode.

CREATE DATABASE main_db WITH OWNER newuser;

The code above creates a database called ‘main_db’ and assigns the owner of this database as ‘newuser’.

Alternatively you can also create a database using the createdb your_database command.

Useful Commands

• \l: list databases
• \du: list users
• \dt: list tables
• \c your_database: switch databases
• \q: quit or exit

Connecting Flask with PostgreSQL

Flask is an awesome microframework for building web applications. In order to work seamlessly with other databases you will need to install via pip Flask-SQLAlchemy and the required database driver such as sqlite,postgresql (psycopg2,psycopg2-binary) and python-mysql.

Since we are using postqreSQL we will need the username, password and the URI for the database. You may have to make sure that your postgreSQL service is running and active and you have created a database.

Let us check the basic URI we will need

postgresql://username:password@localhost:5432/your_database

You can simplify it with this code

username = 'newuser'
password = 'password'
database = 'your_database'

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

Now how do you incorporate it into flask. First of all we will import flask_sqlalchemy and initialize our flask app. Then we will work on the configuration which will include a SECRET_KEY,DATABASE_URI and a database model for our table and fields.
Let us see the code below

from flask import Flask,request,render_template,url_for,jsonify,redirect,flash
from flask_sqlalchemy import SQLAlchemy

# Init App
app = Flask(__name__)
app.config['SECRET_KEY'] = 'thisissecret'
# our database uri
username = "jcharis"
password = "password"
dbname = "maindb"
app.config["SQLALCHEMY_DATABASE_URI"] = f"postgresql://{username}:{password}@localhost:5432/{dbname}"

db = SQLAlchemy(app)

# Create A Model For Table
class BlogPosts(db.Model):
    __tablename__ = 'blogposts'
    id = db.Column(db.Integer, primary_key=True)
    blog_title = db.Column(db.String(1000))
    blog_description = db.Column(db.String(6000))



@app.route('/',methods=['GET'])
def index():
    posts = BlogPosts.query.all()
    return render_template("index.html",posts=posts)


@app.route('/posts',methods=['GET','POST'])
def add_posts():
    if request.method == 'POST':
        blog_title  = request.form['blog_title']
        blog_description  = request.form['blog_description']
        blog_post = BlogPosts(blog_title=blog_title,blog_description=blog_description)
        db.session.add(blog_post)
        db.session.commit()
        flash("Post Added")
    return redirect(url_for('index'))



if __name__ == '__main__':
    with app.app_context():
        db.create_all() # <--- create db object.
    app.run(debug=True)

We have now seen how to incorprate postqresql with flask.

For our front ent we have this in our index.html file

<body>
    <div>
        PSQL Flask
    </div>
    <div>
        <form action="{{url_for('add_posts')}}" method="POST">
            <input type="text" name="blog_title" placeholder="Blog Title">
            <textarea name="blog_description"></textarea>
            <button type="submit">Submit</button>
        </form>
    </div>

    <hr/>
    <div>
        {% for p in posts %}
        <ul>
            <li>{{p.blog_title}}</li>
            <li>{{p.blog_description}}</li>
        </ul>
        {% endfor %}
    </div>


</body>

CHECKING & QUERYING YOUR DATABASE

To directly check and query your database you can login to your PostgreSQL database and then query your database as below

# Access Postgresql
sudo su postgres
# Start PSQL REPL
psql - U newuser
### If you have an error here use `SHOW hba_file` to get the path to the 
### hba_file and edit the line with 'unix domain socket connection only' from ### `peer` to `md5` and then save the file and restart

#List Databases
\du

#Switch Database
\c your_database

#Make A Query
SELECT * FROM blogpost;

Check Databases

Switch Databases and List Tables using \c and \dt.

Making A Query

We have seen how to use flask and postgresql via Flask SQLAlchemy. I hope this was useful. You can check the video tutorial for more

Thanks For Your Time

Jesus Saves

By Jesse E.Agbe(JCharis)