CODEWITHSUNDEEP
pythonoracle-database
Giancarlo Pagliaroli
Giancarlo Pagliaroli

Reputation: 57

Print a value in Python from oracle database

I have an issue when displaying a value in python retrieved from oracle table into CLOB field:

Oracle query:

SELECT EXTRACTVALUE(xmltype(t.xml), '/DCResponse/ResponseInfo/ApplicationId') 
  FROM table t 
 WHERE id = 2

Value displayed in Oracle Client

5701200

Python code

import cx_Oracle 
conn = cx_Oracle.Connection("user/pwd@localhost:1521/orcl")
cursor = conn.cursor()
cursor.execute("""SELECT EXTRACTVALUE(xmltype(t.xml),'/DCResponse/ResponseInfo/ApplicationId') FROM table t where id = 2""")
for row in cursor:
print(row)

Python Console: Nothing is displayed!!! I want to show:5701200

Please Help. Best Regards Giancarlo

Upvotes: 2

Views: 2715

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

There are only a few issues with your code :

  • Replace cx_Oracle.Connection with cx_Oracle.connect
  • Be careful about the indentation related to the print(row)
  • Triple double-quotes, within the SELECT statement, are redundant, replace them with Single double-quotes

  • Prefer Using print(row[0]) in order to return the desired number rather than a tuple printed.

    import cx_Oracle 
conn = cx_Oracle.connect('user/pwd@localhost:1521/orcl')
cursor = conn.cursor()

query  = "SELECT EXTRACTVALUE(xmltype(t.xml),'/DCResponse/ResponseInfo/ApplicationId')"
query += "  FROM tab t "
query += " WHERE t.ID = 2 "

cursor.execute( query )
for row in cursor:
    print(row[0])

Assigning a query to a variable not required, as stated in my case, but preferable to use in order to display the long SELECT statement decently.

Upvotes: 1

loginmind
loginmind

Reputation: 603

If you want to iterate over result, use this one:

for row in cursor.execute("sql_query")
    print(row)

or you can fetch each row like this:

cursor = conn.cursor()
cursor.execute("sql_query")
while True:
    row = cursor.fetchone()
    print(row)

Upvotes: 0

Related Questions