Connecting to an Oracle database using SQLAlchemy

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_engineDIALECT = 'oracle'SQL_DRIVER = 'cx_oracle'USERNAME = 'your_username' #enter your usernamePASSWORD = 'your_password' #enter your passwordHOST = 'subdomain.domain.tld' #enter the oracle db host urlPORT = 1521 # enter the oracle port numberSERVICE = 'your_oracle_service_name' # enter the oracle db service nameENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICEengine = create_engine(ENGINE_PATH_WIN_AUTH)#test queryimport pandas as pdtest_df = pd.read_sql_query('SELECT * FROM global_name', engine)

from sqlalchemy import create_engineimport cx_Oraclehost=hostnameport=portsid='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.

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

from sqlalchemy import create_engineimport pandas as pd engine = create_engine('oracle://myusername:mypassword@SID')con = engine.connect()outpt = con.execute("SELECT * FROM YOUR_TABLE")df = pd.DataFrame(outpt.fetchall())df.columns = outpt.keys()print(df.head())con.close()