0

When using cx_Oracle (8.3.0), it is simple to connect to an Oracle DB with just this line of code:

con = cx_Oracle.connect(user = myname, password = mypw, dsn = "myDBprod")

When I swap out for oracledb module (1.0.2), I get an error:

con = oracledb.connect(user = myname, password = mypw, dsn = "myDBprod")

DatabaseError: DPY-4026: cannot connect to database. File tnsnames.ora not found in C:\Oracle\InstantAdmin

While researching this, I found this post: With python-oracledb what does 'DPY-4027: no configuration directory to search for tnsnames.ora' mean But I have no clue why all these extra steps are necessary.

1337nerd
  • 33
  • 6
  • Its necessary because oracle deemed it so – dcom-launch Aug 02 '22 at 16:17
  • tnsnames.ora file tells your system where to find the database, like host name, instance name, port number, etc. it's a configuration file and there must be one somewhere on your system that cx_Oracle is using, but it must be in a different place than the other module is expecting – Kurt Aug 02 '22 at 16:32
  • @Kurt I've tried creating a config file in the specified C:\Oracle\InstantAdmin folder but that I didn't see anything in there before. I'm not sure where to find the corresponding one for cx_Oracle (had no idea there was one). – 1337nerd Aug 02 '22 at 16:48

1 Answers1

2

I can explain why this is necessary. With cx_Oracle, an Instant Client (or Oracle Database) installation is required. These installations have a default location for tnsnames.ora: <install_dir>/network/admin/tnsnames.ora. So if you put the file in that location you don't need to specify its location. The thin driver doesn't require an Oracle Client (or Database) installation, so there is no default location. So you have to do one of the following:

  • set the environment variable TNS_ADMIN to point to the location where your tnsnames.ora file is found
  • pass the location where your tnsnames.ora file is found in your code, as in:
con = oracledb.connect(user=myname, password=mypw, dsn="myDBprod",
                       config_dir="/the/directory/of/tnsnames.ora")

If you have an Oracle Client installation already (since you were using cx_Oracle), you can also enable thick mode which behaves the same way as cx_Oracle. You can do that by doing this:

oracledb.init_oracle_client()
Anthony Tuininga
  • 6,388
  • 2
  • 14
  • 23
  • Thank you Anthony, very helpful information. I will have to read more about 'thin' vs 'thick' modes. I was able to get my connection established when calling oracledb.init_oracle_client(). – 1337nerd Aug 02 '22 at 17:17
  • You're welcome! If you have any other questions/concerns you can post them on GitHub: https://github.com/oracle/python-oracledb/discussions – Anthony Tuininga Aug 02 '22 at 20:58
  • The direct answer to the raw question is that it isn't necessary: you can use the [Easy Connect syntax](/connection_handling.html#easy-connect-syntax-for-connection-strings) without needing any external configuration files. However if you want to connect using an alias from a tnsnames.ora file, then you can get the background from Anthony's answer. – Christopher Jones Aug 02 '22 at 23:16