2.3 Manipulate database tables and views using familiar Python functions and syntax

With the transparency layer classes, you can manipulate database tables and views using familiar Python functions and syntax, For example, using DataFrame proxy objects that map to database data, users can invoke overloaded Pandas functions that transparently generate SQL that runs in the database, using the database as a high-performance compute engine.

The OML4Py transparency layer does the following:

• Enables creating tables and views from pandas.DataFrame and getting proxy objects to tables and views.

• Overloads specific Python functions that transparently translate functionality to SQL

• Leverages proxy objects for database data

• Uses familiar Python syntax to manipulate database data

The following table lists the transparency layer functions for getting and creating proxy objects and tables/views.

Table 2-1 Transparency Layer Functions for getting and creating proxy objects and tables/views

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 fetched from database object 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 2-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.
oml.Integer	A data class that represents a single column of NUMBER(*,0) data in the database.
oml.Datetime	A series date class that represents a single column of TIMESTAMP or TIMESTAMP WITH TIME ZONE in Oracle Database. oml.Timezone A time class that is used with oml.Datetime to support TIME STAMP WITH TIME ZONE. oml.Timedelta A time class that represents a single column series of differences between two dates or times, or INTERVAL DAY TO SECOND in Oracle Database.
oml.Timezone	A time class that is used with oml.Datetime to support TIME STAMP WITH TIME ZONE.
oml.Timedelta	A time class that represents a single column series of differences between two dates or times, or INTERVAL DAY TO SECOND in Oracle Database.

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

Table 2-3 Python and SQL Data Type Equivalencies

Database Read	Python Data Types	Database Write
N/A	Bool	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
NUMBER(*,0)	int	NUMBER(*,0)
TIMESTAMP or TIMESTAMP WITH TIME ZONE	datetime.datetime	TIMESTAMP or TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	datetime.timezone	TIMESTAMP WITH TIME ZONE
INTERVAL DAY TO SECOND	datetime.timedelta	INTERVAL DAY TO SECOND