Python连接oracle数据库的基本操作

Python连接oracle数据库的基本操作 - 周资源

zhouziyuan 2021-11-01 原文

Python连接oracle数据库的基本操作

Python连接oracle数据库

1，数据库连接和关闭

 1 # 引入cx_Oracle模块
 2 import cx_Oracle
 3 
 4 \'\'\'
 5 方法一：用户名、密码和监听分开写
 6 \'\'\'
 7 db = cx_Oracle.connect(\'username/password@host/orcl\')
 8 
 9 \'\'\'
10 方法二：用户名、密码和监听写在一起
11 \'\'\'
12 db = cx_Oracle.connect(\'username\',\'password\',\'host/orcl\')
13 
14 \'\'\'
15 方法三：配置监听并连接
16 \'\'\'
17 tns = cx_Oracle.makedsn(\'host\',1521,\'orcl\')
18 db = cx_Oracle.connect(\'username\',\'password\',tns)

2，数据库增删改查操作

　建立cursor并执行SQL语句：查询、更新、插入、删除，执行完成后，关闭游标，关闭数据库连接

　　1，数据库查询操作

 1 # 导入cx_Oracle模块
 2 import cx_Oracle as cx
 3 
 4 # 连接数据库
 5 db = cx.connect(\'username/password@host/orcl\')
 6 
 7 # 创建光标
 8 cur = db.cursor()
 9 
10 \'\'\'
11     无需传递参数
12 \'\'\'
13 sql = \'\'\'SELECT * FROM TABLE\'\'\'
14 
15 # 执行sql语句
16 cur.execute(sql)
17 
18 \'\'\'
19     需要传递参数
20 \'\'\'
21 
22 # 字典只有一个键值对
23 pm = {\'id\': 1}
24 cur.execute(\'SELECT * FROM TABLE WHERE ID = :id\', pm)
25 
26 # 字典有多个键值对
27 pa = {\'id\': 1, \'name\': \'卡卡\'}
28 cur.execute(\'SELECT * FROM TABLE WHERE ID = :id AND NAME = :name\', pa)
29 
30 # 名字参数
31 cur.execute(\'\'\'SELECT * FROM TABLE WHERE ID = :id\'\'\', id = 1)
32 
33 cur.execute(\'\'\'SELECT * FROM TABLE WHERE ID = :id AND NAME = :name\'\'\', id = 1, name = \'卡卡\')
34 
35 # 位置参数,数据为元组，只有一个参数时，需加逗号
36 cur.execute(\'\'\'SELECT * FROM TABLE WHERE ID = :a\'\'\', (1,))
37 
38 cur.execute(\'\'\'SELECT * FROM TABLE WHERE ID = :a AND NAME = :b\'\'\', (1, \'卡卡\'))
39 
40 # cur.Prepare的用法,prepare之后，你再去execute的时候，就不用写上sql语句参数了
41 # 传递参数的方式与以上方式相同
42 
43 cur.prepare(\'\'\'SELECT * FROM TABLE WHERE ID = :id\'\'\')
44 
45 # 注意，第一个参数是None
46 cur.execute(None, {\'id\': 1})
47 
48 c_id = 1
49 c_name = "卡卡"
50 
51 # %格式化字符串，%s格式化的字符串不带引号，%r格式化的字符串带单引号
52 sql = \'\'\'SELECT * FROM TABLE WHERE ID = %s\'\'\' % c_id
53 
54 sql = \'\'\'SELECT * FROM TABLE WHERE ID = %s AND NAME = %r\'\'\' % (c_id, c_name)
55 
56 # format格式化字符串
57 sql = \'\'\'SELECT * FROM TABLE WHERE ID = {} \'\'\'.format(c_id)
58 
59 sql = \'\'\'SELECT * FROM TABLE WHERE ID = {0} AND NAME = \'{1}\' \'\'\'.format(c_id, c_name)
60 
61 # 执行sql
62 cur.execute(sql)
63 
64 \'\'\'
65     一次性返回所有的结果集(fetchall)
66 \'\'\'
67 rs = cur.fetchall()    
68 print(rs)
69 for r in rs:   
70     print(r)   
71 
72 \'\'\'
73     一次返回一行(fetchone)
74 \'\'\'
75 while(1):
76     rs = cur.fetchone()    
77     if rs == None:        
78         break    
79     print(rs)
80     
81 # 关闭光标
82 cur.close()
83 
84 # 数据库断开连接
85 db.close()

　　2，插入、更新、删除操作后需要提交commit

 1 # 导入cx_Oracle模块
 2 import cx_Oracle as cx
 3 
 4 # 连接数据库
 5 db = cx.connect(\'username/password@host/orcl\')
 6 
 7 # 创建光标
 8 cur = db.cursor()
 9 
10 \'\'\'
11 INSERT
12 \'\'\'
13 sql = \'INSERT INTO TABLE(ID) VALUES(1)\'
14 
15 \'\'\'
16 UPDATE
17 \'\'\'
18 sql = \'UPDATE TABLE  SET ID = 2\'
19 
20 \'\'\'
21 DELETE
22 \'\'\'
23 sql = \'DELETE FROM TABLE  WHERE ID = 2\'
24 
25 # 执行sql
26 cur.execute(sql)
27 
28 # 关闭光标
29 cur.close()
30 
31 # 数据库提交
32 db.commit()
33 
34 # 关闭数据库
35 db.close()