how do you connect to oracle using pyodbc

Question

I am trying to connect to Oracle db using pyodbc, getting errors. The examples include ms sql server driver:

in my /etc/unixODBC/odbc.ini, I have this entry:

[test_con]
Driver=Oracle
Description=data repository db
Trace=Yes
ServerName=//db1.example.com:1521/db2_svc1


import pyodbc
cnxn=pyodbc.connect('DSN=test_con, UID=user_id, PWD=passwd123')

I get this error:

pyodbc.Error: ('IM012', '[IM012] [unixODBC][Driver Manager]DRIVER keyword syntax error (0) (SQLDriverConnect)')

Answer 1

I came here looking for an answer to this question, but instead found an answer elsewhere to a more general question that I'd like to share. You can very simply connect to an Oracle db without pyodbc, using the cx_Oracle library. Check out the installation instructions below:

https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html

Starter code below:

cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_10")

connection = cx_Oracle.connect(
user="username",
password="password",
dsn="address")

cursor = connection.cursor()

Answer 2

pyodbc maintainer did an excellent job at documenting how to install Oracle ODBC driver and then how to connect to the database. It worked for me: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Oracle-from-RHEL-or-Centos