title: Connect to Oracle with ODBC in Python
body: <p>I managed to install Oracle 12.2.01, running its official <a href="https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance" rel="nofollow noreferrer">Docker image</a> on 64bit Linux with:</p>
  
  <pre class="lang-sh prettyprint-override"><code>docker run --name oracle12-se2 -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=my_pwd oracle/database:12.2.0.1-se2
  </code></pre>
  
  <p>The database seems to be running correctly, I can enter its <code>SQL &gt;</code> prompt with:</p>
  
  <pre class="lang-sh prettyprint-override"><code>docker exec -ti oracle12-se2 sqlplus system@ORCLPDB1
  </code></pre>
  
  <p>and issue commands there.</p>
  
  <p>But trying to connect from another Linux machine via Python's <code>pyodbc</code> 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:</p>
  
  <pre class="lang-py prettyprint-override"><code>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)
  </code></pre>
  
  <p>The error is always <code>Error: ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)")</code>. How to debug this?</p>
  
  <hr>
  
  <p><strong>EDIT:</strong> I made some progress. I can successfully connect to the remote database using <code>cx-oracle</code> instead of <code>pyodbc</code>:</p>
  
  <pre class="lang-py prettyprint-override"><code>import cx_Oracle
  connection = cx_Oracle.connect("system", "my_pwd", "175.201.160.29/ORCLPDB1")
  # …connection cursors work as expected, all good
  </code></pre>
  
  <p>I guess that verifies there is no issue with the database or connectivity as such. However, I need <code>pyodbc</code> to work, not <code>cx-oracle</code>, so my question above still stands.</p>