精通 Oracle+Python，第 1 部分：查詢最佳應踐，oraclepython

精通 Oracle+Python，第 1 部分：查詢最佳應踐，oraclepython

原文連結:http://www.oracle.com/technetwork/cn/articles/dsl/mastering-oracle-python-1391323-zhs.html

 

在 Python 做事方式的核心原則中，有一個規定是要求具有到 API 的進階介面。資料庫 API（在此例中為 Oracle API）就是一個例子。使用 Computronix 的 cx_Oracle Python 模組，您可以在維持與 Python 資料庫 API 規範 v2.0 的相容性的同時，控制 Oracle 的查詢模型。

對於所有遵循該規範的用戶端庫而言，使用 DB API 2.0 查詢資料庫的模型都是一致的。在此基礎上，cx_Oracle 的主要開發人員 Anthony Tuininga 添加了一組豐富的屬性和方法，以向開發人員揭示 Oracle 專屬的特性。僅用標準的方法而忘掉“額外的”方法是絕對可能的，但在本文中您不會這麼做。通用資料庫封裝這一概念可能在某些情況下起作用，但與此同時，您會失去 RDBMS 提供的所有最佳化。

DB API 2.0 和 cx_Oracle 介紹

Python 資料庫 API 規範 v2.0 是集體努力的成果，用於統一不同資料庫系統的訪問模型。擁有一組相對較少的方法和屬性，在更換資料庫供應商時就易於學習並保持一致。它不以任何方式將資料庫物件映射到 Python 結構中。使用者仍然需要手工編寫 SQL。在更換到另一資料庫後，此 SQL 可能需要重新編寫。儘管如此，它還是出色妥善地解決了 Python 資料庫的串連性問題。

該規範定義了 API 的各個部分，如模組介面、連線物件、遊標對象、類型對象和構造器、DB API 的可選擴充以及可選的錯誤處理機制。

資料庫和 Python 語言之間的網關是連線物件。它包含製作資料庫驅動的應用程式所需的全部組件，不僅符合 DB API 2.0，而且是規範方法和屬性的一個超集。在多線程的程式中，模組和串連可以在不同線程間共用，但是不支援遊標共用。這一限制通常是可接受的，因為共用遊標可能帶來死結風險。

Python 大量使用了異常模型，DB API 定義了若干標準異常，它們在調試應用程式中的問題時會非常有用。下面是一些標準異常，同時提供了原因類型的簡要說明：

• Warning — 資料在執行插入操作時被截斷，等等
• Error — 這裡提到的除 Warning 外的所有異常的基類。
• InterfaceError — 資料庫介面而非資料庫本身故障（本例為 cx_Oracle 問題）
• DatabaseError — 嚴格意義上的資料庫問題
• DataError — 包含如下結果資料的問題除數為 0，值超出範圍等
• OperationalError — 與編程人員無關的資料庫錯誤：串連丟失、記憶體配置錯誤、交易處理錯誤等
• IntegrityError — 資料庫的關係完整性受到了影響，例如，外鍵約束失敗
• InternalError — 資料庫遇到內部錯誤，例如，遊標無效、事務不同步
• ProgrammingError — 未找到表、SQL 陳述式中的語法錯誤、指定參數的數量錯誤等
• NotSupportedError — 調用的 API 組件並不存在

串連過程首先從連線物件開始，這是建立遊標對象的基礎。除遊標操作外，連線物件還使用 commit() 和 rollback() 方法對事務進行管理。執行 SQL 查詢、發出 DML/DCL 語句和擷取結果這些過程均受遊標控制。

在遊標和串連類的實現中，cx_Oracle 對標準的 DB API 2.0 規範進行了最大程度的擴充。如果需要，所有這些擴充都將在文本中清楚地標記。

入門

在使用查詢和遊標之前，首先需要建立資料庫連接。提供憑證和資料來源名稱的方法有多種，其結果都是相似的。在從下面的 Python 互動式會話提取的內容中，連線物件 db、db1 和 db2 都是等同的。makedsn() 函數根據給定的參數值建立一個 TNS 條目。此處將它賦值給變數 dsn_tns。如果環境設定得當，您可以使用更短的形式 cx_Oracle.connect('hr/hrpwd')，從而省略用於 db 和 db1 的簡單連接字串。

 

