python - Connecting to an Oracle database using SQLAlchemy

Question

I am able to successfully connect to a SQLite database and access a particular table using the set of commands below:

from sqlalchemy import create_engine, MetaData, Table, and_
from sqlalchemy.sql import select
from pandas import DataFrame 

db = create_engine('sqlite:///path\database.db')
metadata = MetaData(db)
table = Table('table name', metadata, autoload=True)

I am able to fetch data from an Oracle database using cx_Oracle.

However, when I try to connect to an Oracle database in SQLAlchemy, I am getting the following error:

NoSuchTableError: <table name>

I have used the following commands:

db = create_engine('oracle://username:password@hostname:1521/instance name', echo='debug')
md = MetaData(bind=db)
t = Table('table name', md, autoload=True, schema='schema name')

When I use the following command:

t= Table('table name', md, autoload=True, oracle_resolve_synonyms=True)

I get the following error:

AssertionError: There are multiple tables visible to the schema, you must specify owner

Can you please explain where exactly I am going wrong?

Answer 1

You don't need to import cx_Oracle anymore. The newer version of the sqlalchemy module calls the function cx_Oracle.makedsn(). Have a look:

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)