CODEWITHSUNDEEP
pythonmysql
Shiva Krishna Bavandla
Shiva Krishna Bavandla

Reputation: 26728

How to select records with multiple ids from the MYSQL database

I am using python and MYSQL for fetching data , i know this is a basic question, but i am unable to find it.

I had a list of ids generated from some dictionary in python like example 

dic = {'1': u'', '3': u'', '2': u'', '4': u''}
lis_ids = dic.keys()

query = "SELECT * FROM File_upload where id IN %s" % [int(i) for i in a]

print query

Result

"SELECT * FROM File_upload where id IN [1, 3, 2, 4]"

The above result will display syntax error because actually it should be as below

"SELECT * FROM File_upload where id IN (1, 3, 2, 4)"

How we can convert the list in to tuple in python, because i am getting the ids from a list.

Edited Code

Actually this is the second scenario which i got

The dict i have is 

dic = {'submit': u'', '1': u'', '3': u'', '2': u'', '4': u'', 'groups': {}}
lis_ids = dic.keys()

Actually we will get submit and groups also in to the list, but i want only integer keys as ids

so also i want to implement this functionality like if not len(key) > 1 take the key

So can anyone able to implement all this functionality in one line as below ?

Upvotes: 0

Views: 2283

Answers (2)

user1828720
user1828720

Reputation:

Try this:

dic = {'submit': u'', '1': u'', '3': u'', '2': u'', '4': u'', 'groups': {}}
list_ids = [elem for elem in dic.keys() if elem.isdigit()]

query = "SELECT * FROM File_upload where id IN (%s)" % ','.join(list_ids)

print query

Upvotes: 3

cdhowie
cdhowie

Reputation: 169143

Try this:

query = "SELECT * FROM File_upload where id IN %s" % str(tuple(int(i) for i in a))

(The str() call is required, unfortunately; you'll get a TypeError without it.)

If you want to just take the keys that are convertible to integers, try this:

def only_numbers(seq):
    for i in seq:
        try:
            yield int(i)
        except ValueError:
            pass

query = "SELECT * FROM File_upload where id IN %s" % str(tuple(only_numbers(dic.keys())))

It's not as simple, because you have to try to convert each key to an integer and handle errors by doing nothing, so we need a generator function to handle that part of it.

Upvotes: 2

Related Questions