def getCompanys():
comStr = 'select * from zf_companys'
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateData(content,cloumns) # 调用formateData()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def getHasRoleIdLis(companyId: int):
comStr = 'select role_id from zf_ukey where ownercompanynum = :companyId' # 查询字符串 select ukey_id from zf_ukey order by ukey_id
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr, (companyId, )) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateUkeyIds(content,
cloumns) # 调用formateUkeyIds()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def getUkeysBycompanyId(companyId: int = None): # 正在使用的Ukey
if companyId != None:
comStr = 'select u.ukey_id,u.ownername,u.ownermobile,u.ownercarnum,u.usetime,u.unusetime,u.isuse,u.isdestroy,r.r_name,c.compname from zf_companys c,zf_role r, zf_ukey u where u.ownercompanynum = :companyId and u.ownercompanynum = c.compid and u.role_id = r.r_id and u.isuse = 1 and u.isdestroy =0 order by u.ukey_id'
paramers = {'companyId': companyId}
else:
comStr = 'select u.ukey_id,u.ownername,u.ownermobile,u.ownercarnum,u.usetime,u.unusetime,u.isuse,u.isdestroy,r.r_name,c.compname from zf_companys c,zf_role r, zf_ukey u where u.ownercompanynum = c.compid and u.role_id = r.r_id and u.isuse = 1 and u.isdestroy =0 order by u.ukey_id' # 查询字符串
paramers = {}
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr, paramers) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateData(content,
cloumns) # 调用formateData()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
paramers = {
'companyNum': recordObj.companyNum,
'roleNum': recordObj.roleNum,
'ukeyNum': recordObj.ukeyNum,
'actionNum': recordObj.actionNum,
'propName': recordObj.proposerName,
'applicontent': recordObj.applicationContent,
'createTime': recordObj.createTime
}
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
if __name__ == "__main__":
time = ToolsHelp.getCurrentTime()
# res = insertUkeyRecord(1,2,4002,'李四','ukey遗失,重新补办',time,'李四')
res = getUkeyRecords()
print(res)