User input variables in cx_Oracle?

Question

I use cx_Oracle to access our database. I would like the user to be able to enter a station identifier, for example:

stationID = (regardless of what the user enters when prompted)

cursor.execute('''select cruise, station, stratum from union_fscs_svsta where station=stationID order by cruise''') 

Because the statement must be a string, how can I include a user variable?

Answer 1

How to do it:

 id = raw_input("Enter the Station ID") query = "select foo from bar where station={station_id}" cursor.execute(query.format(station_id=id)) 

If someone enters a malicious sql string, it will be executed.

Instead of using python to format the string, let the database server handle it for you. Exactly how you do this depends on the database you are using. I think (?) This is correct for Oracle, but I cannot verify it. Some databases use different characters (for example ? Instead of %s in the case of SQLite).

 id = raw_input("Enter the Station ID") query = "select foo from bar where station=%s" cursor.execute(query, [id]) 

Change Apparently, cx_Oracle uses "named" paramstyle by default (you can check this by looking at cx_Oracle.paramstyle .). In this case, you would do something like this:

 query = "select foo from bar where station=:station_id" cursor.execute(query, station_id=id)