正確安裝好cx_oracle之后，要使用它來連接到oracle數(shù)據(jù)庫進(jìn)行操作，具體應(yīng)該分3步走：
第一步：導(dǎo)入cx_Oracle，建立連接
>>> import cx_Oracle #導(dǎo)入模塊
>>> db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE') 建立連接，3個(gè)參數(shù)分開寫
特別注意：這里的 'localhost:1521/XE'可以是你oracle net manager配置的鏈接名，如oracl，我就是在這里耽擱了半天
>>> db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE') 建立連接，3個(gè)參數(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)
>>> <strong style="line-height: normal;">print db.version</strong><br style="line-height: normal;" />
10.2.0.1.0<br style="line-height: normal;" />
>>> <strong style="line-height: normal;">versioning = db.version.split('.')</strong><br style="line-height: normal;" />
>>> <strong style="line-height: normal;">print versioning</strong><br style="line-height: normal;" />
['10', '2', '0', '1', '0']<br style="line-height: normal;" />
>>> <strong style="line-height: normal;">if versioning[0]=='10':</strong><br style="line-height: normal;" />
... <strong style="line-height: normal;">print "Running 10g"</strong><br style="line-height: normal;" />
... <strong style="line-height: normal;">elif versioning[0]=='9':</strong><br style="line-height: normal;" />
... <strong style="line-height: normal;">print "Running 9i"</strong><br style="line-height: normal;" />
...<br style="line-height: normal;" />
Running 10g<br style="line-height: normal;" />
>>> <strong style="line-height: normal;">print db.dsn</strong><br style="line-height: normal;" />
localhost:1521/XE第二步：建立Cursor光標(biāo)
>>>cursor = db.cursor() 建立一個(gè)cursor
之后，我們可以調(diào)用這個(gè)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í)候，考慮到它的數(shù)據(jù)類型了嗎？下面就是數(shù)據(jù)類型的對(duì)應(yīng)表
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'))
注意：
當(dāng)只有一次參數(shù)的時(shí)候，也要把它寫成元組的形式，比如
Cursor.execute(‘select name from user where id=:1’,(login_Id,))
千萬要注意，login_id后面還帶有一個(gè)逗號(hào)，如果沒有逗號(hào)，他其實(shí)就是一個(gè)數(shù)據(jù)對(duì)象，但是當(dāng)他后面有個(gè)逗號(hào)的時(shí)候，他就變成了元組的一個(gè)數(shù)據(jù)項(xiàng)，千萬要記住啊，我就是在這里徘徊了很久。！
Cursor. Prepare的用法，
這個(gè)方法就是在prepare之后，你再去execute的時(shí)候，就不用寫上sql語句參數(shù)了
>>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')
>>> r = cursor.execute(None, {'min':1000}) #注意，第一個(gè)參數(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 = """<br style="line-height: normal;" />
CREATE TABLE python_modules (<br style="line-height: normal;" />
module_name VARCHAR2(50) NOT NULL,<br style="line-height: normal;" />
file_path VARCHAR2(300) NOT NULL<br style="line-height: normal;" />
)<br style="line-height: normal;" />
"""<br style="line-height: normal;" />
>>> from sys import modules<br style="line-height: normal;" />
>>> cursor.execute(create_table)<br style="line-height: normal;" />
>>> M = []<br style="line-height: normal;" />
>>> for m_name, m_info in modules.items():<br style="line-height: normal;" />
... try:<br style="line-height: normal;" />
... M.append((m_name, m_info.__file__))<br style="line-height: normal;" />
... except AttributeError:<br style="line-height: normal;" />
... pass<br style="line-height: normal;" />
...<br style="line-height: normal;" />
>>> len(M)<br style="line-height: normal;" />
76<br style="line-height: normal;" />
>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")<br style="line-height: normal;" />
>>> cursor.executemany(None, M)<br style="line-height: normal;" />
>>> db.commit()<br style="line-height: normal;" />
>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")<br style="line-height: normal;" />
>>> print cursor.fetchone()<br style="line-height: normal;" />
(76,)<br style="line-height: normal;" />
>>> cursor.execute("DROP TABLE python_modules PURGE")