7.1.Cursor Methods

Cursor.__enter__()

The entry point for the cursor as a context manager. It returns itself.

This method is an extension to the DB API definition.

Cursor.__exit__()

The exit point for the cursor as a context manager. It closes the cursor.

This method is an extension to the DB API definition.

Cursor.__iter__()

Returns the cursor itself to be used as an iterator.

This method is an extension to the DB API definition. It is mentioned in PEP 249 as an optional extension.

Cursor.arrayvar(typ,value[,size])

Creates an array variable associated with the cursor of the given type andsize and returns avariable object. The value is either aninteger specifying the number of elements to allocate or it is a list andthe number of elements allocated is drawn from the size of the list. If thevalue is a list, the variable is also set with the contents of the list. Ifthe size is not specified and the type is a string or binary, 4000 bytesis allocated. This is needed for passing arrays to PL/SQL (in cases wherethe list might be empty and the type cannot be determined automatically) orreturning arrays from PL/SQL.

Array variables can only be used for PL/SQL associative arrays withcontiguous keys. For PL/SQL associative arrays with sparsely populated keysor for varrays and nested tables, the approach shown in thisexample needs to be used.

This method is an extension to the DB API definition.

Cursor.bindnames()

Returns the list of bind variable names bound to the statement. Note that astatement must have been prepared first.

This method is an extension to the DB API definition.

Cursor.callfunc(name,return_type,parameters=[],keyword_parameters={})

Calls a PL/SQL function with the given name and returns its value.

Thereturn_type parameter forcallfunc() is expected tobe a Python type, one of theoracledb types or anObject Type.

The sequence of parameters must contain one entry for each parameter thatthe PL/SQL function expects. Any keyword parameters will be included afterthe positional parameters.

UseCursor.var() to define any OUT or IN OUT parameters, ifnecessary.

SeePL/SQL Stored Functions for examples.

For consistency and compliance with the PEP 8 naming style, the parameterkeywordParameters was renamed tokeyword_parameters. The old namewill continue to work for a period of time.

This method is an extension to the DB API definition.

Note

In line with the Python DB API, it is not recommended to callCursor.setinputsizes() prior to callingcallfunc(). UseCursor.var() instead. Inexisting code that callssetinputsizes(), the firstitem in thesetinputsizes() parameter list refers tothe return value of the PL/SQL function.

Cursor.callproc(name,parameters=[],keyword_parameters={})

Calls a PL/SQL procedure with the given name.

The sequence of parameters must contain one entry for each parameter thatthe procedure expects. The result of the call is a modified copy of theinput sequence. Input parameters are left untouched; output andinput/output parameters are replaced with possibly new values. Keywordparameters will be included after the positional parameters and are notreturned as part of the output sequence.

UseCursor.var() to define any OUT or IN OUT parameters ifnecessary.

No query result set is returned bycallproc(). Instead, useREF CURSOR parameters orImplicit Results.

SeePL/SQL Stored Procedures for an example.

For consistency and compliance with the PEP 8 naming style, the parameterkeywordParameters was renamed tokeyword_parameters. The old namewill continue to work for a period of time.

Note

The DB API definition does not allow for keyword parameters.

Cursor.close()

Closes the cursor now, rather than whenever __del__ is called. The cursorwill be unusable from this point forward; an Error exception will be raisedif any operation is attempted with the cursor.

Cursor.execute(statement,parameters=[],**keyword_parameters)

Executes a statement against the database. SeeExecuting SQL.

Parameters may be passed as a dictionary or sequence or as keywordparameters. If the parameters are a dictionary, the values will be bound byname and if the parameters are a sequence the values will be bound byposition. Note that if the values are bound by position, the order of thevariables is from left to right as they are encountered in the statementand SQL statements are processed differently than PL/SQL statements. Forthis reason, it is generally recommended to bind parameters by name insteadof by position.

Parameters passed as a dictionary are name and value pairs. The name mapsto the bind variable name used by the statement and the value maps to thePython value you wish bound to that bind variable.

