連線

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中使用引數

()

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

()

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

()

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 使用

相關文章