>>> import cx_Oracle
>>> db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE')
>>> db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE')
>>> dsn_tns = cx_Oracle.makedsn('localhost', 1521, 'XE')
>>> print dsn_tns
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=XE)))
>>> db2 = cx_Oracle.connect('hr', 'hrpwd', dsn_tns)

 

 

在連線物件的範圍內（如分配給上面的 db 變數的連線物件），您可以通過查詢版本屬性獲得資料庫版本（這是 DB API 2.0 的一個擴充）。這可以用於使 Python 程式依賴於具體版本的 Oracle 產品。同樣地，您可以通過查詢 dsn 屬性獲得串連的連接字串。

>>> print db.version
10.2.0.1.0
>>> versioning = db.version.split('.')
>>> print versioning
['10', '2', '0', '1', '0']
>>> if versioning[0]=='10':
... print "Running 10g"
... elif versioning[0]=='9':
... print "Running 9i"
...
Running 10g
>>> print db.dsn
localhost:1521/XE
 

遊標對象

您可以使用連線物件的 cursor() 方法定義任意數量的遊標。簡單的程式使用一個遊標就可以了，該遊標可以一再地重複使用。但較大的項目可能要求幾個不同的遊標。

>>> cursor = db.cursor()

應用程式邏輯通常需要明確區分針對資料庫發出的語句的各個處理階段。這有助於更好地理解效能瓶頸並編寫更快且經過最佳化的代碼。語句處理分三個階段： 

在繼續瞭解遊標樣本前，請先瞭解 pprint 模組的 pprint 函數。它用於以清晰、可讀的形式輸出 Python 資料結構。

>>> from pprint import pprint
>>> cursor.execute('SELECT feed_id, feed_url, XMLType.GetClobVal(feed_xml) FROM rss_feeds')
>>> cursor.execute('SELECT * FROM jobs')
[<cx_Oracle.STRING with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>]
>>> pprint(cursor.fetchall())
[('AD_PRES', 'President', 20000, 40000),
('AD_VP', 'Administration Vice President', 15000, 30000),
('AD_ASST', 'Administration Assistant', 3000, 6000),
('FI_MGR', 'Finance Manager', 8200, 16000),
('FI_ACCOUNT', 'Accountant', 4200, 9000),
?
('PR_REP', 'Public Relations Representative', 4500, 10500)]
<cx_oracle.string none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.number none="" value="" with=""><cx_oracle.number none="" value="" with="">

cx_Oracle 遊標是迭代器。利用這些強大的 Python 結構，您可以一種自然的方式對序列進行迭代，該方式僅根據需要擷取後續的項。高成本的資料庫選擇操作自然符合這一思路，因為資料只在需要時才被擷取。您可以進行迭代操作直至找到需要的值或滿足另一條件，而不必建立或擷取整個的結果集。

>>> cursor = db.cursor()
>>> cursor.execute('SELECT * FROM jobs')
[<cx_Oracle.STRING with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>]
>>> for row in cursor: ## notice that this is plain English!
... print row
...
('AD_VP', 'Administration Vice President', 15000, 30000)
('AD_ASST', 'Administration Assistant', 3000, 6000)
('FI_MGR', 'Finance Manager', 8200, 16000)
('FI_ACCOUNT', 'Accountant', 4200, 9000)
('AC_MGR', 'Accounting Manager', 8200, 16000)
?
('PR_REP', 'Public Relations Representative', 4500, 10500)
<cx_oracle.string none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.number none="" value="" with=""><cx_oracle.number none="" value="" with="">

執行 list(cursor) 後，會針對 cursor.fetchall() 執行相同的任務。這是因為內建的 list() 函數會在給定的迭代器結束前一直進行迭代。

資料類型

在擷取階段，基本的 Oracle 資料類型會映射到它們在 Python 中的等同資料類型中。cx_Oracle 維護一個單獨的、有助於這一轉換的資料類型集合。Oracle - cx_Oracle - Python 映射為：

Oracle	cx_Oracle	Python
VARCHAR2 NVARCHAR2 LONG	cx_Oracle.STRING	str
CHAR	cx_Oracle.FIXED_CHAR
NUMBER	cx_Oracle.NUMBER	int
FLOAT		float
DATE	cx_Oracle.DATETIME	datetime.datetime
TIMESTAMP	cx_Oracle.TIMESTAMP
CLOB	cx_Oracle.CLOB	cx_Oracle.LOB
BLOB	cx_Oracle.BLOB

 

