With python-oracledb what does 'DPY-4027: no configuration directory to search for tnsnames.ora' mean

1. With the python-oracledb driver the code:

   import oracledb
   
   cs = "MYDB"
   c = oracledb.connect(user='cj', password=mypw, dsn=cs)
   

   gives the error:

   oracledb.exceptions.DatabaseError: DPY-4027: no configuration directory to search for tnsnames.ora
   

2. The same error also occurs in a second case:

   import oracledb
   
   cs = "MYDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))"
   c = oracledb.connect(user='cj', password=mypw, dsn=cs)
   

   and with this:

   import oracledb
   
   cs = "MYDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb1)))"
   
   cp = oracledb.ConnectParams()
   cp.parse_connect_string(cs)
   

What does this mean?

The python-oracledb error DPY-4027 means you used a connection string that python-oracledb took to be some kind of alias it needed to look up in a tnsnames.ora file, but it didn't know where to find that file. You need to either tell python-oracledb where to find the file, or use a different connection string syntax.

Database connection strings in python-oracledb's dsn connect arguments are commonly one of:

• An Oracle Easy Connect string like:

  c = oracledb.connect(user='cj', password=mypw, 
                       dsn='myhost:1521/orclpdb1')
  

• An Oracle Net Connect Descriptor string like:

  c = oracledb.connect(user='cj', password=mypw, 
                       dsn='(DESCRIPTION=(ADDRESS=(...))')
  

• A TNS alias mapping to a connect descriptor:

  c = oracledb.connect(user='cj', password=mypw, dsn='MYDB')
  

  These connect descriptors are commonly stored in a tnsnames.ora configuration file on the machine where you are running Python. The file maps the alias to a connect descriptor that really tells python-oracledb where the database is located. The tnsnames.ora file might look like:

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

See the connection string documentation.

It is generally your choice about which connection string syntax to use.

Solutions to DPY-4027

1. One option is to change to the equivalent Easy Connect syntax, and not use a tnsnames.ora file:

   c = oracledb.connect(user='cj', password=mypw, 
                        dsn='localhost:1521/orclpdb1')
   

2. If you do use a tnsnames.ora file, then python-oracledb has some heuristics to automatically locate the file. E.g using the TNS_NAMES or ORACLE_HOME environment variables, or via a default location. Refer to the documentation on defaults and make sure your file is in the correct location, and any environment variable is set for the Python process. (Note these heuristics were improved / changed in python-oracledb 3.0).

3. If you are having problems with the heuristics, you can explicitly tell python-oracledb where your tnsnames.ora is. For example, if you have /opt/myconfigdir/tnsnames.ora, then:

   • In python-oracledb's default 'Thin' mode, use:

     c = oracledb.connect(user='cj', password=mypw, dsn='MYDB',
                          config_dir='/opt/myconfigdir')
     

   • In python-oracledb's Thick mode (which is the mode when the app calls init_oracle_client()) then use:

     oracledb.init_oracle_client(config_dir='/opt/myconfigdir')
     c = oracledb.connect(user='cj', password=mypw, dsn='MYDB')