笺注:这是在 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实现共存了)
联网安装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 |