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. 画面.png

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.

Recommended Posts

Created a reading record book in conjunction with PostgreSQL in Flask
Spiral book in Python! Python with a spiral book! (Chapter 14 ~)
I want to transition with a button in flask
"A book that understands Flask from scratch" Reading memo
Try running python in a Django environment created with pipenv
I made a simple book application with python + Flask ~ Introduction ~
Flask-Create a Todo list with CSRF measures in Flask with WTF
Let's make a WEB application for phone book with flask Part 1
A dentist (!?) Created a tongue coating amount judgment app with flask + keras
Let's make a WEB application for phone book with flask Part 2
Let's make a WEB application for phone book with flask Part 3
Let's make a WEB application for phone book with flask Part 4
Create a CSV reader in Flask
Creating a Flask server with Docker
Creating a simple app with flask
[DSU Edition] AtCoder Library reading with a green coder ~ Implementation in Python ~
I got stuck in a flask application redirect with a reverse proxy in between
Create a record with attachments in KINTONE using the Python requests module
Draw a heart in Ruby with PyCall
Make a rare gacha simulator with Flask
Page cache in Python + Flask with Flask-Caching
Created a darts trip with python (news)
Create a simple web app with flask
Launch a Flask app in Python Anywhere
A note on enabling PostgreSQL with Django
Create a web service with Docker + Flask
I created a password tool in Python.
Create a web service in Flask-SQLAlchemy + PostgreSQL
[Python] Created a class to play sin waves in the background with pyaudio