使⽤cx_Oracle实现数据库操作
cx_Oracle 是⼀个Python的扩展模块，通过使⽤所有数据库访问的模块通⽤的数据库API来是实现Oracle数据库的查询和更新。
cx_Oracle的开发历时⼗多年，涵盖了⼤多数需要在Python中访问Oracle的客户的需求。2008年12⽉，⼀个新的主要版本解决了早期版本的众多限制，并增添了对Python3.0和Oracle新推进的⼀些特性的⽀持。
下⾯写⼀个是简单的转账例⼦，附上代码
配置⼯作(Oracle.settings.py)
1 # -*- coding: utf-8 -*-
2
3 import cx_Oracle
4
5
6 ##操作Oracle数据库
7class Oracle:
8 #连接Oracle数据库
9 def oracle_Conn(self,oracle_user,oracle_password,oracle_ip,oracle_port,oracle_service_name):
10 print('正在连接Oracle数据库.......')
11global conn
12 # ⽤户，密码，oracle数据库服务名称(或数据库系统标识符SID)
13 conn = t(oracle_user, oracle_password, '%s:%s/%s'%(oracle_ip,oracle_port,oracle_service_name),encoding = 'UTF-8')
14
15
16 #传进sql查询语句
17 def oracle_DQL(self,DQL_sql,params=[],rownums=0,fetchmany=False,fetchall=False):
18 #建⽴游标
19 # 此代码确保⼀旦完成该块，就将游标关闭并且数据库已回收资源。此外，任何cursor在块外使⽤变量的尝试都将失败
20 with conn.cursor(scrollable=True) as cur:
21 ute(DQL_sql,params)
22if fetchall == True:
23 query_res = cur.fetchall()
24 elif fetchmany == True:
25 query_res = cur.fetchmany(rownums)
26else:
27if rownums != 0:
28 cur.scroll(rownums)
29 query_res = cur.fetchone()
30return query_res
31
32 ##创建表，DDL语句，不⽤提交
33 def oracle_DDL(self,DDL_sql,bind_params=[]):
34 with conn.cursor() as cur:
35 res = ute(DDL_sql,bind_params)
36return'suc'
37
38 #数据操纵语句,insert,update,delete等
39 def oracle_DML(self,DML_SQL,bind_params=[]):
40 with conn.cursor() as cur:
41 res = ute(DML_SQL,bind_params)
42 connmit()
43return'suc'
44
45 def oracle_Conn_Close(self):
46 conn.close()
47 print('Oracle数据库连接已经关闭.......')
48
49'''
50 obj = Oracle()
acle_Conn('back','123','127.0.0.1','1521','orcl')
52 res = acle_DQL('select * from students')
53 print(res)
acle_Conn_Close()
55'''
56
57'''
58注意：对于回收站的对象，只⽀持查询操作，其他DDL，DML操作均不⽀持。
59 DQL：数据查询语句，查询操作，默认隐式提交
60 DML：数据操纵语句 insert，update，delete (Manipulation) connmit()
61 DCL：数据控制语⾔ grant rollback
62⽤户授权或者访问数据库的某种特权，并控制数据库事务发⽣的时间和效果
63 DDL：数据定义语⾔:create 创建数据库各种对象（隐式提交，不能rollback）
64'''
View Code
创建表(transaction_record_table,bankcard_table,alipay_table)
acle_settings import Oracle
2
3 ##连接oracle数据库
4 obj = Oracle()
acle_Conn('back','123','127.0.0.1','1521','orcl')
6
7 #--------------------------------------------------------------------------------------------------------------------
8 ##创建表
9 sql = '''
10 create table transaction_record_table
11 (
12 order_number varchar2(50) not null,
13 transcation_amount number not null,
14 transaction_event varchar2(50) not null,
15 trader varchar2(50) not null,
16 Payee varchar2(50) not null,
17 transaction_time timestamp,
18 status varchar2(20),
19 primary key(order_number)
20 )
21'''
22 ##创建transaction_record表
acle_DDL(sql)
24
acle_DDL(sql)
26 #--------------------------------------------------------------------------------------------------------------------
27
28 sql = '''
29 create table Bankcard_table
30 (
31 bankcard_bid number,
32 owner_name varchar2(20) not null,
33 owner_idcard varchar2(20) not null unique,
34 bankcard_amount number not null check(bankcard_amount>0),
35 lock_status varchar(10) default'False' not null,
36 primary key(Bankcard_bid)
37 )
38'''
39 ##Bankcard
acle_DDL(sql)
41
42
43 sql = '''
44 create table alipay_table
45 (
46 alipay_bid number,
47 owner_name varchar2(20) not null,
48 owner_idcard varchar2(20) not null unique,
49 alipay_amount number(5) not null check(alipay_amount>0 and alipay_amount<100000),
50 lock_status varchar(10) default'False' not null,
51 primary key(alipay_bid)
52 )
53'''
54 ##alipay
acle_DDL(sql)
56 #--------------------------------------------------------------------------------------------------------------------
57
acle_Conn_Close()
View Code
插⼊数据(bankcard_table,alipay_table) 可逐⾏插⼊
acle_settings import Oracle
2
3 ##连接oracle数据库
4 obj = Oracle()
acle_Conn('back','123','127.0.0.1','1521','orcl')
6
7
8 ##插⼊数据
9 #BANKCARD_TABLE
10 insert_sql = """
11 INSERT INTO ALIPAY_TABLE (ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (1, '张三', '5456567687787878', 100,'False');
oracle游标的使用12 INSERT INTO ALIPAY_TABLE(ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (2, '⼩五', '545467676767676', 300,'False');
13 INSERT INTO ALIPAY_TABLE(ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (3, '⼩何', '6676768778787878', 40000,'False');
14 INSERT INTO ALIPAY_TABLE(ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (4, '⼩李', '54545465676767676', 3000,'False');
15 INSERT INTO ALIPAY_TABLE(ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (5, '⼩⿊', '4545465656565656', 10,'False');
16 INSERT INTO ALIPAY_TABLE(ALIPAY_BID, OWNER_NAME, OWNER_IDCARD, ALIPAY_AMOUNT,LOCK_STATUS) VALUES (6, '⼆虎', '4566567676878787', 5666,'False'); 17"""
18 #BANKCARD_TABLE
acle_DML(insert_sql)
20
21
22 ##alipay_table
23 insert_sql = '''
24 insert into BANKCARD_TABLE (BANKCARD_BID, OWNER_NAME, OWNER_IDCARD, BANKCARD_AMOUNT,LOCK_STATUS) values (1, '张三', '5456567687787878', 3000,'False');
25 insert into BANKCARD_TABLE (BANKCARD_BID, OWNER_NAME, OWNER_IDCARD, BANKCARD_AMOUNT,LOCK_STATUS) values (2, '⼩⿊', '4545465656565656', 10000,'False');
26 insert into BANKCARD_TABLE (BANKCARD_BID, OWNER_NAME, OWNER_IDCARD, BANKCARD_AMOUNT,LOCK_STATUS) values (3, '⼩明', '5454546565767676', 500,'False');
27 insert into BANKCARD_TABLE (BANKCARD_BID, OWNER_NAME, OWNER_IDCARD, BANKCARD_AMOUNT,LOCK_STATUS) values (4, '⼩李', '54545465676767676', 3000,'False');
28 insert into BANKCARD_TABLE (BANKCARD_BID, OWNER_NAME, OWNER_IDCARD, BANKCARD_AMOUNT,LOCK_STATUS) values (5, '⼩五', '545467676767676', 1000,'False'); 29'''
acle_DML(insert_sql)
acle_Conn_Close()
View Code
Alipay_table表
Bankcard_table表
Oracle_case
acle_settings import Oracle
2 import random,string
3
4
5class Transaction:
6 #银⾏卡到alipay的请求
7 def bankToAlipay(self,tran_request):
8 ##连接oracle数据库
9 obj = Oracle()
10 acle_Conn('back','123','127.0.0.1','1521','orcl')
11
12
13 #需要取的数据 >> 银⾏卡⾦额(bankcard_amount) , lock_status
14 #绑定变量
15params = {'idcard':tran_request[0]}
16 lock_sql = 'select bankcard_amount,lock_status from BANKCARD_TABLE where owner_idcard = :idcard'
17 res = acle_DQL(lock_sql,params)
18
19 #如果没有被交易的话，则可以进⾏交易，同时需要判断银⾏卡总⾦额是否⼤于所需要交易的⾦额
20if res[1] == 'False' and res[0] >= tran_request[-1]:
21 print('----------------------------------------------------------------------------------------')
22
23 ##⾸先判断此随机⽣成的订单编号是否存在，如果存在，则重新⽣成
24for index in range(5):
25 order_number = str(random.randint(1000, 10000)) + string.printable[:16]
26params = {'order_number_value':order_number}
27 query_sql = 'select * from transaction_record_table where order_number =:order_number_value'
28
29 query_res = acle_DQL(query_sql,params)
30if query_res == None:
31break
32
33 print('正在处理⽤户银⾏卡-alipay的交易[交易编号:%s]........'%order_number)
34
35 lasted_amount = res[0] - tran_request[-1]
36 update_params = {'a':'True','b':lasted_amount,'c':tran_request[0]}
37 print(update_params)
38
39 #此时更新状态为True，原账户需要更改⾦额
40 update_sql = 'update BANKCARD_TABLE set lock_status = :a ,bankcard_amount = :b where owner_idcard = :c'
41 result = acle_DML(update_sql,update_params)
42
43
44 ##如果处理成功，则另⼀个表alipay的⾦额要更改，⽽且需要写⼊到交易记录表记录下来
45if result == 'suc':
46 #需要获取alipay_table的⾦额数量
47 target_params = [tran_request[1]]
48 sql = 'select alipay_amount from alipay_table where owner_idcard=:1'
49 r = acle_DQL(sql,target_params)
50
51 ##如果没有到此⼈的话
52if r == []:
53 print('没有到此⽤户.....')
54 exit()
55 target_amount = tran_request[-1] + r[0]
56
57 ##⽬标账户需要更改⾦额
58params = [target_amount,tran_request[1]]
59 alipay_update_sql = 'update alipay_table set alipay_amount = :1 where owner_idcard = :2'
60 result = acle_DML(alipay_update_sql,params)
61
62
63 ##如果处理成功，则写⼊交易记录表
64if result != 'suc':
65 print('交易处理失败....')
66 exit()
67 #获取当前时间
68 sql = 'select systimestamp from dual'
69 current_time = acle_DQL(sql)[0]
70
71
72 ##
73params = [order_number,tran_request[-1],'银⾏卡 to alipay',tran_request[0],tran_request[1],current_time,'suc']
74 insert_sql = 'insert into transaction_record_table values(:1,:2,:3,:4,:5,:6,:7)'
75 result = acle_DML(insert_sql,params)
76
77 ##最后需要更改状态为False
78if result == 'suc':
79params = ['False',tran_request[0]]
80 sql = 'update BANKCARD_TABLE set lock_status = :1 where owner_idcard = :2'
81 acle_DML(sql,params)
82 acle_Conn_Close()
83 print('处理该⽤户交易[交易编号:%s]成功.....'%order_number)
84 print('----------------------------------------------------------------------------------------')
85return'suc'
86else:
87 print('交易失败')
88return'fail'
89else:
90 print('该⽤户银⾏卡交易已在进⾏，不可再处理，请等待.........')
91return'正在繁忙...'
92
93
94
95 #source_idcard, target_idcard source target（银⾏卡⽀付宝）amount
96
97 #⼩明 > ⼩⿊
98 #tran_request = ['5454546565767676','4545465656565656','银⾏卡','alipay',100]
99 #张三 > ⼆虎
100 tran_request = ['5456567687787878','4566567676878787','银⾏卡','alipay',200]
101 result = Transaction().bankToAlipay(tran_request)
102 print(result)
View Code
交易请求
tran_request = ['5456567687787878','4566567676878787','银⾏卡','alipay',200]
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论