数据库中cx_oracle怎么用使用
这篇文章给大家分享的是有关数据库中cx_oracle怎么用使用的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
连接
importcx_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
importcx_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
importcx_Oracle
ora = cx_Oracle.connect('scott/tiger@192.168.56.152:1521/orcl')
cursor = ora.cursor()
cursor.execute('select*from emp')
while1:
res = cursor.fetchone()
ifres ==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
while1:
res = cursor.fetchmany(10)
ifres == []:
break
print(res)
resCount +=10
cursor.close()
ora.close()
#使用绑定变量
importcx_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()
增、删、改 数据和多次执行
importcx_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')
while1:
res = cursor.fetchone()
ifres ==None:
break
print(res)
cursor.close()
ora.close()
importcx_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')
while1:
res = cursor.fetchone()
ifres ==None:
break
print(res)
cursor.close()
ora.close()
importcx_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')
while1:
res = cursor.fetchone()
ifres ==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的对象类型之间存在转换关系
Oraclecx_OraclePythonVARCHAR2, NVARCHAR2, LONGcx_Oracle.STRINGstrCHARcx_Oracle.FIXED_CHARstrNUMBERcx_Oracle.NUMBERintFLOATcx_Oracle.NUMBERfloatDATEcx_Oracle.DATETIMEdatetime.datetimeTIMESTAMPcx_Oracle.TIMESTAMPdatetime.datetimeCLOBcx_Oracle.CLOBcx_Oracle.LOBBLOBcx_Oracle.BLOBcx_Oracle.LOB获取中文乱码
importos
os.environ['NLS_LANG'] ='SIMPLIFIED CHINESE_CHINA.UTF8’
#或者os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
感谢各位的阅读!关于“数据库中cx_oracle怎么用使用”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。