Create a bulletin board with Heroku, Flask, SQLAlchemy
Introduction
This time, the creation of the bulletin board was described by classifying it into the following six categories. (1) Environment construction (2) Bulletin board with csv (3) Bulletin board with SQL and SQLAlchemy (4) Bulletin board with PostgreSQL and SQLAlchemy (5) Data manipulation using SQLAlchemy (6) Data manipulation using Postgresql
(1) Environment construction
Create a directory test on your desktop. Build a virtual environment in test and start it.
python3 -m venv .
source bin/activate
Install the required framework and web server.
pip install flask
pip install gunicorn
(2) Bulletin board with csv
First, create a bulletin board using csv in the local environment.
① Directory structure
test
├app.py
├articles.csv
├Procfile
├requirements.txt
└templates
├index.html
├layout.html
└index_result.html
② Prepare csv data
Create articles.csv and enter the following data in advance from the viewpoint of comprehensibility.
Marble,Sleepy
White,I'm hungry
Black,Somehow warm
Marble,Poe Poe Poe
Pontan,No toilet paper
Naochin,Chain
③ Create the main app.py
.py:app.py
#coding: utf-8
from flask import Flask,request,render_template
app = Flask(__name__)
@app.route('/')
def bbs():
lines = []
#read the csv file with open
with open('articles.csv',encoding='utf-8') as f:
lines = f.readlines() #readlines returns the contents of csv in list format
#index.Return to html
return render_template('index.html',lines=lines)
#Receive post method
@app.route('/result',methods=['POST'])
def result():
#Get the value of article and name in request
article = request.form['article']
name = request.form['name']
#Write to csv file in overwrite mode
with open('articles.csv','a',encoding='utf-8') as f:
f.write(name + ',' + article + '\n')
#index_result.Return to html
return render_template('index_result.html',article=article,name=name)
if __name__ == '__main__':
app.run(debug=False)
④ Bulletin board body and other templates
.html:index.html
{% extends 'layout.html' %}
{% block content %}
<h1>Nyanko Bulletin Board</h1>
<form action='/result' method='post'>
<label for='name'>Nyanko's name</label>
<input type='text' name='name'>
<p></p>
<label for='article'>Post</label>
<input type='text' name='article'>
<button type='subimit'>Write</button>
</form>
<p></p>
<p></p>
<table border=1>
<tr><th>Nyanko's name</th><th>Posted content</th></tr>
{% for line in lines: %}
<!--Set a variable called column (set is required for jinja2 variable set)-->
<!--Using split,Classify by. split returns a list-->
{% set column = line.rstrip().split(',') %}
<tr><td>{{column[0]}}</td><td>{{column[1]}}</td></tr>
{% endfor %}
</table>
{% endblock %}
.html:layout.html
<!DOCTYPE html>
<html lang='ja'>
<head>
<meta charset='utf-8'>
<title>Nyanko BBS</title>
<style>body{padding:10px;}</style>
</head>
<body>
{% block content %}
{% endblock %}
</body>
</html>
index_result.html
{% extends 'layout.html' %}
{% block content %}
<h1>Nya-I wrote on the bulletin board</h1>
<p>{{name}}{{article}}</p>
<!--in form/Back to-->
<form action='/' method='get'>
<button type='submit'>Return</button>
</form>
{% endblock %}
⑤ Deploy to Heroku
After testing in your local environment, deploy to Heroku. The details of deploying to Heroku are as described in the following article, so I will only use the essence and omit the detailed explanation. How to upload with Heroku, Flask, Python, Git (Part 2) How to upload with Heroku, Flask, Python, Git (3) Log in to Heroku and create an app on Heroku
heroku login
The app name is cat-bbs.
Heroku create cat-bbs
Initialize the directory app,
git init
Link Heroku with your local environment
heroku git:remote -a cat-bbs
Create requirements.txt in the directory app and
pip freeze > requirements.txt
Create a Procfile in the directory app and enter the following. At this time, one blank is required before g, and the app before: app means app of app.py, so be careful (form: app for form.py).
web: gunicorn app:app --log-file -
Add everything,
git add .
This time, commit with the name the-first,
git commit -m'the-first'
Push to Heroku.
git push heroku master
Finally,
heroku open
When I entered the heroku open command, the browser started up and the following was displayed.
If you post the name of Nyanko as "sardines" and the content of the post as "I love fish",
It was properly posted on the bulletin board.
In heroku, the written csv disappears after a certain period of time (30 minutes), so we will start embedding the database.
(3) Bulletin board with SQL and SQLAlchemy
① Directory structure, etc.
test
├app.py
├articles.csv
├Procfile
├requirements.txt
├assets
│ ├init.py
│ ├database.py
│ └models.py
│
└templates
├index.html
├layout.html
└index_result.html
SQLAlchemy is one of the most popular ORMs in Python. First, check the version of sqlite3 (Mac) and install sqlalchemy.
sqlite3 --version
pip install sqlalchemy
Also, from app.py, create init.py in the assets folder as a file required to read database.py and models.py as modules (note that underscores are added).
touch __init__.py
② Initial setting of SQL Alchemy
Create the following two files in the assets folder.
database.py ・ ・ ・ File that defines which database to use, such as sqlite and mysql models.py ・ ・ ・ A file that defines what kind of information is put in the database. First, database.py is as follows.
.py:database.py
#coding: utf-8
#database.py/File that handles initial settings of which database to use, such as sqlite
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime
import os
#data_Named db, database.Where py is (os.path.dirname(__file__)), With an absolute path (os.path.abspath）、data_Save db
database_file = os.path.join(os.path.abspath(os.path.dirname(__file__)),'data.db')
#Using database sqlite (engin)、database_data stored in file_Use db and issue sqlite when running with echo (echo=True)
engine = create_engine('sqlite:///' + database_file,convert_unicode=True,echo=True)
db_session = scoped_session(
sessionmaker(
autocommit = False,
autoflush = False,
bind = engine
)
)
#declarative_Instantiate base
Base = declarative_base()
Base.query = db_session.query_property()
#Function to initialize the database
def init_db():
#Import models in the assets folder
import assets.models
Base.metadata.create_all(bind=engine)
Next, models.py is as follows. Here, the posting date and time are also reflected on the bulletin board.
.py:models.py
#coding: utf-8
from sqlalchemy import Column,Integer,String,Boolean,DateTime,Date,Text
from assets.database import Base
from datetime import datetime as dt
#Database table information
class Data(Base):
#Table name settings,Set to the name data
__tablename__ = "data"
#Set Column information, set unique to False (meaning that the same value is accepted)
#The primary key is required when searching for a row, usually set
id = Column(Integer,primary_key=True)
#name is the poster
name = Column(Text,unique=False)
#article is the posted content
article = Column(Text,unique=False)
#timestamp is the posting date and time
timestamp = Column(DateTime,unique=False)
#initialize
def __init__(self,name=None,article=None,timestamp=None):
self.name = name
self.article = article
self.timestamp = timestamp
③ Modify app.py
The following two are required to create or delete the database, so import them. Import the variable de_session from the database module in the assets folder and the Data class from the models module in the assets folder.
from assets.database import db_session
from assets.models import Data
③-1 Write to database
It is necessary to get the values of article and name from index.html. In addition, it is necessary to acquire the date and time (at the time of writing) at the time of acquisition of each value with today () and assign it to the today variable. Specifically, it is as follows.
article = request.form['article']
name = request.form['name']
today = datetime.datetime.today()
It is necessary to store the above contents in row and write to the database with db_session and de_commit. Specifically, it is as follows.
row = Data(name=name,article=article,timestamp=today)
db_session.add(row)
db_session.commit()
③-2 Reading from database
To read data from the database, you can get it with db_session.query (Data) .all (). For example, if you write the following to retrieve the value in the database,
db_session.query(Data.name,Data.article,Data.timestamp).all()
Output in list format as shown below * (For ease of understanding, it is assumed that several posts are posted on the bulletin board and saved in the database) *
('Mike', 'It's sunny today', datetime.datetime(2020, 3, 13, 0, 7, 4, 828409)),
('White', 'It's raining tomorrow', datetime.datetime(2020, 3, 13, 0, 7, 4, 828409)),
('Black', 'Warm', datetime.datetime(2020, 3, 13, 0, 7, 4, 828409)),
('Pontan', 'Nya Nya Crow is a paper plane', datetime.datetime(2020, 3, 13, 0, 7, 4, 828409)),
('White', 'My back hurts', datetime.datetime(2020, 3, 13, 0, 7, 46, 513144)),
('Mike', 'What is it?', datetime.datetime(2020, 3, 13, 0, 8, 57, 193710)),
('Black', 'Warm', datetime.datetime(2020, 3, 13, 0, 9, 42, 45228)),
('Mike', 'Cloudy today', datetime.datetime(2020, 3, 13, 0, 17, 13, 709028)),
('Boo Taro', 'It's raining all day today', datetime.datetime(2020, 3, 14, 13, 26, 29, 438012)),
It is necessary to process to return the contents of the database read in index.html. Specifically, it is as follows.
data = db_session.query(Data.name,Data.article,Data.timestamp).all()
return render_template('index.html',data=data)
The following is a summary of the modifications made so far for app.py as a whole.
.py:app.py
#coding: utf-8
from flask import Flask,request,render_template
import datetime
#Added when using the database
from assets.database import db_session
from assets.models import Data
app = Flask(__name__)
@app.route('/')
def bbs():
#Read from database
data = db_session.query(Data.name,Data.article,Data.timestamp).all()
#index.Return to html
return render_template('index.html',data=data)
#Receive post method
@app.route('/result',methods=['POST'])
def result():
#Get the value of article and name in request
article = request.form['article']
name = request.form['name']
#Assign the date and time when the post method was received by the today function to the variable
today = datetime.datetime.today()
#index_Write the information from result to the database
row = Data(name=name,article=article,timestamp=today)
db_session.add(row)
db_session.commit()
#index_result.Return to html
return render_template('index_result.html',article=article,name=name)
if __name__ == '__main__':
app.run(debug=False)
③-4 (Reference) Deletion from database
For reference, the deletion from the read database is as follows. Specify the item you want to delete from db_session.query (Data) .all (the first item in the following case) and use de_session.delete
#coding: utf-8
from assets.database import db_session
from assets.models import Data
def csv_sakujo():
data = db_session.query(Data).all()
datum = data[0]
db_session.delete(datum)
db_session.commit()
csv_sakujo()
③-5 (Reference) Write the read database to csv
For reference, the file to write the read database to csv is as follows.
.p:to_csv.py
#coding: utf-8
from assets.database import db_session
from assets.models import Data
#Read data
def csv_kakikomi():
data = db_session.query(Data.name,Data.article,Data.timestamp).all()
print(data)
#Write to csv file in write mode#
with open('articles2.csv','w',encoding='utf-8') as f:
for i in data:
f.write(str(i[0])+',')
f.write(str(i[1])+',')
f.write(str(i[2])+',' + '\n')
csv_kakikomi()
④ Modify index_html.py
Display the value of data sent from app.py. Of the data values, data [2] is the current date and time, but since there is a None value before posting, it is set so that an error does not occur in the if statement. After posting, the datatime type is converted to the str type using strftime and then displayed.
.html:index.html
{% extends 'layout.html' %}
{% block content %}
<h1>Nyanko Bulletin Board</h1>
<form action='/result' method='post'>
<label for='name'>Nyanko's name</label>
<input type='text' name='name'>
<p></p>
<label for='article'>Post</label>
<input type='text' name='article'>
<button type='subimit'>Write</button>
</form>
<p></p>
<p></p>
<table border=1>
<tr>
<th>Nyanko's name</th>
<th>Posted content</th>
<th>Post date and time</th>
</tr>
{% for datum in data %}
<tr>
<td>{{datum[0]}}</td>
<td>{{datum[1]}}</td>
{% if datum[2] == None %}
<td>{{datum[2]}}</td>
{% else %}
<td>{{datum[2].strftime('%Y year%m month%d day/%H o'clock%M minutes%S seconds')}}</td>
{% endif %}
</tr>
{% endfor %}
</table>
{% endblock %}
So far, try once to see if it works normally in the local environment.
After confirming that it works fine, next deploy to Heoku and use Heroku's PostgreSQL.
(4) Bulletin board with PostgreSQL and SQLAlchemy
Deploy to Heroku and use PostgreSQL.
① Environmental preparation
Install postgresql using brew.
brew install postgresql
Next, install psycopg2-binary as a driver for python to use postgresql. If you install psycopg2 as it is, an error will occur for some reason, so install psycopg2-binary (cause unknown).
pip install psycopg2-binary
Next, modify database.py, but describe the process to go to the environment variable on Heroku called environ and get the database called DATABASE_URL. The URL of the connection destination is set in environ. Also, by adding or, sqlite is referenced as a database in the local environment. If you are connected to heroku, refer to the postgresql url, and if you are not connected, go to sql. Specifically, it is as follows.
engine = create_engine(os.environ.get('DATABASE_URL') or 'sqlite:///' + database_file,convert_unicode=True,echo=True)
The entire modified app.py is as follows
.py:database.py
#coding: utf-8
#database.py/File that handles initial settings of which database to use, such as sqlite
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime
import os
database_file = os.path.join(os.path.abspath(os.path.dirname(__file__)),'data.db')
engine = create_engine(os.environ.get('DATABASE_URL') or 'sqlite:///' + database_file,convert_unicode=True,echo=True)
db_session = scoped_session(
sessionmaker(
autocommit = False,
autoflush = False,
bind = engine
)
)
#declarative_Instantiate base
Base = declarative_base()
Base.query = db_session.query_property()
#Initialize the database
def init_db():
#Import models in the assets folder
import assets.models
Base.metadata.create_all(bind=engine)
② Deploy to Heroku
Deploy to Heroku.
heroku login
Link Heroku with your local environment
heroku git:remote -a cat-bbs
Create requirements.txt in the directory app again. (Since psycopg2-binary was installed, it needs to be created again.)
pip freeze > requirements.txt
Since the Prockfile has already been created, I will not touch it this time.
Add everything,
git add .
This time, commit with the name the-second,
git commit -m'the-second'
Push to Heroku.
git push heroku master
~~ Finally heroku open ~~
Initialize the database before deploying to heroku. Start python on heroku (python mode).
heroku run python
Initialize the database. Write the following in python mode.
from assets.database import init_db
init_db()
Exit python mode, restart heroku and open it.
heroku restart
heroku open
Confirm the following with a browser and succeed.
(5) Data manipulation using SQLAlchemy
As an example, try deleting the top item in the database ("black"). Launch Heroku in python mode.
heroku run python
Write the following in python mode
from assets.database import db_session
from assets.models import Data
data = db_session.query(Data).all()
datum = data[0]
db_session.delete(datum)
db_session.commit()
As, when you open heroku and check it with a browser,
The top "black" has been removed.
(Don't forget to exit python mode)
(6) Data manipulation using Postgresql
After installing PostgreSQL, you can use the heroku pg command to work with Heroku Postgres. For example, if you enter the following, you can check the status of the installed Heroku Postgres as follows.
heroku pg:info
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
Connections: 2/20
PG Version: 12.2
Created: 2020-03-14 04:53 UTC
Data Size: 8.2 MB
Tables: 1
Rows: 3/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
hobby-dev plan(Free tier)And Status is Available(Effectiveness) 。
You can connect to Heroku Postgres by typing:
heroku pg:psql
Use PostgreSQL commands after connecting For example, try deleting the top item ("test", "poop").
Table list display command
\dt;
Command to list the data in the table
select *from data (table name);
The following is output.
cat-bbs::DATABASE=> select * from data;
id | name | article | timestamp
----+------------+--------------------+----------------------------
3 |test|Poop| 2020-03-14 05:59:38.062361
4 |Pooh|None| 2020-03-14 15:14:12.453124
5 |Machaaki|What about it| 2020-03-14 15:14:12.453124
6 |Doburock|So| 2020-03-14 15:14:12.635542
7 |strange|None| 2020-03-14 15:14:12.635542
8 |Oh|That's right| 2020-03-14 15:14:12.453124
9 |New|Moon| 2020-03-14 15:32:49.082485
10 |Girls|High| 2020-03-14 15:59:30.175208
11 |Really|Consultation| 2020-03-14 15:59:47.029891
12 |e?|Fondo| 2020-03-14 16:15:58.35794
13 |Naoki|test| 2020-03-14 16:24:47.435301
14 |Pochi|Even if it ’s a cat, it ’s a cat.| 2020-03-14 22:52:41.633207
(12 rows)
Next, delete the top item with delete ("test", "poop").
delete from data (table name) where id=3;
Then
cat-bbs::DATABASE=> select * From data;
id | name | article | timestamp
----+------------+--------------------+----------------------------
4 |Pooh|None| 2020-03-14 15:14:12.453124
5 |Machaaki|What about it| 2020-03-14 15:14:12.453124
6 |Doburock|So| 2020-03-14 15:14:12.635542
7 |strange|None| 2020-03-14 15:14:12.635542
8 |Oh|That's right| 2020-03-14 15:14:12.453124
9 |New|Moon| 2020-03-14 15:32:49.082485
10 |Girls|High| 2020-03-14 15:59:30.175208
11 |Really|Consultation| 2020-03-14 15:59:47.029891
12 |e?|Fondo| 2020-03-14 16:15:58.35794
13 |Naoki|test| 2020-03-14 16:24:47.435301
14 |Pochi|Even if it ’s a cat, it ’s a cat.| 2020-03-14 22:52:41.633207
(11 rows)
Deleted.
Even if I check it with a browser, it is deleted properly.
Recommended Posts