Select to view content in your preferred language

cx_oracle question

642
3
Jump to solution
08-10-2023 08:01 AM
V1212
by
New Contributor II
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!

0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable

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. 

 

View solution in original post

3 Replies
BlakeTerhune
MVP Regular Contributor

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.

by Anonymous User
Not applicable

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. 

 

V1212
by
New Contributor II

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

 

0 Kudos