您好,登錄后才能下訂單哦!
這篇文章給大家分享的是有關(guān)數(shù)據(jù)庫(kù)中cx_oracle怎么用使用的內(nèi)容。小編覺(jué)得挺實(shí)用的,因此分享給大家做個(gè)參考,一起跟隨小編過(guò)來(lái)看看吧。
連接
import cx_Oracle #使用tnsnames文件別名鏈接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串,傳入一個(gè)參數(shù)鏈接 # 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字符串,連接數(shù)據(jù)庫(kù) # 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() #使用位置對(duì)應(yīng)參數(shù) 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字符串,連接數(shù)據(jù)庫(kù) 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() #使用位置對(duì)應(yīng)參數(shù) cursor.execute('select * from t1 where DEPTNO = :1',(10,)) print(cursor.fetchall()) #使用字典傳入?yún)?shù) 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() |
增、刪、改 數(shù)據(jù)和多次執(zhí)行
import cx_Oracle #使用tnsnames文件別名鏈接 # ora = cx_Oracle.connect('scott/tiger@orcl') #使用字符串,傳入一個(gè)參數(shù)鏈接 # 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字符串,連接數(shù)據(jù)庫(kù) 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中使用參數(shù) 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') #使用字符串,傳入一個(gè)參數(shù)鏈接 # 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字符串,連接數(shù)據(jù)庫(kù) 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函數(shù),在execute里面可以不傳入sql語(yǔ)句,直接傳入?yún)?shù)。注意:這里的第一個(gè)參數(shù)必須為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') #使用字符串,傳入一個(gè)參數(shù)鏈接 # 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字符串,連接數(shù)據(jù)庫(kù) 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() #執(zhí)行多條語(yǔ)句 list1 = [(60,'Enginer','Sydney'),(70,'Diver','South Africa')] cursor.prepare('insert into t1 values(:1,:2,:3)') cursor.executemany(None,list1) #使用了prepare函數(shù),在execute里面可以不傳入sql語(yǔ)句,直接傳入?yún)?shù)。注意:這里的第一個(gè)參數(shù)必須為None ora.commit() cursor.execute('select * from t1') while 1: res = cursor.fetchone() if res == None: break print(res) cursor.close() ora.close() |
調(diào)用函數(shù)和存儲(chǔ)過(guò)程
#調(diào)用存儲(chǔ)過(guò)程 cursor.callproc(name, parameters=[], keywordParameters={}) |
#調(diào)用函數(shù) cursor.callfunc(name, returnType, parameters=[], keywordParameters={}) #cx_Oracle.STRING |
cx_Oracle、Python的對(duì)象類型之間存在轉(zhuǎn)換關(guān)系
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' |
感謝各位的閱讀!關(guān)于“數(shù)據(jù)庫(kù)中cx_oracle怎么用使用”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,讓大家可以學(xué)到更多知識(shí),如果覺(jué)得文章不錯(cuò),可以把它分享出去讓更多的人看到吧!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。