Accessing oracle database through python

Question

I have spent almost last 48 hours trying almost all solutions available. My final resort is:

I can access the database through Oracle SQL developer.
Now I want to call the queries in python using pyodbc. But I keep getting this error:

My code looks like this :

import pyodbc 
oracledriver = '{xyxzz}'
oracleuid = 'abcd'
oraclepwd = 'abcd'
oracleConn = pyodbc.connect(DRIVER=oracledriver, UID=oracleuid, PWD=oraclepwd, DBQ=oracledbq)

Error :

<i> InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') <i>

I think the problem is with setting up database or something? Please help me with possible issues. Something on the lines of tsanames? (may be)

Edit : When I try to get DSN file :

I get this pop up :

a connection could not be made using the file data source parameters entered

Further info : All the info I have is :

<property name="URL" value="jdbc:oracle:thin:@abcd:1522/abcd" />
<property name="user" value="12345" />
<property name="password" value="12345" />

I started using a different approach using sql alchemy. It looks to be working. But when I try to execute the query, i receive an error. Code and error follows :

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import cx_Oracle
engine = create_engine('oracle://user:pwdd@localhost:1521/dbname')
connection = engine.connect()
Session = scoped_session(sessionmaker(bind=engine))
s = Session()
result = s.execute('select * from strategy_group_decode;')

The error is :

DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: 64-bit Oracle Client library cannot be loaded: "C:\OraHome_2\oci.dll is not the correct architecture". See https://oracle.github.io/odpi/doc/installation.html#windows for help (Background on this error at: http://sqlalche.me/e/4xp6)

Thank you in advance.

I believe in order to use ODBC you need to install the Oracle client on whatever machine you're running the client software (in this case python) on. I don't believe pyodbc will work with the Oracle JDBC thin client. — David Faber, Jun 04 '18 at 15:49
Hi, The driver is already installed. I am able to execute queries using Oracle SQL Developer — Shariq, Jun 04 '18 at 17:04
Oracle SQL Developer ships with the JDBC thin client; I think you need the so-called "thick" client (Oracle Instant Client) together with the Oracle ODBC driver package http://www.oracle.com/technetwork/database/windows/index-098976.html — David Faber, Jun 04 '18 at 17:20
"C:\OraHome_2\oci.dll " is probably 32-bit but you have a 64-bit Python. Install a 64-bit Oracle Client, e.g. [Instant Client](http://www.oracle.com/technetwork/topics/winx64soft-089540.html) and make sure its libraries are first in PATH when you run Python (and probably not first in PATH when you run your older applications). Don't forget to install the VS Redistributable mentioned on the Instant Client page. — Christopher Jones, Jun 05 '18 at 03:01
Do you have to use pyodbc? I've just started connecting from Python to Oracle and use cx_Oracle, which works well... — wolφi, Jun 08 '18 at 16:04

score 0 · Answer 1 · answered Jun 04 '18 at 13:19

0

Try this:

import pyodbc
connection = pyodbc.connect('DRIVER={oracledriver};SERVER=SQLSRV01;DATABASE=DATABASE;UID=oracleuid;PWD=oraclepwd')
cursor = connection.cursor()

answered Jun 04 '18 at 13:19

NoorJafri

1,787
16
27

Also did you checkout this: https://stackoverflow.com/a/25428598/6198978 Because I tested on my mac and I received this: pyodbc.Error: ('01000', u"[01000] [unixODBC][Driver Manager]Can't open lib – NoorJafri Jun 04 '18 at 13:33
I did check that out. Thats no the issue. How to verify which oracle driver, the database is on? – Shariq Jun 04 '18 at 14:17

score 0 · Answer 2 · answered Aug 28 '19 at 12:58

@Shariq

You could also use the cx_Oracle driver:

from sqlalchemy.engine import create_engine

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username' #enter your username
PASSWORD = 'your_password' #enter your password
HOST = 'subdomain.domain.tld' #enter the oracle db host url
PORT = 1521 # enter the oracle port number
SERVICE = 'your_oracle_service_name' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)


#test query
import pandas as pd
test_df = pd.read_sql_query('SELECT * FROM global_name', engine)

Accessing oracle database through python

2 Answers2