用 SQLite 進行 SQL 數據庫操作，並與 pandas 整合實現數據的讀取和存儲

SQLite是什麼？

SQL 用於管理和操作關聯式資料庫，是一種強大且靈活的語言，能夠進行數據的查詢、插入、更新和刪除操作

SQLite函數介紹及實作

數學函數

ABS(x)返回 |x|
ROUND(x,y)返回 x 四捨五入到小數點後 y 位
POWER(x,y)返回 x^y
RANDOM()返回隨機整數

實作範例：

import sqlite3 

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute("SELECT ABS(-10)")  # 使用游標執行 返回 ABS(-10) 的值
print(f'ABS(-10): {cursor.fetchone()[0]}') # 獲取查詢結果的第一行，使用 [0] 獲取該行的第一個字段的值

cursor.execute("SELECT ROUND(3.14159, 2)")  # 使用游標執行 返回ROUND(3.14159,2) 的值
print(f'ROUND(3.14159, 2): {cursor.fetchone()[0]}')

cursor.execute("SELECT POWER(2, 3)")  # 使用游標執行 返回 POWER(2, 3) 的值
print(f'POWER(2, 3): {cursor.fetchone()[0]}')

cursor.execute("SELECT RANDOM()")  # 使用游標執行 返回 RANDOM() 的值
print(f'RANDOM(): {cursor.fetchone()[0]}')

cursor.close() #關閉游標
conn.close() # 關閉連接

範例輸出：

字串函數

LENGTH(x)返回字串 x 的長度
UPPER(x)將字串 x 轉換成大寫
LOWER(x)將字串 x 轉換成小寫
SUBSTR(x,y,z)返回字串 x 從位置 y 開始的 z 個字符
TRIM(x)去除字串 x 兩端的空白字符

實作範例：

