python tuple in sql-CodePudding

I'm using tuple to set params in sql query but it converts str params to int. I need the params keep being a string.

From a function I create a query using .sql file. My .sql file is something like that:

update table t
set t.status = %s
where t.id in (%s)

My function to create a query from file is:

def create_query(where=[]):
   path = f'./file.sql'
   with open(path, 'r') as file:
      query = file.readlines()
      return ' '.join(query)

   return None

I call my function from this way passing the parameters:

status = 'CREATED'
ids = ('123', '1324', '124512')

params = list([ status, ids ])
query = create_query() % tuple(params)

I get a query like this:

update table t set t.status = CREATED where t.id in (22457, 22458, 22459)

I need at least status variable keep in string 'CREATED'

CodePudding user response：

One approach here is to generate the IN clause with the correct number of placeholders based on the number of values. For example:

status = ('CREATED',)
ids = ('123', '1324', '124512',)
params = status   ids
where = '(%s'   ',%s'*(len(ids) - 1)   ')'
sql = 'update some_table t set t.status = %s where t.id in '   where
cursor.execute(sql, params)

CodePudding user response：

You can use ? in the query so the parameters are replaced by sql and you don't have to worry about the quotes:

status = ('CREATED',)
ids = (123, 1324, 124512, 33333, 44444)
params = status   ids
sql = f'update t set status = ? where id in (?{", ?"*(len(ids)-1)});'
cursor.execute(sql, params)

asuming id is INT