【转】python 和 cx_Oracle 的使用
下载地址:
http://sourceforge.net/projects/cx-oracle/?source=directory(失效的话请自己百度)下载的时候注意数据库版本和操作系统环境。
技术手册:
http://starship.python.net/crew/atuining/cx_Oracle/html/cx_Oracle.html
http://cx-oracle.readthedocs.org/en/latest/index.html
cx_Oracle使用方法
自:http://www.x5dj.com/Blog/00182202/00572497.shtml
正确安装好cx_oracle之后,要使用它来连接到oracle数据库进行操作,具体应该分3步走:
第一步:导入cx_Oracle ,建立连接
>>> import cx_Oracle # 导入模块 >>> db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE') #建立连接,3 个参数分开写 >>> 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', '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之后,我们可以调用这个cursor.execute(‘SQL‘) 来执行SQL语句。比如: >>>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)
这种是名字参数,还可以按位置参数:
r1 = cursor.execute('SELECT * FROM locations WHERE country_id=:1 AND city=:2', ('US', 'Seattle'))
注意:
当只有一次参数的时候,也要把它写成元组的形式,比如
Cursor.execute(‘select name from user where id=:1’,(login_Id,))
千万要注意,login_id后面还带有一个逗号,如果没有逗号,他其实就是一个数据对象,但是当他后面有个逗号的时候,他就变成了元组的一个数据项,千万要记住啊,我就是在这里徘徊了很久。!
Cursor. Prepare的用法,
这个方法就是在prepare之后,你再去execute的时候,就不用写上sql语句参数了
>>> 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 = """ 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 格式的创建:
binary_content = cursor.var(cx_Oracle.BLOB)
binary_content.setvalue(0, content)