python – Connecting to an Oracle database using SQLAlchemy

python – Connecting to an Oracle database using SQLAlchemy

You dont 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 [email protected] + 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)
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.

python – Connecting to an Oracle database using SQLAlchemy

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() 

Leave a Reply

Your email address will not be published.