Print a value in Python from oracle database

Question

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

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

Barbaros Özhan · Accepted Answer · 2020-02-15 15:27:11Z

1

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.

answered Feb 15, 2020 at 15:27

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

loginmind · Accepted Answer · 2020-02-15 13:49:10Z

0

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)

answered Feb 15, 2020 at 13:49

loginmind

6036 silver badges11 bronze badges

Collectives™ on Stack Overflow

Print a value in Python from oracle database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related