領域展開！用 Google Colab, JupySQL, DuckDB 實戰分析數據

談到 Python 數據分析的工具，多數人應該使用 Jupyter Notebook 搭配 pandas 進行數據分析，不過使用 pandas 還是不及使用 SQL 來得直覺一些，如果能夠在 Jupyter Notebook 中直接使用 SQL 對資料進行查詢、分析，對於精通 SQL 的人而言，將會減少需要額外學習 pandas 的成本。

恰好 JupySQL 是 1 個能夠讓我們在 Jupyter 以 SQL 對資料進行查詢、分析的擴充套件(extension)，而且 JupySQL 也整合 pandas, 因此可以將查詢結果轉換成為 pandas 的 DataFrame, 除此之外更整合 DuckDB, SQLite, MySQL, PostgreSQL 等常見資料庫，讓我們能夠輕鬆以 SQL 操作各種不同來源的資料庫。

本文將以 Google Colab 搭配 JupySQL 與 DuckDB, 帶你學習 Jupyter Notebook + pandas 之外的另一種方法！

本文環境 #

• Google Colab
• JupySQL

請在 Colab / Jupyter Notebook 執行以下指令安裝相關套件：

!pip install jupysql duckdb-engine matplotlib ipywidgets SQLAlchemy

Colab / Jupyter Notebook 會有中文字顯示的問題，在開始之前請在 Colab / Jupyter Notebook 執行以下程式碼設定字體：

!wget -O TaipeiSansTCBeta-Regular.ttf https://drive.google.com/uc?id=1eGAsTN1HBpJAkeVM57_C7ccp7hbgSz3_&export=download

import matplotlib as mpl
import matplotlib.pyplot as plt
from matplotlib.font_manager import fontManager

fontManager.addfont('TaipeiSansTCBeta-Regular.ttf')
mpl.rc('font', family='Taipei Sans TC Beta')

範例資料來源 #

本文使用政府資料開放平臺所提供的個股日成交資訊作為範例資料，該資料含有證券代號、證券名稱、成交股數、成交金額、開盤價、最高價、最低價、收盤價、漲跌價差、成交筆數資訊，可以作為練習使用。

在 Colab / Jupyter Notebook 下載檔案 #

如果想下載個股日成交資訊可以在網頁中以右鍵點選 CSV, 再點選 Copy Link Address :

取得下載連結之後，在 Colab 或 Jupyter Notebook 以 Python 內建 urlretrieve() 函式下載檔案：

from urllib.request import urlretrieve

urlretrieve("<the link address>", "stock.csv")

p.s. 請將 <the link address> 修改為你所複製的網址

上述程式碼執行之後，會多 1 個名稱為 stock.csv 的檔案。

DuckDB 介紹 #

DuckDB 是 in-process 的 OLAP 資料庫解決方案，數據分析人員可以用 DuckDB 載入 CSV, Parquet, Arrow 甚至是 RDBMS 上的資料，並用 SQL 語法進行數據分析。

而所謂的 in-process 是指資料庫不需要在另外的 process 執行，應用程式能直接在自身的 process 內運作資料庫，或稱嵌入資料庫(embedded database)，所以也不需要透過 client-server 的架構進行互動、傳輸資料。

此外， DuckDB 支援 Python, R, Java, Node.js, 更支援 UDF(User Defined Function), 以及 S3, Excel, Full Text Search, Iceberg 等擴充(extensions)，所以分析用途之外，也適合做 ETL(Extract, Transform, Load) 等工作。

藉由 DuckDB 的幫助，我們可以輕鬆地以 SQL 操作 CSV, Parquet, Arrow 等格式的資料，同時我們也不需要額外架設資料庫，一切都能夠在 Colab, Jupyter Notebook 中進行，這也是為什麼本文需要安裝 DuckDB 的緣故。

啟用 JupySQL 擴充並連線至 DuckDB #

一切都就緒之後，就可以在 Colab 或 Jupyter Notebook 輸入以下指令，啟用 JupySQL:

%load_ext sql

接著連線至 DuckDB:

%sql duckdb://

上述 duckdb:// 是指連線至 in-memory 的 DuckDB, 因此 DuckDB 能容納多少資料與可用記憶體大小有關

執行 SELECT 查詢 #

使用 JupySQL 執行 SQL 語句有 2 種 magic commands:

1. %sql
2. %%sql

這 2 種 magic commands 的差別在於 %%sql 支援多行的 SQL 語句，例如：

%%sql
SELECT *
FROM stock.csv
LIMIT 10

而 %sql 只能在後方接受 1 行 SQL 語句：

%sql SELECT * FROM stock.csv LIMIT 10

但 %sql 支援將查詢結果指定給 1 個 Python 變數的用法：

result = %sql SELECT * FROM stock.csv LIMIT 10

查詢前 10 大交易股數的股票 #

知道怎麼使用 %sql 與 %%sql 之後，我們實際以 SQL 查詢前 10 大交易股數的股票試試：

%%sql
SELECT *
FROM stock.csv
ORDER BY "成交股數" DESC
LIMIT 10;

