關聯式資料庫經 SQLAlchemy Engine 安全傳輸並轉成 pandas DataFrame 的流程

Python pandas+SQLAlchemy 教學:資料庫查詢與 DataFrame 寫入

抓回來的資料如果只放在記憶體或 CSV,資料量一大就不容易查詢與維護。這篇保留原本「用 SQLAlchemy 連接資料庫,再把查詢結果放進 pandas DataFrame」的核心,並把舊範例更新為 SQLAlchemy 2.x 的連線、參數化 SQL、交易與 pandas 讀寫方式。

本文會完成:建立 SQLite 資料庫、用 text() 執行參數化查詢、用 read_sql_query() 取得 DataFrame,再用 to_sql() 將處理後的資料寫回資料表。

pandas 與 SQLAlchemy 各自負責什麼?

工具 主要用途 本篇使用方式
SQLAlchemy 建立 Engine、管理連線與交易,支援不同資料庫方言與驅動 create_engine()、text()、engine.connect()、engine.begin()
pandas 將查詢結果轉成 DataFrame,進行清理、分析與批次寫入 read_sql_query()、DataFrame.to_sql()

SQLAlchemy 不只提供 ORM,也能直接用 Core 方式執行 SQL。本篇延續原文的簡單查詢情境,不需要先建立 ORM Model。

安裝 SQLAlchemy、pandas 與資料庫驅動

如果先用 SQLite 練習,只要安裝 SQLAlchemy 與 pandas;Python 內建 SQLite 驅動,不需另外安裝。

python -m pip install --upgrade SQLAlchemy pandas

連接 MySQL、PostgreSQL 或 SQL Server 時,還需要相對應的 DBAPI 驅動。請依實際資料庫選一種,不必全部安裝。

資料庫 常見驅動 URL 範例
SQLite 記憶體 Python 內建 sqlite:///:memory:
SQLite 檔案 Python 內建 sqlite:///data.db
MySQL/MariaDB PyMySQL mysql+pymysql://user:password@host:3306/dbname
PostgreSQL psycopg2 postgresql+psycopg2://user:password@host:5432/dbname
SQL Server pyodbc 或 pymssql 依驅動與 ODBC Driver 設定連線 URL

例如使用 PyMySQL:

python -m pip install --upgrade pymysql

建立 Engine:連線在第一次使用時才真正發生

create_engine() 會建立 Engine 與連線池設定,但官方文件指出,真正的 DBAPI 連線通常等到第一次呼叫 connect() 或 begin() 才建立,這是延遲初始化。

from sqlalchemy import create_engine

engine = create_engine("sqlite:///data.db", echo=False)

原文使用 echo=True 來列印 SQL,這在開發除錯時很方便;正式環境可能把查詢與參數寫進日誌,通常應保持 False,只在必要時暫時開啟。

不要把正式密碼直接寫進文章、程式碼或 Git。密碼含有 @、/ 等特殊字元時,字串 URL 還需要編碼。SQLAlchemy 官方提供 URL.create(),可以逐欄建立 URL,避免自己處理密碼跳脫。

import os

from sqlalchemy import URL, create_engine

url = URL.create(
"mysql+pymysql",
username=os.environ["DB_USER"],
password=os.environ["DB_PASSWORD"],
host=os.environ.get("DB_HOST", "localhost"),
port=3306,
database=os.environ["DB_NAME"],
)

engine = create_engine(url)

完整 SQLite 範例:建立資料、查詢並轉成 DataFrame

以下延續原文使用 SQLite 示範。寫入操作放在 engine.begin() 區塊內:成功離開區塊會提交,發生例外則會回滾。查詢使用 text() 與命名參數,避免把值直接拼進 SQL。

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///data.db")

with engine.begin() as conn:
conn.execute(text("""
CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
score INTEGER NOT NULL
)
"""))

conn.execute(
text("""
INSERT INTO test (name, score)
VALUES (:name, :score)
"""),
[
{"name": "Amy", "score": 92},
{"name": "Ben", "score": 78},
{"name": "Cindy", "score": 88},
],
)

query = text("""
SELECT id, name, score
FROM test
WHERE score >= :min_score
ORDER BY score DESC
""")

