flask-sqlalchemy returning no config directory to search for tnsnames.ora when trying to connect

[CiaranGames]

Hello all,

When I try to connect to my Oracle Cloud autonomous DB with the flask-sqlalchemy package I get this error:

sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4027: no configuration directory to search for tnsnames.ora
(Background on this error at: https://sqlalche.me/e/20/4xp6)`.

when I use the SQLALCHEMY_DATABASE_URI = 'oracle+oracledb://username:password@dsn'

and the dsn being:

dsn = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))(connect_data=(service_name=example_database_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

sqlalchemy and oracledb both default to 'thin' mode I am not sure why it should be looking for tnsnames.ora

However when i set sqlalchemy database uri to:

SQLALCHEMY_DATABASE_URI = 'oracle+oracledb://username:[email protected]:1522/?service_name=example_database_medium.adb.oraclecloud.com'

I get the error:

sqlalchemy.exc.OperationalError: (oracledb.exceptions.OperationalError) DPY-6005: cannot connect to database (CONNECTION_ID=fyLyakklPrM9BNTSCnqinw==).
[Errno 11003] getaddrinfo failed

This is unusual because I can connect to my DB just fine when going through the actual oracledb package on a different python file using:

connection=oracledb.connect(
     user=username,
     password=pass,
     dsn=cs)

with cs being the same string as the DSN i showed earlier.

My Current Package versions are:

SQLAlchemy: 2.0.21
flask-sqlalchemy: 3.1.1
oracledb: 1.4.1

Any help or ideas would be greatly appreciated.

[anthony-tuininga]

Thin mode does in fact look at tnsnames.ora when the connect string supplied doesn't start with a "(" character and isn't an easy connect string. You can set the environment variable TNS_ADMIN to provide the location where the tnsnames.ora file will be searched, but in this case it looks more like a potential issue with how connect strings are passed through to SQLAlchemy -- but I don't know enough about SQLAlchemy connect strings to be able to help there, unfortunately! Perhaps post this in the SQLAlchemy help forum?

[cjbj]

See https://medium.com/oracledevs/using-the-development-branch-of-sqlalchemy-2-0-with-python-oracledb-d6e89090899c