15.1.Data Frame Type Mapping

Internally, python-oracledb’sOracleDataFramesupport makes use ofApache nanoarrowlibraries to build data frames.

The following data type mapping occurs from Oracle Database types to the Arrowtypes used in OracleDataFrame objects. Querying any other data types fromOracle Database will result in an exception.Output type handlers cannot be used to map data types.

Numbers

When converting Oracle Database NUMBERs:

• If the column has been created without a precision and scale, then the Arrowdata type will be DOUBLE.

• Ifdefaults.fetch_decimals is set toTrue, then the Arrow datatype is DECIMAL128.

• If the column has been created with a scale of0, and a precision valuethat is less than or equal to18, then the Arrow data type is INT64.

• In all other cases, the Arrow data type is DOUBLE.

Vectors

When converting Oracle Database VECTORs:

• Dense vectors are fetched as lists.

• Sparse vectors are fetched as structs with fieldsnum_dimensions,indices andvalues similar toSparseVector objects.

• Fixed and flexible dimensions are supported for dense VECTOR columns. Forsparse VECTOR columns, the dimension of each vector must be the same.

• VECTOR columns with flexible formats are not supported. Each vector valuemust have the same storage format data type.

• Vector values are fetched as the following types:

  Oracle Database VECTOR format	Arrow data type
  FLOAT64	DOUBLE
  FLOAT32	FLOAT
  INT8	INT8
  BINARY	UINT8

SeeUsing VECTOR data with Data Frames for more information.

LOBs

When converting Oracle Database CLOBs and BLOBs:

• The LOBs must be no more than 1 GB in length.

Dates and Timestamps

When converting Oracle Database DATEs and TIMESTAMPs:

• Arrow TIMESTAMPs will not have timezone data.

• For Oracle Database DATE columns, the Arrow TIMESTAMP will have a time unitof “seconds”.

• For Oracle Database TIMESTAMP types, the Arrow TIMESTAMP time unit depends onthe Oracle type’s fractional precision as shown in the table below:

  Oracle Database TIMESTAMP fractional second precision range	Arrow TIMESTAMP time unit
  0	seconds
  1 - 3	milliseconds
  4 - 6	microseconds
  7 - 9	nanoseconds

15.2.Converting OracleDataFrame to Other Data Frames

To use data frames in your chosen analysis library,OracleDataFrameobjects can be converted. Examples for some libraries areshown in the following sections. Other libraries will have similar methods.

Conversion Overview

The guidelines for convertingOracleDataFrame objects to data frames for other libraries are:

• To convert to aPyArrow Table, usepyarrow.Table.from_arrays() which leverages the Arrow PyCapsule interface.

• To convert to aPandas DataFrame, usepyarrow.Table.to_pandas().

• If you want to use a library other than Pandas or PyArrow, use the library’sfrom_arrow() method to convert a PyArrow Table to the applicable dataframe, if your library supports this. For example, withPolars usepolars.from_arrow().

• If your library does not supportfrom_arrow(), then usefrom_dataframe() if the library supports it. This can be slower,depending on the implementation.

Overall, the general recommendation is to use Apache Arrow as much as possiblebut if there are no options, then usefrom_dataframe(). You should testand benchmark to find the best option for your applications.

importpyarrow# Get an OracleDataFrame# Adjust arraysize to tune the query fetch performancesql="select id, name from SampleQueryTab order by id"odf=connection.fetch_df_all(statement=sql,arraysize=100)# Create a PyArrow tablepyarrow_table=pyarrow.Table.from_arrays(arrays=odf.column_arrays(),names=odf.column_names())print("\nNumber of rows and columns:")(r,c)=pyarrow_table.shapeprint(f"{r} rows,{c} columns")

importpandasimportpyarrow# Get an OracleDataFrame# Adjust arraysize to tune the query fetch performancesql="select * from mytable where id = :1"myid=12345# the bind variable valueodf=connection.fetch_df_all(statement=sql,parameters=[myid],arraysize=1000)# Get a Pandas DataFrame from the data.df=pyarrow.Table.from_arrays(odf.column_arrays(),names=odf.column_names()).to_pandas()# Perform various Pandas operations on the DataFrameprint(df.T)# transformprint(df.tail(3))# last three rows

importpolarsimportpyarrow# Get an OracleDataFrame# Adjust arraysize to tune the query fetch performancesql="select id from SampleQueryTab order by id"odf=connection.fetch_df_all(statement=sql,arraysize=100)# Convert to a Polars DataFramepyarrow_table=pyarrow.Table.from_arrays(odf.column_arrays(),names=odf.column_names())df=polars.from_arrow(pyarrow_table)# Perform various Polars operations on the DataFramer,c=df.shapeprint(f"{r} rows,{c} columns")print(p.sum())

