Updating a Database Table in an Oracle Machine Learning Notebook for Python

Introduction

Oracle Machine Learning Notebooks are very powerful and allow you to leverage the capabilities of Python combined with the ease of accessing data directly from Oracle Autonomous Database. Oracle provides the oml library which can be used to access and manipulate data. However, there is not a way to update a persistent database table using the oml library. This is by design as OML4Py considerers the table static.

Oracle Machine Learning Notebooks and Database Updates

Fortunately, the underlying cx_Oracle driver does allow for updating persistent database tables. The cx_Oracle driver can be seen in the below code updating a persistent database table:

import cx_Oracle

connection = cx_Oracle.connect(user=ORACLE_ADW_USER, password=ORACLE_ADW_PASSWORD, dsn=ORACLE_ADW_DSN)

cr = connection.cursor()

update_sql = "UPDATE MYSCHEMA.MY_STATUS SET LAST_UPDATED=CURRENT_TIMESTAMP, STATUS='complete', MESSAGE='complete' WHERE STATUS_ID = 123"

_ = cr.execute(update_sql)

cr.close()

It should be noted that when oml.sync is introduced into the code, the table will not be updated:

import cx_Oracle
import oml

oml.connect()

connection = cx_Oracle.connect(user=ORACLE_ADW_USER, password=ORACLE_ADW_PASSWORD, dsn=ORACLE_ADW_DSN)

cr = connection.cursor()

my_oml_df = oml.sync(view='TRANSACTIONS')

update_sql = "UPDATE MYSCHEMA.MY_STATUS SET LAST_UPDATED=CURRENT_TIMESTAMP, STATUS='complete', MESSAGE='complete' WHERE STATUS_ID = 123"

_ = cr.execute(update_sql)

cr.close()

Committing Changes

The above code will not throw an exception; however, the update to the persistent table will not occur.

The commit method from the cx_Oracle library can be used to circumvent this issue and ensure the update to the persistent database table is executed:

import cx_Oracle
import oml

oml.connect()

connection = cx_Oracle.connect(user=ORACLE_ADW_USER, password=ORACLE_ADW_PASSWORD, dsn=ORACLE_ADW_DSN)

cr = connection.cursor()

update_sql = "UPDATE MYSCHEMA.MY_STATUS SET LAST_UPDATED=CURRENT_TIMESTAMP, STATUS='complete', MESSAGE='complete' WHERE STATUS_ID = 123"

_ = cr.execute(update_sql)

my_oml_df = oml.sync(view='TRANSACTIONS')

connection.commit() # commit is required if oml.sync is being used

cr.close()