22. API: Pipeline Objects

Pipelining is only supported in python-oracledb Thin mode with asyncio. See Pipelining Database Operations for more information about pipelining.

Note

True pipelining is only available when connected to Oracle Database version 23, or later.

Added in version 2.4.0.

22.1. Pipeline Class

class oracledb.Pipeline

Pipeline objects represent a pipeline used to execute multiple database operations. A Pipeline object is created by calling oracledb.create_pipeline().

22.1.1. Pipeline Methods

Pipeline.add_callfunc(name: str, return_type: Any, parameters: list | tuple | None = None, keyword_parameters: dict | None = None) → PipelineOp

Adds an operation to the pipeline that calls a stored PL/SQL function with the given parameters and return type. The created PipelineOp object is also returned from this function.

When the Pipeline is executed, the PipelineOpResult object that is returned for this operation will have the return_value attribute populated with the return value of the PL/SQL function if the call completes successfully.

Pipeline.add_callproc(name: str, parameters: list | tuple | None = None, keyword_parameters: dict | None = None) → PipelineOp

Adds an operation that calls a stored procedure with the given parameters. The created PipelineOp object is also returned from this function.

Pipeline.add_commit() → PipelineOp

Adds an operation that performs a commit.

Pipeline.add_execute(statement: str, parameters: list | tuple | dict | None = None) → PipelineOp

Adds an operation that executes a statement with the given parameters. The created PipelineOp object is also returned from this function.

Do not use this for queries that return rows. Instead use Pipeline.add_fetchall(), Pipeline.add_fetchmany(), or Pipeline.add_fetchone().

Pipeline.add_executemany(statement: str, parameters: list | int) → PipelineOp

Adds an operation that executes a SQL statement once using all bind value mappings or sequences found in the sequence parameters. This can be used to insert, update, or delete multiple rows in a table. It can also invoke a PL/SQL procedure multiple times.

The created PipelineOp object is also returned from this function.

The parameters parameter can be a list of tuples, where each tuple item maps to one bind variable placeholder in statement. It can also be a list of dictionaries, where the keys match the bind variable placeholder names in statement. If there are no bind values, or values have previously been bound, the parameters value can be an integer specifying the number of iterations.

See also

Batch Statement and Bulk Copy Operations

Pipeline.add_fetchall(statement: str, parameters: list | tuple | dict | None = None, arraysize: int | None = None, rowfactory: Callable | None = None, fetch_lobs: bool | None = None, fetch_decimals: bool | None = None) → PipelineOp

Adds an operation that executes a query and returns all of the rows from the result set. The created PipelineOp object is also returned from this function.

When the Pipeline is executed, the PipelineOpResult object that is returned for this operation will have the rows attribute populated with the list of rows returned by the query.

The default value for arraysize is oracledb.defaults.arraysize.

Internally, this operation’s Cursor.prefetchrows size is set to the value of the explicit or default arraysize parameter value.

The fetch_lobs parameter specifies whether to return LOB locators or str/bytes values when fetching LOB columns. The default value is oracledb.defaults.fetch_lobs.

The fetch_decimals parameter specifies whether to return decimal.Decimal values when fetching columns of type NUMBER. The default value is oracledb.defaults.fetch_decimals.

Changed in version 3.4.0: The fetch_lobs and fetch_decimals parameters were added.

Pipeline.add_fetchmany(statement: str, parameters: list | tuple | dict | None = None, num_rows: int | None = None, rowfactory: Callable | None = None, fetch_lobs: bool | None = None, fetch_decimals: bool | None = None) → PipelineOp

Adds an operation that executes a query and returns up to the specified number of rows from the result set. The created PipelineOp object is also returned from this function.

When the Pipeline is executed, the PipelineOpResult object that is returned for this operation will have the rows attribute populated with the list of rows returned by the query.

The default value for num_rows is the value of oracledb.defaults.arraysize.

Internally, this operation’s Cursor.prefetchrows size is set to the value of the explicit or default num_rows parameter, allowing all rows to be fetched in one round-trip.

Since only one fetch is performed for a query operation, consider adding a FETCH NEXT clause to the statement to prevent the database processing rows that will never be fetched.

The fetch_lobs parameter specifies whether to return LOB locators or str/bytes values when fetching LOB columns. The default value is oracledb.defaults.fetch_lobs.

The fetch_decimals parameter specifies whether to return decimal.Decimal values when fetching columns of type NUMBER. The default value is oracledb.defaults.fetch_decimals.

