SQLAlchemy資料函式庫連線安全性最佳實務

在 Python 開發中，資料函式庫連線的安全性至關重要。本文介紹如何避免在程式碼中硬編碼資料函式庫密碼等敏感資訊，並推薦使用 SQLAlchemy 這個 Python ORM 工具簡化資料函式庫操作流程。我們將示範如何使用環境變數或設定檔儲存連線資訊，並透過 psycopg2 建立 PostgreSQL 資料函式庫連線。接著，我們會示範如何使用 SQLAlchemy 執行 SQL 查詢、取得資料函式庫表格名稱，並將查詢結果轉換為 Pandas DataFrame，方便資料處理與分析。同時，文章也涵蓋 SQL 語言的基礎知識，包括與其他程式語言的比較、ANSI 標準與方言、連線標準的演變以及關鍵術語解析，最後也說明瞭 SQL 查詢的基礎與 SELECT 陳述式的應用。

資料函式庫連線的安全性與SQLAlchemy的使用

在進行資料函式庫操作時，安全性是首要考量。直接在程式碼中硬編碼使用者名稱和密碼並不是一個好的做法，因為這可能會將敏感資訊暴露給未授權的存取者。本篇文章將介紹如何在Python中安全地連線資料函式庫，並使用SQLAlchemy來簡化資料函式庫操作。

提升資料函式庫連線的安全性

為了避免在程式碼中直接寫入敏感資訊，可以採取以下幾種方法：

1. 使用環境變數：將敏感資訊（如使用者名稱和密碼）設定為環境變數，在程式碼中參照這些變數。
2. 建立設定檔：將敏感資訊儲存在一個獨立的設定檔中，並在程式碼中讀取這個檔案。
3. 使用SSH金鑰：透過SSH金鑰進行身份驗證，避免使用密碼。

這裡，我們介紹一種簡單的方法，即將敏感資訊儲存在一個獨立的Python檔案中。

建立db_config.py檔案

首先，建立一個名為db_config.py的檔案，並在其中儲存敏感資訊：

# db_config.py
usr = "alice"
pwd = "password"

在主程式中參照db_config.py

接下來，在主程式中參照db_config.py，並使用其中的變數來建立資料函式庫連線：

# main.py
import psycopg2
import db_config

conn = psycopg2.connect(
    host='localhost',
    database='my_new_db',
    user=db_config.usr,
    password=db_config.pwd
)

執行SQL查詢

建立連線後，就可以執行SQL查詢了。以下是一個簡單的例子：

cursor = conn.cursor()
cursor.execute('SELECT * FROM test')
result = cursor.fetchall()
print(result)

將查詢結果儲存為pandas DataFrame

你也可以將查詢結果直接儲存為pandas DataFrame：

import pandas as pd

df = pd.read_sql('SELECT * FROM test', conn)
print(df)

關閉資料函式庫連線

完成資料函式庫操作後，記得關閉連線以釋放資源：

cursor.close()
conn.close()

使用SQLAlchemy簡化資料函式庫操作

SQLAlchemy是一個強大的ORM（Object-Relational Mapping）工具，可以將資料函式庫中的資料對映為Python物件，讓你能夠以更Pythonic的方式操作資料函式庫。

安裝SQLAlchemy

首先，你需要安裝SQLAlchemy：

pip install sqlalchemy

使用SQLAlchemy連線資料函式庫

以下是使用SQLAlchemy連線PostgreSQL資料函式庫的例子：

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://alice:password@localhost:5432/my_new_db')
conn = engine.connect()

執行SQL查詢與取得表格名稱

使用SQLAlchemy，你可以透過Python方法來執行常見的資料函式庫操作，例如取得資料函式庫中的表格名稱：

print(conn.table_names())

相較於直接寫SQL查詢，使用SQLAlchemy可以讓你的程式碼更簡潔易讀。

第三章：SQL 語言基礎

SQL 與其他程式語言的比較

SQL（Structured Query Language）是一種特殊的程式語言，主要用於管理和操作關聯式資料函式庫。與 Python、Java 或 C++ 等通用程式語言不同，SQL 是一種宣告式語言，使用者只需描述想要執行的操作，而不需要指定如何執行這些操作。

宣告式 vs. 指令式程式設計

• 指令式程式設計：在 Python 等語言中，你需要明確告訴電腦如何一步步執行任務。例如，計算一個列表中所有元素的總和：

  calories = [90, 240, 165]
  total = 0
  for c in calories:
      total += c
  print(total)
  

