python 使用cx_oracle

python 模块方法

---

cx_oracle

#引入模块
import cx_Oracle
#创建连接
connects = cx_Oracle.connect('innpay/innpay@orcl')
#打开游标
cursors = connects.cursor()
#定义一条SQL
workdate_sql = '''select cdate from work_day t
where
--截止当天最后一个工作日最早的一个工作日
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')'''
#执行一条SQL
workdate_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.执行SQL
cursors.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()) ))