A reference to the statement will be retained by the cursor. IfNone orthe same string object is passed in again, the cursor will execute thatstatement again without performing a prepare or rebinding and redefining.This is most effective for algorithms where the same statement is used, butdifferent parameters are bound to it (many times). Note that parametersthat are not passed in during subsequent executions will retain the valuepassed in during the last execution that contained them.

For maximum efficiency when reusing a statement, it is best to use theCursor.setinputsizes() method to specify the parameter types andsizes ahead of time; in particular,None is assumed to be a string oflength 1 so any values that are later bound as numbers or dates will raisea TypeError exception.

If the statement is a query, the cursor is returned as a convenience to thecaller (so it can be used directly as an iterator over the rows in thecursor); otherwise,None is returned.

Note

The DB API definition does not define the return value of this method.

Cursor.executemany(statement,parameters,batcherrors=False,arraydmlrowcounts=False)

Executes a SQL statement once using all bind value mappings or sequencesfound in the sequence parameters. This can be used to insert, update, ordelete multiple rows in a table with a single python-oracledb call. It canalso invoke a PL/SQL procedure multiple times. SeeBatch Statement and Bulk Copy Operations.

Thestatement parameter is managed in the same way as theCursor.execute() method manages it.

Theparameters parameter can be a list of tuples, where each tuple itemmaps to one bind variable placeholder instatement. It can also be alist of dictionaries, where the keys match the bind variable placeholdernames instatement. If there are no bind values, or values havepreviously been bound, theparameters value can be an integerspecifying the number of iterations.

In python-oracledb Thick mode, if the size of the buffers allocated for anyof the parameters exceeds 2 GB, you will receive the errorDPI-1015:arraysizeof<n>istoolarge. If you receive this error, decrease thenumber of rows being inserted.

WhenTrue, thebatcherrors parameter enables batch error supportwithin Oracle Database and ensures that the call succeeds even if anexception takes place in one or more of the sequence of bind values. Theerrors can then be retrieved usingCursor.getbatcherrors().

WhenTrue, thearraydmlrowcounts parameter enables DML row counts tobe retrieved from Oracle after the method has completed. The row counts canthen be retrieved usingCursor.getarraydmlrowcounts().

Both thebatcherrors parameter and thearraydmlrowcounts parametercan only beTrue when executing an insert, update, delete, or mergestatement; in all other cases an error will be raised.

For maximum efficiency, it is best to use theCursor.setinputsizes() method to specify the bind value types andsizes. In particular, if the type is not explicitly specified, the valueNone is assumed to be a string of length 1 so any values that are laterbound as numbers or dates will raise a TypeError exception.

Cursor.fetchall()

Fetches all (remaining) rows of a query result, returning them as a list oftuples. An empty list is returned if no more rows are available. Note thatthe cursor’s arraysize attribute can affect the performance of thisoperation, as internally reads from the database are done in batchescorresponding to the arraysize.

An exception is raised if the previous call toCursor.execute()did not produce any result set or no call was issued yet.

SeeFetch Methods for an example.

Cursor.fetchmany(size=cursor.arraysize)

Fetches the next set of rows of a query result, returning a list of tuples.An empty list is returned if no more rows are available. Note that thecursor’s arraysize attribute can affect the performance of this operation.

The number of rows to fetch is specified by the parameter. If it is notgiven, the cursor’sarraysize attribute determines the number of rowsto be fetched. If the number of rows available to be fetched is fewer thanthe amount requested, fewer rows will be returned.

An exception is raised if the previous call toCursor.execute()did not produce any result set or no call was issued yet.

SeeFetch Methods for an example.

Cursor.fetchone()

Fetches the next row of a query result set, returning a single tuple orNone when no more data is available.

An exception is raised if the previous call toCursor.execute()did not produce any result set or no call was issued yet.

SeeFetch Methods for an example.

Cursor.getarraydmlrowcounts()

Retrieves the DML row counts after a call toCursor.executemany()witharraydmlrowcounts enabled. This will return a list of integerscorresponding to the number of rows affected by the DML statement for eachelement of the array passed toCursor.executemany().

