Python Flask CRUD sample with PostgreSQL database

  • A CRUD sample implemented using Python Flask and PostgreSQL database. All the CRUD operations are explained in detail with code samples.

    To connect to PostgreSQL DB psycopg2 library is used. Complete the step by step tutorial to get familiarized with python web application development and PostgreSQL database integration.

  • Python Initial Setup

    Download and install the latest version of python from Python Website if not already done.

    Keep your favourite Python editor ready. This can be a basic notepad application available by default for your OS or you can download and install any free editor like VS Code.

    Setting up virtual environment is not a must, but its is good to keep all your projects within a virtual environment. Here, we will proceed with a virtual environment.

    For that, go to the command prompt and cd to the folder where you are going to keep your project.
    Run command python -m venv CarSales to create a project folder CarSales and a virtual environment for it.
    Activate the VE using command:

    For Windows:
    CarSales\Scripts\activate.bat
    For Linux, macOS:
    source CarSales /bin/activate

    After VE activation, from cmd type cd CarSales to navigate to the project folder.

  • Install psycopg2

    For PostgreSQL connection we are going to use the psycopg2 library. For quick reference on psycopg2 click psycopg2 basics.
    Install this using the command:
    pip install psycopg2

  • To check if psycopg2 installed successfully

    Type pip freeze. If installation successful, we will get below output.

    psycopg2==2.9.2
    
  • Install Flask

    To install flask, run below command. This will install flask and associated libraries.
    pip install flask

    Run pip freeze to check if flask installed successfully. Should give an output like this.

    click==8.0.3
    colorama==0.4.4
    Flask==2.0.2
    itsdangerous==2.0.1
    Jinja2==3.0.3
    MarkupSafe==2.0.1
    psycopg2==2.9.2
    Werkzeug==2.0.2
    
  • Application Initial Setup

    Create a file named carsales.py (you can provide any name) inside CarSales folder and copy below code to it. carsales.py is going to contain your start-up code which is required to run your webapp. Copy below code to the file. From command prompt navigate to CarSales folder and execute command python carsales.py

    from flask import Flask
    
    carsales = Flask(__name__)
    
    if(__name__ == "__main__"):
        carsales.run()
    

    This code imports Flask module and creates an object carsales of Flask class. Last 2 lines serves as the entry point of the app and starts the server. In cmd, we will see the below message.

        * Serving Flask app 'carsales' (lazy loading)
        * Environment: production
        WARNING: This is a development server.
    	Do not use it in a production deployment.
        Use a production WSGI server instead.
        * Debug mode: off
        * Running on http://127.0.0.1:5000/
    	(Press CTRL+C to quit)
    

    Copy the url http://127.0.0.1:5000/ to your browser and run. We will get the Not Found message as below.

    Flask and SQL Server - Not found

    This means that the Flask has handled the server set up and our application is ready. We will now have to set up our html pages and routing to handle page navigations.

  • Setting up default HTML page

    We are going to keep a list page as the first page of the application.
    In Flask, all our HTML should be placed inside a folder named templates and static files like javascript or stylesheet should be placed inside static folder.So, create a folder structure as below within your application folder and add html file carslist.html inside templates folder.

    Flask web app with PostgreSQL - First Page

    Add below code to carslist.html. To handle routing for this page, modify carsales.py as below. This code imports render_template module and sets up the default route.

    In command prompt. click Ctrl + C to stop the application and run command python carsales.py
    This process is to be done each time we make any changes and want to reload the page. Some times, after stopping flask web from command prompt, just reloading the html will not give you the desired output. If any such issues are faced, close the web page and reopen.

  • Database Setup

    Connect to your PostgreSQL instance and create a database carsales. Set up login for the database (Not covered here). Create a table TblCars with below structure and insert below set of data.

    CREATE TABLE TblCars (
        ID int,
        Name varchar(100),
        Year int,
        Price float
    );
    
    INSERT INTO TblCars VALUES (1, 'Toyota Camry', 2018, 2000);
    INSERT INTO TblCars VALUES (2, 'Honda Civic', 2019, 2200);
    INSERT INTO TblCars VALUES (3, 'Chevrolet Silverado', 2017, 1800);
    INSERT INTO TblCars VALUES (4, 'Ford F-150', 2020, 2500);
    INSERT INTO TblCars VALUES (5, 'Nissan Altima', 2021, 3000);
    
  • List

    carslist.html is going to hold the list of cars available for sale. To list the items, add below code in body of html and modify carsales.py main method as below. Also import psycopg2. In python file, we are reading data from SQL Server database carsales using psycopg2 built-in functions as shown below and pass it to html as a parameter. In html, we are looping through the items and list it as table rows.

    For styling we will use Bootstrap 5. Add below 2 lines inside head of html.

    
        <link rel="stylesheet" crossorigin="anonymous"
    	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" >
        
    	<script crossorigin="anonymous"
    	src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" >
    	</script>
    
    

    On rerun, we will get below output window.

    Flask and SQL Server - List Items
  • Create

    Add a new button in the carslist.html above the table.

        <a class="btn btn-primary" href="addcar">Add New Car</a>
    

    Add new html file, addcar.html and place below code. Add below code in caresales.py as the second route below main method.

    Now we have to import request and redirect. For that, change first line as below.

    from flask import Flask, render_template, request, redirect
    

    In the python code, we are defining a new route for addcar. We are using the same method for GET and POST. GET is just rendering addcar.html. In POST, we are reading data from form controls and save data to table and then redirect to list page.

    In HTML page, we are defining a form with 4 text boxes. Each has a name provided. This name is used in the python file while reading data from controls.
    On rerun, we can navigate to Create page as below and add new Car.

    Flask and SQL Server - Add an Item
  • Update

    For update and delete we will add 2 links in the carslist.html page as below.

    Copied
    
    <table class="table">
        <thead>
            <tr>
            <th scope="col">Id</th>
            <th scope="col">Name</th>
            <th scope="col">Year</th>
            <th scope="col">Price</th>
            <th scope="col">Edit</th>
            <th scope="col">Delete</th>
            </tr>
        </thead>
        <tbody>
            {% for car in cars %}
                <tr>
                    <td>{{ car.id }}</th>
                    <td>{{ car.name }}</td>
                    <td>{{ car.year }}</td>
                    <td>{{ car.price }}</td>
                    <td><a href='updatecar/{{ car.id }}'>Edit</a></td>
                    <td><a href='deletecar/{{ car.id }}'>Delete</a></td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
    

    For easier understanding, we are going to keep separate routes for create and update, although we are using the same addcar.html for update also. For update we will provide the route name updatecar.
    In addcar.html, in order to populate the values, we have to use the value attribute and bind the car object values as in html provided below.

    In carsales.py, add new updatecar route. In GET, we are filtering details based on selected id and using fetchall() function, assigning it to a list variable and pass it to the addcar.html page.
    In POST, we are using the update command and commit
    Note: We have to disable Id textbox to not make changes to Id. Here we are not doing it and if Id is changed no update will happen.

    After the above changes, update will work, but create will throw an error. To fix this, change the render_template line of addcar as below.

    return render_template("addcar.html", car = {})
    

    We are binding an empty car object to make it working.

  • Delete

    For delete, we will add a new route deletecar. And just need to provide delete query as below. If needed, a confirm popup also can be added.

    Copied
    
    @carsales.route('/deletecar/<int:id>')
    def deletecar(id):
        conn = connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM TblCars WHERE id = %s", (str(id)))
        conn.commit()
        conn.close()
        return redirect('/')
    
  • Flask CRUD with PostgreSQL - Complete code

    Complete code below.

Absolute Code Works - Python Topics