Python连接oracle数据库的基本操作1，数据库连接和关闭
1# 引⼊cx_Oracle模块
2import cx_Oracle
3
4'''
5⽅法⼀：⽤户名、密码和监听分开写
6'''
7 db = t('username/password@host/orcl')
8
9'''
10⽅法⼆：⽤户名、密码和监听写在⼀起
11'''
12 db = t('username','password','host/orcl')
13
14'''
15⽅法三：配置监听并连接
16'''
17 tns = cx_Oracle.makedsn('host',1521,'orcl')
18 db = t('username','password',tns)
2，数据库增删改查操作
　建⽴cursor并执⾏SQL语句：查询、更新、插⼊、删除，执⾏完成后，关闭游标，关闭数据库连接
  1，数据库查询操作
1# 导⼊cx_Oracle模块
2import 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语句
ute(sql)
17
18'''
19需要传递参数
20'''
21
22# 字典只有⼀个键值对
23 pm = {'id': 1}
ute('SELECT * FROM TABLE WHERE ID = :id', pm)
25
26# 字典有多个键值对
27 pa = {'id': 1, 'name': '卡卡'}
ute('SELECT * FROM TABLE WHERE ID = :id AND NAME = :name', pa)
29
30# 名字参数
ute('''SELECT * FROM TABLE WHERE ID = :id''', id = 1)
32
ute('''SELECT * FROM TABLE WHERE ID = :id AND NAME = :name''', id = 1, name = '卡卡')
34
35# 位置参数，数据为元组，只有⼀个参数时，需加逗号
ute('''SELECT * FROM TABLE WHERE ID = :a''', (1,))
37
ute('''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
ute(None, {'id': 1})
oracle建立数据库连接
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
ute(sql)
63
64'''
65⼀次性返回所有的结果集(fetchall)
66'''
67 rs = cur.fetchall()
68print(rs)
69for r in rs:
70print(r)
71
72'''
73⼀次返回⼀⾏(fetchone)
74'''
75while(1):
76    rs = cur.fetchone()
77if rs == None:
78break
79print(rs)
80
81# 关闭光标
82 cur.close()
83
84# 数据库断开连接
85 db.close()
  2，插⼊、更新、删除操作后需要提交commit
1# 导⼊cx_Oracle模块
2import cx_Oracle as cx
3
4# 连接数据库
5 db = cx.connect('username/password@host/orcl')
6
7# 创建光标
8 cur = db.cursor()
9
10'''
11INSERT
12'''
13 sql = 'INSERT INTO TABLE(ID) VALUES(1)'
14
15'''
16UPDATE
17'''
18 sql = 'UPDATE TABLE  SET ID = 2'
19
20'''
21DELETE
22'''
23 sql = 'DELETE FROM TABLE  WHERE ID = 2'
24
25# 执⾏sql
ute(sql)
27
28# 关闭光标
29 cur.close()
30
31# 数据库提交
32 dbmit()
33
34# 关闭数据库
35 db.close()

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。