cx_Oracle操作oracle数据库
cx_Oracle 操作oracle数据库
1、安装配置cx_Oracle模块
Window系统配置
1）安装cx_oracle模块
pip3 install cx_Oracle
2）下载所需的软件包，instantclient-basic-windows.x64-11.2.0.4.0.zip（根据⾃⼰连接oracle数据库版本和系统位数下载）
3）解压instantclient-basic-windows.x64-11.2.0.4.0.zip，然后点击运⾏instantclient_11_2⾥的
4）双击安装
5）配置path变量
a）电脑-属性-⾼级系统设置-⾼级-环境变量，将instantclient_11_2路径C:\Program Files\instantclient_11_2添加到path环境变量中；如果本地还安装有oracle数据库，要添加到oracle的路径前⾯
b）电脑-属性-⾼级系统设置-⾼级-环境变量-新建⽤户变量：
ORACLE_HOME=C:\Program Files\instantclient_11_2 ,
TNS_ADMIN=C:\Program Files\instantclient_11_2，
NLS_LANG= C:\Program Files\instantclient_11_2，NLS_LANG是为了防⽌中⽂乱码
6）将instantclient_11_2⽬录下oci.dll，oraocci11.dll，oraociei11.dll、ocijdbc11.dll复制到python\Lib\site-packages⽬录下
7）创建⼀个监听⽂件a到C:\Program Files\instantclient_11_2，打开添加以下内容，并保存
oral=
(DESCRIPTION =
jdbc连接oracle
(ADDRESS = (PROTOCOL = TCP)(HOST= IP地址)(PORT =端⼝))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =数据库名称)
) )
Linux系统配置
1）安装cx_Oracle
python pip install cx_Oracle
2）安装libaio
sudo yum install libaio
3）下载instantclient-basic-linux.x64-21.1.0.0.0.zip和instantclient-sdk-linux.x64-21.1.0.0.0.zip；
mkdir -p /opt/oracle
cd /opt/oracle
unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
删除instantclient-sdk-linux.x64-21.1.0.0.0的SDK⽬录下的admin⽂件夹，然后将SDK⽬录下所有⽂件复制
到/opt/oracle/instantclient_21_1⽬录下
4）配置环境变量：
export ORACLE_HOME=/opt/oracle/instantclient_21_1
export PATH=$PATH:$ORACLE_HOME
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
# 下⾯两⾏是解决编码问题，如果不设置下⾯两⾏，从oracle导出中⽂数据会出现乱码
export LANG="zh_CN.UTF-8"
export NLS_LANG=AMERICAN_AMERICA.UTF8
export TNS_ADMIN=/opt/oracle/instantclient_21_1
source /etc/profile
5）移动a⾄/opt/oracle/instantclient_21_1/network/admin⽬录下
6）copy libclntsh.so到python site-packages
7）查看 libclntsh.so⽂件内容
vim libgmp.so
libclntsh.so.21.1
建⽴软连接：
ln -s libclntsh.so.21.1 libclntsh.so
8）vim /etc/f
添加⼀⾏：
/opt/oracle/instantclient_21_1
运⾏：ldconfig
9）sudo sh -c "echo /opt/oracle/instantclient_21_1 > /etc/f.f"
sudo ldconfig
2、python封装 cx_orcale操作
数据库配置:
>>## 测试_内⽹ >>##
# host_name: '127.0.0.1'
# port: 8080
# service_name: 'service_name'
# username: 'username'
# password: 'password'
>>## 测试_外⽹ >>##
host_name: '127.0.0.1'
port: 8080
sid: 'sid'
username: 'username'
password: 'password'
import cx_Oracle
import os
import sys
import yaml
sys.setrecursionlimit(10000) # 例如这⾥设置为⼀万
# sys.path.insert(0, r'C:\Program Files\My Program Files\instantclient_12_2;')
''' 公共类'''
class MyOracle(object):
def__init__(self, db_path, db_name):
'''
:param db_path: 获取数据库配置⽂件路径
:
param db_name: 数据库名
'''
self.db_conf = self._get_yamlMsg(db_path).get(db_name)
self.hostname = self.('host_name')
self.port = self.('port')
self.sid = self.('sid', None)
self.service_name = self.('service_name', None)
self.username = self.('username')
self.password = self.('password')
def _get_yamlMsg(self, sql_path):
'''
# open⽅法打开直接读出来
:return:
'''
with open(sql_path, 'r', encoding='utf-8') as f:
sqlMsg = f.read()
# ⽤load⽅法转字典
sql_dic = yaml.load(sqlMsg)
return sql_dic
def get_connection(self):
try:
if self.service_name:
tnsname = cx_Oracle.makedsn(self.hostname, self.port, service_name=self.service_name) else:
tnsname = cx_Oracle.makedsn(self.hostname, self.port, self.sid)
< = t(self.username, self.password, tnsname)
except Exception as e:
print('连接数据库出错：', e)
def get_cur(self):
ursor()
def excute_one(self, sql):
try:
cur = _cur()
# cur.prepare('select * from t_emp a pid=:id')
# ute(None,{'id':id})
line = cur.fetchone()
except Exception as e:
print('数据库查询出错：', e)
cur.close()
<_close()
cur.close()
return line
def excute_some(self, sql):
try:
cur = _cur()
lines = cur.fetchall()
except Exception as e:
print('数据库查询出错：', e)
cur.close()
<_close()
cur.close()
return lines
def excute(self, sql):
try:
cur = _cur()
effectRow = wcount
except Exception as e:
print('数据库出错：', e)
cur.close()
<_close()
cur.close()
return effectRow
def con_close(self):
if__name__ == '__main__':
# 获取数据库配置⽂件路径
db_path = os.path.join(os.path.dirname(os.path.dirname(alpath(__file__))), 'conf', "application.yaml") db = MyOracle(db_path, '数据库配置')
<_connection()
ute_some('''select * from dual'''))
<_close()
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
ojdbc与oracle的对应关系
« 上一篇
java oracle存储过程写法及调用
下一篇 »
发表评论