除涉及大型物件的情況外，上述資料類型對於使用者通常是透明的。截至版本 4.3，cx_Oracle 仍然自已處理這些資料類型，而沒有與內建的檔案類型打包到一起。

cx_Oracle 目前不負責處理的其他資料類型包括 XMLTYPE 和所有複雜的類型。目前所有對未支援類型的列的查詢都會失敗，同時引發 NotSupportedError 異常。您需要從查詢中清除它們或將它們轉換為支援的資料類型。

例如，考慮下面用於儲存彙總的 RSS 信源提供的表：

CREATE TABLE rss_feeds (
feed_id NUMBER PRIMARY KEY,
feed_url VARCHAR2(250) NOT NULL,
feed_xml XMLTYPE
);

嘗試使用 Python 查詢此表時，需執行一些額外的步驟。在下例中，XMLType.GetClobVal() 用於以 CLOB 值形式從表中返回 XML。

>>> cursor.execute('SELECT * FROM rss_feeds')

Traceback (most recent call last):
File "<pyshell#37>", line 1, in <module>
cursor.execute('SELECT * FROM rss_feeds')
NotSupportedError: Variable_TypeByOracleDataType: unhandled data type 108
>>> cursor.execute('SELECT feed_id, feed_url, XMLType.GetClobVal(feed_xml) FROM rss_feeds')
[<cx_Oracle.NUMBER with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.CLOB with value None>]

 

您可能已經注意到了，cx_Oracle.Cursor.execute* 系列方法為查詢返回列資料類型。這些是變數對象列表（DB API 2.0 的擴充），它們在擷取階段之前擷取值 None，在擷取階段之後擷取合適的資料值。有關資料類型的詳細資料，可以通過遊標對象的 description 屬性獲得。description 是一個包含 7 項內容的位元組組，每個位元組組包含列名、列類型、顯示大小、內部大小、精度、小數位元以及是否存在空的可能。注意列資訊僅可供 SQL 查詢語句訪問。

>>> column_data_types = cursor.execute('SELECT * FROM employees')
>>> print column_data_types
[<cx_Oracle.NUMBER with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.DATETIME with value None>, <cx_Oracle.STRING with value None>, <cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>]
>>> pprint(cursor.description)
[('EMPLOYEE_ID', <type 'cx_Oracle.NUMBER'>, 7, 22, 6, 0, 0),
('FIRST_NAME', <type 'cx_Oracle.STRING'>, 20, 20, 0, 0, 1),
('LAST_NAME', <type 'cx_Oracle.STRING'>, 25, 25, 0, 0, 0),
('EMAIL', <type 'cx_Oracle.STRING'>, 25, 25, 0, 0, 0),
('PHONE_NUMBER', <type 'cx_Oracle.STRING'>, 20, 20, 0, 0, 1),
('HIRE_DATE', <type 'datetime.datetime'>, 23, 7, 0, 0, 0),
('JOB_ID', <type 'cx_Oracle.STRING'>, 10, 10, 0, 0, 0),
('SALARY', <type 'cx_Oracle.NUMBER'>, 12, 22, 8, 2, 1),
('COMMISSION_PCT', <type 'cx_Oracle.NUMBER'>, 6, 22, 2, 2, 1),
('MANAGER_ID', <type 'cx_Oracle.NUMBER'>, 7, 22, 6, 0, 1),
('DEPARTMENT_ID', <type 'cx_Oracle.NUMBER'>, 5, 22, 4, 0, 1)]
<cx_oracle.number none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.datetime none="" value="" with=""><cx_oracle.string none="" value="" with=""><cx_oracle.number none="" value="" with=""><cx_oracle.number none="" value="" with=""><cx_oracle.number none="" value="" with=""><cx_oracle.number none="" value="" with="">

綁定變數模式

正如 Oracle 大師 Tom Kyte 介紹的那樣，綁定變數是資料庫開發的核心原則。它們不僅使程式運行更快，同時可以防範 SQL 插入式攻擊。請看以下查詢：

