解決問題 PostgreSQL (1)

教學目標

主要解決如何在 Python 中使用 PostgreSQL 資料庫的問題。

重點概念

首先 PostgreSQL 主要是強大的開發源碼物件關係資料庫系統,它已經有 15 年以上的積極發展和經過驗證的架構,在可靠性、資料完整性和正確性方面皆有很不錯的評價。同時它完全符合 ACID 原則,完全支持 Foreign Keys、Joins、Views、Triggers 、Stored Procdures、:等,以及包括大多數 SQL 的資料類型,像是 INTEGER、NUMERIC、BOOLEAN、CHAR、VARCHAR、DATE、INTERVAL、TIMESTAMP、…等,當然也支援以二進位的方式儲存龐大物件,包括圖片、聲音或視頻。

接著 PostgreSQL 做為一個企業級資料庫,它主要擁有複雜的功能,像是多版本平行控管、時間恢復點、資料表空間、非同步複製、巢狀交易、線上熱備份、複雜的查詢規劃和優化器、寫入前記錄容錯、… 等。同時它也支援 UNICODE 國際字元集編碼,以及具備高度可擴展性能夠有效管理資料量和同時在線使用者量,但還是會有限制,請參考下表。

限制 最大值
資料庫大小 無限制
資料表大小 32 TB
資料列大小 1.6 TB
資料欄大小 1 GB
每個資料表的列數 無限制
每個資料表的欄數 250 ~ 1600 (取決於欄位類型)
每個資料表的索引數 無限制

再來 PostgreSQL 符合 ANSI SQL:2008 標準,它完全支援子查詢,讀取提交和可序列化的交易隔離層級,同時 PostegreSQL 有一個完整關係的系統目錄支援每個資料庫的多個模式,以及目錄也可以透過 SQL 標準中定義的資訊綱要進行存取。此外它針對資料完整性主要包括 Primary Keys、Foreign Keys、Check Contraints、Unique Contraints、Not Null Contraints、…等,當然它除了能夠有效確保資料完整性之外,還具備大量擴展和進階的功能,像是 LIMIT 和 OFFSET 允許回傳部份結果集,它支援複合、唯一、部份、功能索引以及任何 B-tree、R-tree、hash 或 GiST 儲存方法加速存取速度。

最後 PostgreSQL 支援許多程式語言,像是 ODBC、Java (JDBC)、Python、C++、…等,本篇主要介紹如何在 Python 程式語言透過 psycopg2 模組操作 PostgreSQL 資料庫,主要有幾個步驟,分別為:

  1. 安裝 psycopg2 模組。
  2. 載入 psycopg2 模組。
  3. 建立 PostgreSQL 資料庫連線。
  4. 針對 PostgreSQL 資料庫執行 SQL 指令
  5. 關閉 PostgreSQL 資料庫連線

安裝 psycopg2 模組

1
$ yum install python-psycopg2

載入 psycopg2 模組

1
2
# 載入 psycopg2 模組
import psycopg2

建立 PostgreSQL 資料庫連線

1
2
# 連線至存在的資料庫
conn = psycopg2.connect(database="testdb", user="postgres", password="xxx", host="127.0.0.1", port="5432")

針對 PostgreSQL 資料庫執行 SQL 指令

1
2
3
4
5
6
# 取得指標執行資料庫操作
cur = conn.cursor()
# 執行 SQL 指令
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
# 提交目前的交易至資料庫中進行修改
conn.commit()

關閉 PostgreSQL 資料庫連線

1
2
# 關閉  PostgreSQL 資料庫連線
conn.close()

總結我們透過 psycopg2 模組就能夠解決透過 Python 操作 PostgreSQL 資料庫的問題,至於資料庫最基本的 CRUD 操作,僅需更改 SQL 指令為 INSERT、SELECT、UPDATE和 DELETE 即可,其中 SELECT 比較特別需要將「conn.commit()」換成「rows = cur.fetchall()」才能夠讀取 SELECT 指令回傳的全部內容。

相關資源