How to save sql table as pandas dataframe?

Question

I have been trying to extracting a sql table using cx_oracle and saving it as pandas dataframe using the following script:

import  cx_Oracle
import pandas as pd

id = 1234
connection = cx_Oracle.connect(user="user", password='pwd',dsn="dsn")
# Obtain a cursor
cursor = connection.cursor()
# Execute the query
query = """select * from table where id= {id}"""
my_sql =cursor.execute(query.format(id=id))
df_sql = pd.read_sql(my_sql, connection)

I am able to connect to the database but I am unable to save it as pandas dataframe. How do I do that? I get the following error :

  File "file/to/path.py", line 38, in file
    df_sql = pd.read_sql(my_sql, connection)
  File "C:\file/to/path\venv\lib\site-packages\pandas\io\sql.py", line 495, in read_sql
    return pandas_sql.read_query(
  File "File/to/path\venv\lib\site-packages\pandas\io\sql.py", line 1771, in read_query
    cursor = self.execute(*args)
  File "File/to/path\venv\lib\site-packages\pandas\io\sql.py", line 1737, in execute
    raise ex from exc
pandas.io.sql.DatabaseError: Execution failed on sql '<cx_Oracle.Cursor on <cx_Oracle.Connection to dsn>>': expecting string or bytes object

can you post the full stack trace? looks like a connection/query issue. — Umar.H
– Umar.H, Commented Jul 7, 2021 at 12:19
I have added it as an aswer (please comment in the answer, which one you used and which one worked) — CutePoison
– CutePoison, Commented Jul 7, 2021 at 12:38
Do not do query = """select * from table where id= {id}""" since this opens you to SQL injection security attacks and also affects performance and scalability. Use a bind variable, see stackoverflow.com/a/51790579/4799035 — Christopher Jones
– Christopher Jones, Commented Jul 9, 2021 at 4:46

CutePoison · Accepted Answer · 2021-07-07 12:38:18Z

2

The first argument to the pd.read_sql should be the query (if I'm not mistaken). You are parsing a cursor object. Try replace my_sql in pd.read_sql with query i.e

pd.read_sql(query.format(id=id))

or use the cursor object i.e

df = pd.DataFrame(my_sql.fetchall())

Note, fetchall() does only return the data i.e not the header, which can be obtained using cursor.description (see the SO answer here )

answered Jul 7, 2021 at 12:38

CutePoison

5,5448 gold badges44 silver badges98 bronze badges

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

1 Comment

disukumo Over a year ago

I used pd.read_sql(query.format(id=id)) and it worked! Thanks for the help

Collectives™ on Stack Overflow

How to save sql table as pandas dataframe?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related