SELECT * FROM emp_details_view WHERE department_id=50 
SELECT * FROM emp_details_view WHERE department_id=60 
SELECT * FROM emp_details_view WHERE department_id=90 
SELECT * FROM emp_details_view WHERE department_id=110 

逐個運行時，它們需要分別進行分析，這為您的應用程式增加了額外的開銷。通過使用綁定變數，您可以告訴 Oracle 對一個查詢只分析一次。cx_Oracle 支援按名稱或位置綁定變數。

按名稱傳遞綁定變數要求執行方法的 parameters 參數是一個字典或一組關鍵字參數。下面的 query1 和 query2 是等同的：

>>> named_params = {'dept_id':50, 'sal':1000}
>>> query1 = cursor.execute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', named_params)
>>> query2 = cursor.execute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000)

在使用已命名的綁定變數時，您可以使用遊標的 bindnames() 方法檢查目前已指定的綁定變數：

>>> print cursor.bindnames() 
['DEPT_ID', 'SAL']

按位置傳遞與此相似，但是您需要謹慎命名。變數名是任意的，因此這種方式很容易使查詢混亂。在下例中，三個查詢 r1、r2 和 r3 都是等同的。parameters 變數必須作為序列提供。

>>> r1 = cursor.execute('SELECT * FROM locations WHERE country_id=:1 AND city=:2', ('US', 'Seattle'))
>>> r2 = cursor.execute('SELECT * FROM locations WHERE country_id=:9 AND city=:4', ('US', 'Seattle'))
>>> r3 = cursor.execute('SELECT * FROM locations WHERE country_id=:m AND city=:0', ('US', 'Seattle'))

在綁定時，您可以首先準備該語句，然後利用改變的參數執行 None。根據綁定變數時準備一個語句即足夠這一原則，Oracle 將如同在上例中一樣對其進行處理。準備好的語句可執行任意次。

>>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')
>>> r = cursor.execute(None, {'min':1000})
>>> print len(cursor.fetchall())
19

您已經限制了分析次數。在下一段中，我們將消除不必要的執行，尤其是成本高昂的批量插入。

一次多行

大型的插入操作不需求多次的單獨插入，這是因為 Python 通過 cx_Oracle.Cursor.executemany 方法完全支援一次插入多行。限制執行操作的數量極大地改善了程式效能，因此在編寫存在大量插入操作的應用程式時應首先考慮這一功能。 

我們首先為 Python 模組列表建立一個表，這次直接從 Python 開始。您將在以後刪除該表。

>>> create_table = """
CREATE TABLE python_modules (
module_name VARCHAR2(50) NOT NULL,
file_path VARCHAR2(300) NOT NULL
)
"""
>>> from sys import modules
>>> cursor.execute(create_table)
>>> M = []
>>> for m_name, m_info in modules.items():
... try:
... M.append((m_name, m_info.__file__))
... except AttributeError:
... pass
...
>>> len(M)
76
>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")
>>> cursor.executemany(None, M)
>>> db.commit()
>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")
>>> print cursor.fetchone()
(76,)
>>> cursor.execute("DROP TABLE python_modules PURGE")

僅向資料庫發出一個執行操作，要求將 76 個模組名稱全部插入。這對大型插入操作而言是一個巨大的效能提升。注意此處的兩點小的不同：cursor.execute(create_tab) 不產生任何輸出，這是因為它是一個 DDL 語句，而 (76,) 是一個有單個元素的位元組組。不含逗號的 (76) 完全等同於整數 76。

總結

熟悉 Oracle-Python 串連性的基本概念之後，您就可以開始編寫自己的資料庫驅動的應用程式了。我強烈建議花一些時間來使用 Python 互動式 shell，因為它確實會使學習過程更加容易。

您瞭解了 SQL 陳述式經曆的三個階段，也瞭解了如何將 Oracle 資料庫需要執行的步驟減至最少。綁定變數是資料庫應用程式開發不可避免的一部分，Python 支援按名稱或位置進行綁定。

您還瞭解了 Oracle 和 Python 資料類型間的平滑轉換，以及在將遊標作為迭代器進行處理的上下文中資料庫資料的自然處理方式。所有這些特性都促進了生產效率的提高並支援專註於資料，而這正是核心所在。