Python操作Oracle資料庫：cx_Oracle

  • 2020 年 4 月 13 日
  • 筆記

 

在前面的部落格中已經介紹過如何使用Python來操作MySQL資料庫，最近需要將一批數據從csv文件中遷移到Oracle資料庫中，也打算用Python來實現，趁著這個機會，也寫一篇部落格學習總結一些如何使用Python來操作Oracle資料庫。

 

1 安裝與導入

 

Python操作Oracle資料庫多用cx_Oracle這個第三方擴展，總體而言，cx_Oracle的使用方式與Python操作MySQL資料庫的pymysql庫還是很相似的，如果還沒有安裝，可以通過下面的命令進行安裝：

 

$ pip install -i //pypi.douban.com/simple cx_oracle

 

使用前導入：

In [1]:
import cx_Oracle
 

千萬注意，包名稱cx_Oracle中，字母「O」是大寫的，寫成小寫將會導入失敗。

 

這裡再附帶多說一點，我在安裝好cx_Oracle第一次使用時，出現這個異常：DatabaseError: DPI-1047,可以按照官方的思路解決：//oracle.github.io/odpi/doc/installation.html#linux

 

2 創建連接

cx_Oracle提供了兩種方式連接Oracle資料庫，分別是創建獨立的單一連接以及創建連接池。

 

2.1 單一連接

創建單一連接主要是通過cx_Oracle模組中提供的connect()方法實現，雖然也可以直接通過Connection()類實現，但是不推薦。connect()方法參數有很多，說說其中最常用的四個：

  • user：用戶名
  • password：密碼
  • dsn：資料庫地址和服務名
  • encoding：編碼，合適的編碼可以避免出現亂碼

這裡要重點說一下dsn，dsn是data source name的縮寫，用於描述數據源的詳細地址，一般由資料庫所在主機地址、埠和服務名組成。在默認情況下，Oracle資料庫對應1521埠，在這種情況下，dsn中可以省略埠：

In [3]:
connection = cx_Oracle.connect("username", "password", "192.168.1.2/helowin", encoding="UTF-8")
 

其中，username是用戶名，password是密碼，192.168.1.2是資料庫所在主機IP，helowin是服務名。
在一般情況下，可以這麼寫：

In [10]:
connection = cx_Oracle.connect("username", "password", "192.168.1.2:1521/helowin", encoding="UTF-8")
 

有時候，我們需要以管理員身份登錄資料庫，這時候，直接連接時不行的，將會跑出異常：DatabaseError: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER，這時候可以傳遞參數mode=cx_Oracle.SYSDBA。

In [7]:
connection = cx_Oracle.connect("sys", "psdpassword", "192.168.1.2:1521/helowin",mode=cx_Oracle.SYSDBA,
         encoding="UTF-8")
 

當確定不在使用連接時，可以使用connection.close()關閉連接（這是個好習慣）。

In [8]:
connection.close()
 

2.2 連接池

cx_Oracle中提供SessionPool()創建連接池，連接池一般是在應用程式初始化時創建。相比通過connect()方法創建單個資料庫連接，使用SessionPool()創建連接池時，需要額外指定最少連接數（min)和最大連接數(max)，連接池創建時會創建有min個資料庫連接，當連接不夠用時會繼續新增連接，當連接未被使用時連接池將會自動減少連接的數量。在創建好連接池後，通過調用acquire()方法可以獲取一個資料庫連接，連接使用完畢之後，最好使用SessionPool.release(connection)或Connection.close()將連接放回連接池。

In [12]:
# 創建連接池
pool = cx_Oracle.SessionPool("username", "password",
        "192.168.1.2:1521/helowin", min=2, max=5, increment=1, encoding="UTF-8")

# 從連接池中獲取一個連接
connection = pool.acquire()

# 使用連接進行查詢
cursor = connection.cursor()
for result in cursor.execute("select * from scott.students"):
    print(result)