import sqlite3

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute("SELECT LENGTH('Hello, World!')")  # 返回字串長度
print(f'LENGTH(\'Hello, World!\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT UPPER('sqlite')")  # 將字串轉換成大寫後返回
print(f'UPPER(\'sqlite\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT LOWER('SQLite')")  # 將字串轉換成小寫後返回
print(f'LOWER(\'SQLite\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT SUBSTR('Hello, World!', 8, 5)")  # 返回字串索引 8 開始的五個字符
print(f'SUBSTR(\'Hello, World!\', 8, 5): {cursor.fetchone()[0]}')

cursor.execute("SELECT TRIM('   Hello, World!   ')")  # 返回刪除字串左右空格後的內容
print(f'TRIM(\'   Hello, World!   \'): {cursor.fetchone()[0]}')

cursor.close() #關閉游標
conn.close() # 關閉連接

範例輸出：

日期和時間函數

DATE(x)返回 x 的日期部分 (默認為YYYY-MM-DD)
TIME(x)返回 x 的時間部分 (默認為HH:MM:SS)
DATETIME(x)返回 x 的日期和時間部分 (默認為YYYY-MM-DD HH:MM:SS)
STRFTIME(format, x)返回 x 格式化後的日期時間 (format 是日期時間的格式化字符)

實作範例：

import sqlite3

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('example.db') 
cursor = conn.cursor()

cursor.execute("SELECT DATE('now')")  # 返回當前日期
print(f'DATE(\'now\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT TIME('now')")  # 返回當前時間
print(f'TIME(\'now\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT DATETIME('now')")  # 返回當前日期和時間
print(f'DATETIME(\'now\'): {cursor.fetchone()[0]}')

cursor.execute("SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now')")  # 返回格式化的當前日期和時間
print(f'STRFTIME(\'%Y-%m-%d %H:%M:%S\', \'now\'): {cursor.fetchone()[0]}')

cursor.close() #關閉游標
conn.close() # 關閉連接

範例輸出：

邏輯函數

COALESCE(x,y,...)返回第一個非空值的內容
NULLIF(x,y)如果 x 和 y 相等返回 NULL 否則返回 x

實作範例：

import sqlite3

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 使用邏輯函數
cursor.execute("SELECT COALESCE(NULL, 1, 2)")  # 返回第一個非空值的內容
print(f'COALESCE(NULL, 1, 2): {cursor.fetchone()[0]}')

cursor.execute("SELECT NULLIF(1, 1)")  # 若兩值相等 返回 NULL
print(f'NULLIF(1, 1): {cursor.fetchone()[0]}')

cursor.close() # 關閉游標
conn.close() #關閉連接

範例輸出：

聚合函數

COUNT(x)返回 x 的行數
SUM(x)返回 x 的總和
AVG(x)返回 x 的平均值
MAX(x),MIN(x)返回 x 的最大值/最小值
STDDEV(x)計算一組資料中 x 值的標準差
VARIANCE(x)計算一組資料中 x 值的變異數

實作範例：

import sqlite3

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 建立表格，定義了 id 欄位類型為 integer 並設為主鍵(PRIMARY KEY)
# 定義名為 product 的欄位類型為 text 來儲存產品名稱或敘述
# 定義名為 quantity 的欄位類型為 integer 來儲存銷售數量
# 定義名為 price 的欄位類型為 real 來儲存單價， real 用於儲存浮點數
cursor.execute('''
CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    quantity INTEGER,
    price REAL 
)
''')

# 插入名稱、銷售數量、單價
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product A', 10, 9.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product B', 20, 19.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product C', 5, 14.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product D', 15, 24.99)")

conn.commit() # 提交事務，把以上動作保存到資料庫

cursor.execute("SELECT COUNT(*) FROM sales")  # 計算行數
print(f'COUNT(*): {cursor.fetchone()[0]}')

cursor.execute("SELECT SUM(quantity) FROM sales")  # 計算數量總和
print(f'SUM(quantity): {cursor.fetchone()[0]}')

cursor.execute("SELECT AVG(price) FROM sales")  # 計算平均價格
print(f'AVG(price): {cursor.fetchone()[0]}')

cursor.execute("SELECT MAX(price) FROM sales")  # 計算最高價格
print(f'MAX(price): {cursor.fetchone()[0]}')

cursor.execute("SELECT MIN(price) FROM sales")  # 計算最低價格
print(f'MIN(price): {cursor.fetchone()[0]}')

cursor.close() # 關閉游標
conn.close() # 關閉連接

範例輸出：

SQLite 與 Pandas 整合

建立好 SQLite 資料後，使用 Pandas 讀取資料，在應用 SQLite 的聚合函數整合，將結果儲存到 Pandas DataFrame，保存 DataFrame 到 SQL 資料庫

實作範例：

import sqlite3
import pandas as pd

# 創建並連接到 SQLite 資料庫
conn = sqlite3.connect('ex.db')
cursor = conn.cursor()

# 建立表格，定義 id 欄位類型為 integer 並設為主鍵(PRIMARY KEY)
# 定義 product 欄位類型為 text 來儲存產品名稱或敘述
# 定義 quantity 欄位類型為 integer 來儲存銷售數量
# 定義 price 欄位類型為 real 來儲存單價， real 用於儲存浮點數
cursor.execute('''
CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    quantity INTEGER,
    price REAL
)
''')

# 插入名稱、銷售數量、單價
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product A', 10, 9.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product B', 20, 19.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product C', 5, 14.99)")
cursor.execute("INSERT INTO sales (product, quantity, price) VALUES ('Product D', 15, 24.99)")
conn.commit()

# 從 SQLite 讀取插入內容到 Pandas DataFrame
df = pd.read_sql_query("SELECT * FROM sales", conn)
print("原始數據：")
print(df)

# 使用 SQLite 聚合函數
cursor.execute("SELECT SUM(quantity), AVG(price), MAX(price), MIN(price) FROM sales")
result = cursor.fetchone()

print(f"總數量: {result[0]}")
print(f"平均價格: {result[1]}")
print(f"最大價格: {result[2]}")
print(f"最小價格: {result[3]}")

# 轉換結果為 DataFrame
aggregated_df = pd.DataFrame([result], columns=['Total Quantity', 'Average Price', 'Max Price', 'Min Price'])
print("聚合結果：")
print(aggregated_df)

# 將 DataFrame 儲存到 SQLite 資料庫中
aggregated_df.to_sql('aggregated_sales', conn, if_exists='replace', index=False)

# 查看保存的聚合數據
saved_df = pd.read_sql_query("SELECT * FROM aggregated_sales", conn)
print("儲存的聚合數據：")
print(saved_df)

# 關閉連接
conn.close()

範例輸出：

參考資料：
https://faculty.stust.edu.tw/~jehuang/oracle/ch4/4-10.htm

https://learn.microsoft.com/zh-tw/sql/machine-learning/data-exploration/python-dataframe-pandas?view=sql-server-ver16

https://ycjhuo.gitlab.io/blogs/Python-Pandas-Download-Data-From-MSSQL.html#google_vignette