1、创建数据库连接

步骤：

创建数据库连接connect，创建游标cursor，然后执行sql语句，执行完成后，关闭游标，关闭数据库连接close；
插入、更新、删除操作后需要提交commit。

#!/usr/bin/python
#-– coding: UTF-8 ––

import cx_Oracle
import os
import re
os.environ[‘NLS_LANG’] = ‘SIMPLIFIED CHINESE_CHINA.UTF8’

2、插入操作（增）

def insertdata():
conn = cx_Oracle.connect(‘数据库名称/密码@数据库ip地址/ORCL’)
cur = conn.cursor()
sql = “insert into TEST123(XXNAME,XXCLASS,XXNUMBER,XXSCORE) VALUES(‘张三’,‘三年级’,‘3’,‘60’)”
try:
cur.execute(sql)
conn.commit() #这里一定要commit才行，要不然数据是不会插入的
print(“插入数据成功”)
except:
conn.rollback() #发生错误时回滚
print(“语句执行错误”)
conn.close()

3、删除操作（删）

def deldata():
conn = cx_Oracle.connect(‘数据库名称/密码@数据库ip地址/ORCL’)
cur = conn.cursor()
sql = “delete from TEST123 WHERE XXNAME = ‘%s’”%(‘张三’)
try:
cur.execute(sql)
conn.commit()
print(“数据删除成功”)
except:
conn.rollback() #发生错误时回滚
print(“语句执行错误”)

4、更新操作（改）

def updatedata():
conn = cx_Oracle.connect(‘数据库名称/密码@数据库ip地址/database’)
cur = conn.cursor()
sql = “update TEST123 set XXSCORE = XXSCORE+100 WHERE XXNAME = ‘%s’”%(‘马云’)
try:
cur.execute(sql)
conn.commit()
print(“数据更新成功”)
except:
conn.rollback() #发生错误时回滚
print(“语句执行错误”)
conn.close()

5、匹配

def data_compare(x,y):
for i in range(0, len(y)):
result = re.search(y[i], x).group(0)
if result == y[i]:
compare_result = 1 # 匹配成功则返回1
z = result
else:
compare_result = 0 # 匹配不成功则返回0
z = “未知”
return compare_result, z

6、查询操作（查）

def getdata(sql):
conn = cx_Oracle.connect(‘数据库名称/密码@数据库ip地址/ORCL’)
cur = conn.cursor()
#sql = “SELECT XXNAME,XXCLASS,XXNUMBER,XXSCORE FROM TEST123”
try:
cur.execute(sql)
data = cur.fetchall()
print(data)
except:
print(“数据查询失败”)
conn.close()

查询通常有两种方式：

一种是使用cursor.fetchall（）获取所有查询结果，然后再一行一行的迭代；另一种每次通过cursor.fetchone()获取一条记录，直到获取的结果为空为止。看下面的例子：
import cx_Oracle
conn = cx_Oracle.connect(‘数据库名称/密码@数据库ip地址/orcl’)
cursor = conn.cursor ()

cursor.execute (“SELECT * FROM TEST”)
rows = cursor.fetchall()
for row in rows:
print (“%d, %s, %s, %s” % (row[0], row[1], row[2], row[3]))
print (“Number of rows returned: %d” % cursor.rowcount)

cursor.execute (“SELECT * FROM TEST”)
while (1):
row = cursor.fetchone()
if row == None:
break
print (“%d, %s, %s, %s” % (row[0], row[1], row[2], row[3]))
print ( “Number of rows returned: %d” % cursor.rowcount)

cursor.close ()
conn.close ()