• 宣告式程式設計：在 SQL 中，你只需要描述你想要的結果，系統會自動決定如何執行。例如，計算 workouts 表中 calories 列的總和：

  SELECT SUM(calories)
  FROM workouts;
  

SQL 的擴充功能

雖然 SQL 本質上是一種宣告式語言，但許多資料函式庫系統提供了擴充功能，使其能夠執行更複雜的操作。例如：

• Oracle 的 PL/SQL：一種程式式語言擴充，讓你能夠編寫更複雜的程式邏輯。
• SQL Server 的 T-SQL：提供事務處理和其他擴充功能。

這些擴充功能使得 SQL 能夠支援更廣泛的應用場景，但也意味著不同資料函式庫系統之間的 SQL 語法可能存在差異。

ANSI 標準與 SQL 方言

ANSI SQL

ANSI（American National Standards Institute）負責制定 SQL 的標準。SQL 在 1986 年成為 ANSI 標準，並且每隔幾年會更新一次標準。目前最新的標準是 ANSI SQL2016。

SQL 方言

雖然 ANSI SQL 提供了一個統一的標準，但不同的資料函式庫系統（如 MySQL、Oracle、PostgreSQL、SQL Server）仍然有自己的 SQL 方言。這些方言在某些語法和功能上可能與 ANSI SQL 不完全相容。

在 R 中使用 SQL

連線資料函式庫

要在 R 中使用 SQL，首先需要連線到資料函式庫。不同資料函式庫系統需要使用不同的 R 包。例如：

# 連線 SQLite 資料函式庫
library(DBI)
con <- dbConnect(RSQLite::SQLite(), "my_new_db.db")

# 連線 MySQL 資料函式庫
library(RMySQL)
con <- dbConnect(RMySQL::MySQL(),
                 host = "localhost",
                 dbname = "my_new_db",
                 user = "alice",
                 password = "password")

執行 SQL 查詢

連線成功後，可以使用 dbGetQuery 函式執行 SQL 查詢並將結果儲存為 R 資料框：

df <- dbGetQuery(con, "SELECT * FROM test WHERE id = 2")
print(df)

關閉資料函式庫連線

完成資料函式庫操作後，記得關閉連線以釋放資源：

dbDisconnect(con)

重點回顧

• SQL 的特點：宣告式語言，主要用於關聯式資料函式庫的操作。
• SQL 與其他程式語言的區別：不需要指定如何執行操作，只需描述想要的結果。
• ANSI SQL 和 SQL 方言：ANSI 提供統一標準，但不同資料函式庫系統有自己的擴充和方言。
• 在 R 中使用 SQL：需要先連線資料函式庫，執行查詢後關閉連線。

隨著資料量的增長和資料分析需求的多樣化，掌握 SQL 和其在不同程式語言中的應用將變得越來越重要。未來，我們將進一步探討如何在不同的應用場景中高效使用 SQL。

SQL 語言標準與術語解析

SQL 語言作為資料函式庫操作的標準語言，其語法與規範對於不同資料函式庫系統之間的相容性至關重要。本文將探討 SQL 語言的標準遵循、術語定義以及實際應用中的注意事項。

為何遵循 SQL 標準

大多數基本的 SQL 語法都遵循 ANSI 標準。然而，當使用特定資料函式庫系統（如 Oracle 或 SQL Server）的專有功能時，可能會偏離這些標準。這種偏離可能導致在不同資料函式庫系統之間移植程式碼時出現相容性問題。

例如，以下 Oracle 特有的 SQL 程式碼使用了 DECODE 函式：

-- Oracle 特有的程式碼
SELECT item, DECODE(flag, 0, 'No', 1, 'Yes') AS Yes_or_No
FROM items;

若要使其相容於多種資料函式庫系統，可以改用 ANSI 標準的 CASE 陳述式：

-- 相容於多種資料函式庫系統的程式碼
SELECT item, CASE WHEN flag = 0 THEN 'No' ELSE 'Yes' END AS Yes_or_No
FROM items;

內容解密：

1. DECODE 函式：這是 Oracle 資料函式庫中的一個內建函式，用於根據條件傳回不同的值。但它並非 ANSI 標準，因此在其他資料函式庫系統中不可用。
2. CASE 陳述式：這是 ANSI SQL 中的條件表示式，能夠根據條件傳回不同的結果。它具有更高的可移植性，能在多種資料函式庫系統中執行。

