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 says that you need to create a ["tnsnames.ora" file]. Have you looked into doing that? — Gord Thompson
– Gord Thompson, Commented Aug 20, 2019 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
– user124114, Commented Aug 20, 2019 at 22:51
The Oracle documentation 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 that talks about it. — Gord Thompson
– Gord Thompson, Commented Aug 20, 2019 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
– user124114, Commented Aug 20, 2019 at 23:05

user124114 · Accepted Answer · 2019-08-20 23:37:24Z

4

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', )]

answered Aug 20, 2019 at 23:37

user124114

8,73113 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user124114 · Accepted Answer · 2019-08-20 23:22:44Z

2

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.

edited Aug 20, 2019 at 23:22

answered Aug 20, 2019 at 23:11

user124114

8,73113 gold badges48 silver badges69 bronze badges

1 Comment

Christopher Jones Over a year ago

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 cx-oracle.readthedocs.io/en/latest/user_guide/… applies to pyodbc

Collectives™ on Stack Overflow

Connect to Oracle with ODBC in Python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related