SQL/Python - Adding list (with multiple entries) to SQL String

Question

I have a list in Python i.e:

['E/123', 'E/145']

I want to add this to a SQL statement that I made:

WHERE GrantInformation.GrantRefNumber LIKE 'E/123' 
OR GrantInformation.GrantRefNumber LIKE 'E/145'

The code I have is:

items = []
i = 0
while 1:
 i += 1
 item = input('Enter item %d: '%i)
 if item == '':
 break
 items.append(item)
 print(items)
refList = " OR GrantInformation.GrantRefNumber LIKE ".join(items)

The problem is, when I insert that String into my SQL it is a String so it is looking for WHERE GrantInformation.GrantRefNumber Like 'ES/P004355/1 OR GrantInformation.GrantRefNumber LIKE ES/P001452/1'

Which obviously returns nothing as 'ES/P004355/1 OR GrantInformation.GrantRefNumber LIKE ES/P001452/1' does not appear in the field.

How do i do it so the ' GrantInformation.GrantRefNumber LIKE ' is not a String?

Thank you

Answer 1

The preferred way to do this, is to use a ORM like SQLAlchemy, which does the query construction for you and you dont have to make the string concentrations yourself.

join(), adds the string between all the items in the array, that is passed as argument. You would need to add the condition into the array as well:

>>> items = ['A', 'B']
>>> " OR ".join(["GrantInformation.GrantRefNumber LIKE '%s'" % num for num in items])
"GrantInformation.GrantRefNumber LIKE 'A' OR GrantInformation.GrantRefNumber LIKE 'B'"

• Nicholas: Hey Maurice, I will look at SQLAlchemy for my next project (thanks!). I am just testing the code now although will have to remove the quote marks around the string somehow as the SQL wont take them :)
• Nicholas: Hey, I dont suppose you know of a way to remove the quote strings from the string that your code produces? I tried finalList = refList[1:-1] but that just removed the first and last character (and not the quote strings)?
• Nicholas: Fixed it. Thank you very much :)