Python calls Sqlplus to manipulate and parse Oracle database methods

Let's look at a simple example of using Python to call Sqlplus to output results:

Import osimport sysfrom subprocess import Popen, PIPE sql = "" "Set linesize 400col owner for A10col object_name for A30 se Lect owner, object_name from Dba_objects where rownum<=10; "" "proc = Popen ([" Sqlplus ","-S ","/"," as "," SYSDBA "], Stdo Ut=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

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.

This encapsulates a function that queries and resolves results based on incoming SQL statements, saves each row of results to a list, and each element in the list is a mapping of the field name to the value.

#!/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/password@10.123.5.123: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行是空行, line 1th can be  Take the field name, line 2nd can get sqlplus The original result in the width of each column, line 3rd starts is true output # 1 parse the 2nd row, get each column width, put in the list liststrtmp = Listqueryresult[2].split (") listintwidth = [] for onestr in ListStrTmp:listIntWidth.append (Len (ONESTR)) # 2 resolves line 1th, gets field name placed in list liststrfieldname = [] Ilasti    Ndex = 0 Linefieldnames = listqueryresult[1] for iwidth in Listintwidth: string between #截取 [Ilastindex, Ilastindex+iwidth] strFieldName = Linefieldnames[ilastindex:ilastindex + iwidth] strfieldname = Strfieldname.strip () #去除两端空白符 listStrFi Eldname.append (strfieldname) Ilastindex = ilastindex + iwidth + 1 # 3 Line 3 begins, parses the result, and establishes the map, stored in the list for I in range (3, Len (l    Istqueryresult)):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.appen  D (FIELDMAP) return listresultdef querybysqlplus (sqlCommand): Global gstrconnection #构造查询命令 strcommand = ' sqlplus-s%s <
 
Where 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.
