亲宝软件园·资讯

展开

Python操作Oracle数据库:cx_Oracle

奥辰 人气:2

 

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

 

1 安装与导入¶

 

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

In [1]:
import cx_Oracle
 

千万注意,包名称cx_Oracle中,字母“O”是大写的,写成小写将会导入失败。
这里再附带多说一点,我在安装好cx_Oracle第一次使用时,出现这个异常:DatabaseError: DPI-1047,可以按照官方的思路解决:https://oracle.github.io/odpihttps://img.qb5200.com/download-x/doc/installation.html#linux

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)]

加载全部内容

相关教程
猜你喜欢
用户评论