CODEWITHSUNDEEP
pythonvariablesdynamic
user3754136
user3754136

Reputation: 529

how to use variable as placeholder in python

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.

Upvotes: 0

Views: 8454

Answers (2)

ayubfkhan
ayubfkhan

Reputation: 1

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"

Upvotes: 0

Friendly King
Friendly King

Reputation: 2486

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).

Upvotes: 4

Related Questions