Introduction

Python with TimesTen Cache LivelLab

This blog shows you how to use Python with the Oracle LiveLab for TimesTen Cache.

The same Python source code will work against either Oracle Database 19c or TimesTen Cache.

 

Part 1 of this blog covers the following topics:

  • Using the Oracle TimesTen Cache LiveLab
  • Installing Python 3.9 in the LiveLab
  • Installing the python-oracledb library
  • Connecting to either Oracle Database 19c or TimesTen Cache
  • Simple queries returning resultsets
  • Dropping tables via a PLSQL block
  • Creating tables using SQL
  • Inserting, updating and deleting rows using SQL
  • Committing ACID transactions
  • How Python programs can work the same with both Oracle Database 19c and TimesTen Cache

 

 

 

 

Using the Oracle TimesTen Cache LiveLab

TimesTen Cache LiveLab

The TimesTen Cache LiveLab is a VM which runs on Oracle Cloud.  The VM uses Docker containers to run the Oracle Database 19c and TimesTen 22.1 Cache.

Docker constainer in LiveLab VM

The Oracle TimesTen Cache LiveLab is here.

You need to have completed Labs 1 to 13 before you can start using Python with TimesTen.

 

 

 

cx_Oracle and python-oracledb Drivers and Python versions

cx_Oracle and python-oracledb libraries

 

Both the Python cx_Oracle and python-oracledb drivers work with the Oracle Database and Oracle TimesTen.

python versions

The python-oracledb driver should be used rather than the older cx_Oracle library for new projects. The python-oracledb driver supports both thick and thin python clients for the Oracle database.  TimesTen needs to use the thick client via the Oracle Instant Client that ships with TimesTen 18.1 or 22.1.

The LiveLab for TimesTen Cache uses Oracle Linux 8.6 and has Python 3.6.8 installed by default and it will work with the cx_Oracle driver.

This blog will use the newer python-oracledb driver and python 3.9.

 

 

 

Installing Python 3.9 in your LiveLab VM container

Install python3 in LiveLab container

To install python 3.9 in your TimesTen Cache LiveLab VM.

First ssh to your LiveLab VM:

ssh oracle@your_livelab_public_ip_address

 

Then ssh to the tthost1 Docker container in the LiveLabs VM:

ssh tthost1

 

Install Python 3.9 in the container:

sudo dnf module install python39

 

This installed Python 3.9.7 on my LiveLab container.

 

Then install the pip package manager for Python 3.9:

sudo dnf install python39-pip

 

 

 

Install the python-oracledb driver

Python-OracleDB driver

Install the python-oracledb driver from the pip package manager:

python3.9 -m pip install oracledb

 

 

 

A Python program that works with Oracle 19c and TimesTen 18c

Create a trivial Python program using the python-oracledb driver that works with both Oracle Database 19c and Oracle TimesTen.

The test.py program does the following:

  • Connects as appuser/appuser@$SVC_NAME
  • Determines the version of the Oracle Client
  • Determines the version of the database server
  • Queries the SYSDATE
  • Queries one row via the primary key
  • Queries a set of rows via a SQL where clause
  • Automatically disconnects

# test.py

import oracledb
import os

username = “appuser”
user_pwd = “appuser”
dsn_name = os.environ.get(“SVC_NAME”)

# Use the Oracle Instant Client to connect
oracledb.init_oracle_client()

with oracledb.connect(user=username, password=user_pwd, dsn=dsn_name) as connection:
    with connection.cursor() as cursor:

        client_ver = oracledb.clientversion()
        print(“\nClient version: “)
        print(client_ver)

        sql = “””select * from v$version”””
        print(“\nServer version:”)
        for r in cursor.execute(sql):
            print(r)

        sql = “””select sysdate from dual”””
        print(“\nThe SYSDATE:”)
        for r in cursor.execute(sql):
            print(r)

        sql = “””select * from vpn_users where vpn_id = 0 and vpn_nb = 0″””
        print(“\nOne row of data from the VPN_USRS table:”)
        for r in cursor.execute(sql):
            print(r)

        sql = “””select * from vpn_users where vpn_id = 0 and vpn_nb < 50 and rownum < 5 order by 2″””
        print(“\nSome data from the VPN_USRS table:”)
        for r in cursor.execute(sql):
            print(r)

print(“\nBye”)

 

The TimesTen Cache LiveLab already has the appuser schema and the VPN_USERS tables with data in it.

TimesTen needs to use the Python thick client via calling oracledb.init_oracle_client().

 

 

Use Oracle Net Service names to connect

There are multiple ways to connect with the python-oracledb driver.  This example uses Oracle Net service names via tnsnames.ora:

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

SAMPLEDB =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = timesten_direct)
      (SERVICE_NAME = sampledb)
    )
  )

SAMPLEDBCS =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = timesten_client)
      (SERVICE_NAME = sampledbcs)
    )
  )

 

This tnsnames.ora file provides three services to connect to:

