Oracle wrapper for Python

M

Many years ago I created simple wrapper to get access from Python to Oracle database. You can find revisited version at my Git repository. All you need to do is to install cx_Oracle module.

You can connect to database using pickle file (see oracle.py) or by following code:

from oracle_wrapper import Oracle

ora = Oracle({
  'user'    : 'USER_NAME',
  'pwd'     : 'PASSWORD',
  'host'    : 'SERVER_NAME',  # default port 1521
  'sid'     : 'SID',
  #'service' : 'SERVICE_NAME',  # use sid or service_name
})

Then you can execute any block of code with or without binded variables. You can also use autobind feature. And you can also use executemany to bulk process rows.

data = ora.execute("BEGIN sess.create_session(in_user_id => :user_id); END;", user_id = 'JKVETINA')

You can also fetch any data from Oracle database into named tuples so you can easily access them.

data = ora.fetch_assoc("""
SELECT namespace, attribute, value
FROM session_context
WHERE namespace LIKE :namespace
ORDER BY 1, 2
""",
  namespace = '%'
)

print(' | '.join(ora.cols))
for row in data:
  print(' | '.join(row))  # row.attribute.ljust(16), row.value
print()

Update April 18th 2021

With some minor adjustments you can easily connect to Oracle Cloud.

I changed pickle file to adjust for wallets (just pass user, password and service_name). The biggest pain was to use tnsnames.ora file on Mac. In theory, you could just use:

cx_Oracle.init_oracle_client(
  lib_dir    = '/Users/dobby/.instantclient_19_3'
  config_dir = '/Users/dobby/.sqldeveloper/Wallet_KVIDO'
)
os.environ['TNS_ADMIN'] = '/Users/dobby/.sqldeveloper/Wallet_KVIDO'

But it was not working for me no matter what. My lib_dir is somehow fixed. So I copied wallet files to instant_client_19_3/network/admin folder and that was it. Not the best solution, but it is working now.

For more info check this article or this video.