文章详情页_Python 连接 Oracle 数据库 For Windows/MacOS

在使用 Python 的过程中，经常会需要连接数据库来处理数据，而连接 Oracle 数据库是一件比较麻烦的事，因为还需要安装各种 Oracle 客户端工具。

有了 cx_Oracle 库，我们就可以连接 Oracle 数据库并执行如下等操作：

连接 Oracle 数据库
执行 SQL 语句
执行存储过程
执行函数

本机(Win7)未安装数据库

本地电脑未安装 Oracle 数据库，使用 Python 连接远程 Oracle 数据库

系统环境

本机环境：

操作系统   ： Win7 64bit 
Python 版本： '3.6.4 [MSC v.1900 64 bit (AMD64)]'

远程环境：

操作系统   ：  CentOS7 x86_64
Oracle 版本：  11.2.0.4 64 bit

下载 Oracle 客户端工具：

instantclient-basic-windows.x64-11.2.0.4.0
instantclient-sdk-windows.x64-11.2.0.4.0
instantclient-sqlplus-windows.x64-11.2.0.4.0

需要注意的是，客户端版本必须要与远程 Oracle 服务端版本一致或客户端版本高于服务端版本
Oracle Instant Client Downloads

配置 Oracle Client

下载好 Oracle 客户端之后，我们还需要进行相应的配置

解压客户端至 D:\oraClient

instantclient-basic-windows.x64-11.2.0.4.0
instantclient-sdk-windows.x64-11.2.0.4.0
instantclient-sqlplus-windows.x64-11.2.0.4.0

将远程数据库的 tnsnames.ora 文件拷贝至该目录下，并修改 HOST 地址

# 将 HOST 修改为远程 IP 地址

(ADDRESS = (PROTOCOL = TCP)(HOST = ipadress)(PORT = 1521))

配置用户环境变量在 Windows 下配置 PATH 、ORACLE_HOME 、TNS_ADMIN 环境变量
创建虚拟环境

mkvirtualenv oracle

将 oraClient 目录中如下文件拷贝至虚拟环境下的 \Lib\site-packages 目录中

oci.dll
ocijdbc11.dll
oraocci11.dll
oraociei11.dll

安装 cx-Oracle

(oracle) $ pip install cx-Oracle

(oracle) $ python
>>> import cx_Oracle
>>> dir(cx_Oracle)
['ATTR_PURITY_DEFAULT', 'ATTR_PURITY_NEW', 'ATTR_PURITY_SELF', 
'BFILE', 'BINARY', 'BLOB', 'BOOLEAN', 'Binary', 'CLOB', 'CURSOR', 'Connection', 'Cursor', 'DATETIME'

操作远程数据库

(oracle) $ python

导入 cx_Oracle 库

>>> import cx_Oracle

建立连接（根据 tnsnames.ora 文件中的配置信息）

>>> conn = cx_Oracle.connect('scott/tiger@192.168.43.96:1521/orcl')

创建 cursor 游标

>>> cursor = conn.cursor()

执行 sql 语句

1. sql 查询语句

>>> query_sql = 'select * from emp where deptno = 10'

# 执行 sql 查询语句
>>> cursor.execute(query_sql)

# 获取记录集（获取一条记录，返回一个元祖）
>>> row = cursor.fetchone()
>>> print(row)
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)

# 获取全部记录
>>> rows = cursor.fetchall()
>>> print(rows)
[(7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0
, None, 10), (7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0
), 1300.0, None, 10)]

注：当运行 cursor.execute 后，结果集会缓存在 cursor 游标中，因为第一次已经从游标中获取了一条记录，所以第二次获取全部记录时，相比数据库查询会少一条记录，再比如：

# 执行 sql 语句，将结果集缓存至 cursor 中
>>> cursor.execute(query_sql)
<cx_Oracle.Cursor on <cx_Oracle.Connection to scott@192.168.43.96:1521/orcl>>

# 第一次获取全部记录
>>> rows = cursor.fetchall()
>>> print(rows)
[(7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None,
10), (7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0,
 None, 10), (7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 130
0.0, None, 10)]

# 第二次获取一条记录（无数据，因为第一次已经全部获取了）
>>> row = cursor.fetchone()
>>> print(row)
None

2. sql 创建语句

>>> create_sql = """
create table employees (
id number not null,
name varchar2(50) not null
)"""

# 执行 sql 创建语句
>>> cursor.execute(create_sql)
<cx_Oracle.Cursor on <cx_Oracle.Connection to scott@192.168.43.96:1521/orcl>>

