怎么實現Python連接Oracle

本篇內容介紹了“怎么實現Python連接Oracle”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一：安裝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 <module>

    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 

“怎么實現Python連接Oracle”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！