# 後端學習紀錄 Backend with Flask - *Website with UI*
###### tags: `backend`
Copyright 2021, [月下麒麟](https://hackmd.io/@YMont/note-catalog)
---
## Target
>該筆記主要目標為使用Web之Python框架**Flask**，
>並結合資料庫框架**Flask-SQLAlchemy**與資料庫**SQLite**，
>另外，會著重在==前端設計==技法應用
## Requirements
```txt
Flask == 2.0.1
Flask-SQLAlchemy == 2.5.1
```
## Architecture
```
|---app
| |---main.py
| |---webflask.db
| |---templates
| |---base.html << point
| |---index.html
| |---query.html << point
| |---view.html
```
## Source Code
**Python**
```python=
from flask import flash
from flask import Flask
from flask import redirect
from flask import render_template
from flask import url_for
from flask import request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import os
import sqlite3
# create an instance in the flask frame
app = Flask(__name__)
@app.route('/')
def index():
return render_template("index.html")
@app.route('/new')
def news():
return render_template("new.html")
@app.route('/query', methods=["POST","GET"])
def querys():
# connect with DB
conn = sqlite3.connect(r'D:\10_database\imdb.db')
cur = conn.cursor()
# operate DB
cur.execute('SELECT DISTINCT director FROM top_rated_movies ORDER BY director ASC')
directors = cur.fetchall()
# operate DB
cur.execute('SELECT DISTINCT release_year FROM top_rated_movies ORDER BY release_year DESC')
release_years = cur.fetchall()
if request.method == 'POST':
conn.close()
return redirect(url_for('view.html'))
conn.close()
return render_template("query.html",
director = directors,
release_year = release_years)
@app.route('/view', methods=["POST","GET"])
def views():
if request.method == 'POST':
request.form.get('director')
request.form.get('release_year')
get_dirs = request.form.get('director')
get_dir_strip = get_dirs.strip('(),\'\'')
conn = sqlite3.connect(r'D:\10_database\imdb.db')
cur = conn.cursor()
# SELECT title FROM top_rated_movies WHERE director = 'Christopher Nolan' ORDER BY director ASC ;
cur.execute("SELECT title FROM top_rated_movies WHERE director = (?) ORDER BY director ASC;", (get_dir_strip,))
#print(get_dirs.strip('(),\'\''))
res_movies = cur.fetchall()
conn.close()
get_years = request.form.get('release_year')
get_year_strip = get_years.strip('(),\'\'')
conn = sqlite3.connect(r'D:\10_database\imdb.db')
cur = conn.cursor()
# SELECT title, release_year FROM top_rated_movies WHERE release_year='1999' ORDER BY title ASC ;
cur.execute("SELECT title, release_year FROM top_rated_movies WHERE release_year=(?) ORDER BY title ASC;", (get_year_strip,))
res_years = cur.fetchall()
conn.close()
return render_template("view.html",
res_director = get_dirs,
res_dir_movie = res_movies,
res_year = get_years,
res_dir_year = res_years )
return render_template("view.html")
@app.route('/add', methods=["POST","GET"])
def adds():
return render_template("add.html")
@app.route('/member')
def members():
return render_template("member.html")
if __name__ == "__main__":
app.run()
```
**HTML**
base.html
```htmlembedded=
<!DOCTYPE html>
<html lang="zh-TW">
<head>
<!-- <link rel="stylesheet" type="text/css" href="static/bar.css"> -->
<title>{% block title %}{% endblock %}</title>
<meta charset="utf-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
</head>
<body>
<div id="title_bar" style="font-size: larger; background-color: goldenrod;">
Welcome to YueLin's movie
</div>
<div id="list_bar">
<nav class="navbar navbar-expand-sm bg-dark navbar-dark"
style="background-color:coral ;">
<ul class="navbar-nav" >
<li class="nav-item">
<a class="nav-link" href="/">首頁</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/new">最新消息</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/query">電影查詢</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/add">電影新增</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/member">會員登入</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/view">查詢結果</a>
</li>
</ul>
</nav>
</div>
<main>
{% block main %}{% endblock %}
</main>
{% block script %}{% endblock %}
</body>
</html>
```
base.html作為所有頁面的母模板
---
index.html
```htmlembedded=
{% extends "base.html" %}
{% block title %}首頁{% endblock %}
{% block main %}
<div>
<img src="https://storage.googleapis.com/afs-prod/media/ed5f8b8cba114c7b84e25e9420cafb6d/3000.jpeg"
width="1400" height="500">
</div>
{% endblock %}
```
套用base.html之後就可以在不同分頁有同樣底頁的效果
(參考網路圖片)
---
query.html
```htmlembedded=
{% extends "base.html" %}
{% block title %}查詢{% endblock %}
{% block main %}
<div>
<form method="POST" action="{{ url_for('views') }}">
<div style="background-color: chocolate;">
<div>
<label>請選擇導演名稱: (e.g. Christopher Nolan)</label>
</div>
<div>
<select name="director">
{% for x in director %}
<option value="{{ x }}">{{ x }}</option>
{% endfor %}
</select>
</div>
</div>
<div style="background-color: darkcyan;">
<div>
<label>請選擇電影年份: (e.g. 1991(含)之後的電影)</label>
</div>
<div>
<select name="release_year">
{% for x in release_year %}
<option value="{{ x }}">{{ x }}</option>
{% endfor %}
</select>
</div>
</div>
<div>
<input type="submit" value="提交">
</div>
</form>
</div>
{% endblock %}
```
---
view.html
```htmlembedded=
{% extends "base.html" %}
{% block title %}視圖{% endblock %}
{% block main %}
<div style="background-color: chocolate;">
<div>
欲查詢的導演名稱:
{{ res_director }}
</div>
<div>
該導演執導的電影:
<br/>
{% for x in res_dir_movie %}
<li>{{ x }}</li>
{% endfor %}
</div>
</div>
<div style="background-color: darkcyan;">
<div>
欲查詢的電影年份:
{{ res_year }}
</div>
<div>
該年份的電影:
<br/>
{% for x in res_dir_year %}
<li>{{ x }}</li>
{% endfor %}
</div>
</div>
{% endblock %}
```
---
## Discuss
.html
**QA \#1**
```htmlembedded=
<div>
<select name="director">
{% for x in director %}
<option value="{{ x }}">{{ x }}</option>
{% endfor %}
</select>
</div>
```
>網路上找到很炫的功能寫法(對大神來說應該在平常不過XD
>可以把選單用templates的做法實作
>對於將DB的資料轉變成選單內容，相當實用
**QA \#2**
.py
```python=
#1
get_years = request.form.get('release_year')
#2
get_year_strip = get_years.strip('(),\'\'')
#3
conn = sqlite3.connect(r'D:\10_database\imdb.db')
#4
cur = conn.cursor()
# SELECT title, release_year FROM top_rated_movies WHERE release_year='1999' ORDER BY title ASC ;
#5
cur.execute("SELECT title, release_year FROM top_rated_movies WHERE release_year=(?) ORDER BY title ASC;", (get_year_strip,))
#6
res_years = cur.fetchall()
#7
conn.close()
```
>此為Python Code，
>#1為取的在query頁面的option資訊選項
>#2因為取得的資料格式不符合SQL語法的string搜尋條件，在此進行處理
>#3連線到DB
>#4連線指標
>#5利用問號，將取得的變數填入，執行SQL語句
>#6取得搜尋資料
>#7結束連線
---
## Summary
這次的練習仍是將前後端放在一起，
但著重在HTML結合==Template==與==Jinja2==的應用，
搭配**base.html**讓後續新增加的頁面模板，可以快速套用底板
因這個為較早時間的自我練習，故截圖沿用他處較模糊，
另外，就不再多著墨程式細節解釋，
如有問題，歡迎留言給我喔~
Sign in with Wallet
Connect another wallet