各位小伙伴们好!本次分享的内容是如何使用Python连接Oracle数据库?学习本节内容后,相信你们就能用Python探索庞大的Oracle数据库了,想想就非常兴奋!
![f160f93febed1487e4ed0752a640b8d1.gif](https://i-blog.csdnimg.cn/blog_migrate/82bde1badaf955ccd237a94e2ff2ce0f.gif)
本文大纲
1. 了解源端和目标端的用户信息
2. Oracle数据库配置信息
3. Python主机配置
4. Python测试连接Oracle数据库
01 了解源端和目标端的用户信息
1.1 Python主机的信息
[python@PYMY-DDB ~]$ 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 ~]$ pyenv activate pyOraclepyenv-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 ~]$ pip install --upgrade pipCollecting pip Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whlInstalling collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3Successfully installed pip-19.0.2
02 Oracle数据库配置信息
2.1 查看数据库服务器的IP地址
[python@PYMY-DDB ~]$ 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 ~]$ pyenv activate pyOraclepyenv-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 ~]$ pip install --upgrade pipCollecting pip Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whlInstalling collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3Successfully installed pip-19.0.2
2.3 查看Oracle数据库名
(pyOracle) [python@PYMY-DDB ~]$ pip install cx_Oracle(pyOracle) [python@PYMY-DDB ~]$ python -c "import cx_Oracle"
03 Python主机配置
3.1 安装Oracle 客户端
[python@PYMY-DDB ~]$ 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 ~]$ pyenv activate pyOraclepyenv-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 ~]$ pip install --upgrade pipCollecting pip Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whlInstalling collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3Successfully installed pip-19.0.2
3.3 创建虚拟环境
(pyOracle) [python@PYMY-DDB ~]$ pip install cx_Oracle(pyOracle) [python@PYMY-DDB ~]$ python -c "import cx_Oracle"
3.4 查看和加载虚拟环境版本
[python@PYMY-DDB ~]$ 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 ~]$ pyenv activate pyOraclepyenv-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 ~]$ pip install --upgrade pipCollecting pip Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whlInstalling collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3Successfully installed pip-19.0.2
3.6 安装和检查cx_Oracle模块
(pyOracle) [python@PYMY-DDB ~]$ pip install cx_Oracle(pyOracle) [python@PYMY-DDB ~]$ python -c "import cx_Oracle"
04 Python测试连接Oracle数据库
4.1 测试连接Oracle数据库
(pyOracle) [python@PYMY-DDB pyOracle]$ 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')#创建游标并赋给变量cursorexecsql=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]$ python connoracle.py Database time: 2019-02-21 15:47:41
4.3 测试查询数据文件信息
(pyOracle) [python@PYMY-DDB pyOracle]$ cat connoracle_dbf.py #!/usr/bin/python# coding=utf8import cx_Oracledef 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 resultif __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]$ cat /oracle/scripts/datafile.sqlselect file_id, tablespace_name,file_name from dba_data_files order by 1
4.5 Python查询Oracle的数据文件信息
(pyOracle) [python@PYMY-DDB pyOracle]$ 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,优质干货,为您第一时间送达!