How to convert SQL Oracle Database into a Pandas DataFrame?

Question

I am trying to get a Oracle SQL database into python so I can aggregate/analyze the data. Pandas would be really useful for this task. But anytime I try to use my code, it just hangs and does not output anything. I am not sure its because I am using the cx oracle package and then using the pandas package?

import cx_Oracle as cxo
import pandas as pd 
dsn=cxo.makedsn(
    'host.net',
    '1111',
    service_name='servicename'
)
conn=cxo.connect(
    user='Username',
    password='password',
    dsn=dsn)
c=conn.cursor()
a=c.execute("SELECT * FROM data WHERE date like '%20%'")
conn.close
df=pd.DataFrame(a)
head(df)

However when I use the code below, it prints out the data I am looking for. I need to convert this data into a panda data frame,

for row in c: print(row)
conn.close()

I am very new to python so any help will be really appreciated!!

Answer 1

To convert a cx_Oracle cursor to dataframe you can use de following code.

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM data WHERE date like '%20%'")
    from pandas import DataFrame
    df = DataFrame(cursor.fetchall())
    df.columns = [x[0] for x in cursor.description]
    print("I got %d lines " % len(df))

Note I'm using the cursor as context manager. So it will be closed automatically on the end of the block.