TNS service names

  • TimesTen client/server connections use TCP/IP sockets [and optionally TLS]. The TimesTen client can be on a different machine than the TimesTen server
  • TimesTen direct linked connections use shared memory
    • Direct linked connections are about 10x faster than TCP sockets
    • The application and TimesTen MUST to be on the same machine.
  • The TimesTen service name [direct linked or client/server] in tnsnames.or MUST point to a working TimesTen DSN in sys.odbc.ini

 

The TimesTen Cache LiveLab configures and uses $TNS_ADMIN/tnsnames.ora to enable connection to either the Oracle Database 19c or Oracle TimesTen.

 

 

 

Choose which service name to connect to

The test.py Python program uses an environment variable [$SVC_NAME] to define the service name to connect to.

You need to define the value of $SVC_NAME to choose which of the three services to connect to.

 

To set the SVC_NAME in the Linux bash shell to point ot Oracle Database 19c:

export SVC_NAME=ORCLPDB1

 

To set the SVC_NAME in the Linux bash shell to point to TimesTen with a direct linked connection:

export SVC_NAME=sampledb

 

To set the SVC_NAME in the Linux bash shell to point to a TimesTen client/server connection:

export SVC_NAME=sampledbcs

 

 

 

Use Python to query Oracle Database 19c or TimesTen

Run test.py against Oracle Database 19c:

export SVC_NAME=ORCLPDB1
python3.9 test.py

 

The output from test.py when connected to Oracle Database 19c:

oracle@tthost1 py]$ python3.9 test.py

Client version:
(19, 14, 0, 0, 0)

Server version:
(‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’, ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production\nVersion 19.3.0.0.0’, ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’, 0)

The SYSDATE:
(datetime.datetime(2022, 10, 13, 1, 10, 28),)

One row of data from the VPN_USRS table:
(0, 0, ‘5500      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘)

Some data from the VPN_USRS table:
(0, 0, ‘5500      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘)
(0, 1, ‘5501      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension1>                                                         ‘)
(0, 2, ‘5502      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension2>                                                         ‘)
(0, 3, ‘5503      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension3>                                                         ‘)

Bye
[oracle@tthost1 py]$

 

The TimesTen Cache LiveLab uses:

  • Oracle Instant Client 19c which ships with TimesTen 22.1.  This is why the Client version output is 19.14
  • Oracle Database 19c which is why the Server version output is 19.3
  • Pre-loaded data which exists in the APPUSER.VPN_USERS table

 

Run test.py against TimesTen Cache:

export SVC_NAME=sampledb
python3.9 test.py

 

The output of test.py when connected to Oracle TimesTen Cache:

[oracle@tthost1 py]$ python3.9 test.py

Client version:
(19, 14, 0, 0, 0)

Server version:
(22, 1, 1, 3, 0, 1)

The SYSDATE:
(datetime.datetime(2022, 10, 13, 1, 37, 54),)

One row of data from the VPN_USRS table:
(0, 0, ‘5500      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘)

Some data from the VPN_USRS table:
(0, 0, ‘5500      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘)
(0, 1, ‘5501      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension1>                                                         ‘)
(0, 2, ‘5502      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension2>                                                         ‘)
(0, 3, ‘5503      ‘, ‘000000000 ‘, ‘<placeholderfordescriptionofVPN0extension3>                                                         ‘)

Bye
[oracle@tthost1 py]$

 

The TimesTen Cache LiveLab uses:

  • Oracle Instant Client 19c which ships with TimesTen 22.1.  This is why the Client version output is 19.14
  • TimesTen 22.1 which is why the Server version output is 22.1.1.3
  • Pre-loaded data which exists in the APPUSER.VPN_USERS table

 

Apart from the Server version and SYSDATE, the output is the same for both Oracle Database 19c and TimesTen Cache:

  • The Server Version output is supposed to be different for Oracle and TimesTen
  • The SYSDATE is different as the programs were not run at the exact same time
  • The output is the same for both TimesTen Direct Linked and Client/Server connections

 

 

 

A Python application to modify Oracle Database 19c or TimesTen

The test2.py program does the following:

  • Connects as appuser/appuser@$SVC_NAME
  • Determines the version of the database server
  • Queries the SYSDATE
  • Drops table T via a PLSQL block
  • Creates table T via SQL
  • Does a batch insert of three rows into table T
  • Updates all of the rows in table T
  • Deletes a row by primary key in table T
  • Commits the transaction
  • Queries the rows in table T after the inserts, updates and deletes
  • Automatically disconnects

 

# test2.py

import oracledb
import os

username = “appuser”
user_pwd = “appuser”
dsn_name = os.environ.get(“SVC_NAME”)

# Use the [thick] Oracle Instant Client to connect
oracledb.init_oracle_client()

