How to read a table and Sql query from Oracle in Pandas?

I don't have Oracle server, so I take Teradata as an example

This is not the only way to to that, just one approach

• Make sure you have installed Teradata ODBC Driver. Please refer to Teradata official website about the steps, I suppose you use Windows (since it is easy to use SQL Assistant to run query against Teradata, that is only on Windows). You can check it in ODBC Data Source Administrator
• Install pyodbc by the command pip install pyodbc. Here is the official website
• The connection string is db_conn_str = "DRIVER=Teradata;DBCNAME={url};UID={username};PWD={pwd}"
• Get a connection object conn = pyodbc.connect(db_conn_str)
• Read data from a SQL query to a DataFrame df = pd.read_sql(sql="select * from tb", con=conn)

The similar for Oracle, you need to have the driver and the format of ODBC connection string. I know there is a python module from Teradata which supports the connection too, but I just prefer use odbc as it is more generic purpose.

Here is an Oracle example:

import cx_Oracle   #  pip install cx_Oracle
from sqlalchemy import create_engine

engine = create_engine('oracle+cx_oracle://scott:tiger@host:1521/?service_name=hr')

df = pd.read_sql('select * from table_name', engine)

One way to query an Oracle DB is with a function like this one:

import pandas as pd
import cx_Oracle

def query(sql: str) -> pd.DataFrame:
    try: 
        with cx_Oracle.connect(username, password, database, encoding='UTF-8') as connection:
            dataframe = pd.read_sql(sql, con=connection)
        return dataframe
    except cx_Oracle.Error as error: print(error)
    finally: print("Fetch end")

here, sql corresponds to the query you want to run. Since it´s a string it also supports line breaks in case you are reading the query from a .sql file eg:

"SELECT * FROM TABLE\nWHERE <condition>\nGROUP BY <COL_NAME>"

or anything you need... it could also be an f-string in case you are using variables.

This function returns a pandas dataframe with the results from the sql string you need. It also keeps the column names on the dataframe