欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Python操作Oracle数据库:cx_Oracle

程序员文章站 2022-05-17 09:29:57
在前面的博客中已经介绍过如何使用Python来操作MySQL数据库,最近需要将一批数据从csv文件中迁移到Oracle数据库中,也打算用Python来实现,趁着这个机会,也写一篇博客学习总结一些如何使用Python来操作Oracle数据库。 ......

 

在中已经介绍过如何使用python来操作mysql数据库,最近需要将一批数据从csv文件中迁移到oracle数据库中,也打算用python来实现,趁着这个机会,也写一篇博客学习总结一些如何使用python来操作oracle数据库。

 

1 安装与导入

 

python操作oracle数据库多用cx_oracle这个第三方扩展,总体而言,cx_oracle的使用方式与python操作mysql数据库的pymysql库还是很相似的,如果还没有安装,可以通过下面的命令进行安装:
$ pip install -i https://pypi.douban.com/simple cx_oracle 使用前导入:

in [1]:
import cx_oracle
 

千万注意,包名称cx_oracle中,字母“o”是大写的,写成小写将会导入失败。
这里再附带多说一点,我在安装好cx_oracle第一次使用时,出现这个异常:databaseerror: dpi-1047,可以按照官方的思路解决:

2 创建连接

cx_oracle提供了两种方式连接oracle数据库,分别是创建独立的单一连接以及创建连接池。

2.1 单一连接

创建单一连接主要是通过cx_oracle模块中提供的connect()方法实现,虽然也可以直接通过connection()类实现,但是不推荐。connect()方法参数有很多,说说其中最常用的四个:

  • user:用户名
  • password:密码
  • dsn:数据库地址和服务名
  • encoding:编码,合适的编码可以避免出现乱码
    这里要重点说一下dsn,dsn是data source name的缩写,用于描述数据源的详细地址,一般由数据库所在主机地址、端口和服务名组成。在默认情况下,oracle数据库对应1521端口,在这种情况下,dsn中可以省略端口:
in [3]:
connection = cx_oracle.connect("username", "password", "192.168.1.2/helowin", encoding="utf-8")
 

其中,username是用户名,password是密码,192.168.1.2是数据库所在主机ip,helowin是服务名。
在一般情况下,可以这么写:

in [10]:
connection = cx_oracle.connect("username", "password", "192.168.1.2:1521/helowin", encoding="utf-8")
 

有时候,我们需要以管理员身份登录数据库,这时候,直接连接时不行的,将会跑出异常:databaseerror: ora-28009: connection as sys should be as sysdba or sysoper,这时候可以传递参数mode=cx_oracle.sysdba。

in [7]:
connection = cx_oracle.connect("sys", "psdpassword", "192.168.1.2:1521/helowin",mode=cx_oracle.sysdba,
         encoding="utf-8")
 

当确定不在使用连接时,可以使用connection.close()关闭连接(这是个好习惯)。

in [8]:
connection.close()
 

2.2 连接池

cx_oracle中提供sessionpool()创建连接池,连接池一般是在应用程序初始化时创建。相比通过connect()方法创建单个数据库连接,使用sessionpool()创建连接池时,需要额外指定最少连接数(min)和最大连接数(max),连接池创建时会创建有min个数据库连接,当连接不够用时会继续新增连接,当连接未被使用时连接池将会自动减少连接的数量。在创建好连接池后,通过调用acquire()方法可以获取一个数据库连接,连接使用完毕之后,最好使用sessionpool.release(connection)或connection.close()将连接放回连接池。

in [12]:
# 创建连接池
pool = cx_oracle.sessionpool("username", "password",
        "192.168.1.2:1521/helowin", min=2, max=5, increment=1, encoding="utf-8")

# 从连接池中获取一个连接
connection = pool.acquire()

# 使用连接进行查询
cursor = connection.cursor()
for result in cursor.execute("select * from scott.students"):
    print(result)

