Python安装cx_Oracle与操作数据测试小结

这里简单总结一下python操作oracle数据库这方面的相关知识。只是简单的整理一下之前的实验和笔记。这里的测试服务器为centos linux release 7.5. 个人实验、测试、采集数据的一台机器。

1:安装cx_oracle

这里简单介绍一下cx_oracle，下面一段内容摘自cx_oracle的作者（anthony tuininga ）的一篇博文，详情参考

cx_oracle是一个python 扩展模块，通过使用所有数据库访问模块通用的数据库 api来实现 oracle 数据库的查询和更新。为使用一些专为 oracle 设计的特性，还加入了多个通用数据库 api 的扩展。cx_oracle 的开发历时十多年，涵盖了大多数需要在 python 中访问 oracle 的客户的需求。2008 年 12 月，一个新的主要版本解决了早期版本的众多限制，并增添了对 python 3.0 和 oracle 新推出的一些特性的支持。

cx_oracle的官方文档：https://cx-oracle.readthedocs.io/en/latest/# 官方文档从架构、特征方面介绍了cx_oracle，简单摘录如下所示：

architecture

python programs call cx_oracle functions. internally cx_oracle dynamically loads oracle client libraries to access oracle database.

fig. 1 cx_oracle architecture
cx_oracle is typically installed from pypi using pip. the oracle client libraries need to be installed separately. the libraries can be obtained from an installation of oracle instant client, from a full oracle client installation, or even from an oracle database installation (if python is running on the same machine as the database).

features

the cx_oracle feature highlights are:

• easily installed from pypi
• support for python 2 and 3, and for multiple oracle database versions
• execution of sql and pl/sql statements
• extensive oracle data type support, including large objects (clob and blob) and binding of sql objects
• connection management, including connection pooling
• oracle database high availability features
• full use of oracle network service infrastructure, including encrypted network traffic and security features
  a complete list of supported features can be seen here.

下面介绍安装cx_oracle，cx_oracle组件有多种安装方式，实在想不出不用pip安装的理由，因为它实在太简单、方便了！

pip install cx_oracle

[root@db-server ~]# pip install cx_oracle
collecting cx_oracle
  downloading https://files.pythonhosted.org/packages/ff/95/6c50d13da95de3e438e7d333dbd7b0946a0981c778e8153c73061b018a33/cx_oracle-7.2.3-cp36-cp36m-manylinux1_x86_64.whl (731kb)
     |████████████████████████████████| 737kb 295kb/s 
installing collected packages: cx-oracle
successfully installed cx-oracle-7.2.3

安装成功后，测试验证

[root@db-server ~]# python
python 3.6.6 (default, sep 20 2019, 08:20:38) 
[gcc 4.8.5 20150623 (red hat 4.8.5-39)] on linux
type "help", "copyright", "credits" or "license" for more information.
>>> import cx_oracle
>>> 

2：安装oracle客户端

这里我们打算安装oracle instant client，可以去下面官方网址选择正确的、所需的版本。本次实验，我选择了instantclient-basic-linux.x64-11.2.0.4.0.zip

这里下载的oracle instant client zip，安装过程简单如下：

1：解压安装包文件到指定目录

 mkdir -p /opt/oracle
 cd /opt/oracle/
 mv instantclient-basic-linux.x64-11.2.0.4.0.zip  /opt/oracle/
 unzip instantclient-basic-linux.x64-11.2.0.4.0.zip 

2：在操作系统安装libaio package，否则有可能在运行python脚本时遇到错误提示“importerror: libaio.so.1: cannot open shared object file: no such file or directory”

yum install libaio

注意：这个不是必须的，有些情况下会遇到这个错误。

3：将instant client永久添加到运行时链接路径

 sudo sh -c "echo /opt/oracle/instantclient_11_2  > /etc/ld.so.conf.d/oracle-instantclient.conf"
 sudo  ldconfig #

或者将环境变量ld_library_path设置为instant client版本的相应目录。

# echo $ld_library_path
/usr/local/hadoop/lib/native

在/etc/profile中加入
export ld_library_path=/opt/oracle/instantclient_11_2:$ld_library_path

