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)')
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()
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
The code below is for mssql on Mac OS and it assumes that you installed unixODBC using:
$ brew install freetds --with-unixodbc
and that you have edited the two odbc config files:
/usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on MacOS
Driver=/usr/local/Cellar/freetds/0.95.80/lib/libtdsodbc.0.so
Setup=/usr/local/Cellar/freetds/0.95.80/lib/libtdsodbc.0.so
FileUsage=1
Edit ~/Library/ODBC/odbc.ini
[sql_server]
Driver=FreeTDS
Server=put_ip_here
Port=1433
== Code:
import pyodbc
connection_string = "Driver={{FreeTDS}};Server={};"\
"Database={};UID={};PWD={};"\
.format(db_host, db_name, db_user, db_password)
with pyodbc.connect(connection_string) as db:
cursor = db.cursor()
cursor.execute("SELECT count(*) FROM aTable")
...
The reply has been late but can be useful for future reader.
Install:
From: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
sudo rpm -Uvh oracle-instantclient12.2-*
set ORACLE_HOME and LD_LIBRARY_PATH
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
In /etc/odbcinst.ini set:
[Oracle_test]
Description=Oracle ODBC driver for Oracle 12c
Driver=/usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1
FileUsage=1
Driver Logging=7
UsageCount=1
In Python shell:
>>> import pyodbc
>>> conn = pyodbc.connect('DRIVER={Oracle_test};Host=1.1.1.1;Port=1521;Service Name=orcl.local;User ID=test1;Password=test1')
>>> print(conn)
<pyodbc.Connection object at 0x7f6acb2c4c00>
Hopefully it helps someone.
PS: You can also set the driver in /etc/ld.so.conf as
/usr/lib/oracle/12.2/client64/lib
Run: ldconfig
Try something like:
import pyodbc
connectString = 'Driver={Microdsoft ODBC for Oracle};Server=<host>:<port>/<db>.<host>;uid= <username>;pwd=<password>'
cnxn = pyodbc.connect(connectString)
Read some docs ;) https://sites.google.com/site/bcgeopython/examples/getting-the-pyodbc-module