with engine.connect() as conn:
result_df = pd.read_sql_query(
query,
conn,
params={"min_score": 80},
)

print(result_df.head())

這裡直接把 SQLAlchemy 的 text() 物件與 Connection 傳給 pandas.read_sql_query()。pandas 會將結果集轉成 DataFrame,不再需要舊範例的 pd.DataFrame([dict(row) for row in result])。

使用 DataFrame.to_sql() 寫回資料庫

整理完 DataFrame 後,可以用 to_sql() 新建資料表或寫入既有資料表:

new_rows = pd.DataFrame(
[
{"name": "David", "score": 95},
{"name": "Eva", "score": 84},
]
)

with engine.begin() as conn:
new_rows.to_sql(
"test",
conn,
if_exists="append",
index=False,
chunksize=1000,
)
if_exists 行為 注意事項
fail 資料表已存在就產生錯誤 預設值,最保守
append 把資料列新增到既有資料表 欄位型別與名稱要相容,也要考慮重複資料
replace 先刪除資料表,再重建並寫入 會失去原資料、索引、約束與資料庫端設定,不應隨意使用

index=False 表示不把 DataFrame 索引另存成資料庫欄位;chunksize 可將大量資料分批寫入。不過 to_sql() 不會替應用程式設計主鍵、唯一約束或完整的資料庫遷移,正式系統仍應由資料庫 schema 與 migration 管理。

SQLAlchemy 2.x 與舊範例最大的差異

舊文章寫法 目前建議 原因
conn.execute("SELECT ...") conn.execute(text("SELECT ...")) SQLAlchemy 2.x 執行文字 SQL 時要明確建立可執行的 text() 物件
用字串格式化拼接查詢值 :name 搭配參數字典 讓驅動處理值,降低 SQL Injection 與格式錯誤風險
手動把 Result Row 轉成 dict pd.read_sql_query() pandas 已能直接接收查詢與 SQLAlchemy connectable
寫入後未明確提交 with engine.begin() 成功時提交、例外時回滾,交易邊界更清楚

常見問題

為什麼出現 ObjectNotExecutableError?

常見原因是把普通 SQL 字串直接傳給 SQLAlchemy 2.x 的 Connection.execute()。請使用 text("SELECT ...");若目的是取得 DataFrame,也可以直接使用 pd.read_sql_query()。

為什麼顯示找不到 pymysql、psycopg2 或 pyodbc?

SQLAlchemy 提供資料庫方言與連線管理,但部分 DBAPI 驅動要另外安裝。連線 URL 中的驅動名稱也必須與已安裝套件一致。

SQLite 的三個斜線代表什麼?

sqlite:///data.db 表示相對路徑資料庫檔案;實際位置取決於程式執行時的工作目錄。若不確定檔案在哪裡,可以先用 pathlib.Path.cwd() 檢查目前路徑。

to_sql() 可以自動避免重複資料嗎?

if_exists="append" 只負責追加,不會自動判斷業務上的重複資料。應在資料庫建立主鍵或唯一約束,或在寫入前依唯一欄位去重。

查詢大量資料時會不會占滿記憶體?

read_sql_query() 支援 chunksize,設定後會回傳可逐批處理的 iterator。大量資料不一定要一次載入完整 DataFrame。

延伸閱讀

要整理 SQLite 檔案的位置與備份,可參考 Python 檔案操作教學;需要更多 DataFrame 範例,可閱讀 Python 抓股票資料與 DataFrame 教學;SQL 已經能執行但速度不理想時,可接著看 SQL IN、EXISTS、JOIN 效能與改寫指南。

結論

原文用 SQLAlchemy 連線 SQLite、MySQL 與 SQL Server,再把查詢結果放進 pandas 的方向仍然正確。更新後的重點是:用 text() 包裝文字 SQL、用參數傳值、用 engine.begin() 管理寫入交易,並善用 read_sql_query() 與 to_sql() 直接在資料庫和 DataFrame 之間讀寫。

官方參考:SQLAlchemy Engine 與資料庫 URL、SQLAlchemy 交易與 SQL 執行、pandas.read_sql_query、DataFrame.to_sql。

Similar Posts

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *