3

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!!

APC
  • 144,005
  • 19
  • 170
  • 281
Rani
  • 81
  • 1
  • 7
  • `.read_sql()` has a connection parameter. [CX_Oracle - import data from Oracle to Pandas dataframe](https://stackoverflow.com/questions/35781580/cx-oracle-import-data-from-oracle-to-pandas-dataframe) shows it being used with a `cx_Oracle` connection. – wwii Mar 27 '20 at 13:52
  • There are other answers with different methods like `pd.DataFrame(cursor.fetchall())` - search with variations of `python pandas oracle sql ` – wwii Mar 27 '20 at 13:57
  • Does this answer your question? [python-pandas and databases like mysql](https://stackoverflow.com/questions/10065051/python-pandas-and-databases-like-mysql) - there is an answer there for `cx_Oracle`. – wwii Mar 27 '20 at 14:01
  • as wwii said - you can pass a query straight into the `read_sql` method - i.e `pd.read_sql("SELECT * ... ", con = conn)` – Umar.H Mar 27 '20 at 14:18
  • When I try to do that the program just hangs.. – Rani Mar 27 '20 at 14:52
  • How much data (how many rows? how many columns?) are you trying to load into the dataframe? Also, how long does that query take to run natively in a database, say through Oracle SQL Developer? How big is the source table? Frankly `SELECT * FROM data WHERE date like '%20%'` looks like a query which will have suck-y performance on a big table, especially if `date` column is in fact defined with a DATE datatype. – APC Mar 27 '20 at 15:18
  • It takes about 15 mins to run the query in an oracle sql developer, I am trying to write a script in python to lower the time. I will try to narrow down my query thanks! – Rani Mar 27 '20 at 15:39

1 Answers1

2

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.

rafaelreuber
  • 103
  • 7