[Solved] Connect to Oracle database using SQLALCHEMY

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 library.

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:[email protected]: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 understand where exactly am i going wrong.

Thanks,

Rohit

Solution #1:

from sqlalchemy import create_engine
import cx_Oracle

host=hostname
port=port
sid='sid'
user='username'
password='password'
sid = cx_Oracle.makedsn(host, port, sid=sid)

cstr = 'oracle://{user}:{password}@{sid}'.format(
    user=user,
    password=password,
    sid=sid
)

engine =  create_engine(
    cstr,
    convert_unicode=False,
    pool_recycle=10,
    pool_size=50,
    echo=True
)

result = engine.execute('select * from TABLE')

for row in result:
    print row

This worked for me. A connection object can also be created like

conn = engine.connect()
conn.close()

which will enable to close the connection. This works even if you have a tunnel to your remote DB from your local port.

Respondent: mkarun2

Solution #2:

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)
Respondent: Dataku

Solution #3:

Assuming you have the Oracle client on your machine with a valid tnsnames.ora file, this works for me:

from sqlalchemy import create_engine
import pandas as pd 
engine = create_engine('oracle://myusername:[email protected]')
con = engine.connect()
outpt = con.execute("SELECT * FROM YOUR_TABLE")
df = pd.DataFrame(outpt.fetchall())
df.columns = outpt.keys()
print(df.head())
con.close() 
Respondent: Steve Olsen

Solution #4:

This works for me, when there is no tnsnames.ora file.

user = 'system'
pwd = 'oracle'
dsn = cx_Oracle.makedsn(
    '192.168.1.105', 49161,
    # service_name="your_service_name_if_any"
)
ora_engine = create_engine(f'oracle+cx_oracle://{user}:{pwd}@{dsn}', echo=True)
ora_engine.connect()
Respondent: Endre

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.