[Python] Python using oracledb to connect to Oracle database with Pandas DataFrame Error:...

I am pretty new to Python and even newer to Pandas and am hoping for some guidance

My company has an on-prem DEV Oracle database that I am trying to connect to using Python & Pandas. After some searching I found that the Python package "oracledb" was recommended to be used.

Using VS code .IPYNB I have the following chunks of code that seem to work with no errors

# python -m pip install --upgrade pandas
import oracledb
import pandas as pd
from sqlalchemy import create_engine

connection = oracledb.connect(user="TEST", password="TESTING", dsn="TESTDB:1234/TEST")

print("Connected")
print(connection)


The above code seems to run just fine which is great

I then run the below code as a quick test

cursor=connection.cursor()
query_test='select * from dm_cnf_date where rownum < 2'

for row in cursor.execute(query_test):
print(row)


This returns a tuple with a row of data so far so good, looks like I can connect to the database and run a query.

Next I wanted to get the data into a Pandas dataframe and this is where I got stuck

I tried this code

df = pd.read_sql(sql=query_test, con=connection)


Which then I get hit with the following error

:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(sql=query_test, con=connection)
​

I was loosely trying to follow this article ("Read data as pandas DataFrame"): https://kontext.tech/article/1019/python-read-data-from-oracle-database

but it didnt seem to work.

I tried to take a look at the sqlalchemy site here: https://docs.sqlalchemy.org/en/20/dialects/oracle.html#module-sqlalchemy.dialects.oracle.oracledb

Which I tried to rewrite my code a bit as follows

conn_url="oracle+oracledb://TEST:TESTING@TESTDB:1234/TEST"
engine=create_engine(conn_url)

df = pd.read_sql(sql=query_test, con=engine)


And I get hit with another error

OperationalError: DPY-6003: SID "TEST" is not registered with the listener at host "TESTDB" port 1234. (Similar to ORA-12505)
​

Just looking to connect to an Oracle DB and grab data into a Pandas dataframe but keep hitting a wall

Any insight would be very much appreciated

Continue reading...