Linux环境 CentOS 7 ;
Python版本 Python 3.7 ;
Oracle数据库版本:Oracle 11.2;
目标:在Linux上使用Python连接Oracle实现数据库增删改查操作
一、在Linux系统安装 oracle客户端
因为是需要使用Python远程访问公司的Oracle服务端,所以Linux本机不需要安装Oracle服务端,只安装客户端即可。
打开Oracle客户端官方下载网址: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
1. 下载两个安装文件
2. 使用Xftp工具将两个文件提交到Linux : /opt 文件夹目录下(可以随以,后续需要安装这两个文件)
3. 运行安装命令
$ cd /opt
$ sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
4. 安装后对Oracle客户端做以下两个配置
a. 在安装目录 /usr/lib/oracle/19.9/client64 中创建目录,并创建配置文件DRG
b. profile中配置Oracle的环境变量
$ mkdir –r network/admin
$ vi tnsnames.ora
插入以下代码并保存,HOST是数据库服务端的IP地址,PORT是端口,这两项根据实际情况来填写
DRG=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.25.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
保存后,继续配置Oracle环境变量
$ vi /etc/profile
在文件底部追加以下配置信息,路径根据实际情况填写
export ORACLE_HOME=/usr/lib/oracle/19.9/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib
export PATH=$ORACLE_HOME:$PATH
接着对环境变量进行生效:
$ source /etc/profile
最后,测试数据库连接情况:
$ sqlplus disease/wonders@DRG
5. 测试出现以下结果,证明数据库连接成功
6. 为Python安装
二、为Python安装cx_Oracle模块
如果对版本有要求,到cx_Oracle官网下载需要的版本 https://pypi.python.org/pypi/cx_Oracle
如果对版本没有要求,直接 pip install cx_Oracle即可
二、使用cx_Oracle模块操作Oracle数据库
此处,我附上了一断代码,可以直接粘贴引用
import pandas as pd
import cx_Oracle
import os
class getOracle(object):
def __init__(self, user, pwd, ip, port, sid):
self.connect = cx_Oracle.connect(user + "/" + pwd + "@" + ip + ":" + port + "/" + sid)
self.cursor = self.connect.cursor()
def select(self, sql):
"""查询语句
:param sql:string
:return:dataframe
"""
try:
self.cursor.execute(sql)
result = self.cursor.fetchall()
col_name_list = [i[0] for i in self.cursor.description]
dataframe = pd.DataFrame(result, columns=col_name_list)
self.connect.commit()
return dataframe
except Exception as e:
print('数据查询失败!!!',e)
# finally:
# self.disconnect()
def disconnect(self):
self.cursor.close()
self.connect.close()
def insertDataframe(self,dataframe,tableName):
"""插入Dataframe数据
:param dataframe:dataframe
:param tableName:数据库表名
:return:None
"""
try:
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8' # 解决编码问题
dataframe = dataframe.reset_index(drop=True) # 重置index
# 1. 拼接dataframe字段数据类型
dtype_ = []
for i, j in zip(dataframe.columns, dataframe.dtypes):
if str(j) in ["float", "float64", "int64"]:
dtype_.append(i + ' number(16,4)')
elif str(j) in ['datetime64[ns]']:
dtype_.append(i + ' DATE')
else:
dtype_.append(i + ' varchar2(64)')
dtype_str = ', '.join(dtype_)
# 2. 创建表
sql = 'CREATE TABLE %s (%s)' % (tableName, dtype_str)
self.create(tableName, sql)
# 3. 批量插入数据
sqlvalue = ','.join([':%s' % i for i in range(1, len(dataframe.columns) + 1)]) # 插入语句
datatuple = [tuple(dataframe.iloc[i].values) for i, r in dataframe.iterrows()] # 元组列表
# b. 批量插入
self.cursor.executemany('insert into %s values(%s)'%(tableName,sqlvalue),datatuple)
self.connect.commit()
print('**************** 成功入库:', len(datatuple), '条数据 ****************')
except Exception as e:
print(e)
finally:
self.disconnect()
def insert_of_sql(self, sql, list_param):
""" sql 语句插入
:param sql:string
:param list_param:list
:return:None
"""
try:
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8' # 解决编码问题
self.cursor.executemany(sql, list_param)
self.connect.commit()
print("插入ok")
except Exception as e:
print(e)
finally:
self.disconnect()
def update(self, sql):
"""更新表
:param sql:
:return:
"""
try:
self.cursor.execute(sql)
self.connect.commit()
except Exception as e:
print(e)
finally:
self.disconnect()
def create(self,tableName, sql):
"""循环创建,删除历史重名表,再进行创建
:param tableName:string
:param sql:string
:return:None
"""
while 1:
try:
self.cursor.execute(sql)
self.connect.commit()
print("create ok")
break
except:
self.delete(tableName)
def delete(self, tableName):
"""删除表
:param tableName:string
:return:None
"""
try:
self.cursor.execute('DROP TABLE {}'.format(tableName))
self.connect.commit()
print("delete ok")
except Exception as e:
print(e)
# finally: # 因为create函数中有while循环,此时不关闭连接
# self.disconnect()