上述 SQL 語句執行結果如下圖，可以看到我們透過 DuckDB 對 stock.csv 進行查詢，並取得成交股數前十大的股票資料，第一名是近期熱門的 00940 元大台灣價值高息 ETF:

除了 SELECT 之外， DuckDB 也支援多種 SQL 語法，可以到官方文件一探究竟。

添加參數到 SQL 語句之中 #

JupySQL 也支援在 SQL 語句中放參數的用法，其參數格式為 {{variable_name}} , 例如我們可以將股票代號做為參數 code 放到 SQL 語句之中， JupySQL 會自動將同名變數放到 SQL 語句之中：

code = "00940"
%sql SELECT * FROM stock.csv WHERE "證券代號" = '{{code}}'

將查詢結果轉為 pandas DataFrame #

前文提到 %sql 支援賦值的用法，所以我們可以用 %sql 將查詢結果指定給 1 個 Python 變數，並轉成各種格式，例如 pandas 的 DataFrame:

result = %sql SELECT "證券代號", "成交股數"/1000 as "成交張數", "漲跌價差" FROM stock.csv ORDER BY "成交股數" DESC LIMIT 10

上述 SQL 語句只查詢成交股數最高的前十大的證券代號、成交張數（成交股數/1,000)、漲跌價差 3 個欄位，並將查詢結果存到 result 之中。

result 是 1 個 sql.run.resultset.ResultSet ，支援將查詢結果轉為以下格式的用法：

1. CSV
2. pandas DataFrame
3. polars DataFrame
4. dict / dicts

以下示範將查詢結果轉為 pandas 的 DataFrame:

df = result.DataFrame()

接著就可以用 pandas 的方法操作 df 囉！

繪圖(Plotting) #

JupySQL 也支援以 magic command %sqlplot 繪圖的功能，它其實是 matplotlib 的再包裝，不過有 JupySQL 的計算是在 SQL engine 之中，得益於各家資料庫對於記憶體管理的功能，因此在記憶體的消耗上會比起單純用 pandas, seaborn 來得好一些，不過在樣式、調整的彈性上目前仍嫌不足，所以個人認為還是可以善用 pandas 原本就有的繪圖功能。

例如，我們將查詢結果轉為 pandas DataFrame 之後，就能呼叫 DataFrame 的 plot() 方法將圖畫出來：

result = %sql SELECT "證券代號", "成交股數"/1000 as "成交張數", "漲跌價差" FROM stock.csv ORDER BY "成交股數" DESC LIMIT 10

df = result.DataFrame()

color = ["r" if x > 0 else "g" for x in df["漲跌價差"]]

df.plot(
    kind="bar",
    title="成交股數",
    x="證券代號",
    y="成交張數",
    grid=True,
    logy=True,
    color=color
)

上述先走訪每 1 筆 df["漲跌價差"] ，如果漲幅小於 0 就用 r 紅色表示，如果漲幅大於 0 則用 g 綠色表示，接著用 df.plot() 畫出 bar chart, X 軸為證券代號， Y 軸為成交張數，並用 color 參數為每個 bar 指定顏色，以方便讓我們看出漲跌。

執行結果如下圖，可以看到雖然 00940 交易張數相當多，但仍處於跌勢：

連線到 MySQL 或 PostgreSQL #

JupySQL 也支援用 SQLAlchemy 的 engine 與資料庫進行連線，只要提供合適的 connection string 給 SQLAlchemy 的 create_engine() 函式，並搭配 %sql 即可連線到 MySQL 或者 PostgreSQL 等資料庫：

from sqlalchemy import create_engine

connection_string = f"mysql+mysqldb://user:password@localhost/db"
engine = create_engine(connection_string)
%sql engine

此外，資料庫由於保護資訊安全的緣故，都會特別設定帳號密碼，不過將密碼明文寫在 Colab 或 Jupyter Notebook 上是相當危險的作法，因此可以使用 Python 的 getpass 模組 輸入密碼，避免將密碼顯示在 Colab, Jupyter Notebook 上，改良之後的作法如下：

from getpass import getpass
from sqlalchemy import create_engine

password = getpass()
connection_string = f"mysql+mysqldb://user:{password}@localhost/db"
engine = create_engine(connection_string)

%sql engine

連線成功之後，就可以用 SQL 與 MySQL 或 PostgreSQL 等資料庫進行互動囉～

總結 #

在 Google Colab 或 Jupyter Notebook 使用 JupySQL 擴充後，能以 SQL 與資料庫進行互動是相當棒的一件事，甚至我們可以將 Jupyter Notebook 當作 Database client 使用，而且搭配 DuckDB 的話，還可以直接在 Google Colab 或 Jupyter Notebook 中對 RDBMS 以外的資料來源進行數據分析、製作視覺化圖表，真的相當方便！推薦你也試試！

以上！

Enjoy!

References #

https://jupysql.ploomber.io/en/latest/quick-start.html

https://colab.research.google.com/github/willismax/matplotlib_show_chinese_in_colab/blob/master/matplotlib_show_chinese_in_colab.ipynb#scrollTo=RfR0uymWF3cB