A database is used to store and maintain persistent data that can be retrieved and manipulated efficiently. we usually need a database, an organized collection of data for example in an e-commerce web app where we need to store the data about the users, orders, carts, shipping, etc. In a database, data is stored in such a way that it makes data transactions very efficient.
In this article, we will cover how we can configure a PostgreSQL database with a Flask app and store some data in the database after configuring it. Now before directly moving to the configuration phase here is a short overview of all tools and software we will use.
Python is a prevalent programming language that we will be using in this article. Flask is a lightweight Python web framework that provides valuable tools and features for creating web applications in Python. PostgreSQL or Postgres is a powerful, open-source object-relations database system. We are using psycopg2 which is a PostgreSQL database adapter that allows you to interact with the PostgreSQL database in Python.
Prerequisites
- A decent understanding of Python and a machine with Python installed
- Understanding of basic concepts of Flask
- Postgres is installed on your local machine
Creating a Database and User
Now we set up PostgreSQL by creating a database with a user, and it is granted all required privileges on the created database.
# create a database named demo CREATE DATABASE demo; # created a user demouser with password 12345678 CREATE USER demouser WITH PASSWORD '12345678'; # configured client encoding to utf8 ALTER ROLE demouser SET client_encoding TO 'utf8'; ALTER ROLE demouser SET default_transaction_isolation TO 'read committed'; ALTER ROLE demouser SET timezone TO 'UTC'; # grant all required privileges to demouser over demo db GRANT ALL PRIVILEGES ON DATABASE demo TO demouser;
Database creation
Setup a Development Environment
Let’s configure the virtual environment for development, this step can be skipped but it is always recommended to use a dedicated development environment for each project to avoid dependency clash, this can be achieved using a Python virtual environment.
mkdir gfg # Move to gfg folder cd gfg
directory screenshot
I created a dedicated folder for the project, you can name it anything you want and cd (change directory) to go into your newly created directory then run the following command that will create a virtual environment for your project.
python -m venv venv
virtual environment creation.
Now to use the virtual environment we need to first activate it, this can be done by executing the activated binary file.
.\venv\Scripts\activate # for Windows source venv/bin/activate # for Linux
using virtual environment
Installing Dependencies for the Project
As the development environment is configured we can install all the required tools. Following command installs Flask, psycopg2 adapter for Postgres.
pip install Flask psycopg2-binary
install necessary libraries output
You now have the required packages installed on your virtual environment. Next, you’ll connect to and set up your database.
Setup a Database for Flask
In this step, we are going to connect to the `demo` database that we created earlier and create a user table in which user data will be inserted using a Flask App.
To create the user’s table in the demo database first we need to connect to the demo database by using the following command.
postgres=# \c demo
using demo database in postgres
Now that we are connected to the demo database Here is how we create a table called users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(120) UNIQUE NOT NULL
);
Creating Table in Postgres
By creating a users table we are done with setting up the database for the Flask App.
Creating a Flask App
We are done with the database setup now we will create a Flask app and connect it to the database we created and insert some user data into it. First, let’s create a Flask app as follows.
create a main.py file in the project directory and add the following code into the file.
Python3
from flask import Flaskapp = Flask(__name__)@app.route('/')def hello_world(): return 'Hello, World!'if __name__ == '__main__': app.run() |
I have created a starter HelloWorld Flask App with just a single route let’s test this out by running then we will connect it to the database.
Flask app terminal output
Output:
Flask app output
Connecting Flask App to Database
We have got a running starter Flask App with some basic code let’s connect it to the database using the following script.
Python3
# Connect to the databaseconn = psycopg2.connect( host="localhost", database="your_database_name", user="your_database_username", password="your_database_password") |
The above script will connect to the Postgres database now we need a post route to create an entry in the database which can be done as follow.
Python3
@app.route('/create', methods=['POST'])def create(): # Get the username and email from the request body username = request.form.get('username') email = request.form.get('email') # Insert the data into the database cur = conn.cursor() cur.execute( "INSERT INTO users (username, email) VALUES (%s, %s)", (username, email)) conn.commit() return 'User created successfully!' |
Here we are getting the username and email from the request body then using a SQL query string we are inserting the data into the database.
For reference here is the overall code that I used for the demo.
Final Code:
Python3
from flask import Flask, requestimport psycopg2# Create a Flask appapp = Flask(__name__)# Connect to the database using psycopg2 library and the database credentialsconn = psycopg2.connect( host="localhost", database="demo", user="demouser", password="12345678")# Root route@app.route('/', methods=['GET'])def hello_world(): return 'Hello, World!'@app.route('/create', methods=['POST'])def create(): # Get the username and email from the request body username = request.form.get('username') email = request.form.get('email') # Insert the data into the database cur = conn.cursor() cur.execute( "INSERT INTO users (username, email) VALUES (%s, %s)", (username, email)) conn.commit() return 'User created successfully!'if __name__ == '__main__': app.run() |
Sending Data from Flask to PostgreSQL
Now let us test the entire script I wrote and if we can insert some data into the database using it, First run the Flask App shown before then make a POST request to /create a route using a tool like Postman.
postman screenshot
The response above looks fine let’s see if there is any entry in the user’s table or not.
Now go to your PostgreSQL shell and Type
SELECT * FROM users;
postgres output
