how to use variable as placeholder in python

Question

i need help with variables in python, where they will be taken during run time, if i give the actual table name in the select query it works but when i am trying variable it fails.

import cx_Oracle
import csv
import sys
tablename = sys.argv[1] # pasing it as input at runtime
host = 'server.com'
port = 7111
SERVICE_NAME = 'gtpts'
login = 'USER'
passwrd = 'password'
SID = 'server.com'
dsn = cx_Oracle.makedsn(host, port, SID).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login, passwrd, dsn)

cur = con.cursor()

cur.execute('select * from table') # direct table name works but variable doesnot
row = cur.fetchall()

Getting error as below:

[20020663]:20020663> python oracleconnect.py employee
Traceback (most recent call last):
  File "oracleconnect.py", line 16, in <module>
    cur.execute('select * from tablename')
cx_Oracle.DatabaseError: ORA-00911: invalid character

I could do the same by using $variable in perl do we have anything in python for same.

String formatting? See http://stackoverflow.com/questions/5082452/python-string-formatting-vs-format — NightShadeQueen, Jun 29 '15 at 13:54
You are running `select * from table` exactly as written, You need to use string formatting to replace the variables. Try `'select * from %s' %(table)`. — brandon.bell, Jun 29 '15 at 13:55

Friendly King · Accepted Answer · 2015-06-29T14:35:40.773

This uses the Oracle Bind Variable syntax, e.g.:

my_tablename = "SomeTableName"
cur = connection.cursor()
sql = "SELECT * FROM :tablename"
param = {"tablename": my_tablename}
cur.execute(sql, param)
row = cur.fetchall()

Note that the parameters here are passed as a dict and Oracle DB API will handle the escaping and quoting of variables, such as your table name. This method, as you can read about in the above link, is also much faster than doing the "hard parse" of the string.

Be careful not to use the string formatting operator %, like so:

cur.execute('SELECT * FROM %s' % my_tablename)

because it does not do any escaping or quoting, and it's prone to things like SQL injection, (not to mention it's less performant).

score 0 · Answer 2 · edited Feb 09 '17 at 16:23

Thanks for this post. this has helped me to develop parameter driven connection strings.

following parameters are passed when the python script is run, after that python logs me into the database and I can perform my inserts.

import cx_Oracle
import csv
import sys

host_args = sys.argv[1]
port_args = sys.argv[2]
SID_args = sys.argv[3]
login_args = sys.argv[4]
passwrd_args = sys.argv[5]
data_dir = sys.argv[6]

dsn = cx_Oracle.makedsn(host_args, port_args, SID_args).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login_args, passwrd_args, dsn)

The following command, I type on the command line to run this python script.

python /dera/ordsDataBranch/nsar/Load_NSARs_to_Oracle.py "myservername" "1521" "devrf1" "hr" "password2016" "/dera/ordsDataBranch/nsar/data"

how to use variable as placeholder in python

2 Answers2