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, Jul 07 '21 at 12:19
I have added it as an aswer (please comment in the answer, which one you used and which one worked) — CutePoison, Jul 07 '21 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 https://stackoverflow.com/a/51790579/4799035 — Christopher Jones, Jul 09 '21 at 04:46

score 2 · Accepted Answer · answered Jul 07 '21 at 12:38

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 07 '21 at 12:38

CutePoison

4,679
5
28
63

I used ```pd.read_sql(query.format(id=id))``` and it worked! Thanks for the help – disukumo Jul 07 '21 at 12:46

How to save sql table as pandas dataframe?

1 Answers1