cx_Oracle. How to view the query generated by cursor.execute() procedure at the debugging stage?

Question

I need the help of " protein" specialists, because soulless "chats" have been chasing me around in circles of their erroneous versions :D

Let's say I have the following code:

    import cx_Oracle

    # SQL query
    query = "SELECT * FROM users WHERE name = :name AND age = :age"
    
    # parameters for the query
    params = {'name': 'John Doe', 'age': 30}
    
    cursor.execute(f""{query}""", params)

Is there any way to see (in the log or print`) what kind of select was obtained after substituting my parameters into it? That is, to make sure that the final query is correct:

SELECT * FROM users WHERE name = 'John Doe' AND age = 30

and not some syntax errors like:

SELECT * FROM users WHERE name = ''John Doe'' AND age = 30

I don't see any syntax errors - it's just that the SQL-query doesn't produce any results when I run it with Python.

Answer 1

The query you are sending to the server is in fact SELECT * FROM users WHERE name = :name AND age = :age -- which is good. No string interpolation takes place as you imagine -- as that could introduce SQL injection errors! You can verify what is being sent to the server by examining the packet output generated by setting the environment variable PYO_DEBUG_PACKETS to any value before running your script.

I'm not sure if this is the reason for why you aren't getting any results -- but your code is not complete. You need to perform a fetch call to actually retrieve the data: rows = cursor.fetchall(). If that isn't the issue it could be due to a whole host of other reasons and you will have to determine which one. One common issue is the fact that data is uncommitted in another session!