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']
Parent topic: Move Data Between the Database and a Python Session