importpyarrowimportpyarrow.parquetaspqFILE_NAME="sample.parquet"# Tune the fetch batch size for your queryBATCH_SIZE=10000sql="select * from mytable"pqwriter=Noneforodfinconnection.fetch_df_batches(statement=sql,size=BATCH_SIZE):# Get a PyArrow table from the query resultspyarrow_table=pyarrow.Table.from_arrays(arrays=odf.column_arrays(),names=odf.column_names())ifnotpqwriter:pqwriter=pq.ParquetWriter(FILE_NAME,pyarrow_table.schema)pqwriter.write_table(pyarrow_table)pqwriter.close()

importnumpyimportpyarrowSQL="select id from SampleQueryTab order by id"# Get an OracleDataFrame# Adjust arraysize to tune the query fetch performanceodf=connection.fetch_df_all(statement=SQL,arraysize=100)# Convert to an ndarray via the Python DLPack specificationpyarrow_array=pyarrow.array(odf.get_column_by_name("ID"))np=numpy.from_dlpack(pyarrow_array)# Perform various numpy operations on the ndarrayprint(numpy.sum(np))print(numpy.log10(np))

importpyarrowimporttorchSQL="select id from SampleQueryTab order by id"# Get an OracleDataFrame# Adjust arraysize to tune the query fetch performanceodf=connection.fetch_df_all(statement=SQL,arraysize=100)# Convert to a Torch tensor via the Python DLPack specificationpyarrow_array=pyarrow.array(odf.get_column_by_name("ID"))tt=torch.from_dlpack(pyarrow_array)# Perform various Torch operations on the tensorprint(torch.sum(tt))print(torch.log10(tt))

15.3.Using VECTOR data with Data Frames

Columns of theVECTOR data type can be fetched withthe methodsConnection.fetch_df_all() andConnection.fetch_df_batches(). VECTOR columns can have flexibledimensions, but flexible storage formats are not supported: each vector valuemust have the same format data type. Vectors can be dense or sparse.

SeeData Frame Type Mapping for the type mapping for VECTORs.

Dense Vectors

By default, Oracle Database vectors are “dense”. These are fetched inpython-oracledb as Arrow lists. For example, if the table:

createtablemyvec(v64vector(3,float64));

contains these two vectors:

[4.1,5.2,6.3][7.1,8.2,9.3]

then the code:

odf=connection.fetch_df_all("select v64 from myvec")pyarrow_table=pyarrow.Table.from_arrays(odf.column_arrays(),names=odf.column_names())

will result in a PyArrow table containing lists of doubles. The table can beconverted to a data frame of your chosen library using functionality describedearlier in this chapter. For example, to convert to Pandas:

pdf=pyarrow_table.to_pandas()print(pdf)

The output will be:

V640[4.1,5.2,6.3]1[7.1,8.2,9.3]

Sparse Vectors

Sparse vectors (where many of the values are 0) are fetched as structs withfieldsnum_dimensions,indices, andvalues similar toSparseVector objects which are discussed in anon-data frame context inUsing SPARSE Vectors.

If the table:

createtablemyvec(v64vector(3,float64,sparse));

contains these two vectors:

[3,[1,2],[4.1,5.2]][3,[0],[9.3]]

then the code to fetch as data frames:

importpyarrowodf=connection.fetch_df_all("select v64 from myvec")pdf=pyarrow.Table.from_arrays(odf.column_arrays(),names=odf.column_names()).to_pandas()print(pdf)print("First row:")num_dimensions=pdf.iloc[0].V64['num_dimensions']print(f"num_dimensions={num_dimensions}")indices=pdf.iloc[0].V64['indices']print(f"indices={indices}")values=pdf.iloc[0].V64['values']print(f"values={values}")

will display:

V640{'num_dimensions':3,'indices':[1,2],'valu...1{'num_dimensions':3,'indices':[0],'values'...Firstrow:num_dimensions=3indices=[12]values=[4.15.2]

You can convert each struct as needed. One way to convert intoPandasdataframes with sparse values is via aSciPycoordinate format matrix. The Pandas methodfrom_spmatrix() can then be used to create thefinal sparse dataframe:

importnumpyimportpandasimportpyarrowimportscipydefconvert_to_sparse_array(val):dimensions=val["num_dimensions"]col_indices=val["indices"]row_indices=numpy.zeros(len(col_indices))values=val["values"]sparse_matrix=scipy.sparse.coo_matrix((values,(col_indices,row_indices)),shape=(dimensions,1))returnpandas.arrays.SparseArray.from_spmatrix(sparse_matrix)odf=connection.fetch_df_all("select v64 from myvec")pdf=pyarrow.Table.from_arrays(odf.column_arrays(),odf.column_names()).to_pandas()pdf["SPARSE_ARRAY_V64"]=pdf["V64"].apply(convert_to_sparse_array)print(pdf.SPARSE_ARRAY_V64)

The code will print:

0[0.0,4.1,5.2]Fill:0.0IntIndexIndices:ar...1[9.3,0.0,0.0]Fill:0.0IntIndexIndices:ar...Name:SPARSE_ARRAY_V64,dtype:object