topic Re: cx_oracle question in Python Questions

cx_oracle question

V1212 — Thu, 10 Aug 2023 15:01:01 GMT

import cx_Oracle
import pandas as pd

pointer = connection.cursor()


# part 1
query = """
SELECT a.HM_ID, c.DI_ID
FROM wh.wc206ng_m_08 a, wh.wc26ng_m_03 b,wh.wc201mb c
WHERE a.p_mb_id = b.mb_id
 """

df = pd.read_sql(query, connection)


# Part 2
query2 = """
SELECT a.*, b.PODE,b.CODE
FROM wh.wc201di_06 b 
JOIN df a
ON a.DI_ID = b.DI_ID"""

df2 = pd.read_sql(query2,connection)
print(df2)


pointer.close()
connection.close()

print("Connection Closed")

I keep getting this error message for query 2:

ORA-00942: table or view does not exist

Query1 works on its own. I believe I'm getting this error message because Im using the results of query1 in query2. I dont know how to fix this issue.

Thanks for the help!

Re: cx_oracle question

BlakeTerhune — Thu, 10 Aug 2023 15:46:20 GMT

Where you are referring to df in query2, it's just a string. There's nothing to distinguish df in query 2 as a Pandas dataframe object in memory vs a table named "df" in your Oracle connection. You would need to refer to df in your query as a variable.

query2 = f""" SELECT a.*, b.PODE,b.CODE FROM wh.wc201di_06 b JOIN {df} a ON a.DI_ID = b.DI_ID"""

However, I don't use Pandas so I'm not sure how exactly you would go about this. I suspect you would be better off not mixing use of cx_Oracle and Pandas. Either query all the data from your Oracle tables into their own Pandas dataframes and do the joins and queries on the dataframes, or build out PL-SQL to do the query solely using cx_Oracle.

Re: cx_oracle question

Anonymous User — Fri, 11 Aug 2023 11:55:44 GMT

You could nest the first query into the second in sql fashion-

# Part 2 query2 = """ SELECT a.*, b.PODE, b.CODE FROM wh.wc201di_06 b JOIN (SELECT c.HM_ID, e.DI_ID FROM wh.wc206ng_m_08 c, wh.wc26ng_m_03 d, wh.wc201mb e WHERE c.p_mb_id = d.mb_id) a ON a.DI_ID = b.DI_ID""" df2 = pd.read_sql(query2,connection) print(df2)

As written, the sql engine would try to find whatever the df value is within the database tables set in the oracle connection. If the nesting statements doesn't work, try reading both queries into pandas and then merge like @BlakeTerhune mentions.

Re: cx_oracle question

V1212 — Wed, 16 Aug 2023 14:43:20 GMT

Thanks, I was able to use nesting and common table expression to get a working script.