Displaying results from an Oracle query based on user input

Question

I am trying to query the records for a specific ID in an Oracle table based on what the user inputs.

Here is my code:

import cx_Oracle
con = cx_Oracle.connect('dbuser/dbpassword@oracle_host/service_ID')
cur = con.cursor()
id_number = raw_input('What is the ID Number?')
cur.execute('select id, info from oracle_table_name where id=:id_number')
for result in cur:
    print "test", result
cur.close()
con.close()

The following error pops up: cx_Oracle.DatabaseError: ORA-01008: not all variables bound

When I remove the user input and the variable substitution and run the query, everything works fine.

Radosław Roszkowiak · Accepted Answer · 2016-01-13 15:42:43Z

2

:id_number in your SQL is a parameter (variable). You need to provide its value. execute method accepts parameters as the second argument.

Example: query = "select * from some_table where col=:my_param" cursor.execute(query, {'my_param': 5})

Check the documentation at http://cx-oracle.readthedocs.org/en/latest/cursor.html#Cursor.execute

edited Jan 13, 2016 at 15:42

answered Jan 13, 2016 at 15:35

Radosław Roszkowiak

6,9613 gold badges17 silver badges27 bronze badges

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

2 Comments

Tiberius Over a year ago

For a set value for my_param, that worked great. The other part of the question was in regards to taking dynamic user input and using that as the my_param value to achieve the same results.

Radosław Roszkowiak Over a year ago

replace "5" of the example (in the parameter dictionary) here with your dynamic value, which is {'my_param': id_number}.

Tiberius · Accepted Answer · 2016-01-13 16:16:41Z

1

I assigned a name to the user_value:

user_value = raw_input('What is the ID Number?')

And then referenced it in the execute statement:

cur.execute(query, {'id': (user_value)})

Thanks to Radoslaw-Roszkowiak for the assist!!

answered Jan 13, 2016 at 16:16

Tiberius

1452 gold badges2 silver badges9 bronze badges

Collectives™ on Stack Overflow

Displaying results from an Oracle query based on user input

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related