1.3 Transparently Convert Python to SQL

With the transparency layer classes, you can convert select Python objects to Oracle database objects and also invoke a range of familiar Python functions that are overloaded to invoke the corresponding SQL on tables in the database.

The OML4Py transparency layer does the following:

• Contains functions that convert Python pandas.DataFrame objects to database tables

• Overloads Python functions, translating their functionality into SQL

• Leverages proxy objects for database data

• Uses familiar Python syntax to manipulate database data

The following table lists the transparency layer functions.

Table 1-1 Transparency Layer Functions

Function	Description
oml.create	Creates a table in a the database schema from a Python data set.
oml_object.pull	Creates a local Python object that contains a copy of data referenced by the oml object.
oml.push	Pushes data from a Python session into an object in a database schema.
oml.sync	Creates a DataFrame proxy object in Python that represents a database table or view.
oml.dir	Return the names of oml objects in the Python session workspace.
oml.drop	Drops a persistent database table or view.

Transparency layer proxy classes map SQL data types or objects to corresponding Python types. The classes provide Python functions and operators that are the same as those on the mapped Python types. The following table lists the transparency layer data type classes.

Table 1-2 Transparency Layer Data Type Classes

Class	Description
oml.Boolean	A boolean series data class that represents a single column of 0, 1, and NULL values in database data.
oml.Bytes	A binary series data class that represents a single column of RAW or BLOB database data types.
oml.Float	A numeric series data class that represents a single column of NUMBER, BINARY_DOUBLE, or BINARY_FLOAT database data types.
oml.String	A character series data class that represents a single column of VARCHAR2, CHAR, or CLOB database data types.
oml.DataFrame	A tabular DataFrame class that represents multiple columns of oml.Boolean, oml.Bytes, oml.Float, and oml.String data.

The following table lists the mappings of OML4Py data types for both the reading and writing of data between Python and the database.

Table 1-3 Python and SQL Data Type Equivalencies

Database Read	Python Data Types	Database Write
N/A	Boolean	If oranumber == True, then NUMBER (the default), else BINARY_DOUBLE.
BLOB RAW	bytes	BLOB RAW
BINARY_DOUBLE BINARY_FLOAT NUMBER	float	If oranumber == True, then NUMBER (the default), else BINARY_DOUBLE.
CHAR CLOB VARCHAR2	str	CHAR CLOB VARCHAR2