This method is only available for Oracle Database 12.1 and later.

This method is an extension to the DB API definition.

Cursor.getbatcherrors()

Retrieves the exceptions that took place after a call toCursor.executemany() withbatcherrors enabled. This will return alist of Error objects, one error for each iteration that failed. The offsetcan be determined by looking at the offset attribute of the error object.

This method is an extension to the DB API definition.

Cursor.getimplicitresults()

Returns a list of cursors which correspond to implicit results madeavailable from a PL/SQL block or procedure without the use of OUT refcursor parameters. The PL/SQL block or procedure opens the cursors andmarks them for return to the client using the proceduredbms_sql.return_result. In python-oracledb Thick mode, closing the parentcursor will result in the automatic closure of the implicit result setcursors. SeeImplicit Results.

This method is only available for Oracle Database 12.1 (or later). Forpython-oracledbThick mode, Oracle Client 12.1 (orlater) is additionally required.

This method is an extension to the DB API definition. It is most like the DB API method nextset(), but unlike that method (which requires that the next result set overwrite the current result set), this method returns cursors which can be fetched independently of each other.

Cursor.parse(statement)

This can be used to parse a statement without actually executing it(parsing step is done automatically by Oracle when a statement isexecuted).

This method is an extension to the DB API definition.

Note

You can parse any DML or DDL statement. DDL statements are executedimmediately and an implied commit takes place. You can also parsePL/SQL statements.

Cursor.prepare(statement,tag,cache_statement=True)

This can be used before a call toCursor.execute() orCursor.executemany() to define the statement that will beexecuted. When this is done, the prepare phase will not be performed whenthe call toCursor.execute() orCursor.executemany() ismade withNone or the same string object as the statement.

If thetag parameter is specified and thecache_statement parameterisTrue, the statement will be returned to the statement cache with thegiven tag.

If thecache_statement parameter isFalse, the statement will beremoved from the statement cache (if it was found there) or will simply notbe cached.

SeeStatement Caching for more information.

This method is an extension to the DB API definition.

Cursor.scroll(value=0,mode='relative')

Scrolls the cursor in the result set to a new position according to themode.

If mode isrelative (the default value), the value is taken as an offsetto the current position in the result set. If set toabsolute, valuestates an absolute target position. If set tofirst, the cursor ispositioned at the first row and if set tolast, the cursor is set to thelast row in the result set.

An error is raised if the mode isrelative orabsolute and the scrolloperation would position the cursor outside of the result set.

This method is an extension to the DB API definition. It is mentioned in PEP 249 as an optional extension.

Cursor.setinputsizes(*args,**keywordArgs)

This can be used before calls toCursor.execute() orCursor.executemany() to predefine memory areas used forbind variables. Each parameter should be a type objectcorresponding to the data that will be used for a bind variable placeholderin the SQL or PL/SQL statement. Alternatively, it can be an integerspecifying the maximum length of a string bind variable value.

Use keyword parameters whenbinding by name. Usepositional parameters whenbinding by position. Theparameter value can beNone to indicate that python-oracledb shoulddetermine the required space from the data value provided.

The parameters or keyword names correspond to the bind variableplaceholders used in the SQL or PL/SQL statement. Note this means that foruse withCursor.executemany() it does not correspond to the numberof bind value mappings or sequences being passed.

When repeated calls toCursor.execute() orCursor.executemany() are made binding different string datalengths, usingsetinputsizes() can help reduce thedatabase’s SQL “version count” for the statement. SeeReducing theSQL Version Count.

Note

Cursor.setinputsizes() should not be used for bind variablespassed toCursor.callfunc() orCursor.callproc(). Instead, useCursor.var().

IfCursor.setinputsizes() is used withCursor.callfunc(), the first parameter in the list refers tothe return value of the PL/SQL function.

Cursor.setoutputsize(size[,column])

This method does nothing and is retained solely for compatibility with theDB API. Python-oracledb automatically allocates as much space as needed tofetch LONG and LONG RAW columns, and also to fetch CLOB as string and BLOBas bytes.