SQL 連線標準的演變

SQL 連線語法隨著時間演進，已形成多個標準。以下是兩個主要的連線語法標準：

-- ANSI-89 風格的連線語法
SELECT c.id, c.name, o.date
FROM customer c, order o
WHERE c.id = o.id;

-- ANSI-92 風格的連線語法（推薦使用）
SELECT c.id, c.name, o.date
FROM customer c INNER JOIN order o
ON c.id = o.id;

在撰寫新的 SQL 程式碼時，建議使用最新的 ANSI 標準（如 ANSI SQL2016）或遵循所用資料函式庫系統的檔案規範。同時，瞭解舊標準有助於維護和管理舊有的程式碼。

內容解密：

1. ANSI-89 與 ANSI-92：這兩者都是 SQL 連線操作的標準。ANSI-89 使用逗號分隔表格並在 WHERE 子句中定義連線條件，而 ANSI-92 引入了顯式的 JOIN 語法，使查詢更具可讀性和可維護性。
2. 顯式與隱式連線：ANSI-92 的顯式連線語法更受歡迎，因為它清晰地區分了連線條件和篩選條件。

SQL 語言中的關鍵術語

理解 SQL 語言中的基本術語有助於更有效地撰寫和最佳化查詢。以下是一些常見的 SQL 術語：

• 關鍵字：SQL 中具有特定含義的保留字，例如 SELECT、FROM、WHERE 等。
• 函式：對資料進行操作的內建函式，例如 COUNT()、YEAR() 等。
• 識別符：使用者定義的資料函式庫物件名稱，例如表格名稱、欄位名稱等。
• 別名：臨時重新命名錶格或欄位，以簡化查詢或提高可讀性。
• 運算元：以關鍵字開始、以分號結束的完整 SQL 陳述式。
• 子句：SQL 陳述式中的特定部分，例如 WHERE 子句、GROUP BY 子句等。

範例分析

考慮以下 SQL 查詢範例：

-- 銷售資料統計查詢
SELECT e.name, COUNT(s.sale_id) AS num_sales
FROM employee e
LEFT JOIN sales s ON e.emp_id = s.emp_id
WHERE YEAR(s.sale_date) = 2021
AND s.closed IS NOT NULL
GROUP BY e.name;

內容解密：

1. SELECT 陳述式：選擇需要檢索的欄位，本例中為員工姓名和銷售數量。
2. COUNT() 函式：計算銷售記錄的數量，用於統計每個員工的銷售業績。
3. LEFT JOIN：將 employee 表格與 sales 表格進行左連線，確保包含所有員工，即使他們沒有銷售記錄。
4. YEAR() 函式：提取銷售日期的年份，用於篩選特定年份的銷售資料。
5. GROUP BY 子句：按照員工姓名分組結果，以便統計每個員工的銷售數量。

SQL 語言基礎與結構

SQL（Structured Query Language）是一種專門用於管理和操作關聯式資料函式庫的語言。它具備豐富的功能，能夠支援資料查詢、資料定義、資料操縱及資料控制等操作。以下將對 SQL 的基本概念、語法結構及相關術語進行詳細介紹。

SQL 陳述式的基本結構

一個典型的 SQL 查詢陳述式由多個子句組成，每個子句負責不同的功能。以下是一個具體的例子：

SELECT e.name, COUNT(s.sale_id) AS num_sales
FROM employee e
LEFT JOIN sales s ON e.emp_id = s.emp_id
WHERE YEAR(s.sale_date) = 2021
AND s.closed IS NOT NULL
GROUP BY e.name;

內容解密：

1. SELECT e.name, COUNT(s.sale_id) AS num_sales：此子句指定了要檢索的欄位，包括員工名稱 e.name 和銷售記錄的數量 COUNT(s.sale_id)。AS num_sales 為計數結果指定了一個別名 num_sales，使結果更易讀。
2. FROM employee e：此子句定義了查詢的主要資料來源，即 employee 表，並為其指定了一個別名 e，方便後續參照。
3. LEFT JOIN sales s ON e.emp_id = s.emp_id：此子句將 employee 表與 sales 表進行左外連線，條件是兩表的 emp_id 相等。左外連線確保了即使某些員工沒有對應的銷售記錄，他們仍會出現在結果中。
4. WHERE YEAR(s.sale_date) = 2021 AND s.closed IS NOT NULL：此子句對查詢結果進行篩選，僅保留銷售日期在 2021 年且 closed 不為空的記錄。YEAR(s.sale_date) 函式提取了銷售日期的年份進行比較，而 IS NOT NULL 檢查 closed 欄位是否包含值。
5. GROUP BY e.name：此子句將結果按照員工名稱進行分組，以便對每個員工的銷售記錄進行匯總統計。

