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

Maurice Meyer · Accepted Answer · 2017-10-12 11:24:41Z

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'"

answered Oct 12, 2017 at 11:24

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nicholas Over a year ago

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 Over a year ago

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 Over a year ago

Fixed it. Thank you very much :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related