Python连接Oracle数据库实现增删改查
数据库原表:
查询操作(查):
#查询操作(查)
def getdata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
cur = conn.cursor()
sql = "SELECT XXNAME,XXCLASS,XXNUMBER,XXSCORE FROM TEST123"
try:
cur.execute(sql)
data = cur.fetchall()
print(data)
except:
print("数据查询失败")
conn.close()
结果:
[('马云', '一年级', 1, 100), ('马风', '二年级', 1, 101), ('马雨', '三年级', 1, 99), ('马电', '五年级', 1, 88)]
插入操作(增)
#插入操作(增)
def insertdata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
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()
结果:
插入数据成功
删除操作(删)
#删除操作(删)
def deldata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
cur = conn.cursor()
sql = "delete from TEST123 WHERE XXNAME = '%s'"%('张三')
try:
cur.execute(sql)
conn.commit()
print("数据删除成功")
except:
conn.rollback() #发生错误时回滚
print("语句执行错误")
conn.close()
结果:
数据删除成功
更新操作(改)
#更新操作(改)
def updatedata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/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()
结果:
数据更新成功
完整代码如下:
# -*- coding: UTF-8 -*-
#Python连接Oracle数据库实现增删改查
import cx_Oracle
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
#查询操作(查)
def getdata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
cur = conn.cursor()
sql = "SELECT XXNAME,XXCLASS,XXNUMBER,XXSCORE FROM TEST123"
try:
cur.execute(sql)
data = cur.fetchall()
print(data)
except:
print("数据查询失败")
conn.close()
#插入操作(增)
def insertdata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
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()
#删除操作(删)
def deldata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/database')
cur = conn.cursor()
sql = "delete from TEST123 WHERE XXNAME = '%s'"%('张三')
try:
cur.execute(sql)
conn.commit()
print("数据删除成功")
except:
conn.rollback() #发生错误时回滚
print("语句执行错误")
conn.close()
#更新操作(改)
def updatedata():
conn = cx_Oracle.connect('abc/abc@192.168.91.9/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()
if __name__ == '__main__':
getdata()
# insertdata()
# deldata()
# updatedata()