这篇文章给大家分享的是有关数据库中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' |
感谢各位的阅读！关于“数据库中cx_oracle怎么用使用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
