Python操作Oracle数据库：cx_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)]