Flask PostgreSQL-Insert Data into Table

In this post we will explain how to insert data into a table with Flask postgreSQL application. We will use Insert SQL statement  to do this.

Installing PostgreSQL

To create a Flask PostgreSQL web application you need to install PostgreSQL in your system.  You can click here and download PostgreSQL and install. You need to download a stable version which is compatible with FLASK version you are using to develop application. In the example we have used PostgreSQL (postgresql-10.13-1-windows-x64) with Flask 1.1.2. After installing PostgreSQL you have to set it up by defining a password. This password is required when you will connect the Flask Website with PostgreSQL database.

Create the local Website

Prepare your website folder following the steps in this post.

Installing SQLAlchemy

To install SQLAlchemy in windows, use pip install SQLAlchemy. Open command window using cmd. Open your project folder using CD DOS command and run the pip command.

Create the Database Table

Once you install PostgreSQL you can create tables in two ways- command line or GUI interface launched with pgAdmin available when installed PostgreSQL as shown in the diagram below.

Flask PostgreSQL Admin dashboard

If you have used Microsoft Access, SQL Server, Oracle or MySQL you will be easily able to create the database.

Here you can create a new database for your application. On Databases right click and select Create Database and specify name and other details of your database. Once you create database, open the schemas group and create table there. You can create tables in two ways-

  • Right click on Tables and open table creation dialog box by choosing Create Table
  • Create it by using the Query Tool . Right click on tables and write the create table statement.
CREATE TABLE public.booklist
(
    bookid integer NOT NULL DEFAULT nextval('booklist_bookid_seq'::regclass),
    isbn character varying COLLATE pg_catalog."default" NOT NULL,
    title character varying COLLATE pg_catalog."default" NOT NULL,
    author character varying COLLATE pg_catalog."default" NOT NULL,
    year integer NOT NULL
)

Creating Application.py

Once you have created the database, next step is to set up the database information in Python Flask application.py file so that data can be stored and accessed data from DB tables. 

Include these import statements at the top of this file

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

Set up database engine in application.py with these statements-

engine = create_engine("postgresql://postgres:yourpassword@localhost:5432/yourdatabasename")
db = scoped_session(sessionmaker(bind=engine))

In the first statement the username is postgres. Colon (:) is followed by the password you gave while installing PostgreSQL. After @ you need to specify the server and port. Since you are creating a web application at local server, most of the times the value given here will work. If it doesn’t, check the browser address bar of pgAdmin to get the database server details and change it in application.py. Lastly replace yourdatabasename with the name of the database your have created.

In the application.py file after app = Flask(__name__) statement

app.secret_key = '12345678' ' this key is used to communicate with database.
#Configure session to use filesystem
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"

Create the Data Entry Form

The data entry form is created by using HTML form tags. We have created a table with following fields to store booklist table details.

This form is created (addbook.html)

Data entry form
        <form action="{{ url_for('bookadd')}}" method="post"> 
		<h5>Enter the book Details</h5>
		<div class="form-group">
            <input class="form-control" type="text" name="isbn" placeholder="ISBN">
        </div>
		<div class="form-group">
            <input class="form-control" type="text" name="title" placeholder="Title">
        </div>
		<div class="form-group">
            <input class="form-control" type="text" name="author" placeholder="Author">
        </div>
		<div class="form-group">
            <input class="form-control" type="text" name="year" placeholder="Year">
        </div>
		<div class="form-group">
			<button class="btn btn-primary">Submit</button>
		</div>
		</form>

Add a route in application.py to access this HTML form in browser

@app.route("/addbook")
def addbook():    
    return render_template("addbook.html")

Add route and code to Insert data in Table

The last step is to add the code that saves the data in the booklist table.  INSERT statement is created using the data submitted from the addbook page with POST method.

@app.route("/bookadd", methods=["POST"])
def bookadd():
    isbn=request.form.get("isbn")
    title=request.form.get("title")
    author=request.form.get("author")
    year=request.form.get("year")
    db.execute("INSERT INTO booklist (isbn, title,author, year) VALUES (:isbn, :title,:author,:year)",
            {"isbn": isbn, "title": title, "author":author,"year":year}) 
    db.commit() 
    return render_template("addbook.html")

In the next post you will learn how to display the saved data in a tabular form.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *