python oracle数据库操作_Python 封装 Oracle 数据库操作（使用类封装基本的增删改查）...

前面2篇看了SQLite和Mysql数据库的封装，这篇看下Oracle的封装.

Python 封装 SQLite 数据库操作（使用类封装基本的增删改查）

Python 封装 MySQL 数据库操作（使用类封装基本的增删改查）

# -*- coding: utf-8 -*-

import cx_Oracle

import datetime

class MyOracle:

SHOW_SQL = True

def __init__(self, host='127.0.0.1', port=1521, user='system', password='oracle', sid='cndba'):

self.host = host

self.port = port

self.user = user

self.password = password

self.sid = sid

def get_con(self):

try:

dsn_tns = cx_Oracle.makedsn(self.host, self.port, self.sid)

# 如果是Oracle 12c 数据库需要替换sid 为service_name

dsn_tns = dsn_tns.replace('SID', 'SERVICE_NAME')

conn = cx_Oracle.connect(self.user, self.password, dsn_tns)

return conn

except cx_Oracle.Error, e:

print "cx_Oracle Error:%s" % e

def select_all(self, sql):

try:

con = self.get_con()

# print con

cur = con.cursor()

cur.execute(sql)

fc = cur.fetchall()

return fc

except cx_Oracle.Error, e:

print "cx_Oracle Error:%s" % e

finally:

cur.close()

con.close()

def select_by_where(self, sql, data):

try:

con = self.get_con()

# print con

d = (data,)

cur = con.cursor()

cur.execute(sql, d)

fc = cur.fetchall()

# if len(fc) > 0:

# for e in range(len(fc)):

# print(fc[e])

return fc

except cx_Oracle.Error, e:

print "cx_Oracle Error:%s" % e

finally:

cur.close()

con.close()

def dml_by_where(self, sql, params):

try:

con = self.get_con()

cur = con.cursor()

for d in params:

if self.SHOW_SQL:

print('执行sql:[{}],参数:[{}]'.format(sql, d))

cur.execute(sql, d)

con.commit()

except cx_Oracle.Error, e:

con.rollback()

print "cx_Oracle Error:%s" % e

finally:

cur.close()

con.close()

# 不带参数的更新方法

def dml_nowhere(self, sql):

try:

con = self.get_con()

cur = con.cursor()

count = cur.execute(sql)

con.commit()

return count

except cx_Oracle.Error, e:

con.rollback()

print "cx_Oracle Error:%s" % e

finally:

cur.close()

con.close()

# 开始测试函数

def select_all():

sql = "select * from dave"

fc = db.select_all(sql)

for row in fc:

print row

def select_by_where():

sql = "select * from dave where USER_ID=:1"

data = 0551

fc = db.select_by_where(sql, data)

for row in fc:

print row

def ins_by_param():

sql = "insert into dave(USERNAME,USER_ID,CREATED) values(:1,:2,:3)"

date = datetime.datetime.now()

data = [('http://www.cndba.cn', 0551, date), ('http://www.cndba.cn/dave', 0556, date)]

db.dml_by_where(sql, data)

def del_by_where():

sql = "delete from dave where USERNAME = :1 and USER_ID=:2"

data = [('HR', 107)]

db.dml_by_where(sql, data)

def update_by_where():

sql = "update dave set USER_ID=:1 where USER_ID=:2"

data = [(0551, 0556)]

db.dml_by_where(sql, data)

def del_nowhere():

sql = "delete from dave"

print db.dml_nowhere(sql)

if __name__ == "__main__":

db = MyOracle( '192.168.1.4', port=1521, user='system', password='www.cndba.cn', sid='cndba')

# ins_by_param()

# del_by_where()

# update_by_where()

# del_nowhere()

# select_all()

# del_by_where()

# ins_by_param()

del_nowhere()

select_by_where()

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

https://www.cndba.cn/dave/article/2172

版权声明：本文为博主原创文章，未经博主允许不得转载。