- 增加一個資料表 ads 存放訊息：
C:\workspace\LineBot> heroku login -i
C:\workspace\LineBot> python
>>> import os
>>> import psycopg2
>>> heroku_pgCLI = 'heroku config:get DATABASE_URL -a 你的APP名稱'
>>> DATABASE_URL = os.popen(heroku_pgCLI).read()[:-1]
>>> print(DATABASE_URL)
>>> connection_db=psycopg2.connect(DATABASE_URL, sslmode='require')
>>> cursor = connection_db.cursor()
>>> SQL_cmd = '''CREATE TABLE ads(
... ads_id serial PRIMARY KEY,
... adstitle VARCHAR (150) NOT NULL,
... adscontent TEXT NOT NULL,
... adspicname VARCHAR (150) NOT NULL
... );'''
>>> cursor.execute(SQL_cmd)
>>> connection_db.commit()
>>> cursor.close()
>>> connection_db.close()
>>>
- 修改 app/dataSQL/connectDB.py，寫入一段顯示活動資料的SQL語法：
def showAds():
DATABASE_URL = os.environ['DATABASE_URL']
connection_db = psycopg2.connect(DATABASE_URL,sslmode='require')
cursor = connection_db.cursor()
SQL_cmd = f"""SELECT * FROM ads ORDER by ads_id DESC limit 5;"""
cursor.execute(SQL_cmd)
connection_db.commit()
raws = cursor.fetchall()
data = []
for raw in raws:
data.append(raw)
cursor.close()
connection_db.close()
return data
- 修改 app/router.py 檔案，加入一段引導到活動列表的程式：
# 顯示活動清單列表
@app.route("/showads")
def showads():
adslists = connectDB.showAds()
return render_template('showads.html',menulist=adslists)
- 增加一個顯示活動清單的 html 程式碼 app/templates/showads.html：
{% extends "base.html" %}
{% block title %}大學麵店活動通知系統{% endblock %}
{% block main %}
<!-- 宣告巨集 -->
{% macro show_row(data, tag) -%}
{% for menu in data %}
<div class="row">
{% if menu[4] == 'Action' %}
{% for item in menu %}
<div class="col">
<{{ tag }}>{{ item }}</{{ tag }}>
</div>
{% endfor %}
{% else %}
{% for item in menu %}
<div class="col">
<{{ tag }}>{{ item }}</{{ tag }}>
</div>
{% endfor %}
<div class="col">
<button type="submit" value="{{ menu[0],menu[1] }}" name="deldata">刪除</button>
</div>
{% endif %}
</div>
{% endfor %}
{%- endmacro %}
<!-- 宣告結束 -->
<div class="container my-5 py-5">
<div class="row">
<div class="rol">
<h1>活動列表</h1>
</div>
</div>
<!-- 顯示菜單用 -->
{% set cols = (("項次","活動標題","活動內容","參考圖檔","Action"),)%}
{{ show_row(cols, "h2") }}
<hr class="my-4">
<form action="/delmenu" method="post">
{{ show_row(menulist,"p") }}
</form>
<form action="/addmenu" method="get">
<input type="submit" value="新增活動">
</form>
</div>
{% endblock %}
- 推送上 Heroku 專案！檢視一下成果！
- 修改 app/templates/showads.html 檔案內容，確定新增/刪改的路徑：
{{ show_row(cols, "h2") }}
<hr class="my-4">
<form action="/delads" method="post">
{{ show_row(menulist,"p") }}
</form>
<form action="/addads" method="get">
<input type="submit" value="新增活動">
</form>
- 修改 app/router.py 檔案內容，加上新增與刪除的程式碼：
# 新增活動清單
@app.route("/addads",methods = ['GET','POST'])
def addads():
if request.method == 'POST':
return showads()
else:
return showads()
# 刪除活動清單
@app.route("/delads",methods = ['GET','POST'])
def delads():
if request.method == 'POST':
print(request.form)
data = ""
data1 = ""
for key, value in request.form.items():
data = eval(''.join(value))
data1 = str(data[0])+','+str(data[1])
print(data1)
connectDB.deleteAds(data1)
return showads()
else:
return render_template("showads.html")
- 修改 app/dataSQL/connectDB.py 程式，加入下列程式碼：
(前面略過....)
def deleteAds(text):
DATABASE_URL = os.environ['DATABASE_URL']
connection_db = psycopg2.connect(DATABASE_URL,sslmode='require')
cursor = connection_db.cursor()
data = text.split(',')
SQL_cmd = f"""DELETE FROM ads WHERE ads_id = %s;"""
print(int(data[0]))
cursor.execute(SQL_cmd,[int(data[0])])
connection_db.commit()
cursor.close()
connection_db.close()
return text
- 推送上 Heroku 專案！檢視一下成果！
- 增加一個填寫資料內容的檔案 app/templates/addads.html：
{% extends "base.html" %}
{% block title %}大學麵店活動訊息系統{% endblock %}
{% block main %}
<div class="container">
<div class="row">
<div class="col">
<h1>新增活動訊息</h1>
<form action="/addads" method="post">
<label for="adstitle">訊息標題</label>
<input type="text" id="adstitle" placeholder="請輸入訊息標題" name="adstitle">
<br>
<br>
<label for="adscontent">訊息內容</label>
<input type="text" id="adscontent" placeholder="請輸入訊息內容" name="adscontent">
<br>
<br>
<label for="adspicname">圖片名稱</label>
<input type="text" id="adspicname" placeholder="請輸入圖片名稱" name="adspicname">
<br>
<input type="submit" value="送出">
</form>
</div>
</div>
</div>
{% endblock %}
- 修改檔案 app/timer.py：
(前面略過....)
@scheduleEvent.scheduled_job('interval',minutes=1)
def timed_job():
msg = connectDB.showAds()
print(msg)
line_bot_api.push_message("你的ＩＤ",TextSendMessage(text='test'))
- 修改檔案 app/dataSQL/connectDB.py：
(前面略過....)
def addads(text):
DATABASE_URL = os.environ['DATABASE_URL']
connection_db = psycopg2.connect(DATABASE_URL,sslmode='require')
cursor = connection_db.cursor()
data = text.split(',')
table_columns = '(adsname,adscontent,adspicname)'
SQL_cmd = f"""INSERT INTO menu { table_columns } VALUES(%s,%s,%s);"""
cursor.execute(SQL_cmd,(str(data[0]),str(data[1]),str(data[2])))
connection_db.commit()
cursor.close()
connection_db.close()
return text
- 修改 add/router.py 檔案內容：
(前面略過....)
# 新增活動清單
@app.route("/addads",methods = ['GET','POST'])
def addads():
if request.method == 'POST':
print(request.form)
data = ""
for key,value in request.form.items():
data += value
data += ','
data = data[:-1]
connectDB.addads(data)
return showads()
else:
return render_template("addads.html")
(後面略過....)
- 推送上 Heroku 專案！