# 將連接放回連接池
pool.release(connection)

# 關閉連接池
pool.close()
 
(1, '張三', 20)
(2, '李四', 30)
 

如果是在多執行緒下同時使用連接，那麼在創建連接池時應該傳遞一個threaded參數，並將值設置為True：

In [2]:
# 創建連接池
pool = cx_Oracle.SessionPool("username", "password",
        "192.168.1.2:1521/helowin", min=2, max=5, increment=1, threaded=True, encoding="UTF-8")
In [3]:
pool.close()
 

3 游標

 

有了資料庫連接之後，可以通過連接來獲取游標：

In [64]:
cur = connection.cursor()
 

通過游標，可以執行SQL語句，實現與資料庫的交互，但是記住，游標使用完之後記得關閉：

In [65]:
cur.close()
 

游標對象中定義有Cursor.excute()方法和Cursor.executemany()兩個方法用於執行SQL語句，前者一次只能執行一條SQL語句，後者一次可執行多條SQL。當有類似的大量SQL語句需要執行時，使用Cursor.executemany()而不是多次執行Cursor.excute()可以極大提升性能。
另外，所有cx_Oracle執行的語句都含有分號「;」或斜杠「/」：

In [66]:
connection = cx_Oracle.connect("username", "password", "192.168.1.2/helowin", encoding="UTF-8")
cur = connection.cursor()
In [68]:
cur.execute("select * from SCOTT.STUDENTS;")  # 含有分號，拋出異常
 
---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-68-2181d3923cb0> in <module>
----> 1cur.execute("select * from SCOTT.STUDENTS;")  # 含有分號，拋出異常

DatabaseError: ORA-00911: invalid character
 

4 執行SQL

 

4.1 SQL語句拼接

（1）使用Python原生佔位符拼接

 

在很多應用場景中，我們查詢所用的SQL語句並不是固定的，而是根據當時環境靈活的對SQL進行拼接。最簡單的方式就是直接使用Python原生提供的佔位符進行拼接，不過要注意如果變數是字元串時，引號不能少。

In [123]:
cur.execute("insert into SCOTT.STUDENTS (id, name, age) values ({student_id}, '{student_name}', {student_age})".format(
    student_id=4,
    student_name='李六',
    student_age=15
))
connection.commit()
In [124]:
student_id = 4
result = cur.execute("select * from SCOTT.STUDENTS where id={}".format(student_id))
In [125]:
result.fetchone()
Out[125]:
(4, '李六', 15)
In [118]:
student_name = "張三"
result = cur.execute("select * from SCOTT.STUDENTS where name='{}'".format(student_name))
In [119]:
result.fetchone()
Out[119]:
(1, '張三', 20)
 

(2)通過變數名拼接

使用這種拼接方式時，字元串中的名稱與真實變數名必須一一對應。
所有變數可以統一存儲在一個字典中：

In [127]:
student = {'student_id':5, 'student_name':'陳七', 'student_age': 25}  # 將所有變數存儲到一個字典中
cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)',student)
connection.commit()
 

也可以逐一賦值：

In [128]:
cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)',
            student_id=6,student_name='毛八',student_age=60)
connection.commit()
 

(3)通過參數位置拼接

通過參數位置進行拼接時，所有變數可以統一存儲在一個list中，list中的變數的順序必須與字元串中定義的順序保持一致。

In [129]:
cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)',
            [7,'魏九',30])
connection.commit()
 

這時候，在字元串中也可以不顯式的出現參數名，而是以數字來代替出現位置：

In [130]:
cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)',
            [8,'吳十',90])
connection.commit()
 

4.2 執行語句

cx_Oracle的游標中定義了execute()和executemany()兩個方法用於執行SQL語句，區別在於execute()一次只能執行一條SQL，而executemany()一次能執行多條SQL。在大量結構一樣，參數不同的語句需要執行時，使用executemany()而不是多次調用execute()執行可以大大提高程式碼性能。

