CODEWITHSUNDEEP
python-3.xdictionarytuples
NAB0815
NAB0815

Reputation: 471

How to remove an extra comma in dictionary of tuples in python

I have a dictionary of tuple values some with 1-element tuples and some with multiple element tuples and I want to pass each tuple from the dictionary to an Oracle SQL query and perform some operation. But, unfortunately, 1-element tuples generate an additional comma at the end just to differentiate between a list and a tuple in python which is annoying.

My dictionary of tuples looks like below:

orderid_dict = 

`{foo : (121, 122, 123, 124),
  bar : (221,),
  zoo : (331, 332), 
  xyz : (441,442,443, 444, 445,446),
  abc : (551,)}`

`
dfs=[]
for x in orderid_dict:
     query =  """select  * from table where order_id in 
      """+str(orderid_dict[x])
 df=pd.read_sql_query(query, conn)
dfs.append(df)`

While I execute this loop, it throws an error saying:

DatabaseError: Execution failed on sql 'select * from table where order_id in (221,)':ORA-00936: missing expression

Is there a way I can strip of the last comma for all the 1-element tuples in the dictionary?

I tried using: 

`for x in orderid_dict:
     ",".join(str(x) for x in orderid_dict[x])`

and it doesn't give me an error and it doesn't work.

I'd really appreciate if some can help me with the problem.

Upvotes: 0

Views: 434

Answers (1)

user2722968
user2722968

Reputation: 16475

I'd strongly advice to not try to paste input-fragments directly into the SQL-string. Instead, use late binding:

inputs = (1,2,3,)
sql = """SELECT *
         FROM table
         WHERE order_id IN (%s)
      """ % (",".join(["?"]*len(inputs)), )
pd.read_sql_query(sql, conn, params=inputs)

What's happening here is that the SQL-string is expanded to ... IN (?, ?, ?, ?, ...), depending on the number of parameters. The actual parameters are then passed via the driver.

Not only does this work in all cases (except when inputs is empty, depending on your sql-engine WHERE ... IN () may be a syntax error), there is zero chance this will ever expand to an injection attack vector.

Upvotes: 1

Related Questions