dsnless connection using oracledb

Question

Using VBA I can make a dsnless connection to oracle in the following way.

Driver={Oracle in OraClient21Home1};DBQ=<<string_returned_from_tnsping>>;User Id=fake_user;Password=fake_password;

Is there a similar way I can do the same in Python with oracledb?

Answer 1

• Use Thick mode with libraries from your Oracle Database home
• Don't pass a DSN parameter when connecting; this will pick up the environment ORACLE_SID

import os
import platform

import oracledb

ld = None  # On Linux, pass None
if platform.system() == 'Darwin':
    ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_23_3'
elif platform.system() == 'Windows':
    ld = r'C:\oracle\instantclient_19_23'
oracledb.init_oracle_client(lib_dir=ld)
print('Using Thick mode')

un = 'cj'
pw = 'cj'

with oracledb.connect(user=un, password=pw) as connection:
    with connection.cursor() as cursor:
        sql = """select systimestamp from dual"""
        for (r,) in cursor.execute(sql):
            print(r)

This actually also works with Instant Client to a remote database if you set the old Oracle environment variable TWO_TASK to your connect string.