SQL query format-CodePudding

I have a list of string that I need to pass to an sql query.

        listofinput = []
        for i in input:
         listofinput.append(i)
        if(len(listofinput)>1):
         listofinput = format(tuple(listofinput))

sql_query = f"""SELECT *  FROM countries
                                where
                                name in {listofinput};
                                """

This works when I have a list, but in case of just one value it fails.

as listofinput = ['USA'] for one value 
but listofinput ('USA', 'Germany') for multiple

also I need to do this for thousands of input, what is the best optimized way to achieve the same. name in my table countries is an indexed column

CodePudding user response：

Change if(len(listofinput)>1): to if(len(listofinput)>=1): This might work.

CodePudding user response：

Remove condition if(len(listofinput)>1) .

Because if you don't convert to tuple your query should be like this:

... where name in ['USA']

... where name in []

and in [...] not acceptable in SQL, only in (...) is acceptable.

You can remove format() too:

listofinput = tuple(listofinput)

Final Code:

listofinput = []
for i in input:
  listofinput.append(i)
listofinput = tuple(listofinput)

sql_query = f"""SELECT *  FROM countries
                WHERE
                name IN {listofinput};
                """

CodePudding user response：

Yes the tuple with one element will required a "," To circumvent your problem, maybe you can use string instead by just changing your code to the below:

listofinput = []
for i in input:
   listofinput.append(i)
if(len(listofinput)>1):
   listofinput = format(tuple(listofinput))
else:
   listofinput='(' listofinput[0] ')'

CodePudding user response：

You can just convert to tuple and then if the second last character is a coma, remove it.

listofinput = format(tuple(input))

if listofinput[-2] == ",":
    listofinput = f"{listofinput[:-2]})"

sql_query = f"""SELECT *  FROM countries
where name in {listofinput};"""