（1）execute()

對於execute()方法，其實在上面程式碼實例中以及多次使用，大致形式如下：

In [131]:
cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)',
            [9,'蕭十一',32])
connection.commit()
 

（2）executemany()

In [132]:
students = [
    [10,'蕭十一',32],
    [11,'何十二',40],
    [12,'穆十三',35]
]
cur.executemany('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)',
            students)
connection.commit()
 

cx_Oracle執行SQL時需要注意，若是執行查詢，可通過游標獲取查詢結果，具體如何獲取請繼續看下文；若是執行insert或update操作，需要在執行後繼續一步connection.commit()操作。

5 獲取查詢結果

 

當使用游標進行查詢後，可以直接迭代取出查詢結果

In [79]:
result = cur.execute("select * from SCOTT.STUDENTS")
In [80]:
for row in result:
    print(row)
 
(1, '張三', 20)
(2, '李四', 30)
(3, '王五', 40)
 

注意，這裡的游標查詢結果對象result雖然不是生成器，但是可以當做生成器來用，每一次使用next()方法時，可以獲取一條記錄。當然，也與生成器一樣，查詢結果只能迭代遍歷一次，再次使用迭代不會有任何輸出：

In [82]:
result = cur.execute("select * from SCOTT.STUDENTS")
In [83]:
next(result)
Out[83]:
(1, '張三', 20)
In [84]:
next(result)
Out[84]:
(2, '李四', 30)
In [85]:
next(result)
Out[85]:
(3, '王五', 40)
In [86]:
for row in result:  # 沒有任何輸出結果
    print(row)
 

其實，通過循環來獲取查詢結果時，每一次調用next()方法，result對象都會對資料庫發起一次請求，獲取一條查詢記錄，如果查詢記錄數量比較大時，性能會比較低，這時候，可以通過設置cur.arraysize參數改善性能。cur.arraysize參數配置的是每次請求獲取的數據包大小，默認為100，當設置為更大值時，一次請求就可以獲取更多的記錄，減少客戶端與資料庫伺服器端網路往返次數，從而提高性能，當然缺點就是消耗的記憶體會更大。

In [91]:
cur.arraysize = 500
for row in cur.execute("select * from SCOTT.STUDENTS"):
    print(row)
 
(1, '張三', 20)
(2, '李四', 30)
(3, '王五', 40)
 

除了在循環中直接遍歷外，還可以通過fetchone()、fetchmany()、fetchall()三個方法取出查詢結果。

  • fetchone()

fetchone()每次只取出一條記錄，功能效果與直接對result使用next()方法一樣。

In [93]:
cur = connection.cursor()
result = cur.execute("select * from SCOTT.STUDENTS")
In [95]:
result.fetchone()
Out[95]:
(1, '張三', 20)
In [96]:
result.fetchone()
Out[96]:
(2, '李四', 30)
In [97]:
result.fetchone()
Out[97]:
(3, '王五', 40)
 
  • fetchmany()
 

fetchmany()可以一次取出指定數量的記錄，如果不指定數量，表示一次性去除所有記錄。

In [102]:
cur = connection.cursor()
result = cur.execute("select * from SCOTT.STUDENTS")
In [103]:
result.fetchmany(2)
Out[103]:
[(1, '張三', 20), (2, '李四', 30)]
In [104]:
result.fetchmany(2)
Out[104]:
[(3, '王五', 40)]
In [105]:
result.fetchmany(2)  # 若所有記錄都取出來了，返回空列表
Out[105]:
[]
 
  • fetchall()
 

fetchall()一次性去除所有結果。

In [106]:
cur = connection.cursor()
result = cur.execute("select * from SCOTT.STUDENTS")
In [107]:
result.fetchall()
Out[107]:
[(1, '張三', 20), (2, '李四', 30), (3, '王五', 40)]

VirMach 便宜 VPS

QNews

QNews