Python Forum
Cursor Variable inside Another Cursor . CX_ORacle
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Cursor Variable inside Another Cursor . CX_ORacle
#1
Hi all,

I have 3 cursors, want to pass values from first and second on third cursor query as below.



import cx_Oracle as oracledb
conn = oracledb.connect("/", mode=oracledb.SYSASM)
cursor_dgnames = conn.cursor()
cursor_dgnames.execute("select distinct name from myview1")
for v_dgnames in cursor_dgnames:
    print(str(v_dgnames[0]))
    v_dgname=str(v_dgnames[0])
    cursor_fgnmaes = conn.cursor()
    cursor_fgnmaes.execute("select distinct fg from myview2 where TYPE<>'XYZ'")
    for v_fgnames in cursor_fgnmaes:
        print(str(v_fgnames[0]))
        v_fgname=str(v_fgnames[0])
        cursormain = conn.cursor()
        cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=%s and d.F=%s")(v_dgnames,v_fgnames)
I tried different ways but none worked. Anyone could help on this please?
Reply
#2
(Apr-08-2022, 02:41 PM)paulo79 Wrote: I tried different ways but none worked
It is always difficult to judge what exactly did not work. Is the result not what you expected? Did you get an error message?

This does not seem to be correct:
cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=%s and d.F=%s")(v_dgnames,v_fgnames)
Placehoders in cx_Oracle are a colon and an identifier or a number, as far as I know. And the values should be supplied as a list or a dictionary. Like this:
cursormain.execute("select d.name from myview3 d, myview4 dg where d.N = dg.N and dg.name=:dg and d.F=:df", [v_dgnames,v_fgnames])
Please let me know if this helps.

For reasons of efficiency it would be better to create the three cursors before the for loop. And also note cursors should be closed after use.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Question Sqlite3 how to know when cursor.execute didn't return anything ? SpongeB0B 2 238 Dec-18-2022, 06:13 PM
Last Post: deanhystad
  Command error - cursor.executemany(comandoSQL,valoresInserir) TecInfo 2 272 Nov-18-2022, 01:57 PM
Last Post: TecInfo
  Cursor write 3rd file empty paulo79 3 1,074 Mar-10-2022, 02:51 PM
Last Post: DeaD_EyE
  cursor.execute: How to insert dynamic number in a string? stoeberhai 2 2,218 Mar-18-2021, 12:55 PM
Last Post: stoeberhai
  change mouse cursor (in Windows) mgroen 0 1,097 Nov-11-2020, 11:26 AM
Last Post: mgroen
  Correct syntax for a variable inside a quotes: MP4Box command JonnyDriller 2 2,092 Feb-02-2020, 01:22 AM
Last Post: JonnyDriller
  Installing cx_Oracle 64-bit on AIX Nash 0 1,414 Dec-10-2019, 12:20 AM
Last Post: Nash
  How to avoid slow cursor operations? sevensixtwo 0 1,400 Oct-11-2019, 02:23 PM
Last Post: sevensixtwo
  cx_Oracle.DatabaseError: ORA-12541: TNS:no listener ARV 2 8,771 Oct-03-2019, 12:53 PM
Last Post: ARV
  Trouble retrieving dictionary from mysql.connector cursor swechsler 2 2,268 Sep-17-2019, 05:21 PM
Last Post: swechsler

Forum Jump:

User Panel Messages

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