在 CGI 環境下使用 Python 讀取 SQLite 資料並輸出 HTML 表格,展示資料庫查詢、Row Factory 字典存取、HTTP 標頭輸出、HTML 轉義防 XSS。
#!/usr/bin/env python3
import sys
import sqlite3 as db
import html
DB_FILE = 'test.db'
def main():
# CGI 必須先輸出 HTTP 標頭
sys.stdout.write("Content-Type: text/html; charset=utf-8\r\n\r\n")
conn = db.connect(DB_FILE)
conn.row_factory = db.Row # 字典式存取
cursor = conn.cursor()
try:
cursor.execute('SELECT * FROM person')
rows = cursor.fetchall()
# 輸出 HTML
sys.stdout.write("\n")
sys.stdout.write("Name Age Address \n")
for row in rows:
# html.escape 防 XSS
name = html.escape(row['name'] or '')
age = html.escape(str(row['age'] or ''))
addr = html.escape(row['address'] or '')
sys.stdout.write(f"{name} {age} {addr} \n")
sys.stdout.write("
")
except Exception as e:
sys.stdout.write(f"Error: {html.escape(str(e))}")
finally:
conn.close()
if __name__ == '__main__':
main()
Content-Type 標頭並以空行結束 (\r\n\r\n),否則瀏覽器顯示原始碼或 500 錯誤。conn.row_factory = db.Row 讓 row['col'] 可用欄位名存取。html.escape() 防止資料中含 `