Connect to Oracle database from SQLAlchemy using pyodbc

Question

I have set up a data source name(DSN) in ODBC driver and supplying that in a query. My below code is working like a charm.

import pyodbc as db
cnxn = db.connect('DSN=Oracle Prod DW;PWD=******')

I want to create a sqlalchemy connection for the same, but I fail. I tried different approaches but it didn't work. I just want to supply a password and DSN.

Answer 1

Oracle dialect + ODBC Driver is not seem to be supported by SqlAlchemy

https://docs.sqlalchemy.org/en/13/core/engines.html#oracle

Only in Java Runtime you can do that apparently

https://docs.sqlalchemy.org/en/13/dialects/oracle.html#module-sqlalchemy.dialects.oracle.zxjdbc

https://www.jython.org/jython-old-sites/archive/21/docs/zxjdbc.html

That being said

If you have an oracle client installation with proper tnsnames setup

You can do something like follows

1. Install cx_Oracle

2. Setup tnsnames i.e.

   DEVDB=
   (DESCRIPTION =
     (ADDRESS =(PROTOCOL =TCP)(HOST =10.10.10.11)(PORT =1521))
       (CONNECT_DATA =
       (SERVER =DEDICATED)
       (SERVICE_NAME =SVCDEV)
    )
   )
   

3. Code

   import sqlalchemy as alc
   from sqlalchemy.orm import sessionmaker
   import cx_Oracle
   import pandas as pd
   
   conn_str = 'oracle://DEVDB'
   
   engine = alc.create_engine(conn_str, echo=False)
   Session = sessionmaker(bind=engine)
   # YOU MIGHT NEED THIS sometimes
   # cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\x64\product\19.0.0\client_1\bin")
   
   sess = Session()
   result = sess.execute("select 'foo' from dual")
   df = pd.DataFrame(result.fetchall(), columns=result.keys())
   print(df.to_string())