正确安装好cx_oracle之后，要使用它来连接到oracle数据库进行操作，具体应该分3步走：
第一步：导入cx_Oracle，建立连接
>>>
import cx_Oracle #导入模块
>>> db = cx_Oracle.connect('hr','hrpwd','localhost:1521/XE')建立连接，3个参数分开写
>>> db = cx_Oracle.connect('hr','hrpwd','localhost:1521/XE')建立连接，3个参数分开写
特别注意：这里的'localhost:1521/XE'可以是你oracle net manager配置的链接名，如oracl，我就是在这里耽搁了半天
>>>
db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE')建立连接，3个参数连写
>>>
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',dsn_tns)
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
(CONNECT_DATA=(SID=XE)))
>>> db2 = cx_Oracle.connect('hr',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光标
>>>cursor = db.cursor() 建立一个cursor
>>>cursor.execute(‘select * from tabs’)
执行完毕以后，可以调用cursor.fetchall()一次取完所有结果，或者cursor.fetchone()一次取一行结果
>>>row=cursor.fetchall()
>>>for x in row:
For y in x:
Print y,
Print
这样就可以按照表格的形式打印取得的结果了！
在从oracle取出数据的时候，考虑到它的数据类型了吗？下面就是数据类型的对应表
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
|
带参数的查询：
>>> 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)
>>> 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)
这种是名字参数，还可以按位置参数：
r1 = cursor.execute('SELECT * FROM locations
WHERE country_id=:1 AND city=:2',('US','Seattle'))
WHERE country_id=:1 AND city=:2',('US','Seattle'))
注意：
当只有一次参数的时候，也要把它写成元组的形式，比如
Cursor.execute(‘select name from user where id=:1’,(login_Id,))
千万要注意，login_id后面还带有一个逗号，如果没有逗号，他其实就是一个数据对象，但是当他后面有个逗号的时候，他就变成了元组的一个数据项，千万要记住啊，我就是在这里徘徊了很久。！
Cursor. Prepare的用法，
>>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')
>>> r = cursor.execute(None,{'min':1000}) #注意，第一个参数是None,
一次执行多条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") 原文链接：https://www.f2er.com/oracle/208613.html