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

.read_sql() has a connection parameter. CX_Oracle - import data from Oracle to Pandas dataframe shows it being used with a cx_Oracle connection. — wwii
– wwii, Commented Mar 27, 2020 at 13:52
There are other answers with different methods like pd.DataFrame(cursor.fetchall()) - search with variations of python pandas oracle sql — wwii
– wwii, Commented Mar 27, 2020 at 13:57
Does this answer your question? python-pandas and databases like mysql - there is an answer there for cx_Oracle. — wwii
– wwii, Commented Mar 27, 2020 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
– Umar.H, Commented Mar 27, 2020 at 14:18

rafaelreuber · Accepted Answer · 2020-03-27 20:26:04Z

4

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.

answered Mar 27, 2020 at 20:26

rafaelreuber

1237 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert SQL Oracle Database into a Pandas DataFrame?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related