python查询最高分_精通 Oracle+Python，第 1 部分：查询最佳应践
作者：Przemyslaw Piotrowski
首先，熟悉 Oracle-Python
連接性的基本概念
2007 年 9 月發布
參見系列目錄
在 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')
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('.')
versioning
['10', '2', '0', '1', '0']
>>> if
versioning[0]=='10':
... print "Running 10g"
... elif versioning[0]=='9':
... print "Running 9i"
...
Running 10g
db.dsn
localhost:1521/XE
游標對象
您可以使用連接對象的 cursor()
方法定義任意數量的游標。簡單的程序使用一個游標就可以了，該游標可以一再地重復使用。但較大的項目可能要求幾個不同的游標。
>>>
cursor = db.cursor()
應用程序邏輯通常需要明確區分針對數據庫發出的語句的各個處理階段。這有助于更好地理解性能瓶頸并編寫更快且經過優化的代碼。語句處理分三個階段：
分析(可選)
cx_Oracle.Cursor.parse([statement])
實際上并不需要調用，因為在執行階段會自動分析 SQL 語句。該方法可以用于在執行語句前對其進行驗證。當這類語句中檢測出錯誤時，會引發
DatabaseError 異常，相應的錯誤消息通常可能是“ORA-00900:invalid SQL statement,
ORA-01031:insufficient privileges or ORA-00921:unexpected end of
SQL command.”
執行
cx_Oracle.Cursor.execute(statement, [parameters],
**keyword_parameters)此方法可以接受單個參數 — 一條 SQL 語句 —
直接針對數據庫來運行。通過 parameters 或 keyword_parameters
參數賦值的綁定變量可以指定為字典、序列或一組關鍵字參數。如果已經提供了字典或關鍵字參數，那么這些值將與名稱綁定。如果給出的是序列，將根據這些值的位置對它們進行解析。如果是查詢操作，此方法返回一個變量對象列表；如果不是，則返回
None。
cx_Oracle.Cursor.executemany(statement,
parameters)對于批量插入尤其有用，因為它可以將所需的 Oracle
執行操作的數量限制為僅一個。有關如何使用該方法的詳細信息，請參見下面的“一次多行”部分。
獲取(可選)— 僅用于查詢(因為 DDL 和 DCL
語句不返回結果)。在不執行查詢的游標上，這些方法將引發 InterfaceError 異常。
cx_Oracle.Cursor.fetchall()
以字節組列表形式獲取結果集中的所有剩余行。如果沒有剩余的行，它返回一個空白列表。獲取操作可以通過設置游標的 arraysize
屬性進行調整，該屬性可設置在每個底層請求中從數據庫中返回的行數。arraysize
的設置越高，需要在網絡中往返傳輸的次數越少。arraysize 的默認值為 1。
cx_Oracle.Cursor.fetchmany([rows_no])
獲取數據庫中接下來的 rows_no 行。如果該參數未指定，該方法獲取的行數是 arraysize 的數量。如果 rows_no
大于獲取到的行的數目，該方法獲取的行數是剩余的行數。
cx_Oracle.Cursor.fetchone()
從數據庫中獲取單個字節組，如果沒有剩余行，則返回 none。
在繼續了解游標示例前，請先了解 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')
[,
,
,
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 游標是迭代器。利用這些強大的 Python
結構，您可以一種自然的方式對序列進行迭代，該方式僅根據需要獲取后續的項。高成本的數據庫選擇操作自然符合這一思路，因為數據只在需要時才被獲取。您可以進行迭代操作直至找到需要的值或滿足另一條件，而不必創建或獲取整個的結果集。
>>>
cursor = db.cursor()
>>>
cursor.execute('SELECT * FROM jobs')
[,
,
,
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)
執行 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 "", line 1, in
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')
[,
,
None>]
您可能已經注意到了，cx_Oracle.Cursor.execute* 系列方法為查詢返回列數據類型。這些是變量對象列表(DB
API 2.0 的擴展)，它們在獲取階段之前獲取值
None，在獲取階段之后獲取合適的數據值。有關數據類型的詳細信息，可以通過游標對象的 description
屬性獲得。description 是一個包含 7
項內容的字節組，每個字節組包含列名、列類型、顯示大小、內部大小、精度、小數位數以及是否存在空的可能。注意列信息僅可供 SQL
查詢語句訪問。
>>>
column_data_types = cursor.execute('SELECT * FROM employees')
column_data_types
[,
,
,
,
,
,
,
,
,
,
None>]
>>>
pprint(cursor.description)
[('EMPLOYEE_ID',
'cx_Oracle.NUMBER'>, 7, 22, 6, 0, 0),
('FIRST_NAME',
'cx_Oracle.STRING'>, 20, 20, 0, 0, 1),
('LAST_NAME',
'cx_Oracle.STRING'>, 25, 25, 0, 0, 0),
('EMAIL', ,
25, 25, 0, 0, 0),
('PHONE_NUMBER',
'cx_Oracle.STRING'>, 20, 20, 0, 0, 1),
('HIRE_DATE',
'datetime.datetime'>, 23, 7, 0, 0, 0),
('JOB_ID', ,
10, 10, 0, 0, 0),
('SALARY', ,
12, 22, 8, 2, 1),
('COMMISSION_PCT',
'cx_Oracle.NUMBER'>, 6, 22, 2, 2, 1),
('MANAGER_ID',
'cx_Oracle.NUMBER'>, 7, 22, 6, 0, 1),
('DEPARTMENT_ID',
'cx_Oracle.NUMBER'>, 5, 22, 4, 0,
1)]
綁定變量模式
正如 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() 方法檢查目前已指定的綁定變量：
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})
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")
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
數據類型間的平滑轉換，以及在將游標作為迭代器進行處理的上下文中數據庫數據的自然處理方式。所有這些特性都促進了生產效率的提高并支持專注于數據，而這正是核心所在。
Przemyslaw Piotrowski
是一名信息技術專家，專門研究新興技術和動態、靈活的開發環境。他擁有很強的 IT
專業技術背景(包括管理、開發和設計)，并發現了許多軟件互操作方法。
總結
以上是生活随笔為你收集整理的python查询最高分_精通 Oracle+Python，第 1 部分：查询最佳应践的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python怎么测试程序_python如
- 下一篇： matlab旋转机械转子故障信号仿真,旋