python 使用cx_oracle

python 模块方法

---

cx_oracle

#引入模块import cx_Oracle#创建连接connects = cx_Oracle.connect('innpay/innpay@orcl')#打开游标cursors = connects.cursor()#定义一条SQLworkdate_sql = '''select cdate from work_day twhere--截止当天最后一个工作日最早的一个工作日t.cdate >=(select max(cdate) from work_day t where t.work = 'Y' and t.cdate <(select min(cdate) from work_day t where t.work ='Y' and t.cdate >= '20161006'))--截止包含当天最后一个工作日and t.cdate < (select min(cdate) from work_day t where t.work ='Y' and t.cdate >= '20161006')'''#执行一条SQLworkdate_list_cursor = cursors.execute(workdate_sql)#将结果存入到本地元组all_rows = workdate_list_cursor.fetchall()#至此获取到列表值print all_rows[('20160930',), ('20161001',), ('20161002',), ('20161003',), ('20161004',), ('20161005',), ('20161006',), ('20161007',)]#插入数据1.定义可绑变量的SQL
Mtools_guocai_db_insert_sql ='''insert into YLJG_CHECK(
SERIAL_NO,ORDER_NO,CHANNEL_CODE,TRANSMSN_DATE_TIME,PAY_TYPE,
TRANS_AMT,REFUND_AMT,FEE_AMT,SETT_AMT,SEET_ARREARS_AMT)values(
:serial_no,:order_no,decode(:channel_code,'支付宝支付','gc_ali','微信支付','gc_wx','gc_unknow'),
:transmsn_date_time,decode(:pay_type,'支付宝支付','gc_ali','微信支付','gc_wx','gc_unknow')
:trans_amt,:refund_amt,:fee_amt,:sett_amt,:sett_arrears_amt)''' 2.绑定变量
bind_values = {'serial_no':row_values[0],'order_no':row_values[2],'channel_code':row_values[3].encode('utf8'),
'trans_amt':row_values[8],'refund_amt':row_values[9],'fee_amt':row_values[10],'sett_amt':row_values[11],
'transmsn_date_time':row_values[5],'pay_type':row_values[7].encode('utf-8'),
'sett_arrears_amt':row_values[12]}
3.执行SQLcursors.execute(Mtools_guocai_db_insert_sql,bind_values)#此处注意使用数据库连接的返回对象而非游标对象db.commit()

调用存储过程

存储过程注意入参可以使用python类型接收数据，但出参必须用cx_oracle数据类型.

• cx_oracle 数据类型
  
• demo
  proc_in_date = sett_date
proc_out_rst = cursors.var(cx_Oracle.NUMBER)
proc_out_msg = cursors.var(cx_Oracle.STRING)
logging.info('6.将国采和本地交易中被撤销的交易过滤掉')
returns = cursors.callproc('pkg_balance.pro_trans_gc_check1', (proc_in_date,proc_out_rst,proc_out_msg))
logging.info('6.国采交易与本地交易相互比对完成 %d:%s' %(proc_out_rst.getvalue(),changeCode(proc_out_msg.getvalue()) ))