[Python] Error Connecting to Oracle Data Warehouse DW2_DEV: Unsupported Password Verifier Type...

Description:


I am attempting to connect to the DW2_DEV database within our Oracle data warehouse using Python 3.11 and the oracledb library version 3.1.1. While I can successfully connect to the DW3_UAT database using a different set of credentials, I encounter an error when trying to connect to DW2_DEV.

The connection to DW3_UAT works flawlessly, and I am able to run a query that lists the total number of tables:

Connected to Oracle Database DW3_UAT!
1861 Tables Found


However, when attempting to connect to DW2_DEV, I receive the following error:

Error connecting to database: DPY-3015: password verifier type 0x939 is not supported by python-oracledb in thin mode


Here's what I've tried so far:

1. Consulted with our database administrators to ensure that the username, password, port, host name, and SID are all correct for DW2_DEV.
2. Verified that there are no firewall issues and confirmed connectivity via Telnet.
3. Attempted to force a thick mode connection, but have not been successful.

Below is my code for connecting to the database:

import oracledb
import getpass
import json
import os

def credentials():
filename = 'creds.json'
with open(filename, 'r') as file:
data = json.load(file)

credentials = {}
for key, value in data.items():
credential = {
'username': value['username'],
'password': value['password'],
'sid': value['sid'],
'hostname': value['hostname'],
'port': value['port']
}
credentials[value['sid']] = credential

return credentials

# Main function
def main(sid):
# Load credentials
creds = credentials()
if not creds:
print("Failed to load credentials. Exiting.")
return

# Select connection
selected_conn = creds[sid]

# Get connection details
username = selected_conn["username"]
hostname = selected_conn["hostname"]
port = int(selected_conn["port"])
password = selected_conn["password"]

try:
# Create a DSN (Data Source Name)
dsn = oracledb.makedsn(hostname, port, sid=sid)

# Create a connection
connection = oracledb.connect(user=username, password=password, dsn=dsn)
print(f"Connected to Oracle Database {sid}!")

# Your database operations here
cursor = connection.cursor()

# Execute query to list tables
cursor.execute("SELECT table_name FROM all_tables")

# Fetch all results
tables = cursor.fetchall()

# Print the tables
print(f"{len(tables)} Tables Found")

cursor.close()
connection.close()

except Exception as e:
print(f"Error connecting to database: {e}")

if __name__ == "__main__":
main('DW2_DEV')


I've been unable to resolve this error and would greatly appreciate any suggestions or insights from the community on how to address this issue and successfully connect to the DW2_DEV database.

Continue reading...