### 1⃣️ 資料庫設計
*簡單版*
```sql
CREATE TABLE IF NOT EXISTS attraction_images (
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(30),
image_url TEXT NULL,
FOREIGN KEY(attraction_id) REFERENCES attractions(id),
FOREIGN KEY(attraction_name) REFERENCES attractions(name)
);
CREATE TABLE IF NOT EXISTS attractions(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) UNIQUE,
CAT VARCHAR(30) NULL,
MRT VARCHAR(30) NULL,
idpt VARCHAR(30) NULL,
address VARCHAR(200) NULL,
longitude FLOAT NULL,
latitude FLOAT NULL,
location POINT,
direction VARCHAR(2000) NULL,
rate INT NULL,
description VARCHAR(2000) NULL,
MEMO_TIME VARCHAR(1000) NULL,
date DATE NULL,
langinfo INT NULL,
REF_WP INT,
RowNumber INT,
POI VARCHAR(10),
avBegin DATE,
avEnd DATE,
SERIAL_NO VARCHAR(16),
old_id INT
);
```
### 複雜版
**主表**
| Id（主）| 景點名|語言 | 之前的 id |
| -------- | -------- | -------- |-------- |
| INT | INT | Text | INT |
**圖片關聯表**：（同簡單版）
| Id（主）| 景點id (外)| 景點名（外）|圖片網址陣列 |
| -------- | -------- | -------- |-|
| INT | INT | Text | |
**🌍🚄 地理關聯表**
| Id（主）| 景點id (外)| 景點名（外）|捷運站 |交通方向| 地址|經度|緯度 |
| -------- | -------- | -------- |-|-|-|-|-|
| INT | INT | Text | |||||
**✏️ 描述關聯表**
| Id（主）| 景點id (外)| 景點名（外）|評分 |描述| 時間備註|種類|來源 |
| -------- | -------- | -------- |-|-|-|-|-|
| INT | INT | Text | |||||
**📅 日期關聯表**
| Id（主）| 景點id (外)| 景點名（外）| 日期 | 開始日 | 結束日 |
| -------- | -------- | -------- |-|-|-|
| INT | INT | Text | |||
**💬 其他關聯表**
| Id（主）| 景點id (外)| 景點名（外）| POI | REF_WP | 序號 SERIAL_NO | 列號|來源 |
| -------- | -------- | -------- |-|-|-|-|-|
| INT | INT | Text | |||||
```sql
CREATE TABLE IF NOT EXISTS attractions(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
langinfo INT NULL,
old_id INT
);
CREATE TABLE IF NOT EXISTS attractions_location(
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(100),
MRT VARCHAR(30) NULL,
direction VARCHAR(2000) NULL,
address VARCHAR(200) NULL,
longitude FLOAT NULL,
latitude FLOAT NULL,
location POINT,
FOREIGN KEY(attraction_id) REFERENCES attractions(id)
);
CREATE TABLE IF NOT EXISTS attractions_description(
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(100),
rate INT NULL,
description VARCHAR(2000) NULL,
MEMO_TIME VARCHAR(1000) NULL,
CAT VARCHAR(30) NULL,
idpt VARCHAR(30) NULL,
FOREIGN KEY(attraction_id) REFERENCES attractions(id)
);
CREATE TABLE IF NOT EXISTS attractions_date(
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(100),
date DATE NULL,
avBegin DATE,
avEnd DATE,
FOREIGN KEY(attraction_id) REFERENCES attractions(id)
);
--
CREATE TABLE IF NOT EXISTS attractions_others(
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(100),
POI VARCHAR(10),
REF_WP INT,
SERIAL_NO VARCHAR(16),
RowNumber INT,
FOREIGN KEY(attraction_id) REFERENCES attractions(id)
);
-- 照片
CREATE TABLE IF NOT EXISTS attraction_images (
id INT AUTO_INCREMENT PRIMARY KEY,
attraction_id INT,
attraction_name VARCHAR(100),
image_url TEXT NULL,
FOREIGN KEY(attraction_id) REFERENCES attractions(id)
);
```
### 2⃣️ 最後ㄧ頁
```python
next_page = page + 1 if len(attractions) == 12 else None
```
隱含了一個錯誤假設，除以 12 除不盡才是最後ㄧ頁，但如果最後ㄧ頁剛好是 12 * N 怎麼辦？
```python
# 個數不能超過總數
is_last_page = (page * 12 + len(attractions)) >= total_count
next_page = page + 1 if not is_last_page else None
```
總數
```python!
query_count = "SELECT COUNT(*) FROM attractions "
if keyword:
query_count += "WHERE name LIKE %s OR mrt = %s "
# 萬用字元的 %{keyword}% 讓名稱能模糊匹配到關鍵字
cursor.execute(query_count, (f"%{keyword}%", keyword))
else:
cursor.execute(query_count)
# 從查詢結果中獲取總數。
# `etchone() 返回一個 tuple，而 [0] 取 tuple 的第一個元素，也就是計數結果
total_count = cursor.fetchone()['COUNT(*)']
```
### 3⃣️ 分析景點
```sql
SELECT mrt, COUNT(*) as count
FROM attractions
-- 排除那些沒有關聯到捷運站的景點
WHERE mrt IS NOT NULL
GROUP BY mrt
-- 根據景點數量進行降序排序(多 ->少)
ORDER BY count DESC
LIMIT 40
```
```python
mrts = [
row['mrt'] for row
in result_raw
]
# 或
mrts = [
row['mrt']
if row['mrt'] is not None
else '未知'
for row in result_raw
]
```
### 4⃣️ 價格
- 實例類型（例如：t2.micro, t3.small 等）
- 運行時間（一個月有大約 720 小時）
- 地理區域（不同區域有不同價格）
- 附加資源（例如：儲存、資料傳輸等）
```python
subquery =
db.session.query(
Attraction.MRT,
func.count(Attraction._id).label('num_around_attractions')
)
.group_by(Attraction.MRT)
.subquery()
mrt_attraction_counts =
db.session.query(
subquery.c.MRT,
subquery.c.num_around_attractions
)
.order_by(subquery.c.num_around_attractions.desc()).all()
```
### ❓ 其他資料庫設計參考
```sql
SELECT
attractions.attraction_id,
attractions.attraction,
attractions.category,
attractions.description,
attractions.address,
attractions.direction,
mrt.mrt_station,
attractions.lat,
attractions.lng,
attractions.id
FROM
mrt
INNER JOIN
attractions
ON
mrt.id = attractions.mrt_id
WHERE
mrt.mrt_station=%s
OR
attractions.attraction LIKE %s
ORDER BY
attractions.id
LIMIT %s,12
```
```sql!
SELECT attraction.*, GROUP_CONCAT(images.images_link) AS images_links
FROM attraction
LEFT JOIN images ON attraction.id = images.attraction_id
WHERE mrt = %s OR name LIKE %s
GROUP BY attraction.id
LIMIT %s, %s
```
```sql
on, cursor = connect_to_database()
cursor.execute("SELECT MRT FROM attraction")
data = cursor.fetchall()
con.close()
data_list=[]
for name in data:
if name[0]!=None:
data_list.append(name[0])
element_count = Counter(data_list)
sorted_elements = sorted(element_count.items(), key=lambda x: x[1], reverse=True)
sort_list=[]
for i in sorted_elements:
sort_list.append(i[0])
response={
"data": sort_list
}
```
Sign in with Wallet
Connect another wallet