4. sql 插入语句

# 以字典形式，插入一条记录
>>> param = {'id':1001, 'name':'Smith'}
>>> cursor.execute('insert into employees(id, name) values(:id, :name)' ,param);
# 提交
>>> conn.commit()

# 以字典的列表形式，插入多条记录
>>> param = [{'id':1002, 'name':'King'}, {'id':1003, 'name':'Scott'}]
>>> cursor.executemany('insert into employees(id, name) values(:id, :name)' ,param);
# 提交
>>> conn.commit()

4. sql 更新语句

>>> update_sql = "update employees set name = 'Adams' where id = 1003"

# 执行 sql 更新语句
>>> cursor.execute(update_sql)

# 提交
>>> conn.commit()

5. sql 删除语句

>>> delete_sql = "delete from employees"

# 执行 sql 删除语句
>>> cursor.execute(delete_sql)

# 提交
>>> conn.commit()

6. 执行存储过程

# 创建存储过程

CREATE OR REPLACE PROCEDURE P_UPDATE_EMP(I_EMPNO IN NUMBER,
                                         O_ERRCODE OUT NUMBER,
                                         O_ERRMSG OUT VARCHAR2)
AS
  V_EMPNO NUMBER;
BEGIN
  O_ERRCODE   := 0;
  O_ERRMSG    := '运行成功';
  V_EMPNO     := I_EMPNO;

  UPDATE EMP E SET E.ENAME = 'Python'
  WHERE E.EMPNO = V_EMPNO;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    O_ERRCODE := SQLCODE;
    O_ERRMSG  := SUBSTR('未处理异常:' || SQLERRM || '。' ||
                        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                        1,
                        1000);
END P_UPDATE_EMP;

# 为存储过程参数赋值
>>> i_empno = 7369  # 入参
>>> o_code = cursor.var(cx_Oracle.NUMBER)  # 出参
>>> o_msg = cursor.var(cx_Oracle.STRING)   # 出参  

# 执行存储过程
>>> cursor.callproc('p_update_emp', [i_empno, o_code, o_msg])
[7369, 0.0, '????']

# 查看运行结果（根据返回参数）
>>> print(o_code, o_msg)
<cx_Oracle.NUMBER with value 0.0> <cx_Oracle.STRING with value '????'>

出现乱码的解决方案：

# 修改 Windows 环境变量
NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

# 执行存储过程前，先修改 Python 字符集
>>> import os
>>> os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

重新打开 cmd 执行存储过程：

>>> cursor.callproc('p_update_emp', [i_empno, o_code, o_msg])
[7369, 0.0, '运行成功']

>>> print(o_code, o_msg)
<cx_Oracle.NUMBER with value 0.0> <cx_Oracle.STRING with value '运行成功'>

>>> print(o_code.getvalue(), o_msg.getvalue())
0.0 运行成功

存储过程、cx_Oracle、Python 的对象类型之间的转换关系：

Oracle	cx_Oracle	Python
VARCHAR2, LONG	cx_Oracle.STRING	str
CHAR	cx_Oracle.STRING	str
NUMBER	cx_Oracle.NUMBER	int
FLOAT	cx_Oracle.NUMBER	float
DATE	cx_Oracle.DATETIME	datetime.datetime
TIMESTAMP	cx_Oracle.TIMESTAMP	datetime.datetime
CLOB	cx_Oracle.CLOB	cx_Oracle.LOB
BLOB	cx_Oracle.BLOB	cx_Oracle.LOB

7. 执行函数

# 创建函数

CREATE OR REPLACE FUNCTION FUN_GET_ENAME(I_EMPNO IN NUMBER)
RETURN VARCHAR2 IS
  V_EMPNO NUMBER;
  V_ENAME VARCHAR2(100);
BEGIN
  V_EMPNO := I_EMPNO;

  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = V_EMPNO;

  RETURN V_ENAME;

END FUN_GET_ENAME;

# 为函数参数赋值
>>> i_empno = 7369

# 执行函数
>>> res = cursor.callfunc('FUN_GET_ENAME', cx_Oracle.STRING, [i_empno])

# 查看运行结果（根据返回参数）
>>> print(res)
Python

关闭游标

>>> cursor.close()

释放连接

>>> conn.close()

本机(Win7)已安装数据库

本地电脑已安装数据库，连接本地 Oracle 数据库和连接远程 Oracle 数据库

系统环境

本机环境：

