CODEWITHSUNDEEP
oracle-databasepython-3.xpandasteradata
Ramsey
Ramsey

Reputation: 365

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

I am completely new to Python and pandas. I want to load a some tables and Sql Queries from Oracle and Teradata to pandas Dataframes and want to analyse them. I know, we have to create some connection strings to Oracle and Teradata in Pandas. Can you please suggest me them and also add the sample code to read both table and SQL query in that?

Thanks Inadvance

Upvotes: 1

Views: 10282

Answers (3)

norman123123
norman123123

Reputation: 987

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

Upvotes: 0

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

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)

Upvotes: 1

Zhong Dai
Zhong Dai

Reputation: 514

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.

Upvotes: 2

Related Questions