Changed in version 3.4.0: The fetch_lobs and fetch_decimals parameters were added.

See also

Database Round-trips, and Limiting Rows

Pipeline.add_fetchone(statement: str, parameters: list | tuple | dict | None = None, rowfactory: Callable | None = None, fetch_lobs: bool | None = None, fetch_decimals: bool | None = None) → PipelineOp

Adds an operation that executes a query and returns the first row of the result set if one exists (or None, if no rows exist). The created PipelineOp object is also returned from this function.

When the Pipeline is executed, the PipelineOpResult object that is returned for this operation will have the rows attribute populated with this row if the query is performed successfully.

Internally, this operation’s Cursor.prefetchrows and Cursor.arraysize sizes will be set to 1.

Since only one fetch is performed for a query operation, consider adding a WHERE condition or using a FETCH NEXT clause in the statement to prevent the database processing rows that will never be fetched.

The fetch_lobs parameter specifies whether to return LOB locators or str/bytes values when fetching LOB columns. The default value is oracledb.defaults.fetch_lobs.

The fetch_decimals parameter specifies whether to return decimal.Decimal values when fetching columns of type NUMBER. The default value is oracledb.defaults.fetch_decimals.

Changed in version 3.4.0: The fetch_lobs and fetch_decimals parameters were added.

See also

Limiting Rows

22.1.2. Pipeline Attributes

property Pipeline.operations: list[PipelineOp]

This read-only attribute returns the list of operations associated with the pipeline.

22.2. PipelineOp Class

class oracledb.PipelineOp

A PipelineOp object should be created by calling the methods in the Pipeline class.

22.2.1. PipelineOp Attributes

property PipelineOp.arraysize: int

This read-only attribute returns the array size that will be used when fetching query rows with Pipeline.add_fetchall(). For all other operations, the value returned is 0.

See also

Tuning Fetch Performance

property PipelineOp.fetch_decimals: bool

Returns whether or not to fetch columns of type NUMBER as decimal.Decimal values for a query.

Added in version 3.4.0.

property PipelineOp.fetch_lobs: bool

Returns whether or not to fetch LOB locators for a query.

Added in version 3.4.0.

property PipelineOp.keyword_parameters: Any

This read-only attribute returns the keyword parameters to the stored procedure or function being called by the operation, if applicable.

property PipelineOp.name: str | None

This read-only attribute returns the name of the stored procedure or function being called by the operation, if applicable.

property PipelineOp.num_rows: int

This read-only attribute returns the number of rows to fetch when performing a query of a specific number of rows. For all other operations, the value returned is 0.

property PipelineOp.op_type: PipelineOpType

This read-only attribute returns the type of operation that is taking place.

See Pipeline Operation Types for types of operations.

property PipelineOp.parameters: Any

This read-only attribute returns the parameters to the stored procedure or function or the parameters bound to the statement being executed by the operation, if applicable.

property PipelineOp.return_type: Any

This read-only attribute returns the return type of the stored function being called by the operation, if applicable.

property PipelineOp.rowfactory: Callable | None

This read-only attribute returns the row factory callable function to be used in a query executed by the operation, if applicable.

property PipelineOp.statement: str | None

This read-only attribute returns the statement being executed by the operation, if applicable.

22.3. PipelineOpResult Objects

class oracledb.PipelineOpResult

When AsyncConnection.run_pipeline() is called, it returns a list of PipelineOpResult objects. These objects contain the results of the executed PipelineOp objects operations.

22.3.1. PipelineOpResult Attributes

property PipelineOpResult.columns: list[FetchInfo] | None

This read-only attribute is a list of FetchInfo objects. This attribute will be None for operations that do not return rows.

Added in version 2.5.0.

property PipelineOpResult.error: _Error | None

This read-only attribute returns the error that occurred when running this operation. If no error occurred, then the value None is returned.

property PipelineOpResult.operation: PipelineOp

This read-only attribute returns the PipelineOp operation object that generated the result.

property PipelineOpResult.return_value: Any

This read-only attribute returns the return value of the called PL/SQL function, if a function was called for the operation.

property PipelineOpResult.rows: list | None

This read-only attribute returns the rows that were fetched by the operation, if a query was executed.

property PipelineOpResult.warning: _Error | None

This read-only attribute returns any warning that was encountered when running this operation. If no warning was encountered, then the value None is returned.

Added in version 2.5.0.

See also

PL/SQL Compilation Warnings