python实时连接oracle_Oracle 自动化运维-Python连接Oracle

Oracle 自动化运维-Python连接Oracle

一：安装Python 3.6

二：

安装cx_Oracle模块

三：使用cx_Oracle连接Oracle(基础篇)

参考：http://www.zhaibibei.cn/python1/1.1/

一：安装Python 3.6

环境设置

Linux:Centos 6.7

Python:Python 3.6

DB:Oracle 19C

新增oracle用户

名字可随便取，脚本都放该用户下

[root@jumplinux01 ~]# useradd oracle

[root@jumplinux01 ~]# passwd oracle

新建安装目录

[root@jumplinux01 ~]# su - oracle

[oracle@jumplinux01 ~]$ mkdir softwore

[oracle@jumplinux01 ~]$ cd softwore/

[oracle@jumplinux01 softwore]$

安装Python3.6

1.由于系统安装完成后Python的版本为2.6，需先升级至3.6

[oracle@jumplinux01 softwore]$ python

Python 2.6.6 (r266:84292, Jul 23 2015, 15:22:56)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-11)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>>

下载安装文件并解压

[oracle@jumplinux01 softwore]$ wget https://www.python.org/ftp/python/3.6.1/Python-3.6.1.tgz

[oracle@jumplinux01 softwore]$ ll -rth

total 22M

-rw-rw-r-- 1 oracle oracle 22M Mar 21  2017 Python-3.6.1.tgz

[oracle@jumplinux01 softwore]$ tar -zxvf Python-3.6.1.tgz

[oracle@jumplinux01 softwore]$ cd Python-3.6.1

创建Python安装目录

[root@jumplinux01 ~]#mkdir /usr/local/python36

[root@jumplinux01 ~]# chown oracle.oracle /usr/local/python36 -R

[root@jumplinux01 ~]# su - oracle

[oracle@jumplinux01 ~]$ cd softwore/Python-3.6.1

[oracle@jumplinux01 Python-3.6.1]$ ./configure --prefix=/usr/local/python36

[oracle@jumplinux01 Python-3.6.1]$ make

[oracle@jumplinux01 Python-3.6.1]$ make install

修改链接

[root@jumplinux01 ~]# mv /usr/bin/python /usr/bin/python_old

[root@jumplinux01 ~]# ln -s /usr/local/python36/bin/python3.6 /usr/bin/python

[root@jumplinux01 ~]# su - oracle

验证是否安装成功

[oracle@jumplinux01 ~]$ python

Python 3.6.1 (default, Mar 16 2020, 21:33:03)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-23)] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>>

yum问题处理

升级完之后yum会无法使用 这时需要修改yum可执行文件文件

问题：

[root@jumplinux01 ~]# yum list

File "/usr/bin/yum", line 30

except KeyboardInterrupt, e:

^

SyntaxError: invalid syntax

处理：

[root@jumplinux01 ~]# vim /usr/bin/yum

#!/usr/bin/python2.6

[root@jumplinux01 ~]# yum list

Loaded plugins: fastestmirror

Determining fastest mirrors

* base: mirrors.aliyun.com

* extras: mirrors.aliyun.com

* updates: mirrors.aliyun.com

...

二：cx_Oracle模块的安装

这节包含 oracle客户端的安装和cx_Oracle模块的安装两部分

环境设置

Linux系统为 Centos 6.7

Python环境为 Python 3.6

Oracle 模块:cx_Oracle

Oracle客户端:Oracle 12c Instant Client

1. 安装Oracle 12c Instant Client

由于这次我们使用Python连接Oracle，所以需要oracle客户端，这里我们使用Oracle 12c Instant Client

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

[root@jumplinux01 ~]# chown oracle.oracle /home/oracle/softwore -R

[root@jumplinux01 ~]# ll -rth /home/oracle/softwore/

total 73M

-rw-rw-r--  1 oracle oracle  22M Mar 21  2017 Python-3.6.1.tgz

drwxr-xr-x 18 oracle oracle 4.0K Mar 16 21:34 Python-3.6.1

-rw-r--r--  1 oracle oracle 593K Mar 16 22:15 oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm

-rw-r--r--  1 oracle oracle  51M Mar 16 22:15 oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

1.1安装Oracle客户端(root用户)

[root@jumplinux01 softwore]# rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

[root@jumplinux01 softwore]# rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm

1.2添加ORACLE_HOME用户环境变量(root用户)

[root@jumplinux01 softwore]# vi ~/.bash_profile

export ORACLE_HOME=/usr/lib/oracle/12.2/client64

export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib

[root@jumplinux01 softwore]# source ~/.bash_profile

2.下载cx_Oracle模块

专门用于连接Oracle数据的一个模块

https://pypi.org/project/cx-Oracle/5.2.1/#downloads

[root@jumplinux01 softwore]# ll -rth cx_Oracle-5.2.1.tar.gz

-rw-r--r-- 1 oracle oracle 111K Mar 16 22:23 cx_Oracle-5.2.1.tar.gz

3.安装cx_Oracle模块(root用户)

[oracle@jumplinux01 softwore]$ tar -zxvf cx_Oracle-5.2.1.tar.gz

[oracle@jumplinux01 softwore]$ cd cx_Oracle-5.2.1

[oracle@jumplinux01 cx_Oracle-5.2.1]$ python setup.py build

[oracle@jumplinux01 cx_Oracle-5.2.1]$ python setup.py install

...

Installed /usr/local/python36/lib/python3.6/site-packages/cx_Oracle-5.2.1-py3.6-linux-x86_64.egg

Processing dependencies for cx-Oracle==5.2.1

Finished processing dependencies for cx-Oracle==5.2.1

验证安装

如import无错误则说明安装成功

[oracle@jumplinux01 cx_Oracle-5.2.1]$ python

Python 3.6.1 (default, Mar 16 2020, 21:33:03)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-23)] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>> import cx_Oracle

>>>

三：使用cx_Oracle连接Oracle(基础篇)

[oracle@jumplinux01 softwore]$ vi connectoracle.py

#!/usr/bin/python

#coding=utf8

#导入cx_Oracle模块

import cx_Oracle

#创建到Oracle数据库的连接并赋给变量

db=cx_Oracle.connect('cjc/cjc@192.168.2.222:1521/cjcpdb01')

#创建游标并赋给变量cursor

cursor=db.cursor()

#执行Oracle SQL语句

cursor.execute('select sysdate from dual')

#获取执行结果并赋给变量data

#这里fetchone表示获取一行，fetchall为获取所有行

#fetchone返回的是一个字符串

#fetchall返回的是一个列表,哪怕结果只有一行

data=cursor.fetchone()

#打印结果

print ('Database time: %s ' %data)

#关闭数据库连接

cursor.close()

db.close()

运行结果:

错误：

[root@jumplinux01 ~]# su - oracle

[oracle@jumplinux01 ~]$ cd softwore/

[oracle@jumplinux01 softwore]$ python connectoracle.py

Traceback (most recent call last):

File "connectoracle.py", line 8, in

db=cx_Oracle.connect('cjc/cjc@192.168.2.222:1521/cjcpdb01')

cx_Oracle.DatabaseError: ORA-24454: client host name is not set

解决方案：添加hosts

[root@jumplinux01 ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.2.221 jumplinux01

192.168.2.222 cjcos

再次运行结果：

[oracle@jumplinux01 softwore]$ python connectoracle.py

Database time: 2020-03-16 22:35:17

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！