Python连接Oracle数据库

一、安装环境

服务器：Windows Server 2016

Python版本：Python 3.8.5（如果不知道所安装的版本，可用如下命令来查询）

1 2 3

D:\> python Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:57:54) [MSC v.1924 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information.

　　

二、在python中安装cx_Oracle

1. 在PyPI网站上下载cx_Oracle，下载网址为：https://pypi.org/project/cx_Oracle/#files。找到对应的版本，本例中下载的是cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl

2. 安装wheel用于支持安装whl文件

1 2 3 4 5 6 7

D:\python\python38> pip install wheel Collecting wheel   Downloading wheel-0.35.1-py2.py3-none-any.whl (33 kB) Installing collected packages: wheel Successfully installed wheel-0.35.1 WARNING: You are using pip version 20.1.1; however, version 20.2.4 is available. You should consider upgrading via the 'd:\python\python38\python.exe -m pip install --upgrade pip' command.

出现如上提示表明wheel-0.35.1安装成功。

插曲：在上面安装wheel的提示中，出现一条WARNING信息，提示pip的版本可以升级，因此也根据提示进行升级。

1 2 3 4 5 6 7 8 9 10 11 12 13

D:\Python\Python38>python -m pip install --upgrade pip Collecting pip   Downloading pip-20.2.4-py2.py3-none-any.whl (1.5 MB)      |████████████████████████████████| 1.5 MB 544 kB/s Installing collected packages: pip   Attempting uninstall: pip     Found existing installation: pip 20.1.1     Uninstalling pip-20.1.1: ERROR: Could not install packages due to an EnvironmentError: [WinError 5] 拒绝访问。: 'd:\\python\\python38\\lib\\site-packages\\pip-20.1.1.dist-info\\entry_points.txt' Consider using the `--user` option or check the permissions.   WARNING: You are using pip version 20.1.1; however, version 20.2.4 is available. You should consider upgrading via the 'D:\Python\Python38\python.exe -m pip install --upgrade pip' command.

出现了拒绝访问错误，可能是权限问题造成的，提示使用--user参数。

1 2 3 4 5 6 7 8 9 10 11 12

D:\Python\Python38>python -m pip install --user --upgrade pip Collecting pip   Using cached pip-20.2.4-py2.py3-none-any.whl (1.5 MB) Installing collected packages: pip   WARNING: The scripts pip.exe, pip3.8.exe and pip3.exe are installed in 'C:\Users\metc\AppData\Roaming\Python\Python38\Scripts' which is not on PATH.   Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location. Successfully installed pip-20.2.4 WARNING: You are using pip version 20.1.1; however, version 20.2.4 is available. You should consider upgrading via the 'D:\Python\Python38\python.exe -m pip install --upgrade pip' command.   D:\Python\Python38>python -m pip install --user --upgrade pip Requirement already up-to-date: pip in c:\users\metc\appdata\roaming\python\python38\site-packages (20.2.4)

至此pip已经升级到最新版本20.2.4

3. 安装cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl

1 2 3 4

D:\Python>pip install cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl Processing d:\python\cx_oracle-8.0.1-cp38-cp38-win_amd64.whl Installing collected packages: cx-Oracle Successfully installed cx-Oracle-8.0.1

　　

三、安装Oracle轻量即时客户端Instant Client

1. 根据服务器类型下载安装文件，下载地址为：https://www.oracle.com/database/technologies/instant-client/downloads.html，本例中下载的是Instant Client for Microsoft Windows (x64)。

2. 将下载的文件解压缩，本例中放在D:\oracle\instantclient_19_8\，然后将此目录配置到系统环境变量的PATH中

3. 配置完成后需要重启服务器，以使环境变量生效。

 

四、用py程序测试连接

编写以下testOracle.py代码进行测试：

1 2 3 4 5 6 7 8 9 10 11 12

import cx_Oracle host = "192.168.1.101" port = "1521" sid = "oracledb" dsn = cx_Oracle.makedsn(host, port, sid) conn = cx_Oracle.connect("usrName", "passWord", dsn) cursor = cx_Oracle.Cursor(conn) cursor.execute("select * from v_user") result = cursor.fetchall() print(result) cursor.close() conn.close()