[PYTHON] Created a reading record book in conjunction with PostgreSQL in Flask

Introduction

After studying on Saturdays and Sundays, I made a little reading record book. Click here for the screen.

All you have to do is register the books you have read and see the list of registered books.

Source code

The source code is as follows. I will omit the html because it is as you see it.

app.py

from flask import Flask, render_template,url_for,request,redirect
from sqlalchemy import create_engine
import pandas
import psycopg2

'''
Referenced site
https://tanuhack.com/pandas-postgres-readto/

'''

#PostgreSQL connection information
connection_config = {
    'user': 'user',
    'password': 'password',
    'host': 'localhost',
    'port': '5432',
    'database': 'mydb'
}

#DB connection using psycopg2
connection = psycopg2.connect(**connection_config)

#Creating df
df = pandas.read_sql(sql='SELECT * FROM books;', con=connection) 
header = ['id','Book title','Author','Reading date','Evaluation']
record = df.values.tolist() #A list of 2D arrays of all records that do not contain the DataFrame index

app = Flask(__name__)

@app.route('/')
def index():
    #SELECT statement update from DB every time index is read
    df = pandas.read_sql(sql='SELECT * FROM books;', con=connection) 
    record = df.values.tolist()
    return render_template('index.html',  header=header, record=record)

@app.route('/result', methods=['GET','POST'])
def addition():
    if request.method == "POST":
        #Need to reacquire the number of records
        df = pandas.read_sql(sql='SELECT * FROM books;', con=connection) 
        record = df.values.tolist() 

        #Get the value of the INSERT statement based on the contents of POST, id is the current number of records+1
        book_id = len(record)+1
        res1 = request.form['Book title']
        res2 = request.form['Author']
        res3 = request.form['Reading date']
        res4 = request.form['Evaluation']
        dict1={'id':[book_id],'name':[res1],'writer':[res2],'read_day':[res3],'rank':[res4]}

        #SQLAlchemy required to fly to DB
        engine = create_engine('postgresql://{user}:{password}@{host}:{port}/{database}'.format(**connection_config))
        df = pandas.DataFrame(data=dict1)
        df.to_sql('books', con=engine, if_exists='append', index=False)
        return redirect(url_for('index'))

##Magic
if __name__ == "__main__":
    app.run(debug=True)

As an overview, there is a table of books read in PostgreSQL, the contents acquired as a DataFrame by the SELECT statement are listed, and sent to index.html with render_template to display as "books you read". Then, the registration of the book you read is processed as a POST method by entering the information in the input form and pressing the registration button. The id is automatically assigned the latest number, the others are obtained from the input form, and one line of dictionary type data is created. Finally, convert the dictionary type data to DataFrame and skip the INSERT statement to the DB with df.to_sql to add the data.

Task

・ Could not deploy I tried to use heroku, but the deployed one didn't start well and I gave up. The cause is not clear, but I think I should have used virtualenv because it seemed to be affected by what I did in the raw environment.

・ Insufficient function As you can see, it only has the ability to add data. If you want to modify or delete the registration information, you have to go to the DB directly at the command prompt.

For the future

For the time being, I was able to link the DB and the web page (acquisition from the DB and addition to the DB) that I wanted to do at a minimum, so I will give it a point. Next time I haven't decided whether to remake the reading book or make another one, but I would like to use virtualenv and deploy it to heroku little by little.