Cursor.var(typ[,size,arraysize,inconverter,outconverter,typename,encoding_errors,bypass_decode,convert_nulls])

Creates avariable object with the specifiedcharacteristics. This method can be used for binding to PL/SQL IN and OUTparameters where the length or type cannot be determined automatically fromthe Python variable being bound. It can also be used ininput andoutput type handlers.

Thetyp parameter specifies the type of data that should be stored in thevariable. This should be one of thedatabase type constants,DB API constants, an object type returned fromthe methodConnection.gettype() or one of the following Pythontypes:

Python Type	Database Type
bool	oracledb.DB_TYPE_BOOLEAN
bytes	oracledb.DB_TYPE_RAW
datetime.date	oracledb.DB_TYPE_DATE
datetime.datetime	oracledb.DB_TYPE_DATE
datetime.timedelta	oracledb.DB_TYPE_INTERVAL_DS
decimal.Decimal	oracledb.DB_TYPE_NUMBER
float	oracledb.DB_TYPE_NUMBER
int	oracledb.DB_TYPE_NUMBER
str	oracledb.DB_TYPE_VARCHAR

Thesize parameter specifies the length of string and raw variables and isignored in all other cases. If not specified for string and raw variables,the value4000 is used.

Thearraysize parameter specifies the number of elements the variable willhave. If not specified the bind array size (usually1) is used. When avariable is created in an output type handler this parameter should be setto the cursor’s array size.

Theinconverter andoutconverter parameters specify methods used forconverting values to/from the database. More information can be found inthe section onvariable objects.

Thetypename parameter specifies the name of a SQL object type and must bespecified when using typeoracledb.OBJECT unless the type objectwas passed directly as the first parameter.

Theencoding_errors parameter specifies what should happen when decodingbyte strings fetched from the database into strings. It should be one ofthe values noted in the builtindecodefunction.

Thebypass_decode parameter, if specified, should be passed as aboolean value. Passing aTrue value causes values of database typesDB_TYPE_VARCHAR,DB_TYPE_CHAR,DB_TYPE_NVARCHAR,DB_TYPE_NCHAR andDB_TYPE_LONG to be returned as bytes instead of str,meaning that python-oracledb does not do any decoding. SeeFetching rawdata for more information.

Theconvert_nulls parameter, if specified, should be passed as a booleanvalue. Passing the valueTrue causes theoutconverter to be calledwhen a null value is fetched from the database; otherwise, theoutconverter is only called when non-null values are fetched from thedatabase.

For consistency and compliance with the PEP 8 naming style, the parameterencodingErrors was renamed toencoding_errors. The old name willcontinue to work as a keyword parameter for a period of time.

Changed in version 1.4.0:Theconvert_nulls parameter was added.

This method is an extension to the DB API definition.

7.2.Cursor Attributes

Cursor.arraysize

This read-write attribute can be used to tune the number of rows internallyfetched and buffered by internal calls to the database when fetching rowsfrom SELECT statements and REF CURSORS. The value can drastically affectthe performance of a query since it directly affects the number of networkround trips between Python and the database. For methods likeCursor.fetchone() andCursor.fetchall() it does not changehow many rows are returned to the application. ForCursor.fetchmany() it is the default number of rows to fetch.

The attribute is only used for tuning row and SODA document fetches fromthe database. It does not affect data inserts.

Due to the performance benefits, the defaultCursor.arraysize is100instead of the1 that the Python DB API recommends.

SeeTuning Fetch Performance for more information.

Cursor.bindvars

This read-only attribute provides the bind variables used for the laststatement that was executed on the cursor. The value will be either a listor a dictionary, depending on whether binding was done by position orname. Care should be taken when referencing this attribute. In particular,elements should not be removed or replaced.

This attribute is an extension to the DB API definition.

Cursor.connection

This read-only attribute returns a reference to the connection object onwhich the cursor was created.

This method is an extension to the DB API definition. It is mentioned in PEP 249 as an optional extension.

Cursor.description

