连接oracle服务地址,使用servicename连接Oracle数据库

importjsonimportloggerimportcx_Oracle as oracleimporttracebackfrom config importoracle_configclassDatabaseAdapter:

orahost=None

oraport= 1521oraservicename=None

orauser=None

orapassword=None

connectionstr=None

conn=None

cursor=Nonedef __init__(self):'''初始化，从配置文件读取服务器信息'''

try:

self.orahost= oracle_config['host']

self.oraport= oracle_config['port']

self.orauser= oracle_config['user']

self.orapassword= oracle_config['password']

self.oraservicename= oracle_config['servicename']except:

logger.writeLog("读取数据库配置文件失败!")deforaconnect(self):'''连接数据库方法'''self.connectionstr= "%s/%s@%s/%s"%(self.orauser, self.orapassword, self.orahost, self.oraservicename)try:

self.conn= oracle.connect(self.connectionstr,encoding="UTF-8")

self.cursor=self.conn.cursor()returnself.conn,self.cursorexcept:

errstr=traceback.format_exc()

logger.writeLog("Oracle数据库连接错误:" +errstr)definsert(self, sqlstr, para):'''数据库插入

需要采用绑定变量的方式进行，否则会有安全问题'''

#para = { dept_id=280, dept_name="Facility" }

#cursor.execute("""

#insert into departments (department_id, department_name)

#values (:dept_id, :dept_name)""", data)

try:ifself.cursor:

self.cursor.execute(sqlstr, para)

self.conn.commit()else:#进行重连

logger.writeLog("Oracle数据库尝试重新连接", "insertfail.log")

self.oraconnect()ifself.cursor:

self.cursor.execute(sqlstr, para)

self.conn.commit()else:

logger.writeLog("Oracle数据库重连插入失败:" + sqlstr + json.dumps(para), "insertfail.log")except:

errstr=traceback.format_exc()

logger.writeLog("Oracle数据库插入失败:" + errstr + sqlstr + json.dumps(para), "insertfail.log")def search(self, sqlstr, para=None):'''数据库查询'''

try:if para ==None:

self.cursor.execute(sqlstr)else:

self.cursor.execute(sqlstr, para)

rows=self.cursor.fetchall()returnrowsexcept:

logger.writeLog("Oracle数据库查询失败:" +sqlstr)returnFalsedefcloseconn(self):'''关闭数据库连接'''

try:

self.cursor.close()

self.conn.close()except:

errstr=traceback.format_exc()

logger.writeLog("Oracle数据库连接关闭错误:" +errstr)if __name__ == "__main__":

db=DatabaseAdapter()

conn, cur=db.oraconnect()print(conn, cur)#测试验证

#jsonobj = {'ESN': 76511706, 'FuelVolumeTotal': 0.57263308763504, 'DEFVolumeTotal': 0.0235559437423944,

#'OccurrenceTime': '2020-2-28 9:0:54'}

#sqlstr = """

#insert into flxuser2.BASE_00059_3(ESN, FuelVolumeTotal, DEFVolumeTotal, OccurrenceTime)

#values (:ESN, :FuelVolumeTotal, :DEFVolumeTotal, to_date(:OccurrenceTime, 'YYYY-MM-DD HH24:MI:SS'))

#"""

#parameters = {'ESN': jsonobj['ESN'],

#'FuelVolumeTotal': jsonobj['FuelVolumeTotal'],

#'DEFVolumeTotal': jsonobj['DEFVolumeTotal'],

#'OccurrenceTime': jsonobj['OccurrenceTime']

#}

sqlstr= """select * from user1.BASE_01"""ret=db.search(sqlstr)print(ret)