通过cx_Oracle模块,连接Oracle数据库之后,可以做很多的操作和配置。例如批量查看百余台数据库实例的RMAN备份情况等,现在第一步,连接上Oracle数据库。
Python操作Oracle数据库
1、安装cx_Oracle模块
G:Py>pip install cx_Oracle
2、安装oracle客户端
这里实验环境是: oracle 11.2.0.4版本。安装的客户端名称如下:
instantclient-basic-windows.x64-11.2.0.4.0.zip
,非常要注意的问题。
- 必须是64位。要一一匹配
- 配置PATH路径。
我这里解压的客户端路径为:D:PLSQLinstantclient_11_2
通过配置如下步骤:Control Panel -> System -> Advanced System Settings -> Advanced -> Environment Variables -> System Variables -> PATH。把D:PLSQLinstantclient_11_2
加入到系统变量的PATH变量中。
效果如下:
G:Py>set
Path=C:Program Files (x86)Common FilesOracleJavajavapath;C:Program Files (
x86)InteliCLS Client;C:Program FilesInteliCLS Client;C:Windowssystem32;
C:Windows;C:WindowsSystem32Wbem;C:WindowsSystem32WindowsPowerShellv1.0;
C:Program Files (x86)NVIDIA CorporationPhysXCommon;C:Program Files (x86)In
telIntel(R) Management Engine ComponentsDAL;C:Program FilesIntelIntel(R) Ma
nagement Engine ComponentsDAL;C:Program Files (x86)IntelIntel(R) Management
Engine ComponentsIPT;C:Program FilesIntelIntel(R) Management Engine Componen
tsIPT;C:Program FilesIntelWiFiin;C:Program FilesCommon FilesIntelWire
lessCommon;C:Program FilesGitcmd;C:Program FilesTortoiseGitin;D:PLSQLi
nstantclient_11_2;C:UserssdzhangAppDataLocalProgramsPythonPython37Script
s;C:UserssdzhangAppDataLocalProgramsPythonPython37;C:MinGWin;C:Prog
ram Files (x86)Diffuse;D:PLSQLinstantclient_11_2;
可以看到之后有了D:PLSQLinstantclient_11_2
路径。
3、python操作oracle示例
import cx_Oracle
conn = cx_Oracle.connect('username/passwd@172.17.xx.204/instancename')
##conn = cx_Oracle.connect('system/xx@172.17.10.204/oradb')
curs = conn.cursor()
sql = 'select * from product_component_version'
curs.execute(sql)
for result in curs:
print(result)
curs.close()
conn.close()
另存为oracle01.py
,执行效果如下:
G:Py>python oracle01.py
('NLSRTL ', '11.2.0.4.0', 'Production')
('Oracle Database 11g Enterprise Edition ', '11.2.0.4.0', '64bit Production')
('PL/SQL ', '11.2.0.4.0', 'Production')
('TNS for Linux: ', '11.2.0.4.0', 'Production')
4、配置TNS
172.17.10.204 =
(description =
(address = (protocol = tcp)(host = 172.17.10.204)(port = 1521))
(connect_data =
(service_name = oradb)
)
)
5.通过tns连接数据库的python
import cx_Oracle
conn = cx_Oracle.connect('system/xx@172.17.10.204')
curs = conn.cursor()
sql = 'select * from dual'
curs.execute(sql)
for result in curs:
print(result)
curs.close()
conn.close()
另存为:oracle02.py
,效果如下:
G:Py>python oracle02.py
('X',)