cx_oracle 使用

连接

import cx_Oracle #使用tnsnames文件别名链接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串，传入一个参数链接 # ora = cx_Oracle.connect('scott/tiger@192.168.56.152:1521/orcl') # 使用字符串，分别传入用户名密码等 # ora = cx_Oracle.connect('scott','tiger','192.168.56.152:1521/orcl') # 使用dsn解析成tns字符串，连接数据库 # tnsname = cx_Oracle.makedsn('192.168.56.152','1521','orcl') # ora = cx_Oracle.connect('scott','tiger',tnsname) #使用sysdba或者其他角色链接 ora = cx_Oracle.connect( 'sys', 'oracle', '192.168.56.152:1521/orcl', mode=cx_Oracle.SYSDBA) cursor = ora.cursor() #使用位置对应参数 cursor.execute( 'select  *  from scott.t1 where DEPTNO = :1',( 10,)) print(cursor.fetchall()) cursor.close() ora.close()

查询

#fetchall import cx_Oracle ora = cx_Oracle.connect( 'scott/tiger@192.168.56.152:1521/orcl') cursor = ora.cursor() cursor.execute( 'select  *  from emp') print(cursor.fetchall()) cursor.close() ora.close()

#fetchone import cx_Oracle ora = cx_Oracle.connect( 'scott/tiger@192.168.56.152:1521/orcl') cursor = ora.cursor() cursor.execute( 'select  *  from emp') while  1:     res = cursor.fetchone()      if res ==  None:          break      print(res) cursor.close() ora.close()

#fetchmany # 使用dsn解析成tns字符串，连接数据库 tnsname = cx_Oracle.makedsn( '192.168.56.151', '1521', 'orcl') ora = cx_Oracle.connect( 'system', 'oracle',tnsname) cursor = ora.cursor() cursor.execute( 'select  *  from dba_objects') resCount= 0 while  1:     res = cursor.fetchmany( 10)      if res == []:          break      print(res)     resCount +=  10 cursor.close() ora.close()

#使用绑定变量 import cx_Oracle ora = cx_Oracle.connect( 'scott/tiger@192.168.56.152:1521/orcl') cursor = ora.cursor() #使用位置对应参数 cursor.execute( 'select  *  from t1 where DEPTNO = :1',( 10,)) print(cursor.fetchall()) #使用字典传入参数 param={ 'dno': 20} cursor.execute( 'select  *  from t1 where DEPTNO = :dno',param) print(cursor.fetchall()) cursor.execute( 'select  *  from t1 where DEPTNO = :dno or DNAME=:dn', dno= 40, dn= 'ACCOUNTING') print(cursor.fetchall()) cursor.close() ora.close()

增、删、改 数据和多次执行

import cx_Oracle #使用tnsnames文件别名链接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串，传入一个参数链接 # ora = cx_Oracle.connect('scott/tiger@192.168.56.152:1521/orcl') # 使用字符串，分别传入用户名密码等 # ora = cx_Oracle.connect('scott','tiger','192.168.56.152:1521/orcl') # 使用dsn解析成tns字符串，连接数据库 tnsname = cx_Oracle.makedsn( '192.168.56.152', '1521', 'orcl') ora = cx_Oracle.connect( 'scott', 'tiger',tnsname) #使用sysdba或者其他角色链接 # ora = cx_Oracle.connect('sys','oracle','192.168.56.152:1521/orcl',mode=cx_Oracle.SYSDBA) cursor = ora.cursor() cursor.execute( 'insert into t1 values(50,:1,:2)',( 'DBA', 'CHINA'))   #sql中使用参数 ora.commit() cursor.execute( 'select  *  from t1') while  1:     res = cursor.fetchone()      if res ==  None:          break      print(res) cursor.close() ora.close()

import cx_Oracle #使用tnsnames文件别名链接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串，传入一个参数链接 # ora = cx_Oracle.connect('scott/tiger@192.168.56.152:1521/orcl') # 使用字符串，分别传入用户名密码等 # ora = cx_Oracle.connect('scott','tiger','192.168.56.152:1521/orcl') # 使用dsn解析成tns字符串，连接数据库 tnsname = cx_Oracle.makedsn( '192.168.56.152', '1521', 'orcl') ora = cx_Oracle.connect( 'scott', 'tiger',tnsname) #使用sysdba或者其他角色链接 # ora = cx_Oracle.connect('sys','oracle','192.168.56.152:1521/orcl',mode=cx_Oracle.SYSDBA) cursor = ora.cursor() cursor.prepare( 'update t1 set LOC=:loc where DEPTNO=:dno') cursor.execute( None,{ 'loc': 'BEIJING', 'dno': 50})   #使用了prepare函数，在execute里面可以不传入sql语句，直接传入参数。注意：这里的第一个参数必须为None ora.commit() cursor.execute( 'select  *  from t1') while  1:     res = cursor.fetchone()      if res ==  None:          break      print(res) cursor.close() ora.close()

import cx_Oracle #使用tnsnames文件别名链接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串，传入一个参数链接 # ora = cx_Oracle.connect('scott/tiger@192.168.56.152:1521/orcl') # 使用字符串，分别传入用户名密码等 # ora = cx_Oracle.connect('scott','tiger','192.168.56.152:1521/orcl') # 使用dsn解析成tns字符串，连接数据库 tnsname = cx_Oracle.makedsn( '192.168.56.152', '1521', 'orcl') ora = cx_Oracle.connect( 'scott', 'tiger',tnsname) #使用sysdba或者其他角色链接 # ora = cx_Oracle.connect('sys','oracle','192.168.56.152:1521/orcl',mode=cx_Oracle.SYSDBA) cursor = ora.cursor() #执行多条语句 list1 = [( 60, 'Enginer', 'Sydney'),( 70, 'Diver', 'South Africa')] cursor.prepare( 'insert into t1 values(:1,:2,:3)') cursor.executemany( None,list1)   #使用了prepare函数，在execute里面可以不传入sql语句，直接传入参数。注意：这里的第一个参数必须为None ora.commit() cursor.execute( 'select  *  from t1') while  1:     res = cursor.fetchone()      if res ==  None:          break      print(res) cursor.close() ora.close()

调用函数和存储过程

#调用存储过程 cursor.callproc(name,  parameters=[],  keywordParameters={})

#调用函数 cursor.callfunc(name, returnType,  parameters=[],  keywordParameters={}) #cx_Oracle.STRING

cx_Oracle、Python的对象类型之间存在转换关系

Oracle	cx_Oracle	Python
VARCHAR2, NVARCHAR2, LONG	cx_Oracle.STRING	str
CHAR	cx_Oracle.FIXED_CHAR	str
NUMBER	cx_Oracle.NUMBER	int
FLOAT	cx_Oracle.NUMBER	float
DATE	cx_Oracle.DATETIME	datetime.datetime
TIMESTAMP	cx_Oracle.TIMESTAMP	datetime.datetime
CLOB	cx_Oracle.CLOB	cx_Oracle.LOB
BLOB	cx_Oracle.BLOB	cx_Oracle.LOB

获取中文乱码

import os os.environ[ 'NLS_LANG'] =  'SIMPLIFIED CHINESE_CHINA.UTF8 ’ #或者os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2152558/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28572479/viewspace-2152558/