# source /etc/profile
# echo $ld_library_path
/opt/oracle/instantclient_11_2:/usr/local/hadoop/lib/native

mkdir -p /opt/oracle/instantclient_11_2/network/admin

其它平台的安装（或者rpm安装）， 官方文档https://oracle.github.io/odpi/doc/installation.html#linux 有详细介绍，这里就不画蛇添足。 rpm的安装也很简单，如下所示

#rpm -ivh oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm 
preparing...                ########################################### [100%]
   1:oracle-instantclient19.########################################### [100%]

需要注意的是，安装是必须版本一致：cx_oracle、oracle client、python最好一致，否则就会出现很多问题，例如下面这些（学习、测试中遇到的错误）

- 1：cx_oracle 报错：cx_oracle.databaseerror: dpi-1050: oracle client library must be at version 11.2
- 2：cx_oracle.databaseerror: dpi-1047: cannot locate a 64-bit oracle client library: "/lib64/libc.so.6: version `glibc_2.14' not found (required by /usr/lib/oracle/19.3/client64/lib/libclntsh.so)". see https://oracle.github.io/odpi/doc/installation.html#linux for help

oracle的连接方式

cx_oracle连接oracle数据库的连接字符串（connection strings）有多种方式，下面简单整理一下:

首先，我们可以看看cx_oracle.connect这个函数的参数如下：

cx_oracle.connect(user=none, password=none, dsn=none, mode=cx_oracle.default_auth, handle=0, pool=none, threaded=false, events=false, cclass=none, purity=cx_oracle.attr_purity_default, newpassword=none, encoding=none, nencoding=none, edition=none, appcontext=[], tag=none, matchanytag=none, shardingkey=[], supershardingkey=[])

参数看起来相当多，眼花缭乱的。不过没有关系，其实你用到的不多，很多参数基本上可以选择性忽略。比较常用的基本上有下面几个：

user 用户名
password 密码
dsn dsn
encoding 编码

easy connect syntax for connection strings

dbhost.example.com是机器名，也可以数据库服务器ip地址，orclpdb1是service names, 注意，这种方式不能使用早期的sid

import cx_oracle

# obtain password string from a user prompt or environment variable
userpwd = ". . ." 

connection = cx_oracle.connect(username, userpwd, "dbhost.example.com/orclpdb1", encoding="utf-8")

如果数据库不是默认的端口号，那么需要在连接字符串中指定端口号，如下所示：

connection = cx_oracle.connect(username, userpwd, "dbhost.example.com:1984/orclpdb1",
        encoding="utf-8")

oracle net connect descriptor strings

介绍这种连接字符串方式前，我们必须先了解一下函数makedns,它的功能是创建一个dns对象， 如下所示：

cx_oracle.makedns
( host
, port
, sid=none
, service_name=none
, region=none
, sharding_key=none
, super_sharding_key=none)

dsn = cx_oracle.makedsn("dbhost.example.com", 1521, service_name="orclpdb1")
connection = cx_oracle.connect(username, userpwd, dsn, encoding="utf-8")

 另外，你也可以用创建类似的连接描述符字符串，如下所示：

dsn = """(description=
             (failover=on)
             (address_list=
               (address=(protocol=tcp)(host=sales1-svr)(port=1521))
               (address=(protocol=tcp)(host=sales2-svr)(port=1521)))
             (connect_data=(service_name=sales.example.com)))"""

connection = cx_oracle.connect(username, userpwd, dsn, encoding="utf-8")

net service names for connection strings

这种连接字符串方式就不用多介绍了，基本上学习oracle的入门的时候，就已经了解了这方面的知识点

mydb =
  (description =
    (address = (protocol = tcp)(host = xxxxx)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orclpdb1)
    )
  )

connection = cx_oracle.connect(username, userpwd, "mydb", encoding="utf-8")

jdbc and oracle sql developer connection strings

cx_oracle连接字符串语法与java jdbc和通用oracle sql developer语法不同,一般jdbc的连接字符串如下：

jdbc:oracle:thin:@hostname:port/service_name、

cx_oracle的方式如下：

connection = cx_oracle.connect("hr", userpwd, "dbhost.example.com:1521/orclpdb1", encoding="utf-8")

