各位小伙伴们好！本次分享的内容是如何使用Python连接Oracle数据库？学习本节内容后，相信你们就能用Python探索庞大的Oracle数据库了，想想就非常兴奋！
本文大纲
1. 了解源端和目标端的用户信息
2. Oracle数据库配置信息
3. Python主机配置
4. Python测试连接Oracle数据库
01 了解源端和目标端的用户信息
1.1 Python主机的信息
[python@PYMY-DDB ~]nbsp;pyenv virtualenvs
2.7.15/envs/autoDjango (created from /home/python/.pyenv/versions/2.7.15)
2.7.15/envs/testDjango (created from /home/python/.pyenv/versions/2.7.15)
3.6.5/envs/pyOracle (created from /home/python/.pyenv/versions/3.6.5)
autoDjango (created from /home/python/.pyenv/versions/2.7.15)
pyOracle (created from /home/python/.pyenv/versions/3.6.5)
testDjango (created from /home/python/.pyenv/versions/2.7.15)
[python@PYMY-DDB ~]nbsp;pyenv activate pyOracle
pyenv-virtualenv: prompt changing will be removed from future release. configure `export PYENV_VIRTUALENV_DISABLE_PROMPT=1' to simulate the behavior.1.2 目标数据库的用户信息
yOracle) [python@PYMY-DDB ~]nbsp;pip install --upgrade pip
Collecting pip
Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl
Installing collected packages: pip
Found existing installation: pip 9.0.3
Uninstalling pip-9.0.3:
Successfully uninstalled pip-9.0.3
Successfully installed pip-19.0.202 Oracle数据库配置信息
2.1 查看数据库服务器的IP地址
[python@PYMY-DDB ~]nbsp;pyenv virtualenvs
2.7.15/envs/autoDjango (created from /home/python/.pyenv/versions/2.7.15)
2.7.15/envs/testDjango (created from /home/python/.pyenv/versions/2.7.15)
3.6.5/envs/pyOracle (created from /home/python/.pyenv/versions/3.6.5)
autoDjango (created from /home/python/.pyenv/versions/2.7.15)
pyOracle (created from /home/python/.pyenv/versions/3.6.5)
testDjango (created from /home/python/.pyenv/versions/2.7.15)
[python@PYMY-DDB ~]nbsp;pyenv activate pyOracle
pyenv-virtualenv: prompt changing will be removed from future release. configure `export PYENV_VIRTUALENV_DISABLE_PROMPT=1' to simulate the behavior.2.2 查看监听服务
yOracle) [python@PYMY-DDB ~]nbsp;pip install --upgrade pip
Collecting pip
Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl
Installing collected packages: pip
Found existing installation: pip 9.0.3
Uninstalling pip-9.0.3:
Successfully uninstalled pip-9.0.3
Successfully installed pip-19.0.22.3 查看Oracle数据库名
(pyOracle) [python@PYMY-DDB ~]nbsp;pip install cx_Oracle
(pyOracle) [python@PYMY-DDB ~]nbsp;python -c "import cx_Oracle"03 Python主机配置
3.1 安装Oracle 客户端
[python@PYMY-DDB ~]nbsp;pyenv virtualenvs
2.7.15/envs/autoDjango (created from /home/python/.pyenv/versions/2.7.15)
2.7.15/envs/testDjango (created from /home/python/.pyenv/versions/2.7.15)
3.6.5/envs/pyOracle (created from /home/python/.pyenv/versions/3.6.5)
autoDjango (created from /home/python/.pyenv/versions/2.7.15)
pyOracle (created from /home/python/.pyenv/versions/3.6.5)
testDjango (created from /home/python/.pyenv/versions/2.7.15)
[python@PYMY-DDB ~]nbsp;pyenv activate pyOracle
pyenv-virtualenv: prompt changing will be removed from future release. configure `export PYENV_VIRTUALENV_DISABLE_PROMPT=1' to simulate the behavior.3.2 Python用户环境变量
yOracle) [python@PYMY-DDB ~]nbsp;pip install --upgrade pip
Collecting pip
Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl
Installing collected packages: pip
Found existing installation: pip 9.0.3
Uninstalling pip-9.0.3:
Successfully uninstalled pip-9.0.3
Successfully installed pip-19.0.23.3 创建虚拟环境
(pyOracle) [python@PYMY-DDB ~]nbsp;pip install cx_Oracle
(pyOracle) [python@PYMY-DDB ~]nbsp;python -c "import cx_Oracle"3.4 查看和加载虚拟环境版本
[python@PYMY-DDB ~]nbsp;pyenv virtualenvs
2.7.15/envs/autoDjango (created from /home/python/.pyenv/versions/2.7.15)
2.7.15/envs/testDjango (created from /home/python/.pyenv/versions/2.7.15)
3.6.5/envs/pyOracle (created from /home/python/.pyenv/versions/3.6.5)
autoDjango (created from /home/python/.pyenv/versions/2.7.15)
pyOracle (created from /home/python/.pyenv/versions/3.6.5)
testDjango (created from /home/python/.pyenv/versions/2.7.15)
[python@PYMY-DDB ~]nbsp;pyenv activate pyOracle
pyenv-virtualenv: prompt changing will be removed from future release. configure `export PYENV_VIRTUALENV_DISABLE_PROMPT=1' to simulate the behavior.3.5 升级pyOracle项目的pip
yOracle) [python@PYMY-DDB ~]nbsp;pip install --upgrade pip
Collecting pip
Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl
Installing collected packages: pip
Found existing installation: pip 9.0.3
Uninstalling pip-9.0.3:
Successfully uninstalled pip-9.0.3
Successfully installed pip-19.0.23.6 安装和检查cx_Oracle模块
(pyOracle) [python@PYMY-DDB ~]nbsp;pip install cx_Oracle
(pyOracle) [python@PYMY-DDB ~]nbsp;python -c "import cx_Oracle"04 Python测试连接Oracle数据库
4.1 测试连接Oracle数据库
(pyOracle) [python@PYMY-DDB pyOracle]nbsp;cat connoracle.py
#!/usr/bin/python
#coding=utf8
#导入cx_Oracle模块
import cx_Oracle
#创建到Oracle数据库的连接并赋给变量
db=cx_Oracle.connect('scott/tiger@192.168.117.100:1521/ORCL')
#创建游标并赋给变量cursor
execsql=db.cursor()
#执行Oracle SQL语句
execsql.execute('select sysdate from dual')
#获取执行结果并赋给变量data
#这里fetchone表示获取一行，fetchall为获取所有行
#fetchone返回的是一个字符串
#fetchall返回的是一个列表,哪怕结果只有一行
result=execsql.fetchone()
#打印结果
print ('Database time: %s ' %result)
#关闭数据库连接
execsql.close()
db.close()4.2 Python连接Oracle数据库
(pyOracle) [python@PYMY-DDB pyOracle]nbsp;python connoracle.py
Database time: 2019-02-21 15:47:41 4.3 测试查询数据文件信息
(pyOracle) [python@PYMY-DDB pyOracle]nbsp;cat connoracle_dbf.py
#!/usr/bin/python
# coding=utf8
import cx_Oracle
def oraclesql(execsql):
# 这里我们使用python的open方法打开文件并读取文件内容作为SQL语句执行
# 可使用绝对路径或相对路径
sqlfile = open('/oracle/scripts/datafile.sql', 'r')
# fp=open('./tablespace.sql','r')
sqlfilecontent = sqlfile.read()
execsql.execute(sqlfilecontent)
result = execsql.fetchall()
return result
if __name__ == "__main__":
ipaddress = '192.168.117.100'
username = 'sys'
password = 'oracle'
port = '1521'
tnsname = 'ORCL'
# 这里我们利用Python的异常处理来捕获异常，具体用法请参考文章开始提到的教程
try:
# 这里我们使用sysdba权限连接oracle数据库(和上期连接普通用户的不同)
db = cx_Oracle.connect(username + '/' + password + '@' + ipaddress + ':' + port + '/' + tnsname,mode=cx_Oracle.SYSDBA)
except Exception as e:
content = (tnsname + ' is Unreachable,The reason is ' + str(e)).strip()
print(content)
else:
execsql = db.cursor()
result = oraclesql(execsql)
execsql.close()
db.close()
# 由于上面获取的是一个列表(多行)，这里使用for循环来遍历
# 注意i也是一个列表
print ('数据文件编号 表空间名称 数据文件名字')
for i in result:
print (i)4.4 查询数据文件的SQL语句
(pyOracle) [python@PYMY-DDB pyOracle]nbsp;cat /oracle/scripts/datafile.sql
select file_id, tablespace_name,file_name from dba_data_files order by 14.5 Python查询Oracle的数据文件信息
(pyOracle) [python@PYMY-DDB pyOracle]nbsp;python connoracle_dbf.py
数据文件编号 表空间名称 数据文件名字
(1, 'SYSTEM', '+DATA/orcl/datafile/system.266.978200739')
(2, 'SYSAUX', '+DATA/orcl/datafile/sysaux.260.978200741')
(3, 'UNDOTBS1', '+DATA/orcl/datafile/undotbs1.264.978200743')
(4, 'USERS', '+DATA/orcl/datafile/users.263.978200743')
(5, 'EXAMPLE', '+DATA/orcl/datafile/example.258.978200911')
(6, 'USER_STU_DATA', '+DATA/orcl/datafile/user_stu_data.256.979408775')
(7, 'OGG', '+DATA/orcl/datafile/ogg.268.979421969')
(8, 'SQLTUNE_DATA', '+DATA/orcl/datafile/sqltune_data.269.986846807')
(9, 'SQLTUNE_DATA', '+DATA/orcl/datafile/sqltune_data.270.986847073')
(10, 'SQLTUNE_DATA', '+DATA/orcl/datafile/sqltune_data.271.986847785')
(11, 'SQLTUNE_DATA', '+DATA/orcl/datafile/sqltune_data.272.986847789')
(12, 'SQLTUNE_DATA', '+DATA/orcl/datafile/sqltune_data.273.986847891')05 总结
Python连接Oracle数据库是不是很简单呢？这只是开始，后续还会分享更多进阶实战喔！
[微信公众号：python自动化运维]，关注小安Sir，优质干货，为您第一时间送达！
