Connect to Oracle with ODBC in Python

Question

I managed to install Oracle 12.2.01, running its official Docker image on 64bit Linux with:

docker run --name oracle12-se2 -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=my_pwd oracle/database:12.2.0.1-se2

The database seems to be running correctly, I can enter its SQL > prompt with:

docker exec -ti oracle12-se2 sqlplus system@ORCLPDB1

and issue commands there.

But trying to connect from another Linux machine via Python's pyodbc fails. I'm using the official Oracle Instant Client + ODBC driver 19.3, but I got lost in all the acronyms: DBQ, SID, DSN, TSN, instance, SERVER_NAME, tnsnames.ora, ports… None of the following work:

import pyodbc  # version 4.0.26
conn_params = [
    {'server': '175.201.160.29:1521', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
    {'server': '175.201.160.29:1521/ORCLCDB', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
    {'server': '175.201.160.29:1521/ORCLPDB1', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
    {'dbq': '175.201.160.29:1521', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
    {'dbq': '175.201.160.29:1521/ORCLCDB', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
    {'dbq': '175.201.160.29:1521/ORCLPDB1', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"},
]
for attempt in conn_params:
    try:
        conn = pyodbc.connect(**attempt)
        print('SUCCESS!')
    except Exception:
        print('failed with', attempt)

The error is always Error: ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)"). How to debug this?

EDIT: I made some progress. I can successfully connect to the remote database using cx-oracle instead of pyodbc:

import cx_Oracle
connection = cx_Oracle.connect("system", "my_pwd", "175.201.160.29/ORCLPDB1")
# …connection cursors work as expected, all good

I guess that verifies there is no issue with the database or connectivity as such. However, I need pyodbc to work, not cx-oracle, so my question above still stands.

The [Oracle ODBC documentation](https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#BABGJJAF) says that you need to create a ["tnsnames.ora" file]. Have you looked into doing that? — Gord Thompson, Aug 20 '19 at 22:37
@GordThompson I ran the provided `odbc_update_ini.sh` and symlinked all shared libs. Where do I get this `tnsnames.ora`, what do I put in it, and how do I configure pyodbc after that? The official docs are completely opaque to me. — user124114, Aug 20 '19 at 22:51
[The Oracle documentation](https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#BABDFDJB) says that by using the Oracle Net Configuration Assistant (NETCA) "you can create an entry in the tnsnames.ora file for each TNS Service Name. NETCA is installed when you install Oracle Net Services.". Failing that there's also a [dba.stackexchange.com question](https://dba.stackexchange.com/q/28326/21390) that talks about it. — Gord Thompson, Aug 20 '19 at 23:00
No idea what NETCA is, but I created the `tsnnames.ora` file by hand. It seems to work. I'll post it as an answer, see if it makes any sense or I'm completely off. — user124114, Aug 20 '19 at 23:05

score 4 · Accepted Answer · answered Aug 20 '19 at 23:37

A solution that doesn't require creating the extra tnsnames.ora file:

import pyodbc

conn = pyodbc.connect(**{'DBQ': '175.201.160.29/ORCLPDB1', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"})
print(list(conn.cursor().execute("""select * from global_name""")))

[('ORCLCDB', )]

user124114 · Answer 2 · 2019-08-20T23:22:44.427

I'm still hazy on all the Oracle terminology (how much BS do you need to connect to a database?), but the following seems to work:

$ # on the machine with pyodbc
$ export ORACLE_HOME=/opt/oracle/instantclient_19_3
$ cat $ORACLE_HOME/network/admin/tnsnames.ora

LISTENER = (ADDRESS=(PROTOCOL=tcp)(HOST=175.201.160.29)(PORT=1521))

test=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=175.201.160.29)(PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=ORCLCDB)))

And then from Python:

>>> import pyodbc
>>> conn = pyodbc.connect(**{'DBQ': 'test', 'uid': 'system', 'pwd': 'my_pwd', 'driver': "Oracle 19 ODBC driver"})
>>> list(conn.cursor().execute("""select * from global_name"""))
[('ORCLCDB', )]

Not sure why pyodbc needs that file and cx-oracle doesn't.

Connect strings in the C based drivers (e.g. Python cx_Oracle, ODBC, node-oracledb, PHP OCI8 etc) are the same. Documentation on the forms of cx_Oracle connection strings https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-strings applies to pyodbc — Christopher Jones, Aug 22 '19 at 01:34

Connect to Oracle with ODBC in Python

2 Answers2