第一步：導入cx_Oracle ，建立連接
復制代碼

import cx_Oracle # 導入模塊
db = cx_Oracle.connect(‘hr’, ‘hrpwd’, ‘localhost:1521/XE’) #建立連接，3 個參數(shù)分開寫
db1 = cx_Oracle.connect(‘hr/hrpwd@localhost:1521/XE’) #建立連接，3 個參數(shù)連寫
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)
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()

建立一個cursor之后，我們可以調(diào)用這個cursor.execute（‘SQL‘） 來執(zhí)行SQL語句。比如：

cursor.execute(‘select * from tabs’)

執(zhí)行完畢以后，可以調(diào)用cursor.fetchall()一次取完所有結(jié)果，或者cursor.fetchone()一次取一行結(jié)果

row=cursor.fetchall()
for x in row:
For y in x:
Print y,
Print

復制代碼
這樣就可以按照表格的形式打印取得的結(jié)果了！
在從oracle取出數(shù)據(jù)的時候，考慮到它的數(shù)據(jù)類型了嗎？下面就是數(shù)據(jù)類型的對應表
Datatypes
During the fetch stage, basic Oracle data types get mapped into their Python equivalents. cx_Oracle maintains a separate set of data types that helps in this transition. The Oracle - cx_Oracle - Python mappings are:

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

帶參數(shù)的查詢：

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)
這種是名字參數(shù)，還可以按位置參數(shù)：

r1 = cursor.execute(‘SELECT * FROM locations WHERE country_id=:1 AND city=:2’, (‘US’, ‘Seattle’))
注意：
當只有一次參數(shù)的時候，也要把它寫成元組的形式，比如
Cursor.execute(‘select name from user where id=:1’,(login_Id,))
千萬要注意，login_id后面還帶有一個逗號，如果沒有逗號，他其實就是一個數(shù)據(jù)對象，但是當他后面有個逗號的時候，他就變成了元組的一個數(shù)據(jù)項，千萬要記住啊，我就是在這里徘徊了很久。！

Cursor. Prepare的用法，
這個方法就是在prepare之后，你再去execute的時候，就不用寫上sql語句參數(shù)了

cursor.prepare(‘SELECT * FROM jobs WHERE min_salary>:min’)
r = cursor.execute(None, {‘min’:1000}) #注意，第一個參數(shù)是None
一次執(zhí)行多條sql語句：
Large insert operations don’t require many separate inserts because Python fully supports inserting many rows at once with the cx_Oracle.Cursor.executemany method. Limiting the number of execute operations improves program performance a lot and should be the first thing to think about when writing applications heavy on INSERTs.
Let’s create a table for a Python module list, this time directly from Python. You will drop it later.
復制代碼
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”)

復制代碼
BLOB & CLOB 格式的創(chuàng)建:

binary_content = cursor.var(cx_Oracle.BLOB)
binary_content.setvalue(0, content)

---

第一步：導入cx_Oracle ，建立連接

import cx_Oracle # 導入模塊
db = cx_Oracle.connect(‘hr’, ‘hrpwd’, ‘localhost:1521/XE’) 建立連接，3 個參數(shù)分開寫
db1 = cx_Oracle.connect(‘hr/hrpwd@localhost:1521/XE’) 建立連接，3 個參數(shù)連寫
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)
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() 建立一個cursor
之后，我們可以調(diào)用這個cursor.execute（‘SQL‘） 來執(zhí)行SQL語句。比如：
cursor.execute(‘select * from tabs’)
執(zhí)行完畢以后，可以調(diào)用cursor.fetchall()一次取完所有結(jié)果，或者cursor.fetchone()一次取一行結(jié)果
row=cursor.fetchall()
for row in rows:
For v in row:
Print v,
Print
這樣就可以按照表格的形式打印取得的結(jié)果了！
在從oracle取出數(shù)據(jù)的時候，考慮到它的數(shù)據(jù)類型了嗎？下面就是數(shù)據(jù)類型的對應表

帶參數(shù)的查詢：

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)
這種是名字參數(shù)，還可以按位置參數(shù)：
r1 = cursor.execute(‘SELECT * FROM locations WHERE country_id=:1 AND city=:2’, (‘US’, ‘Seattle’))
注意：
當只有一次參數(shù)的時候，也要把它寫成元組的形式，比如
Cursor.execute(‘select name from user where id=:1’,(login_Id,))
千萬要注意，login_id后面還帶有一個逗號！
Cursor. Prepare的用法，
這個方法就是在prepare之后，你再去execute的時候，就不用寫上sql語句參數(shù)了
cursor.prepare(‘SELECT * FROM jobs WHERE min_salary>:min’)
r = cursor.execute(None, {‘min’:1000}) #注意，第一個參數(shù)是None,
一次執(zhí)行多條sql語句

Large insert operations don’t require many separate inserts because Python fully supports inserting many rows at once with the cx_Oracle.Cursor.executemany method. Limiting the number of execute operations improves program performance a lot and should be the first thing to think about when writing applications heavy on INSERTs.
Let’s create a table for a Python module list, this time directly from Python. You will drop it later.

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”)

創(chuàng)建一個簡單的python文件，測試安裝是否成功
import cx_Oracle

conn = cx_Oracle.connect(‘fkong/fkong@172.17.23.129/orcl’)
cursor = conn.cursor ()
cursor.execute (“select * from dual”)
row = cursor.fetchone ()
print row[0]

cursor.close ()
conn.close ()
4. 下面看一個數(shù)據(jù)庫建表和插入操作
import cx_Oracle

conn = cx_Oracle.connect(‘fkong/fkong@172.17.23.129/orcl’)
cursor = conn.cursor ()

cursor.execute (“CREATE TABLE TEST(ID INT, COL1 VARCHAR(32), COL2 VARCHAR(32), COL3 VARCHAR(32))”)

cursor.execute (“INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(1, ‘a(chǎn)’, ‘b’, ‘c’)”)
cursor.execute (“INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(2, ‘a(chǎn)a’, ‘bb’, ‘cc’)”)
cursor.execute (“INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(3, ‘a(chǎn)aa’, ‘bbb’, ‘ccc’)”)
conn.commit()

cursor.close ()
conn.close ()
5. 下面再來看看查詢，查詢通常有兩種方式：一種是使用cursor.fetchall（）獲取所有查詢結(jié)果，然后再一行一行的迭代；另一種每次通過cursor.fetchone()獲取一條記錄，直到獲取的結(jié)果為空為止。看一下下面的例子：
import cx_Oracle

conn = cx_Oracle.connect(‘fkong/fkong@172.17.23.129/orcl’)
cursor = conn.cursor ()

cursor.execute (“SELECT * FROM TEST”)
rows = cursor.fetchall()
for row in rows:
print “%d, %s, %s, %s” % (row[0], row[1], row[2], row[3])

print “Number of rows returned: %d” % cursor.rowcount

cursor.execute (“SELECT * FROM TEST”)
while (1):
row = cursor.fetchone()
if row == None:
break
print “%d, %s, %s, %s” % (row[0], row[1], row[2], row[3])

print “Number of rows returned: %d” % cursor.rowcount

cursor.close ()
conn.close ()

總結(jié)

以上是生活随笔為你收集整理的cx_Oracle使用方法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。