Connect to an Oracle Database

6.2.3 Connect to an Oracle Database

Establish an OML4Py connection to an on-premises Oracle database with oml.connect.

The oml.connect function establishes a connection to the user’s schema in an on-premises Oracle database.

The syntax of the oml.connect function is the following.

oml.connect(user=None, password=None, host=None, port=None, sid=None,
service_name=None, dsn=None, encoding=’UTF-8’, nencoding=’UTF-8’, automl=None)

To create a basic connection to the database, you can specify arguments to the oml.connect function in the following mutually exclusive combinations:

user, password, dsn
user, password, host, port, sid
user, password, host, port, service_name

The arguments specify the following values

Table 6-2 Pararmeters to oml.connect

Parameter	Description
`user`	A string specifying a username.
`password`	A string specifying the password for the user.
`host`	A string specifying the name of the host machine on which the OML4Py server is installed.
`port`	An `int` or a string specifying the Oracle database port number on the host machine.
`sid`	A string specifying the system identifier (SID) of the Oracle database.
`service_name`	A string specifying the service name of the Oracle database.
`dsn`	A string specifying a data source name, which can be a TNS entry for the database or a TNS alias in an Oracle Wallet.
`encoding`	A string specifying the encoding to use for regular database strings.
`nencoding`	A string specifying the encoding to use for national character set database strings.
`automl`	A string or a boolean specifying whether to enable an Automatic Machine Learning (AutoML) connection, which uses the database-resident connection pool. If there is a connection pool running for a host, port, SID (or service name), then you can specify that host, port, SID (or service name) and `automl=True`. If the `dsn` argument is a data source name, then the `automl` argument must be a data source name for a running connection pool. If the `dsn` argument is a TNS alias, then the `automl` argument must be a TNS alias for a connection pool specified in an Oracle Wallet.

To use the AutoML capabilities of OML4Py, the following must be true:

A connection pool must be running on the server.
You must explicitly use the automl argument in an oml.connect invocation to specify the running connection pool on the server.

Note:

Before you can create an AutoML connection, a database administrator must first activate the database-resident connection pool in your on-premises Oracle database by issuing the following SQL statement:

EXECUTE DBMS_CONNECTION_POOL.START_POOL();

Once started, the connection pool remains in this state until a database administrator explicitly stops it by issuing the following command:

EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

Only one active OML4Py connection can exist at a time during a Python session. If you call oml.connect when an active connection already exists, then the oml.disconnect function is implicitly invoked, any temporary objects that you created in the previous session are discarded, and the new connection is established. Before attempting to connect, you can discover whether an active connection exists by using the oml.isconnected function.

You explicitly end a connection with the oml.disconnect function. If you do not invoke oml.disconnect, then the connection is automatically terminated when the Python session ends.

Examples

In the following examples, the values of the some of the arguments to the oml.connect function are string variables that are not declared in the example. To use any of the following examples, replace the username, password, port, and variable argument values with the values for your user and database.

Example 6-1 Connecting with a Host, Port, and SID

This example uses the host, port, and sid arguments. It also shows the use of the oml.isconnected, oml.check_embed, and oml.disconnect functions.

import oml

oml.connect(user='oml_user', password='oml_user_password', host='myhost', 
            port=1521, sid='mysid')

# Verify that the connection exists.
oml.isconnected()

# Find out whether Embedded Python Execution is enabled in the 
# database instance.
oml.check_embed()

# Disconnect from the database.
oml.disconnect()

# Verify that the connection has been terminated.
oml.isconnected()

Listing for This Example

>>> import oml
>>>
>>> oml.connect(user='oml_user', password='oml_user_password', host='myhost',
...             port=1521, sid='mysid')
>>> 
>>> # Verify that the connection exists.
... oml.isconnected()
True
>>>
>>> # Find out whether Embedded Python Execution is enabled in the 
... # database instance.
... oml.check_embed()
True
>>>
>>> # Disconnect from the database.
... oml.disconnect()
>>>
>>> # Verify that the connection has been terminated.
... oml.isconnected()
False

Example 6-2 Connecting with Host, Port, and Service Name

This example uses the host, port, and service_name arguments.

import oml

oml.connect(user='oml_user', password='oml_user_password', host='myhost',
            port=1521, service_name='myservice')

Example 6-3 Connecting with a DSN Containing a SID

This example uses the dsn argument to specify a SID.

import oml

mydsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1521))\
          (CONNECT_DATA=(SID=mysid)))"
oml.connect(user='oml_user', password='oml_user_password', dsn=mydsn)

Example 6-4 Connecting with a DSN Containing a Service Name

This example uses the dsn argument to specify a service name.

import oml

myinst = "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)\
          (PORT=1521))\
          (CONNECT_DATA=(SERVICE_NAME=myservice.example.com)))"
oml.connect(user='oml_user', password='oml_user_password', dsn=myinst)

Example 6-5 Creating a Connection with a DSN and with AutoML Enabled

This example creates an OML4Py connection with AutoML enabled. The example connects to a local database.

import oml

mydsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)\
          (PORT=1521))(CONNECT_DATA=(SID=mysid)))"

dsn_pool = "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)\
             (PORT=1521))\
             (CONNECT_DATA=(SERVICE_NAME=myservice.example.com)\
             (SERVER=POOLED)))"

oml.connect(user='oml_user', password='oml_user_password',
            dsn=mydsn, automl=dsn_pool)

# Verify that the connection exists and that AutoML is enabled.
oml.isconnected(check_automl=True)

Example 6-6 Connecting with an Oracle Wallet

This example creates a connection using the dsn argument to specify an Oracle wallet. The dsn value, waltcon in the example, must refer to the alias in the database tnsnames.ora file that was used to create the appropriate credential in the wallet.

import oml

oml.connect(user='', password='', dsn='waltcon')