Python calls SQLPlus to operate and parse the Oracle database, sqlplusoracle

Python calls SQLPlus to operate and parse the Oracle database, sqlplusoracle

Let's look at a simple example of using python to call sqlplus to output the result:

import osimport sysfrom subprocess import Popen, PIPE sql = """set linesize 400col owner for a10col 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

If you use Python to query Oracle, you are advised to use the cx_Oracle database. However, if you are subject to various restrictions and cannot install a third-party Python library, you can use existing resources and stick to your head.

When using Python to call SqlPlus to query Oracle, you must first know what the results returned by SqlPlus are:

(This is blank line) Number Name Address ------------ ----------- ---------------- 1001 Zhang San Nanjing Road 1002 Li Si Shanghai Road

Rows 1st are empty rows, 2nd are field names, and 3rd are horizontal bars separated by spaces. Rows 4th start with query results.

When the query results are regular, the structure can be clearly displayed based on the 3rd rows, which is easier to parse using Python. However, if a table contains a large number of fields and a large number of records, the results returned by calling SqlPlus will be messy by default, which requires some settings before calling the query, for example:

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

The query results of such calls are relatively regular. The next step is to use powerful Python to parse the query results.

A function is encapsulated here. You can query and parse the result based on the input SQL statement and save the result of each row to the list. Each element in the list is a ing between field names and values.

#! /Usr/bin/python # coding = UTF-8 ''' @ author: Gemini @ open source China @ summary: Query through SqlPlus doneles database ''' import OS; OS. environ ['nls _ LANG '] = 'American _ AMERICA. AL32UTF8 'gstrconnection = 'username/password@10.123.5.123: 1521/ora11g' # parse SqlPlus query results, return list def parseQueryResult (listQueryResult): listResult = [] # If less than 4 rows, the query result is empty. if len (listQueryResult) <4: return listResult # Row 0th is empty, field name can be obtained in row 1st, and width of each column in original SQLPlus results can be obtained in row 2nd, the first line is the real output #1 Parse 2nd rows and get the width of each column. Put listStrTmp = listQueryResult in the list [2]. split ('') listIntWidth = [] for oneStr in listStrTmp: listIntWidth. append (len (oneStr) #2 parse the 1st rows and obtain the field name in the list. listStrFieldName = [] iLastIndex = 0 lineFieldNames = listQueryResult [1] for iWidth in listIntWidth: # truncate the string strFieldName = lineFieldNames [iLastIndex: iLastIndex + iWidth] strFieldName = strFieldName. strip () # Remove the blank character listStrFieldName at both ends. append (strFieldName) iLastIndex = iLastIndex + iWidth + 1 #3 3rd rows start, parse the result, create a ing, and store it in the 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 [iLastIndex: iLastIndex + listIntWidth [j] strFieldValue = strFieldValue. strip () fieldMap [listStrFie LdName [j] = strFieldValue iLastIndex = iLastIndex + listIntWidth [j] + 1 listResult. append (fieldMap) return listResultdef QueryBySqlPlus (sqlCommand): global gStrConnection # construct the query command 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' # Call the system command to collect the result = OS. popen (strCommand) list = [] for line in result: list. append (line) return parseQueryResult (list)

The OS. environ ['nls _ LANG '] value comes from

select userenv['language'] from dual;

When calling, as long as it is similar:

listResult = QueryBySqlPlus('select * from studentinfo')

Then you can print the results in a loop.

Articles you may be interested in:

• How to import oracle Data Using Python
• How to connect python to an Oracle database
• Python uses the cx_Oracle module to export data from oracle to csv files
• Connect python to an oracle database instance