python更新Oracle数据

python要连接oracle，需要安装instantclient和cx_Oracle。

1、准备环境cx_Oracle
#!/bin/sh

yum -y install git python-devel libaio libaio-devel python3-devel
git clone https://github.com/oracle/python-cx_Oracle.git cx_Oracle

cd cx_Oracle
git submodule init
git submodule update
python3 setup.py install

###############
In file included from src/cxoApiType.c:11:0:
src/cxoModule.h:14:20: fatal error: Python.h: No such file or directory
.x86_64
yum -y install python3-devel

Installed /usr/local/lib64/python3.6/site-packages/cx_Oracle-8.0.0.dev0-py3.6-linux-x86_64.egg

2、准备oracle上传
instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /opt
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0 -d /opt
cd /opt/instantclient_11_2
ldd sqlplus检查是否能识别
sqlplus /nolog

3、让环境识别oracle库文件
cat /etc/ld.so.conf.d/ora.conf << EOF
/opt//opt/instantclient_11_2
EOF
ldconfig

4、cat 1undate_user_from_cid_uid.py
#!/usr/bin/python3
#cursor.execute('select tablespace_name,file_name from dba_data_files')
#all_data=cursor.fetchall()
#print(all_data)
#cursor.close()

import hashlib
import sys
import random
import string
import cx_Oracle

def md5(pwd):
obj = hashlib.md5()
obj.update(pwd.encode("utf-8"))
return obj.hexdigest()

pw = ''.join(random.sample(string.ascii_letters + string.digits, 8))
#print(pw,md5(pw))

if __name__ == '__main__':
if len(sys.argv) < 2 :
print(f'Usage: {sys.argv[0]} cid uid')
sys.exit()
cid= sys.argv[1]
uid= sys.argv[2]

conn = cx_Oracle.connect('uid/pw@ip:1521/service')
cursor = conn.cursor()

maindb1 = f"update r1 set password='{md5(pw)}' where company_id={cid} and user_id='{uid}'"
maindb2 = f"update r1@MAINDB set password='{md5(pw)}' where company_id={cid} and user_id='{uid}'"

try:
cursor.execute(maindb1)
cursor.execute(maindb2)
except Exception as e:
conn.rollback()
print('failed', e)
else:
conn.commit()
print('update success', cursor.rowcount)
#print (cid,uid,pw,md5(pw))
print (cid,uid,pw)

cursor.close()
conn.close()

相关日志