5.1.1.Fetch Methods

Rows can be fetched in various ways.

• AfterCursor.execute(), the cursor is returned as a convenience. Thisallows code to iterate over rows like:

  cursor=connection.cursor()forrowincursor.execute("select * from MyTable"):print(row)

• Rows can also be fetched one at a time using the methodCursor.fetchone():

  cursor=connection.cursor()cursor.execute("select * from MyTable")whileTrue:row=cursor.fetchone()ifrowisNone:breakprint(row)

• If rows need to be processed in batches, the methodCursor.fetchmany()can be used. The size of the batch is controlled by thesize parameter,which defaults to the value ofCursor.arraysize.

  cursor=connection.cursor()cursor.execute("select * from MyTable")num_rows=10whileTrue:rows=cursor.fetchmany(size=num_rows)ifnotrows:breakforrowinrows:print(row)

  Note thesize parameter only affects the number of rows returned to theapplication, not to the internal buffer size used for tuning fetchperformance. That internal buffer size is controlled only by changingCursor.arraysize, seeTuning Fetch Performance.

• If all of the rows need to be fetched and can be contained in memory, themethodCursor.fetchall() can be used.

  cursor=connection.cursor()cursor.execute("select * from MyTable")rows=cursor.fetchall()forrowinrows:print(row)

  The fetch methods return data as tuples. To return results as dictionaries, seeChanging Query Results with Rowfactories.

• Data can also be fetched in data frame format, seeWorking with Data Frames.

5.1.2.Closing Cursors

Once cursors are no longer needed, they should be closed in order to reclaimresources in the database. Note cursors may be used to execute multiplestatements before being closed.

Cursors can be closed in various ways:

• A cursor will be closed automatically when the variable referencing it goesout of scope (and no further references are retained). Awith contextmanager block is a convenient and preferred way to ensure this. For example:

  withconnection.cursor()ascursor:forrowincursor.execute("select * from MyTable"):print(row)

  This code ensures that once the block is completed, the cursor is closed anddatabase resources can be reclaimed. In addition, any attempt to use thevariablecursor outside of the block will fail.

• Cursors can be explicitly closed by callingCursor.close():

  cursor=connection.cursor()...cursor.close()

5.1.3.Query Column Metadata

After executing a query, the column metadata such as column names and data typescan be obtained usingCursor.description:

withconnection.cursor()ascursor:cursor.execute("select * from MyTable")forcolumnincursor.description:print(column)

This could result in metadata like:

('ID', <class 'oracledb.DB_TYPE_NUMBER'>, 39, None, 38, 0, 0)('NAME', <class 'oracledb.DB_TYPE_VARCHAR'>, 20, 20, None, None, 1)

To extract the column names from a query you can use code like:

withconnection.cursor()ascursor:cursor.execute("select * from locations")columns=[col.nameforcolincursor.description]print(columns)forrincursor:print(r)

This will print:

['LOCATION_ID','STREET_ADDRESS','POSTAL_CODE','CITY','STATE_PROVINCE','COUNTRY_ID'](1000,'1297 Via Cola di Rie','00989','Roma',None,'IT')(1100,'93091 Calle della Testa','10934','Venice',None,'IT')...

Changing Column Names to Lowercase

To change all column names to lowercase you could do:

cursor.execute("select * from locations where location_id = 1000")columns=[col.name.lower()forcolincursor.description]print(columns)

The output is:

['location_id','street_address','postal_code','city','state_province','country_id']

5.1.4.Fetch Data Types

The following table provides a list of all of the data types that python-oracledbknows how to fetch. The middle column gives the type that is returned in thequery metadata. The last column gives the type ofPython object that is returned by default. Python types can be changed withOutput Type Handlers.

If the precision and scale obtained from query column metadata indicatethat the value can be expressed as an integer, the value will bereturned as an int. If the column is unconstrained (no precision andscale specified), the value will be returned as a float or an intdepending on whether the value itself is an integer. In all other casesthe value is returned as a float.

The timestamps returned are naive timestamps without any time zoneinformation present.

These include all user-defined types such as VARRAY, NESTED TABLE, etc.

If the JSON is an object, then a dict is returned. If it is an array,then a list is returned. If it is a scalar value, then that particularscalar value is returned.

5.1.5.Changing Fetched Data

Data returned by python-oracledb queries can be changed by using output typehandlers, by using “outconverters”, or by using row factories.

handler(cursor,metadata)

