Python Forum
Retrieve oracle view containing clob to dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Retrieve oracle view containing clob to dataframe
#1
Hello Everyone,
I am trying to retrieve the data from oracle and write it into excel file using python dataframe. However my challenge is that I can't modify anything on db side and the query has total 5 columns out of which 2 columns are clob hence I am unable to write it in python data frame.
Any suggestions on how to approach retrieving query result containing multiple clob columns to dataframe?
Thank you in advance.
Here's my python code. It generates excel file only with column header. Column data is missing.
import pandas as pd

import cx_Oracle

dsn = cx_Oracle.makedsn(host='10.150.0.1',port=1515,service_name='ESO_SYS21_TESTAPP.ABC.COM')

try:   

    con = cx_Oracle.connect(user=username, password=password, dsn= dsn ,encoding = "UTF-8")#, nencoding = "UTF-8")

except cx_Oracle.DatabaseError as exception:     

    print("failed to connect to %s\n",dsn)     

    print(exception)     

my_sql_query=("select * from order where Order_id = 1234")

df = pd.read_sql(my_sql_query, con=con)

df.to_excel('test' + '.xlsx', encoding='utf8', index=False)
Reply
#2
(Jan-08-2021, 12:34 AM)jash Wrote: File "C:/user/Personal/Scripts/oracledb1.py", line 12, in <module> dsn = cx_Oracle.makedsn(host='xx.xx.xx.xx',port=xxxx,sid=None,service_name='test')

NameError: name 'xxxx' is not defined
Obviously you need to fill in the real values instead of the 'xx*'. For host you have to fill in the name or ip address of the database server. The port is usually 1521 unless configured differently. The service_name is the name of the database.
Reply
#3
@ibreeden Thanks. I was modifying the script to mask the ip address at that time I missed it. My bad.
I've updated my post with correct code. The code generates only the excel file with column header only. Please suggest.
Reply
#4
No, you were right not to show the real data in your code. But I was looking in the error message:
(Jan-08-2021, 09:14 AM)ibreeden Wrote: NameError: name 'xxxx' is not defined
Now you deleted the error message. But if I remember correctly, the message showed an error on the process of connecting to the database. So please look at that part. Can you connect to the database with something else than Python (like sqlplus)? How do you do that? And are the connection details the same as in your Python program?
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020