This read-only attribute contains information about the columns used in aquery. It is a sequence ofFetchInfo objects, one percolumn. This attribute will beNone for statements that are not SELECT orWITH statements, or if the cursor has not hadCursor.execute()invoked yet.

Changed in version 1.4.0:Previously, this attribute was a sequence of 7-tuples. Each of thesetuples contained information describing one query column: “(name, type,display_size, internal_size, precision, scale, null_ok)”.

Cursor.fetchvars

This read-only attribute specifies the list of variables created for thelast query that was executed on the cursor. Care should be taken whenreferencing this attribute. In particular, elements should not be removedor replaced.

This attribute is an extension to the DB API definition.

Cursor.inputtypehandler

This read-write attribute specifies a method called for each value that isbound to a statement executed on the cursor and overrides the attributewith the same name on the connection if specified. The method signature ishandler(cursor, value, arraysize) and the return value is expected to be avariable object orNone in which case a default variable object will becreated. If this attribute isNone, the default behavior will take placefor all values bound to the statements.

SeeChanging Bind Data Types using an Input Type Handler.

This attribute is an extension to the DB API definition.

Cursor.lastrowid

This read-only attribute returns the rowid of the last row modified by thecursor. If no row was modified by the last operation performed on thecursor, the valueNone is returned.

Cursor.outputtypehandler

This read-write attribute specifies a method called for each column that isto be fetched from this cursor. The method signature ishandler(cursor, metadata) and the return value is expected to be avariable object orNone in which case a default variableobject will be created. If this attribute isNone, then the defaultbehavior will take place for all columns fetched from this cursor.

SeeChanging Fetched Data Types with Output Type Handlers.

This attribute is an extension to the DB API definition.

Changed in version 1.4.0:The method signature was changed. The previous signaturehandler(cursor, name, default_type, length, precision, scale) willstill work but is deprecated and will be removed in a future version.

Cursor.prefetchrows

This read-write attribute can be used to tune the number of rows that theOracle Client library fetches when a SELECT statement is executed. Thisvalue can reduce the number of round-trips to the database that arerequired to fetch rows but at the cost of additional memory. Setting thisvalue to0 can be useful when the timing of fetches must be explicitlycontrolled.

The attribute is only used for tuning row fetches from the database. Itdoes not affect data inserts.

Queries that return LOBs and similar types will never prefetch rows, so theprefetchrows value is ignored in those cases.

SeeTuning Fetch Performance for more information.

This method is an extension to the DB API definition.

Cursor.rowcount

This read-only attribute specifies the number of rows that have currentlybeen fetched from the cursor (for select statements) or that have beenaffected by the operation (for insert, update, delete, and mergestatements). For all other statements the value is always0. If thecursor or connection is closed, the value returned is-1.

Cursor.rowfactory

This read-write attribute specifies a method to call for each row that isretrieved from the database. Ordinarily, a tuple is returned for each rowbut if this attribute is set, the method is called with the tuple thatwould normally be returned, and the result of the method is returnedinstead.

SeeChanging Query Results with Rowfactories.

This attribute is an extension to the DB API definition.

Cursor.scrollable

This read-write boolean attribute specifies whether the cursor can bescrolled or not. By default, cursors are not scrollable, as the serverresources and response times are greater than nonscrollable cursors. Thisattribute is checked and the corresponding mode set in Oracle when callingthe methodCursor.execute().

This attribute is an extension to the DB API definition.

Cursor.statement

This read-only attribute provides the string object that was previouslyprepared withCursor.prepare() or executed withCursor.execute().

This attribute is an extension to the DB API definition.

Cursor.warning

This read-only attribute provides anoracledb._Errorobject giving information about any database warnings (such as PL/SQLcompilation warnings) that were generated during the last call toCursor.execute() orCursor.executemany(). This value isautomatically cleared on the next call toCursor.execute() orCursor.executemany(). If no warning was generated the valueNone is returned.

SeePL/SQL Compilation Warnings for more information.

This attribute is an extension to the DB API definition.

Added in version 2.0.0.