Flask and PostgreSQL provide a reliable combination for developing database-backed web applications. The application allows users to create, view, update, and delete product records while storing the data in a PostgreSQL database.
- PgAdmin 4 provides a graphical interface for creating, managing, and monitoring PostgreSQL databases.
- psycopg2 is a Python adapter that enables applications to connect to and interact with PostgreSQL databases.
- Ensure that PostgreSQL is installed and running before proceeding with the project setup.
- The Flask application will use psycopg2 to establish a database connection and execute SQL queries for managing data.
Steps to make Flask app using a PostgreSQL Database
Step 1: Open your command line tool and connect with Postgres. After entering the username it will ask you for the password enter it appropriately.
psql -U username
Step 2: Once we successfully connected then create a new database 'flask_db'. We will use it further to create tables in it.
Step 3: Create a folder structure like this.
Step 4: Install Required Dependencies
Run the following command to install Flask and the PostgreSQL adapter required for connecting Python applications to PostgreSQL databases:
pip install flask psycopg2-binary
Step 5: Connect with the database. you will have to use psycopg2 connect to use the commands.
conn = psycopg2.connect(database="flask_db",
user="postgres",
password="root",
host="localhost", port="5432")
cur = conn.cursor()
conn.commit()
cur.close()
conn.close()
Step 6: app.py
This will authenticate the user and allows us to execute queries on it. It creates a cursor on the connection by using cur. execute() method executes the queries for us. Once we completed our queries we have to close the connection for security purposes.
from flask import Flask, render_template, request, redirect, url_for
import psycopg2
app = Flask(__name__)
# Connect to the database
conn = psycopg2.connect(database="flask_db", user="postgres",
password="root", host="localhost", port="5432")
# create a cursor
cur = conn.cursor()
# if you already have any table or not id doesnt matter this
# will create a products table for you.
cur.execute(
'''CREATE TABLE IF NOT EXISTS products (id serial \
PRIMARY KEY, name varchar(100), price float);''')
# commit the changes
conn.commit()
# close the cursor and connection
cur.close()
conn.close()
@app.route('/')
def index():
# Connect to the database
conn = psycopg2.connect(database="flask_db",
user="postgres",
password="root",
host="localhost", port="5432")
# create a cursor
cur = conn.cursor()
# Select all products from the table
cur.execute('''SELECT * FROM products''')
# Fetch the data
data = cur.fetchall()
# close the cursor and connection
cur.close()
conn.close()
return render_template('index.html', data=data)
@app.route('/create', methods=['POST'])
def create():
conn = psycopg2.connect(database="flask_db",
user="postgres",
password="root",
host="localhost", port="5432")
cur = conn.cursor()
# Get the data from the form
name = request.form['name']
price = request.form['price']
# Insert the data into the table
cur.execute(
'''INSERT INTO products \
(name, price) VALUES (%s, %s)''',
(name, price))
# commit the changes
conn.commit()
# close the cursor and connection
cur.close()
conn.close()
return redirect(url_for('index'))
@app.route('/update', methods=['POST'])
def update():
conn = psycopg2.connect(database="flask_db",
user="postgres",
password="root",
host="localhost", port="5432")
cur = conn.cursor()
name = request.form['name']
price = request.form['price']
id = request.form['id']
cur.execute(
'''UPDATE products SET name=%s,
price=%s WHERE id=%s''',
(name, price, id))
conn.commit()
cur.close()
conn.close()
return redirect(url_for('index'))
@app.route('/delete', methods=['POST'])
def delete():
conn = psycopg2.connect
(database="flask_db", user="postgres",
password="root",
host="localhost", port="5432")
cur = conn.cursor()
# Get the data from the form
id = request.form['id']
# Delete the data from the table
cur.execute('''DELETE FROM products WHERE id=%s''', (id,))
# commit the changes
conn.commit()
# close the cursor and connection
cur.close()
conn.close()
return redirect(url_for('index'))
if __name__ == '__main__':
app.run(debug=True)
Explanation:
- The index() route retrieves all product records from the database and passes them to the HTML template for display.
- The create() route receives product details from the form and inserts a new record into the products table.
- The update() route retrieves the product ID, name, and price from the form and updates the corresponding database record.
- The delete() route removes a product from the database using its unique ID.
- Execute Database Operations: SQL queries are executed using a PostgreSQL cursor, and changes are saved using commit().
Step 7: templates/index.html
You will need this index file to see results and perform operations. Just add these template codes to their respective path.
<html>
<head>
<title>CRUD App</title>
</head>
<body>
<h1>CRUD App</h1>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Price</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
{% for row in data %}
<tr>
<td>{{ row[0] }}</td>
<td>{{ row[1] }}</td>
<td>{{ row[2] }}</td>
<td>
<form action="/update" method="post">
<input type="hidden" name="id" value="{{ row[0] }}">
<input type="text" name="name" value="{{ row[1] }}">
<input type="text" name="price" value="{{ row[2] }}">
<input type="submit" value="Update">
</form>
<form action="/delete" method="post">
<input type="hidden" name="id" value="{{ row[0] }}">
<input type="submit" value="Delete">
</form>
</td>
</tr>
{% endfor %}
</tbody>
</table>
<h2>Create a new product</h2>
<form action="/create" method="post">
<input type="text" name="name" placeholder="Name">
<input type="text" name="price" placeholder="Price">
<input type="submit" value="Create">
</form>
</body>
</html>
Step 8: Run our app
Enter this command to run project in the development mode and make sure the flask debug is on.
flask run
Through this, we have successfully executed Crud operations on the database. You will have the output attached to this and a GitHub repository link where you can find all the source code.
Output:
