6.3.4 Create a Python Proxy Object for a Database Object

Use the oml.sync function to create a Python object as a proxy for a database table, view, or SQL statement.

The oml.sync function returns an oml.DataFrame object or a dictionary of oml.DataFrame objects. The oml.DataFrame object returned by oml.sync is a proxy for the database object.

You can use the proxy oml.DataFrame object to select data from the table. When you run a Python function that selects data from the table, the function returns the current data from the database object. However, if some application has added a column to the table, or has otherwise changed the metadata of the database object, the oml.DataFrame proxy object does not reflect such a change until you again invoke oml.sync for the database object.

Tip:

To conserve memory resources and save time, you should only create proxies for the tables that you want to use in your Python session.

You can use the oml.dir function to list the oml.DataFrame proxy objects in the environment for a schema.

The syntax of the oml.sync function is the following:

oml.sync(schema=None, regex_match=False, table=None, view=None, query=None)

The schema argument in oml.sync specifies the name of the schema where the database object exists. If schema=None, which is the default, then the current schema is used.

To create an oml.DataFrame object for a table, use the table parameter. To create one for a view, use the view parameter. To create one for a SQL SELECT statement, use the query parameter. You can only specify one of these parameters in an oml.sync invocation: the argument for one of the parameters must be a string and the argument for each of the other two parameters must be None.

Creating a proxy object for a query enables you to create an oml.DataFrame object without creating a view in the database. This can be useful when you do not have the CREATE VIEW system privilege for the current schema. You cannot use the schema parameter and the query parameter in the same ore.sync invocation.

With the regex_match argument, you can specify whether the value of the table or view argument is a regular expression. If regex_match=True, then oml.sync creates oml.DataFrame objects for each database object that matches the pattern. The matched tables or views are returned in a dict with the table or view names as keys.

Example 6-10 Creating a Python Object for a Database Table

This example creates an oml.DataFrame Python object as a proxy for a database table. For this example, the table COFFEE exists in the user's schema.

import oml

# Create the Python object oml_coffee as a proxy for the 
# database table COFFEE.
oml_coffee = oml.sync(table = 'COFFEE')
type(oml_coffee)

# List the proxy objects in the schema.
oml.dir()

oml_coffee.head()

Listing for This Example

>>> import oml
>>>
>>> # Create the Python object oml_coffee as a proxy for the 
... # database table COFFEE.
... oml_coffee = oml.sync(table = 'COFFEE')
>>> type(oml_coffee)
<class 'oml.core.frame.DataFrame'>
>>>
>>> # List the proxy objects in the schema.
... oml.dir()
['oml_coffee']
>>>
>>> oml_coffee.head()
   ID COFFEE WINDOW
0   1    esp      w
1   2    cap      d
2   3    cap      w
3   4    kon      w
4   5    ice      w

Example 6-11 Using the regex_match Argument

This example uses the regex_match argument in creating a dict object that contains oml.DataFrame proxy objects for tables whose names start with C. For this example, the COFFEE and COLOR tables exist in the user's schema and are the only tables whose names start with C.

# Create a dict of oml.DataFrame proxy objects for tables
# whose names start with 'C'.
oml_cdat = oml.sync(table="^C", regex_match=True)

oml_cdat.keys()
oml_cdat['COFFEE'].columns
oml_cdat['COLOR'].columns

Listing for This Example

>>> # Create a dict of oml.DataFrame proxy objects for tables
... # whose names start with 'C'.
... oml_cdat = oml.sync(table="^C", regex_match=True)
>>>
>>> oml_cdat.keys()           
dict_keys(['COFFEE', 'COLOR']
>>> oml_cdat['COFFEE'].columns
['ID', 'COFFEE', 'WINDOW']
>>> oml_cdat['COLOR'].columns
['REGION', 'EYES', 'HAIR', 'COUNT']

Example 6-12 Synchronizing an Updated Table

This example uses oml.sync to create an oml.DataFrame for the database table COFFEE. For the example, the new column BREW has been added to the database table by some other database process after the first invocation of oml.sync. Invoking oml.sync again synchronizes the metadata of the oml.DataFrame with those of the table.

oml_coffee =  oml.sync(table = "COFFEE")
oml_coffee.columns

# After a new column has been inserted into the table.
oml_coffee =  oml.sync(table = "COFFEE")
oml_coffee.columns

Listing for This Example

>>> oml_coffee =  oml.sync(table = "COFFEE")
>>> oml_coffee.columns
['ID', 'COFFEE', 'WINDOW']
>>>
>>> # After a new column has been inserted into the table.
... oml_coffee =  oml.sync(table = "COFFEE")
>>> oml_coffee.columns
['ID', 'COFFEE', 'WINDOW', 'BREW']