Python调用SQLPlus来操作和解析Oracle数据库的方法
先来看一个简单的利用python调用sqlplus来输出结果的例子：
import os import sys from subprocess import Popen, PIPE sql = """ set linesize 400 col owner for a10 col object_name for a30 select owner, object_name from dba_objects where rownum<=10; """ proc = Popen(["sqlplus", "-S", "/", "as", "sysdba"], stdout=PIPE, stdin=PIPE, stderr=PIPE) proc.stdin.write(sql) (out, err) = proc.communicate() if proc.returncode != 0: print err sys.exit(proc.returncode) else: print out
用Python查询Oracle，当然最好用cx_Oracle库，但有时候受到种种限制，不能安装Python第三方库，就得利用现有资源，硬着头皮上了。
用Python调用SqlPlus查询Oracle，首先要知道SqlPlus返回结果是什么样的：
（这是空行） Number Name Address ------------ ----------- ------------------ 1001 张三 南京路 1002 李四 上海路
第1行是空行，第2行是字段名称，第3行都是横杠，有空格隔开，第4行开始是查询到的结果。
在查询结果规整的情况下，根据第3行可以很清晰的看到结构，用Python解析起来也比较方便。但是，如果一张表字段特别多，记录数也相当多，那么默认情况下调用SqlPlus查询出的结果会比较乱，这就需要在调用查询之前做一些设定，比如：
set linesize 32767 set pagesize 9999 set term off verify off feedback off tab off set numwidth 40
这样的调用查询结果就比较规整了。接下来就是用强大的Python来解析查询结果。
这里封装了一个函数，可以根据传入的SQL语句查询并解析结果，将每行结果存到列表中，列表中的每个元素是一个字段名称与值的映射。
#!/usr/bin/python
#coding=UTF-8
'''
@author: 双子座@开源中国
@summary: 通过SqlPlus查询Oracles数据库
'''
import os;
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
gStrConnection = 'username/password@10.123.5.123:1521/ora11g'
#解析SqlPlus的查询结果，返回列表
def parseQueryResult(listQueryResult):
listResult = []
#如果少于4行，说明查询结果为空
if len(listQueryResult) < 4:
return listResult
#第0行是空行，第1行可以获取字段名称，第2行可获取SQLPlus原始结果中每列宽度，第3行开始是真正输出
# 1 解析第2行，取得每列宽度，放在列表中
listStrTmp = listQueryResult[2].split(' ')
listIntWidth = []
for oneStr in listStrTmp:
listIntWidth.append(len(oneStr))
# 2 解析第1行，取得字段名称放在列表中
listStrFieldName = []
iLastIndex = 0
lineFieldNames = listQueryResult[1]
for iWidth in listIntWidth:
#截取[iLastIndex, iLastIndex+iWidth)之间的字符串
strFieldName = lineFieldNames[iLastIndex:iLastIndex + iWidth]
strFieldName = strFieldName.strip() #去除两端空白符
listStrFieldName.append(strFieldName)
iLastIndex = iLastIndex + iWidth + 1
# 3 第3行开始，解析结果，并建立映射，存储到列表中
for i in range(3, len(listQueryResult)):
oneLiseResult = unicode(listQueryResult[i], 'UTF-8')
fieldMap = {}
iLastIndex = 0
for j in range(len(listIntWidth)):
strFieldValue = oneLiseResult[iLastIndex:iLastIndex + listIntWidth[j]]
strFieldValue = strFieldValue.strip()
fieldMap[listStrFieldName[j]] = strFieldValue
iLastIndex = iLastIndex + listIntWidth[j] + 1
listResult.append(fieldMap)
return listResult
def QueryBySqlPlus(sqlCommand):
global gStrConnection
#构造查询命令
strCommand = 'sqlplus -S %s <<!\n' % gStrConnection
strCommand = strCommand + 'set linesize 32767\n'
strCommand = strCommand + 'set pagesize 9999\n'
strCommand = strCommand + 'set term off verify off feedback off tab off \n'
strCommand = strCommand + 'set numwidth 40\n'
strCommand = strCommand + sqlCommand + '\n'
#调用系统命令收集结果
result = os.popen(strCommand)
list = []
for line in result:
list.append(line)
return parseQueryResult(list)
其中os.environ['NLS_LANG']的值来自
select userenv['language'] from dual;
listResult = QueryBySqlPlus('select * from studentinfo')
然后就可以用循环打印出结果了。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1379
52
PHP and Python: Code Examples and Comparison
Apr 15, 2025 am 12:07 AM
PHP and Python have their own advantages and disadvantages, and the choice depends on project needs and personal preferences. 1.PHP is suitable for rapid development and maintenance of large-scale web applications. 2. Python dominates the field of data science and machine learning.
How is the GPU support for PyTorch on CentOS
Apr 14, 2025 pm 06:48 PM
Enable PyTorch GPU acceleration on CentOS system requires the installation of CUDA, cuDNN and GPU versions of PyTorch. The following steps will guide you through the process: CUDA and cuDNN installation determine CUDA version compatibility: Use the nvidia-smi command to view the CUDA version supported by your NVIDIA graphics card. For example, your MX450 graphics card may support CUDA11.1 or higher. Download and install CUDAToolkit: Visit the official website of NVIDIACUDAToolkit and download and install the corresponding version according to the highest CUDA version supported by your graphics card. Install cuDNN library:
Detailed explanation of docker principle
Apr 14, 2025 pm 11:57 PM
Docker uses Linux kernel features to provide an efficient and isolated application running environment. Its working principle is as follows: 1. The mirror is used as a read-only template, which contains everything you need to run the application; 2. The Union File System (UnionFS) stacks multiple file systems, only storing the differences, saving space and speeding up; 3. The daemon manages the mirrors and containers, and the client uses them for interaction; 4. Namespaces and cgroups implement container isolation and resource limitations; 5. Multiple network modes support container interconnection. Only by understanding these core concepts can you better utilize Docker.
Python vs. JavaScript: Community, Libraries, and Resources
Apr 15, 2025 am 12:16 AM
Python and JavaScript have their own advantages and disadvantages in terms of community, libraries and resources. 1) The Python community is friendly and suitable for beginners, but the front-end development resources are not as rich as JavaScript. 2) Python is powerful in data science and machine learning libraries, while JavaScript is better in front-end development libraries and frameworks. 3) Both have rich learning resources, but Python is suitable for starting with official documents, while JavaScript is better with MDNWebDocs. The choice should be based on project needs and personal interests.
What to do after centos stops maintenance
Apr 14, 2025 pm 08:48 PM
After CentOS is stopped, users can take the following measures to deal with it: Select a compatible distribution: such as AlmaLinux, Rocky Linux, and CentOS Stream. Migrate to commercial distributions: such as Red Hat Enterprise Linux, Oracle Linux. Upgrade to CentOS 9 Stream: Rolling distribution, providing the latest technology. Select other Linux distributions: such as Ubuntu, Debian. Evaluate other options such as containers, virtual machines, or cloud platforms.
Centos options after stopping maintenance
Apr 14, 2025 pm 08:51 PM
CentOS has been discontinued, alternatives include: 1. Rocky Linux (best compatibility); 2. AlmaLinux (compatible with CentOS); 3. Ubuntu Server (configuration required); 4. Red Hat Enterprise Linux (commercial version, paid license); 5. Oracle Linux (compatible with CentOS and RHEL). When migrating, considerations are: compatibility, availability, support, cost, and community support.
How to install nginx in centos
Apr 14, 2025 pm 08:06 PM
CentOS Installing Nginx requires following the following steps: Installing dependencies such as development tools, pcre-devel, and openssl-devel. Download the Nginx source code package, unzip it and compile and install it, and specify the installation path as /usr/local/nginx. Create Nginx users and user groups and set permissions. Modify the configuration file nginx.conf, and configure the listening port and domain name/IP address. Start the Nginx service. Common errors need to be paid attention to, such as dependency issues, port conflicts, and configuration file errors. Performance optimization needs to be adjusted according to the specific situation, such as turning on cache and adjusting the number of worker processes.
How to operate distributed training of PyTorch on CentOS
Apr 14, 2025 pm 06:36 PM
PyTorch distributed training on CentOS system requires the following steps: PyTorch installation: The premise is that Python and pip are installed in CentOS system. Depending on your CUDA version, get the appropriate installation command from the PyTorch official website. For CPU-only training, you can use the following command: pipinstalltorchtorchvisiontorchaudio If you need GPU support, make sure that the corresponding version of CUDA and cuDNN are installed and use the corresponding PyTorch version for installation. Distributed environment configuration: Distributed training usually requires multiple machines or single-machine multiple GPUs. Place