操作系统    ： Win7 64bit 
Python  版本： '3.6.4 [MSC v.1900 64 bit (AMD64)]' 
Oracle  版本： 11.2.0.3 64bit

安装 Oracle 11g

Windows 环境下安装 Oracle 11g，这里就不再赘述

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

安装 cx-Oracle

创建虚拟环境

mkvirtualenv oracle

安装 cx-Oracle

pip install cx-Oracle

操作本地数据库

(oracle) $ python
>>> import cx_Oracle

>>> conn = cx_Oracle.connect('scott/tiger@127.0.0.1:1521/orcl')
>>> cursor = conn.cursor()

>>> quert_sql = 'select * from emp where deptno=20'
>>> cursor.execute(quert_sql)

>>> row = cursor.fetchone()
>>> print(row)
(7369, 'Python1', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)

>>> cursor.close()
>>> conn.close()

操作远程数据库

操作远程数据库，需要安装 Oracle 客户端，并将远程数据库的 tnsnames.ora 文件，拷贝至本地

安装 Oracle 客户端
将远程数据库的 tnsnames.ora 文件拷贝至该目录下，并修改 HOST 地址

(ADDRESS = (PROTOCOL = TCP)(HOST = ipadress)(PORT = 1521))

创建虚拟环境

mkvirtualenv oracle

将 oraClient 目录中如下文件拷贝至虚拟环境下的 \Lib\site-packages 目录中

oci.dll
ocijdbc11.dll
oraocci11.dll
oraociei11.dll

安装 cx-Oracle

pip install cx-Oracle

操作远程 Oracle 数据库

(oracle) $ python
>>> import cx_Oracle

>>> conn = cx_Oracle.connect('username/passwd@ip:port/instancename')
>>> cursor = conn.cursor()

>>> query_sql = 'select * from me_dim where rownum <= 2'
>>> cursor.execute(query_sql)

>>> rows = cursor.fetchall()
>>> print(rows)
[('1102', '交易性金融资产—基金', '33', 'TOTAL_SUBCODE', '持仓汇总编码', '1'), ('1103', '交易性金融资产—债券', '34', 'TOTAL_SUBCODE', '持仓汇总编码', '1')]

>>> cursor.close()
>>> conn.close()

MacOS 连接远程数据库

MacOS 目前不能安装 Oracle 11g，所以只能用于连接远程数据库

系统环境

本机环境：

操作系统    ： 10.13.2 64 bit
Python  版本： '3.6.0 64 bit'

远程环境：

操作系统   ： Windows7 64 bit
Oracle 版本： 11.2.0.4 64 bit

MacOS 系统下的 Oracle 客户端安装包：

instantclient-basic-macos.x64-11.2.0.4.0.zip
instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
instantclient-sdk-macos.x64-11.2.0.4.0.zip

配置 Oracle Client

解压客户端至 /opt/oracle/instantclient_11_2

instantclient-basic-windows.x64-11.2.0.4.0
instantclient-sdk-windows.x64-11.2.0.4.0
instantclient-sqlplus-windows.x64-11.2.0.4.0

将远程数据库的 tnsnames.ora 文件拷贝至该目录下，并修改 HOST地址

(ADDRESS = (PROTOCOL = TCP)(HOST = ipadress)(PORT = 1521))

配置用户环境变量

[allenlideMacBook-Pro:~ allen$] vi .bash_profile 
export ORACLE_HOME=/opt/oracle/instantclient_11_2
export ORACLE_SID=orcl
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=/opt/oracle/instantclient_11_2:$PATH
[allenlideMacBook-Pro:~ allen$] source .bash_profile

问题：

若出现 ORA-12170: TNS:Connect timeout occurred 解决方法
a. ping ip 是否正常
b. telnet ip port 是否正常
c. 检查远程服务器防火墙是否已关闭

安装 cx-Oracle

[allenlideMacBook-Pro:~ allen$] pip3 install cx-Oracle
[allenlideMacBook-Pro:~ allen$] python3
>>> import cx_Oracle
>>> dir(cx_Oracle)

操作远程数据库

[allenlideMacBook-Pro:~ allen$] python3
>>> import cx_Oracle

>>> conn = cx_Oracle.connect('scott/tiger@192.168.1.100:1521/orcl')
>>> cursor = conn.cursor()

>>> query_sql = 'select * from emp'
>>> cursor.execute(query_sql)

>>> row = curs.fetchone()
>>> print(row)
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)

>>> curs.close()
>>> conn.close()

原创文章，转载请注明出处：http://www.opcoder.cn/article/12/