SQL 中的表示式與謂詞

表示式

表示式是一種計算公式，用於生成特定的值。例如：

• COUNT(s.sale_id)：計算銷售記錄的數量。
• s.sale_id + 10：對銷售 ID 進行算術運算。
• CURRENT_DATE：取得目前日期。

謂詞

謂詞是一種邏輯比較，用於評估條件是否成立，其結果可能是 TRUE、FALSE 或 UNKNOWN。在上述範例中：

• YEAR(s.sale_date) = 2021：檢查銷售日期是否屬於 2021 年。
• s.closed IS NOT NULL：檢查 closed 欄位是否包含值。

SQL 中的註解、引號及空白字元

註解

註解是用於解釋程式碼的文字，不會被執行。SQL 支援單行和多行註解：

• 單行註解：-- 這是單行註解
• 多行註解：/* 這是多行註解 */

引號

SQL 中有兩種引號：

• 單引號（’ ‘）：用於表示字串，例如 'Bob'。
• 雙引號（" “）：用於識別符號，例如 "This column"。在某些資料函式庫系統中（如 MySQL），識別符號可以使用反引號（``）。

空白字元

SQL 對空白字元（如空格、製表符和換行符）不敏感，因此可以靈活地格式化程式碼，以提高可讀性。

SQL 的子語言

SQL 可分為五個子語言，每個子語言負責不同的功能：

1. DQL（Data Query Language）：用於查詢資料，主要命令為 SELECT。
2. DDL（Data Definition Language）：用於定義或修改資料函式庫結構，主要命令包括 CREATE、ALTER 和 DROP。
3. DML（Data Manipulation Language）：用於操作資料，主要命令包括 INSERT、UPDATE 和 DELETE。
4. DCL（Data Control Language）：用於控制資料存取許可權，主要命令包括 GRANT 和 REVOKE。
5. TCL（Transaction Control Language）：用於管理交易，主要命令包括 COMMIT 和 ROLLBACK。

SQL 查詢基礎：SELECT 陳述式與資料檢索

SQL 查詢是用於從資料函式庫中檢索資料的基本工具，主要透過 SELECT 陳述式實作。以下將探討 SELECT 陳述式的各個組成部分及其應用。

SELECT 陳述式的基本結構

SELECT 陳述式由六個主要子句組成，分別為：

1. SELECT：指定要檢索的欄位。
2. FROM：指定要檢索的資料表。
3. WHERE：設定檢索條件。
4. GROUP BY：對結果進行分組。
5. HAVING：對分組結果進行篩選。
6. ORDER BY：對結果進行排序。

以下是一個綜合運用上述子句的查詢範例：

SELECT t.name AS tour_name, COUNT(*) AS num_waterfalls
FROM tour t 
LEFT JOIN waterfall w ON t.stop = w.id
WHERE w.open_to_public = 'y'
GROUP BY t.name
HAVING COUNT(*) >= 2
ORDER BY tour_name;

SELECT 子句的詳細解析

選擇特定欄位

在 SELECT 子句中，可以指定要檢索的欄位。例如：

SELECT id, name 
FROM owner;

選擇所有欄位

使用星號（*）可以檢索資料表中的所有欄位。例如：

SELECT * 
FROM owner;

選擇表示式與函式

除了選擇欄位外，SELECT 子句還可以包含表示式和函式。例如，計算人口數量的 90%：

SELECT name, ROUND(population * 0.9, 0) 
FROM county;

亦可使用內建函式，例如取得當前日期：

SELECT CURRENT_DATE;

使用別名（Alias）

別名可用於為欄位或表示式提供臨時名稱，使結果更具可讀性。例如：

SELECT id AS county_id, name, ROUND(population * 0.9, 0) AS estimated_pop 
FROM county;

建議在處理表示式時使用別名，以明確結果欄位的意義。

命名規範與最佳實踐

在命名別名時，建議遵循以下規範：

1. 使用小寫字母。
2. 以底線（_）取代空格。

此規範有助於提升查詢陳述式的可讀性和一致性。