返回列表 发帖

Python3脚本本地管理Oracle11gR2

笺注:这是在 Navicat连接Oracle11gR2 的基础上进行的。


相关软件的下载链接:https://pan.baidu.com/s/1Bip6wckcZ8eEiMoJIii4lA  提取码:mwoi


Oracle Linux6.9安装Python3:

安装依赖包:(以本地光盘作为Yum源即可)
yum -y install openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel wget gcc-c++ make


下载软件包:
wget https://www.python.org/ftp/python/3.6.8/Python-3.6.8.tgz

编译安装Python3:
tar -zxvf Python-3.6.8.tgz
mv Python-3.6.8 /usr/local/
cd /usr/local/Python-3.6.8/
./configure
make
make install


给新Python创建新链接:
ln -s /usr/local/bin/python3.6 /usr/bin/python3


测试:(Python2和Python3实现共存了)
图片1.png
2020-10-29 08:39






联网安装cx_Oracle模块:
[root@oracle ~]# pip3 install cx_Oracle -i http://mirrors.aliyun.com/pypi/simple --trusted-host=mirrors.aliyun.com
Looking in indexes: http://mirrors.aliyun.com/pypi/simple
Collecting cx_Oracle
  Downloading http://mirrors.aliyun.com/pypi/packages/9f/ce/9117cc84d05800b7962f1842bf5fb20455ee08264cda052a3c79d476cf01/cx_Oracle-8.0.1-cp36-cp36m-manylinux1_x86_64.whl (756kB)
    100% |████████████████████████████████| 757kB 1.4MB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-8.0.1
You are using pip version 18.1, however version 20.2.4 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.


下载cx_Oracle模块:(可选)
[root@oracle ~]# wget http://mirrors.aliyun.com/pypi/packages/9f/ce/9117cc84d05800b7962f1842bf5fb20455ee08264cda052a3c79d476cf01/cx_Oracle-8.0.1-cp36-cp36m-manylinux1_x86_64.whl




创建Python3脚本:(进行本地连接Oracle11gR2)
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'" #查看数据库版本信息
        cursor.execute(sql) # 执行sql语句
        Results=cursor.fetchone() # 获取数据
        print(Results[0]) #输出结果的第一个区块
except Exception as e:
        print(e) # 显示异常对象真正的异常信息
finally:
        db.close() # 关闭数据库连接


[root@oracle ~]# chmod a+x xx.py


[root@oracle ~]# python3 xx.py
-1 DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help


根据提示登录网站查看文档、下载软件包:
https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html


安装相关RPM包:(以本地光盘作为Yum源即可)
[root@oracle ~]# yum -y install oracle-instantclient18.3-basic-18.3.0.0.0-3.x86_64.rpm


[root@oracle ~]# sudo sh -c "echo /usr/lib/oracle/18.3/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"

[root@oracle ~]# sudo ldconfig


输出结果:(Python3脚本本地连接Oracle11gR2成功)
[root@oracle ~]# python3 xx.py
11.2.0.1.0






######

查看数据库允许的最大连接数:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select value from v$parameter where name='processes'" #查看数据库允许的最大连接数
        cursor.execute(sql) # 执行sql语句
        Results=cursor.fetchone() # 获取数据
        print(Results[0]) #输出结果的第一个区块
except Exception as e:
        print(e) # 显示异常对象真正的异常信息
finally:
        db.close() # 关闭数据库连接


输出结果:
[root@oracle ~]# python3 xx.py
150





######

显示多条记录、多个字段:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select * from STU_01"
        cursor.execute(sql) # 执行sql语句
        Results=cursor.fetchall() # 获取数据
        print(Results) # 输出数据
except Exception as e:
        print(e) # 显示异常对象真正的异常信息
finally:
        db.close() # 关闭数据库连接


输出结果:
[root@oracle ~]# python3 xx.py
[(1, '小明', 80), (2, '小李', 90), (3, 'zhuohua', 100)]



