- 利用己學過的知識,進行商業化活動!
建立廣告活動訊息資料庫
- 增加一個資料表 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("你的ID",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 專案!