用户名/密码@oracle服务器ip/oracle的service_name")
db_conn=cx_oracle.connect('test/test123456@10.20.57.24/gsp')

oracle的基本操作

下面简单介绍一下使用cx_oracle操作数据库的一些简单例子

oracle的查询

简单查询

import cx_oracle

db_conn=cx_oracle.connect('test/test123456@10.20.57.24/gsp.localdomain')
db_cursor=db_conn.cursor()

sql_cmd='select * from test.test'

db_cursor.execute(sql_cmd)


for row in  db_cursor:
    print(row)

db_cursor.close()
db_conn.close()

注意事项：sql语句里面不能加上分号（;)这类结束符，否则就会报 ora-00911: invalid character错误：

如果上面sql脚本里面多了一个分号，就会出现下面错误：

sql_cmd='select * from test.test;'



[root@mydb python]# python orace_select.py 
traceback (most recent call last):
  file "orace_select.py", line 8, in <module>
    db_cursor.execute(sql_cmd)
cx_oracle.databaseerror: ora-00911: invalid character

带参数查询

import cx_oracle

db_conn=cx_oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="utf-8")
db_cursor=db_conn.cursor()

sql_cmd='select * from test where id=:id'
sql_p_id={'id':100}

db_cursor.execute(sql_cmd,sql_p_id)


for row in  db_cursor:
    print(row)

db_cursor.close()
db_conn.close()

如果要获取多行记录，可以使用fetchall函数。不用使用

import cx_oracle

db_conn=cx_oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="utf-8")
db_cursor=db_conn.cursor()

sql_cmd='select * from test where id=:id'
sql_p_id={'id':100}

db_cursor.execute(sql_cmd,sql_p_id)

db_records= db_cursor.fetchall()
print(db_records)


db_cursor.close()
db_conn.close()

oracle的dml

其实对于insert、delete、update的操作，基本上差不很多。下面简单简单举了一个update例子，没有从细节和分类（update、insert、delete）展开。

#-*- coding: utf-8 -*-
import cx_oracle

db_conn=cx_oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="utf-8")
db_cursor = db_conn.cursor()

sql_cmd = 'insert into test.test(owner,object_id,object_name)' \
          'values(:owner, :object_id, :object_name)'


db_cursor.execute(sql_cmd, ('test', 1, 'kerry1'))
db_cursor.execute(sql_cmd, ('test', 2, 'kerry2'))

db_conn.commit()

db_cursor.close()
db_conn.close()

注意，如果是插入多行记录，你使用execute就会报如下错误：

cx_oracle.notsupportederror: python value of type tuple not supported.

#-*- coding: utf-8 -*-
import cx_oracle

db_conn=cx_oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="utf-8")
db_cursor = db_conn.cursor()

sql_cmd = 'insert into test.test(owner,object_id,object_name)' \
          'values(:1, :2, :3)'

insert_record=[('test', 1, 'kerry1'),('test', 2, 'kerry2'),('test', 3, 'kerry3')]
db_cursor.bindarraysize =3
db_cursor.setinputsizes(30,int, 30)
#db_cursor.execute(sql_cmd, ('test', 1, 'kerry1'))
#db_cursor.execute(sql_cmd, ('test', 2, 'kerry2'))
db_cursor.execute(sql_cmd, insert_record)

db_conn.commit()

db_cursor.close()
db_conn.close()

正确的做法是使用executemany：

#-*- coding: utf-8 -*-
import cx_oracle

db_conn=cx_oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="utf-8")
db_cursor = db_conn.cursor()

sql_cmd = 'insert into test.test(owner,object_id,object_name)' \
          'values(:1, :2, :3)'

insert_record=[('test', 1, 'kerry1'),('test', 2, 'kerry2'),('test', 3, 'kerry3')]
db_cursor.bindarraysize =3
db_cursor.setinputsizes(30,int, 30)
db_cursor.executemany(sql_cmd, insert_record)

db_conn.commit()


db_cursor.close()
db_conn.close()

个人在总结的时候，发现官方文档里面的例子实在是太详细了，这里完全没有必要画蛇添足。

参考资料：

https://github.com/oracle/python-cx_oracle/tree/master/samples