###

关键字查询区分英文大小写的:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select * from STU_01 where NAME='zhuohua'"
        cursor.execute(sql) # 执行sql语句
        Results=cursor.fetchall() # 获取数据
        print(Results) # 输出数据
except:
        pass #占位符
finally:
        db.close() # 关闭数据库连接


输出结果:
[root@oracle ~]# python3 xx.py
[(3, 'zhuohua', 100)]


###
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select * from STU_01 where NAME='Zhuohua'"
        cursor.execute(sql) # 执行sql语句
        Results=cursor.fetchall() # 获取数据
        print(Results) # 输出数据
except:
        pass #占位符
finally:
        db.close() # 关闭数据库连接

输出结果:
[root@oracle ~]# python3 xx.py
[]





######

查询中只取一个值:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select AGE from STU_01 where NAME='zhuohua'"
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results[0]) #输出结果的第一个区块
except:
        print("没有找到匹配的记录")
finally:
        db.close() # 关闭数据库连接


输出结果:(找到匹配记录时)
[root@oracle ~]# python3 xx.py
100

输出结果:(没有找到匹配记录时)
[root@oracle ~]# python3 xx.py
没有找到匹配的记录



###

查询中使用like语句:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select AGE from STU_01 where NAME like '%zhuo%'"
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results[0]) #输出结果的第一个区块
except:
        print("没有找到匹配的记录")
finally:
        db.close() # 关闭数据库连接


输出结果:(找到匹配记录时)
[root@oracle ~]# python3 xx.py
100

输出结果:(没有找到匹配记录时)
[root@oracle ~]# python3 xx.py
没有找到匹配的记录




###

查询中使用聚会函数:
[root@oracle ~]# cat xx.py
#coding=utf-8
import cx_Oracle
try:
        db=cx_Oracle.connect('happy/mima@127.0.0.1:1521/orcl')
        cursor=db.cursor() # 游标操作
        sql="select min(AGE) from STU_01"
        cursor.execute(sql)
        Results=cursor.fetchone()
        print(Results[0]) #输出结果的第一个区块
except:
        pass #占位符
finally:
        db.close() # 关闭数据库连接


输出结果:
[root@oracle ~]# python3 xx.py
80







######

卸载cx_Oracle模块:
[root@oracle ~]# pip3 uninstall cx_Oracle -y
Uninstalling cx-Oracle-8.0.1:
  Successfully uninstalled cx-Oracle-8.0.1


离线安装cx_Oracle模块:
[root@oracle ~]# ls *.whl
cx_Oracle-8.0.1-cp36-cp36m-manylinux1_x86_64.whl
[root@oracle ~]#
[root@oracle ~]# pip3 install cx_Oracle-8.0.1-cp36-cp36m-manylinux1_x86_64.whl
Processing ./cx_Oracle-8.0.1-cp36-cp36m-manylinux1_x86_64.whl
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-8.0.1








######

修改数据库Oracle11gR2允许的最大连接数:
SQL> Alter system set processes=300 scope=spfile;
Alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use



解决办法:
SQL> conn /as sysdba;
Connected.
SQL>
SQL> show user;
USER is "SYS"

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             281022296 bytes
Database Buffers          780140544 bytes
Redo Buffers                5554176 bytes
Database mounted.
Database opened.
SQL>



修改数据库允许的最大连接数为300:
SQL> Alter system set processes=300 scope=spfile;

System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             281022296 bytes
Database Buffers          780140544 bytes
Redo Buffers                5554176 bytes
Database mounted.
Database opened.


修改数据库允许的最大连接数成功:
SQL> select value from v$parameter where name='processes';

VALUE
--------------------------------------------------------------------------------
300





相关文章:
Python3脚本远程管理Oracle11gR2
CentOS8_Python3脚本远程管理Oracle11gR2

返回列表