# 将连接放回连接池
pool.release(connection)

# 关闭连接池
pool.close()
 
(1, '张三', 20)
(2, '李四', 30)
 

如果是在多线程下同时使用连接,那么在创建连接池时应该传递一个threaded参数,并将值设置为true:

in [2]:
# 创建连接池
pool = cx_oracle.sessionpool("username", "password",
        "192.168.1.2:1521/helowin", min=2, max=5, increment=1, threaded=true, encoding="utf-8")
in [3]:
pool.close()
 

3 游标

 

有了数据库连接之后,可以通过连接来获取游标:

in [64]:
cur = connection.cursor()
 

通过游标,可以执行sql语句,实现与数据库的交互,但是记住,游标使用完之后记得关闭:

in [65]:
cur.close()
 

游标对象中定义有cursor.excute()方法和cursor.executemany()两个方法用于执行sql语句,前者一次只能执行一条sql语句,后者一次可执行多条sql。当有类似的大量sql语句需要执行时,使用cursor.executemany()而不是多次执行cursor.excute()可以极大提升性能。
另外,所有cx_oracle执行的语句都含有分号“;”或斜杠“/”:

in [66]:
connection = cx_oracle.connect("username", "password", "192.168.1.2/helowin", encoding="utf-8")
cur = connection.cursor()
in [68]:
cur.execute("select * from scott.students;")  # 含有分号,抛出异常
 
---------------------------------------------------------------------------
databaseerror                             traceback (most recent call last)
<ipython-input-68-2181d3923cb0> in <module>
----> 1cur.execute("select * from scott.students;")  # 含有分号,抛出异常

databaseerror: ora-00911: invalid character
 

4 执行sql

4.1 sql语句拼接

(1)使用python原生占位符拼接
在很多应用场景中,我们查询所用的sql语句并不是固定的,而是根据当时环境灵活的对sql进行拼接。最简单的方式就是直接使用python原生提供的占位符进行拼接,不过要注意如果变量是字符串时,引号不能少。

in [123]:
cur.execute("insert into scott.students (id, name, age) values ({student_id}, '{student_name}', {student_age})".format(
    student_id=4,
    student_name='李六',
    student_age=15
))
connection.commit()
in [124]:
student_id = 4
result = cur.execute("select * from scott.students where id={}".format(student_id))
in [125]:
result.fetchone()
out[125]:
(4, '李六', 15)
in [118]:
student_name = "张三"
result = cur.execute("select * from scott.students where name='{}'".format(student_name))
in [119]:
result.fetchone()
out[119]:
(1, '张三', 20)
 

(2)通过变量名拼接
使用这种拼接方式时,字符串中的名称与真实变量名必须一一对应。
所有变量可以统一存储在一个字典中:

in [127]:
student = {'student_id':5, 'student_name':'陈七', 'student_age': 25}  # 将所有变量存储到一个字典中
cur.execute('insert into scott.students (id, name, age) values (:student_id, :student_name, :student_age)',student)
connection.commit()
 

也可以逐一赋值:

in [128]:
cur.execute('insert into scott.students (id, name, age) values (:student_id, :student_name, :student_age)',
            student_id=6,student_name='毛八',student_age=60)
connection.commit()
 

(3)通过参数位置拼接
通过参数位置进行拼接时,所有变量可以统一存储在一个list中,list中的变量的顺序必须与字符串中定义的顺序保持一致。

in [129]:
cur.execute('insert into scott.students (id, name, age) values (:student_id, :student_name, :student_age)',
            [7,'魏九',30])
connection.commit()
 

这时候,在字符串中也可以不显式的出现参数名,而是以数字来代替出现位置:

in [130]:
cur.execute('insert into scott.students (id, name, age) values (:1, :2, :3)',
            [8,'吴十',90])
connection.commit()
 

4.2 执行语句