defoutput_type_handler(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(oracledb.DB_TYPE_VARCHAR,arraysize=cursor.arraysize)

cursor.outputtypehandler=output_type_handlercursor.execute("select 123 from dual")r=cursor.fetchone()print(r)

cursor.outputtypehandler=None

defoutput_type_handler(cursor,metadata):defout_converter(d):ifisinstance(d,str):returnf"{d} was a string"else:returnf"{d} was not a string"ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(oracledb.DB_TYPE_VARCHAR,arraysize=cursor.arraysize,outconverter=out_converter)

cursor.outputtypehandler=output_type_handlercursor.execute("select 123 as col1, 'abc' as col2 from dual")forrincursor.fetchall():print(r)

('123 was a string','abc')

cursor.execute("select * from locations where location_id = 1000")columns=[col.nameforcolincursor.description]cursor.rowfactory=lambda*args:dict(zip(columns,args))data=cursor.fetchone()print(data)

{'LOCATION_ID':1000,'STREET_ADDRESS':'1297 Via Cola di Rie','POSTAL_CODE':'00989','CITY':'Roma','STATE_PROVINCE':None,'COUNTRY_ID':'IT'}

selectcat_name,cats.colorascat_color,dog_name,dogs.colorfromcats,dogs

defoutput_type_handler(cursor,metadata):defout_converter(d):iftype(d)isstr:returnf"{d} was a string"else:returnf"{d} was not a string"ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(oracledb.DB_TYPE_VARCHAR,arraysize=cursor.arraysize,outconverter=out_converter)cursor.outputtypehandler=output_type_handlercursor.execute("select 123 as col1, 'abc' as col2 from dual")columns=[col.name.lower()forcolincursor.description]cursor.rowfactory=lambda*args:dict(zip(columns,args))forrincursor.fetchall():print(r)

{'col1':'123 was a string','col2':'abc'}

5.1.6.Fetched Number Precision

Oracle Database uses decimal numbers and these cannot be converted seamlesslyto binary number representations like Python floats. In addition, the range ofOracle numbers exceeds that of floating point numbers. Python has decimalobjects which do not have these limitations. In python-oracledb you can setdefaults.fetch_decimals so that Decimals are returned to theapplication, ensuring that numeric precision is not lost when fetching certainnumbers.

The following code sample demonstrates the issue:

cursor.execute("create table test_float (X number(5, 3))")cursor.execute("insert into test_float values (7.1)")cursor.execute("select * from test_float")val,=cursor.fetchone()print(val,"* 3 =",val*3)

This displays7.1*3=21.299999999999997

Using Python decimal objects, however, there is no loss of precision:

oracledb.defaults.fetch_decimals=Truecursor.execute("select * from test_float")val,=cursor.fetchone()print(val,"* 3 =",val*3)

This displays7.1*3=21.3

Seesamples/return_numbers_as_decimals.py

An equivalent, longer, older coding idiom todefaults.fetch_decimals isto use anoutput type handler do the conversion.

importdecimaldefnumber_to_decimal(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(decimal.Decimal,arraysize=cursor.arraysize)cursor.outputtypehandler=number_to_decimalcursor.execute("select * from test_float")val,=cursor.fetchone()print(val,"* 3 =",val*3)

This displays7.1*3=21.3

The Pythondecimal.Decimal converter gets called with the stringrepresentation of the Oracle number. The output fromdecimal.Decimal isreturned in the output tuple.

5.1.7.Scrollable Cursors

Scrollable cursors enable applications to move backwards, forwards, to skiprows, and to move to a particular row in a query result set. The result set iscached on the database server until the cursor is closed. In contrast, regularcursors are restricted to moving forward.

A scrollable cursor is created by setting the parameterscrollable=Truewhen creating the cursor. The methodCursor.scroll() is used to move todifferent locations in the result set.

Examples are:

cursor=connection.cursor(scrollable=True)cursor.execute("select * from ChildTable order by ChildId")cursor.scroll(mode="last")print("LAST ROW:",cursor.fetchone())cursor.scroll(mode="first")print("FIRST ROW:",cursor.fetchone())cursor.scroll(8,mode="absolute")print("ROW 8:",cursor.fetchone())cursor.scroll(6)print("SKIP 6 ROWS:",cursor.fetchone())cursor.scroll(-4)print("SKIP BACK 4 ROWS:",cursor.fetchone())

Seesamples/scrollable_cursors.py for a runnable example.

5.1.8.Fetching Oracle Database Objects and Collections

Oracle Database named object types and user-defined types can be fetcheddirectly in queries. Each item is represented as aPython object corresponding to the Oracle Database object. This Python objectcan be traversed to access its elements. Attributes includingDbObjectType.name andDbObjectType.iscollection, and methodsincludingDbObject.aslist() andDbObject.asdict() are available.

For example, if a table MYGEOMETRYTAB contains a column GEOMETRY ofOracle’s predefined Spatial object typeSDO_GEOMETRY,then it can be queried and printed:

cursor.execute("select geometry from mygeometrytab")forobj,incursor:dumpobject(obj)

Wheredumpobject() is defined as:

defdumpobject(obj,prefix=""):ifobj.type.iscollection:print(prefix,"[")forvalueinobj.aslist():ifisinstance(value,oracledb.Object):dumpobject(value,prefix+"  ")else:print(prefix+"  ",repr(value))print(prefix,"]")else:print(prefix,"{")forattrinobj.type.attributes:value=getattr(obj,attr.name)ifisinstance(value,oracledb.Object):print(prefix+"   "+attr.name+":")dumpobject(value,prefix+"  ")else:print(prefix+"   "+attr.name+":",repr(value))print(prefix,"}")

This might produce output like:

{SDO_GTYPE:2003SDO_SRID:NoneSDO_POINT:{X:1Y:2Z:3}SDO_ELEM_INFO:[110033]SDO_ORDINATES:[1157]}

Other information on using Oracle objects is inUsing Bind Variables.

Performance-sensitive applications should consider using scalar types instead ofobjects. If you do use objects, avoid callingConnection.gettype()unnecessarily, and avoid objects with large numbers of attributes.

5.1.9.Limiting Rows

Query data is commonly broken into one or more sets:

• To give an upper bound on the number of rows that a query has to process,which can help improve database scalability.

• To perform ‘Web pagination’ that allows moving from one set of rows to anext, or previous, set on demand.

• For fetching of all data in consecutive small sets for batch processing.This happens because the number of records is too large for Python to handleat one time.

The latter can be handled by callingCursor.fetchmany() with oneexecution of the SQL query.

‘Web pagination’ and limiting the maximum number of rows are detailed in thissection. For each ‘page’ of results, a SQL query is executed to get theappropriate set of rows from a table. Since the query may be executed morethan once, ensure to usebind variables for row numbers androw limits.

Oracle Database 12c SQL introduced anOFFSET /FETCH clause which issimilar to theLIMIT keyword of MySQL. In Python, you can fetch a set ofrows using:

myoffset=0# do not skip any rows (start at row 1)mymaxnumrows=20# get 20 rowssql="""SELECT last_name     FROM employees     ORDER BY last_name     OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY"""withconnection.cursor()ascursor:cursor.prefetchrows=mymaxnumrows+1cursor.arraysize=mymaxnumrowsforrowincursor.execute(sql,offset=myoffset,maxnumrows=mymaxnumrows):print(row)

In applications where the SQL query is not known in advance, this methodsometimes involves appending theOFFSET clause to the ‘real’ user query. Bevery careful to avoid SQL injection security issues.

For Oracle Database 11g and earlier there are several alternative waysto limit the number of rows returned. The old, canonical paging queryis:

SELECT*FROM(SELECTa.*,ROWNUMASrnumFROM(YOUR_QUERY_GOES_HERE--includingtheorderby)aWHEREROWNUM<=MAX_ROW)WHERErnum>=MIN_ROW

Here,MIN_ROW is the row number of first row andMAX_ROW is the rownumber of the last row to return. For example:

SELECT*FROM(SELECTa.*,ROWNUMASrnumFROM(SELECTlast_nameFROMemployeesORDERBYlast_name)aWHEREROWNUM<=20)WHERErnum>=1

This always has an ‘extra’ column, here called RNUM.

An alternative and preferred query syntax for Oracle Database 11g uses theanalyticROW_NUMBER() function. For example, to get the 1st to 20th names thequery is:

SELECTlast_nameFROM(SELECTlast_name,ROW_NUMBER()OVER(ORDERBYlast_name)ASmyrFROMemployees)WHEREmyrBETWEEN1and20

Ensure to usebind variables for the upper and lower limitvalues.

5.1.10.Fetching Data in Parallel

The performance benefit of selecting table data in parallel from OracleDatabase compared with a executing a single query depends on manyfactors. Partitioning the table and reading one partition per connection isusually the most efficient database-side solution. However, even if a parallelsolution appears to be faster, it could be inefficient, thereby impacting, oreventually being limited by, everyone else. Only benchmarking in yourenvironment will determine whether to use this technique.

A naive example using multiple threads is:

# A naive example for fetching data in parallel.# Many factors affect whether this is beneficial# The degree of parallelism / number of connections to openNUM_THREADS=10# How many rows to fetch in each threadBATCH_SIZE=1000# Internal buffer size: Tune for performanceoracledb.defaults.arraysize=1000# Note OFFSET/FETCH is not particularly efficient.# It would be better to use a partitioned tableSQL="""    select data    from demo    order by id    offset :rowoffset rows fetch next :maxrows rows only    """defdo_query(tn):withpool.acquire()asconnection:withconnection.cursor()ascursor:cursor.execute(SQL,rowoffset=(tn*BATCH_SIZE),maxrows=BATCH_SIZE)whileTrue:rows=cursor.fetchmany()ifnotrows:breakprint(f'Thread{tn}',rows)pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",min=NUM_THREADS,max=NUM_THREADS)thread=[]foriinrange(NUM_THREADS):t=threading.Thread(target=do_query,args=(i,))t.start()thread.append(t)foriinrange(NUM_THREADS):thread[i].join()

When considering to parallelize queries from a table, some of the many factorsinclude:

• Each connection to Oracle Database can only execute one statement at a time,so to parallelize queries requires using multiple connections.

• Python’s threading behavior and impact of the Python’s Global InterpreterLock (GIL) may have an impact. You may need to spread work over multipleprocesses.

• What level of parallelism is most efficient?

• How many rows to fetch in each batch?

• What is your application doing with the data - can the receiving endefficiently process it, or write it to a disk?

• The OFFSET FETCH syntax will still cause database table blocks to be scannedeven though not all data is returned to the application. Can the table bepartitioned instead?

• There will be extra load on the database, both from the additionalconnections, and the work they are performing.

• Do your queries use up all of the database’s parallel servers?

• Is the data in the database spread across multiple disk spindles or is it theone disk which continually has to seek?

• Are Oracle Database zone maps being used?

• Is Oracle Exadata with storage indexes being used?

• Do you have function based indexes that are being invoked for every row?

5.1.11.Fetching Raw Data

Sometimes python-oracledb may have problems converting data stored in the database toPython strings. This can occur if the data stored in the database does not matchthe character set defined by the database. Theencoding_errors parameter toCursor.var() permits the data to be returned with some invalid datareplaced, but for additional control the parameterbypass_decode can be setto True and python-oracledb will bypass the decode step and returnbytes insteadofstr for data stored in the database as strings. The data can then beexamined and corrected as required. This approach should only be used fortroubleshooting and correcting invalid data, not for general use!

The following sample demonstrates how to use this feature:

# define output type handlerdefreturn_strings_as_bytes(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_VARCHAR:returncursor.var(str,arraysize=cursor.arraysize,bypass_decode=True)# set output type handler on cursor before fetching datawithconnection.cursor()ascursor:cursor.outputtypehandler=return_strings_as_bytescursor.execute("select content, charset from SomeTable")data=cursor.fetchall()

This will produce output as:

[(b'Fianc\xc3\xa9',b'UTF-8')]

Note that last\xc3\xa9 is é in UTF-8. Since this is valid UTF-8 you can thenperform a decode on the data (the part that was bypassed):

value=data[0][0].decode("UTF-8")

This will return the value “Fiancé”.

If you want to saveb'Fianc\xc3\xa9' into the database directly withoutusing a Python string, you will need to create a variable usingCursor.var() that specifies the type asoracledb.DB_TYPE_VARCHAR (otherwise the value will be treated asoracledb.DB_TYPE_RAW). The following sample demonstrates this:

withoracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb")asconn:withconn.cursor()cursor:var=cursor.var(oracledb.DB_TYPE_VARCHAR)var.setvalue(0,b"Fianc\xc4\x9b")cursor.execute("""            update SomeTable set                SomeColumn = :param            where id = 1""",param=var)

5.1.12.Querying Corrupt Data

If queries fail with the error “codec can’t decode byte” when you select data,then:

• Check if yourcharacter set is correct. Review thedatabase character sets. CheckFetching Raw Data. Note that the encoding used for all characterdata in python-oracledb is “UTF-8”.

• Check for corrupt data in the database and fix it. For example, if you havea table MYTABLE with a character column MYVALUE that you suspect has somecorrupt values, then you may be able to identify the problem data by using aquery likeselectidfrommytablewhereutl_i18n.validate_character_encoding(myvalue)>0 which will print out thekeys of the rows with invalid data.

If corrupt data cannot be modified, you can pass options to the internaldecode()used by python-oracledb to allow it to be selected and prevent the whole queryfailing. Do this by creating anoutputtypehandlerand settingencoding_errors. For example, to replace corrupt characters incharacter columns:

defoutput_type_handler(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_VARCHAR:returncursor.var(metadata.type_code,size,arraysize=cursor.arraysize,encoding_errors="replace")cursor.outputtypehandler=output_type_handlercursor.execute("select column1, column2 from SomeTableWithBadData")

Other codec behaviors can be chosen forencoding_errors, seeError Handlers.

5.2.1.Inserting NULLs

Oracle Database requires a type, even for null values. When you pass the valueNone, then python-oracledb assumes its type is a string. If this is not thedesired type, you can explicitly set it. For example, to insert a nullOracle Spatial SDO_GEOMETRY object:

type_obj=connection.gettype("SDO_GEOMETRY")cursor=connection.cursor()cursor.setinputsizes(type_obj)cursor.execute("insert into sometable values (:1)",[None])