Python calls sqlplus query Oracle

Querying Oracle with Python is, of course, best used with the Cx_oracle library, but sometimes constrained to install Python's third-party library, you have to leverage existing resources and bite the bullet.

Call Sqlplus in Python to query Oracle, first to know what the Sqlplus return result is:

(This is a blank line) number       Name        Address-----------------------------------------1001         Zhang San         Nanjing Road 1002         John Doe         Shang Hai Lu

Line 1th is blank, line 2nd is the field name, line 3rd is a horizontal bar, space is separated, and the 4th line starts with the result of the query.

in the case of the results of the query, according to the 3rd line can be very clear to see the structure, with Python parsing is more convenient. However, if a table field is particularly large and the number of records is quite large, the result of calling the Sqlplus query by default is messy, which requires some setup before invoking the query, such as:

Set linesize 32767set pagesize 9999set term off verify off feedback off tab offset Numwidth 40

Such call query results are more structured. The next step is to parse the query results with powerful python.

#!/usr/bin/python#coding=utf-8 "@author: gemini @ open source China @summary: Sqlplus Query oracles database ' import os;os.environ[' Nls_ LANG '] = ' american_america. Al32utf8 ' gstrconnection = ' username/[email protected]:1521/ora11g ' #解析SqlPlus的查询结果, returns the list Def parsequeryresult ( Listqueryresult): Listresult = [] #如果少于4行, indicating that the query result is empty if Len (Listqueryresult) < 4:return Listresult #第 0 rows is a blank line, the 1th row can get the field name, the 2nd row can get sqlplus the original result in the width of each column, the 3rd line starts is true output # 1 Resolution 2nd row, get each column width, put in the list liststrtmp = Listqueryresult[2].split (" ) Listintwidth = [] for onestr in ListStrTmp:listIntWidth.append (Len (ONESTR)) # 2 parse line 1th, get field name put in the list Li Ststrfieldname = [] Ilastindex = 0 linefieldnames = listqueryresult[1] for iwidth in listintwidth: #截取 [ILa  Stindex, Ilastindex+iwidth) between the string strfieldname = Linefieldnames[ilastindex:ilastindex + iwidth] StrFieldName    = Strfieldname.strip () #去除两端空白符 liststrfieldname.append (strfieldname) Ilastindex = ilastindex + iwidth + 1 # 3 starts with Line 3, parses the result, andEstablish mappings, store to list for I in range (3, Len (Listqueryresult)): Oneliseresult = Unicode (listqueryresult[i], ' UTF-8 ') FieldMap = {} Ilastindex = 0 for J in Range (Len (listintwidth)): Strfieldvalue = Oneliseresult[il Astindex:ilastindex + listintwidth[j]] Strfieldvalue = Strfieldvalue.strip () Fieldmap[liststrfieldna ME[J]] = strfieldvalue Ilastindex = Ilastindex + listintwidth[j] + 1 listresult.append (FIELDMAP) Retu RN listresultdef 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) 

which the value of os.environ[' Nls_lang '] comes from

Select userenv[' language '] from dual;

at the time of invocation, just like:

Listresult = Querybysqlplus (' select * from Studentinfo ')

then you can print out the results in a loop.

The above method assumes that the query operation has no database errors and limited ability.

Python calls sqlplus query Oracle