with oracledb.connect(user=username, password=user_pwd, dsn=dsn_name) as connection:
    with connection.cursor() as cursor:

        # Is this running on Oracle Database 19c or TimesTen
        sql = “””select * from v$version”””
        print(“\nServer version:”)
        for r in cursor.execute(sql):
            print(r)
        print(“”)

        # Use a PLQL block to drop a table
        cursor.execute(“””
           begin
              execute immediate ‘drop table t’;
           exception when others
              then null;
           end;
        “””)

        # Create a table
        cursor.execute(“””
          create table t (
             id     number not null primary key,
             addr   varchar2(64),
             price  number(6,2),
             sold   date default sysdate
           )”””)

        # Some data to insert
        rows = [
                (1, “addr 1”, 10.12),
                (2, “addr 2”, 42.69),
                (3, “addr 3”, 1078.00)
              ]

        # Batch insert
        cursor.executemany(“insert into t (id, addr, price) values (:1, :2, :3)”, rows)
        print(cursor.rowcount, “rows inserted into table t”)

        # Get the resultset of inserted rows
        sql = “””select * from t”””
        print(“\nSome inserted data from table t:”)
        for r in cursor.execute(sql):
            print(r)

        print(“”)
        cursor.execute(“””update t set addr = ‘Unknown'”””)
        print(cursor.rowcount, “rows updated in table t”)

        # Get the resultset of updated rows
        sql = “””select * from t”””
        print(“\nSome updated data from table t:”)
        for r in cursor.execute(sql):
            print(r)

        print(“”)
        cursor.execute(“””delete from t where id = 1″””)
        print(cursor.rowcount, “rows deleted in table t”)

        # Get the resultset after the deletes
        sql = “””select * from t”””
        print(“\nSome data after the delete from table t:”)
        for r in cursor.execute(sql):
            print(r)

        # Commit the changes
        connection.commit()

print(“\nBye”)

 

Run test2.py against Oracle Database 19c:

export SVC_NAME=ORCLPDB1
python3.9 test2.py

 

The output of test2.py when connected to Oracle Database 19c:

[oracle@tthost1 py]$ python3.9 test2.py

Server version:
(‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’, ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production\nVersion 19.3.0.0.0’, ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’, 0)

3 rows inserted into table t

Some inserted data from table t:
(1, ‘addr 1’, 10.12, datetime.datetime(2022, 10, 13, 16, 31, 33))
(2, ‘addr 2’, 42.69, datetime.datetime(2022, 10, 13, 16, 31, 33))
(3, ‘addr 3’, 1078.0, datetime.datetime(2022, 10, 13, 16, 31, 33))

3 rows updated in table t

Some updated data from table t:
(1, ‘Unknown’, 10.12, datetime.datetime(2022, 10, 13, 16, 31, 33))
(2, ‘Unknown’, 42.69, datetime.datetime(2022, 10, 13, 16, 31, 33))
(3, ‘Unknown’, 1078.0, datetime.datetime(2022, 10, 13, 16, 31, 33))

1 rows deleted in table t

Some data after the delete from table t:
(2, ‘Unknown’, 42.69, datetime.datetime(2022, 10, 13, 16, 31, 33))
(3, ‘Unknown’, 1078.0, datetime.datetime(2022, 10, 13, 16, 31, 33))

Bye
[oracle@tthost1 py]$

 

Run test2.py against TimesTen Cache:

export SVC_NAME=SAMPLEDB
python3.9 test2.py

 

The output of test2.py when connected to TimesTen Cache:

[oracle@tthost1 py]$ export SVC_NAME=SAMPLEDB
[oracle@tthost1 py]$ python3.9 test2.py

Server version:
(22, 1, 1, 3, 0, 1)

3 rows inserted into table t

Some inserted data from table t:
(1, ‘addr 1’, 10.12, datetime.datetime(2022, 10, 13, 16, 34, 37))
(2, ‘addr 2’, 42.69, datetime.datetime(2022, 10, 13, 16, 34, 37))
(3, ‘addr 3’, 1078.0, datetime.datetime(2022, 10, 13, 16, 34, 37))

3 rows updated in table t

Some updated data from table t:
(1, ‘Unknown’, 10.12, datetime.datetime(2022, 10, 13, 16, 34, 37))
(2, ‘Unknown’, 42.69, datetime.datetime(2022, 10, 13, 16, 34, 37))
(3, ‘Unknown’, 1078.0, datetime.datetime(2022, 10, 13, 16, 34, 37))

1 rows deleted in table t

Some data after the delete from table t:
(2, ‘Unknown’, 42.69, datetime.datetime(2022, 10, 13, 16, 34, 37))
(3, ‘Unknown’, 1078.0, datetime.datetime(2022, 10, 13, 16, 34, 37))

Bye
[oracle@tthost1 py]$

 

Apart from the Server version, the output is the same for both Oracle Database 19c and TimesTen Cache:

  • The Server version output is supposed to be different for Oracle and TimesTen
  • The output is the same for both TimesTen Direct Linked and Client/Server connections

 

 

 

Summary

  • The LiveLab for Oracle TimesTen Cache runs on Oracle Cloud
  • You can use either the cx_Oracle or the newer python-oracledb driver
  • The same Python program can be used with Oracle 19c or TimesTen Cache
  • Connect to the database via a service name in tnsnames.ora
  • TimesTen must use the thick client in python-oracledb
  • Part 2 will cover more details

 

 

Learn more SQL Language APIs

 

 

Learn more about TimesTen XE:

 

 

More TimesTen XE Blogs

 

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.