cx_oracle的游标中定义了execute()和executemany()两个方法用于执行sql语句,区别在于execute()一次只能执行一条sql,而executemany()一次能执行多条sql。在大量结构一样,参数不同的语句需要执行时,使用executemany()而不是多次调用execute()执行可以大大提高代码性能。
(1)execute()
对于execute()方法,其实在上面代码实例中以及多次使用,大致形式如下:

in [131]:
cur.execute('insert into scott.students (id, name, age) values (:1, :2, :3)',
            [9,'萧十一',32])
connection.commit()
 

(2)executemany()

in [132]:
students = [
    [10,'萧十一',32],
    [11,'何十二',40],
    [12,'穆十三',35]
]
cur.executemany('insert into scott.students (id, name, age) values (:1, :2, :3)',
            students)
connection.commit()
 

cx_oracle执行sql时需要注意,若是执行查询,可通过游标获取查询结果,具体如何获取请继续看下文;若是执行insert或update操作,需要在执行后继续一步connection.commit()操作。

5 获取查询结果

当使用游标进行查询后,可以直接迭代取出查询结果

in [79]:
result = cur.execute("select * from scott.students") 
in [80]:
for row in result:
    print(row)
 
(1, '张三', 20)
(2, '李四', 30)
(3, '王五', 40)
 

注意,这里的游标查询结果对象result虽然不是生成器,但是可以当做生成器来用,每一次使用next()方法时,可以获取一条记录。当然,也与生成器一样,查询结果只能迭代遍历一次,再次使用迭代不会有任何输出:

in [82]:
result = cur.execute("select * from scott.students") 
in [83]:
next(result)
out[83]:
(1, '张三', 20)
in [84]:
next(result)
out[84]:
(2, '李四', 30)
in [85]:
next(result)
out[85]:
(3, '王五', 40)
in [86]:
for row in result:  # 没有任何输出结果
    print(row)
 

其实,通过循环来获取查询结果时,每一次调用next()方法,result对象都会对数据库发起一次请求,获取一条查询记录,如果查询记录数量比较大时,性能会比较低,这时候,可以通过设置cur.arraysize参数改善性能。cur.arraysize参数配置的是每次请求获取的数据包大小,默认为100,当设置为更大值时,一次请求就可以获取更多的记录,减少客户端与数据库服务器端网络往返次数,从而提高性能,当然缺点就是消耗的内存会更大。

in [91]:
cur.arraysize = 500
for row in cur.execute("select * from scott.students"):
    print(row)
 
(1, '张三', 20)
(2, '李四', 30)
(3, '王五', 40)
 

除了在循环中直接遍历外,还可以通过fetchone()、fetchmany()、fetchall()三个方法取出查询结果。

  • fetchone()
    fetchone()每次只取出一条记录,功能效果与直接对result使用next()方法一样。
in [93]:
cur = connection.cursor()
result = cur.execute("select * from scott.students")
in [95]:
result.fetchone()
out[95]:
(1, '张三', 20)
in [96]:
result.fetchone()
out[96]:
(2, '李四', 30)
in [97]:
result.fetchone()
out[97]:
(3, '王五', 40)
 
  • fetchmany()
    fetchmany()可以一次取出指定数量的记录,如果不指定数量,表示一次性去除所有记录。
in [102]:
cur = connection.cursor()
result = cur.execute("select * from scott.students")
in [103]:
result.fetchmany(2)
out[103]:
[(1, '张三', 20), (2, '李四', 30)]
in [104]:
result.fetchmany(2)
out[104]:
[(3, '王五', 40)]
in [105]:
result.fetchmany(2)  # 若所有记录都取出来了,返回空列表
out[105]:
[]
 
  • fetchall()
    fetchall()一次性去除所有结果。
in [106]:
cur = connection.cursor()
result = cur.execute("select * from scott.students")
in [107]:
result.fetchall()
out[107]:
[(1, '张三', 20), (2, '李四', 30), (3, '王五', 40)]