拉勾教育Python连接Oracle数据库
一、安装环境
服务器：Windows Server 2016，拉勾IT课小编为大家分析。
Python版本：Python 3.8.5（如果不知道所安装的版本，可用如下命令来查询）
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://pyp***/project/cx_Oracle/#files。找到对应的版本，本例中下载的是cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
2. 安装wheel用于支持安装whl文件
D:pythonpython38> 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:pythonpython38python.exe -m pip install --upgrade pip' command.
出现如上提示表明wheel-0.35.1安装成功。
插曲：在上面安装wheel的提示中，出现一条WARNING信息，提示pip的版本可以升级，因此也根据提示进行升级。
D:PythonPython38>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:PythonPython38python.exe -m pip install --upgrade pip' command.
出现了拒绝访问错误，可能是权限问题造成的，提示使用--user参数。
D:PythonPython38>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:UsersmetcAppDataRoamingPythonPython38Scripts' 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:PythonPython38python.exe -m pip install --upgrade pip' command.
D:PythonPython38>python -m pip install --user --upgrade pip
Requirement already up-to-date: pip in c:usersmetcappdataroamingpythonpython38site-packages (20.2.4)
至此pip已经升级到新版本20.2.4
3. 安装cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
D:Python>pip install cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
Processing d:pythoncx_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.o***/database/technologies/instant-client/downloads.html，本例中下载的是Instant Client for Microsoft Windows (x64)。
2. 将下载的文件解压缩，本例中放在D:oracleinstantclient_19_8，然后将此目录配置到系统环境变量的PATH中
3. 配置完成后需要重启服务器，以使环境变量生效。
四、用py程序测试连接
编写以下testOracle.py代码进行测试：
import cx_Oracle
host = "192.168.1.101"
port = "1521"
sid = "oracledb"
dsn = cx_Oracle.makedsn(host, port, sid)
conn = cx_O***nnect("usrName", "passWord", dsn)
cursor = cx_Oracle.Cursor(conn)
cursor.execute("select * from v_user")
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
服务器：Windows Server 2016，拉勾IT课小编为大家分析。
Python版本：Python 3.8.5（如果不知道所安装的版本，可用如下命令来查询）
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://pyp***/project/cx_Oracle/#files。找到对应的版本，本例中下载的是cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
2. 安装wheel用于支持安装whl文件
D:pythonpython38> 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:pythonpython38python.exe -m pip install --upgrade pip' command.
出现如上提示表明wheel-0.35.1安装成功。
插曲：在上面安装wheel的提示中，出现一条WARNING信息，提示pip的版本可以升级，因此也根据提示进行升级。
D:PythonPython38>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:PythonPython38python.exe -m pip install --upgrade pip' command.
出现了拒绝访问错误，可能是权限问题造成的，提示使用--user参数。
D:PythonPython38>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:UsersmetcAppDataRoamingPythonPython38Scripts' 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:PythonPython38python.exe -m pip install --upgrade pip' command.
D:PythonPython38>python -m pip install --user --upgrade pip
Requirement already up-to-date: pip in c:usersmetcappdataroamingpythonpython38site-packages (20.2.4)
至此pip已经升级到新版本20.2.4
3. 安装cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
D:Python>pip install cx_Oracle-8.0.1-cp38-cp38-win_amd64.whl
Processing d:pythoncx_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.o***/database/technologies/instant-client/downloads.html，本例中下载的是Instant Client for Microsoft Windows (x64)。
2. 将下载的文件解压缩，本例中放在D:oracleinstantclient_19_8，然后将此目录配置到系统环境变量的PATH中
3. 配置完成后需要重启服务器，以使环境变量生效。
四、用py程序测试连接
编写以下testOracle.py代码进行测试：
import cx_Oracle
host = "192.168.1.101"
port = "1521"
sid = "oracledb"
dsn = cx_Oracle.makedsn(host, port, sid)
conn = cx_O***nnect("usrName", "passWord", dsn)
cursor = cx_Oracle.Cursor(conn)
cursor.execute("select * from v_user")
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
