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。















