How to query and sort list using odbc driver in python?

Question

I have this query statement:

def search(self, lists): //example input: lists = [1,2,3]
    if not self.connected:
        self.connect()
    for word in lists:
        query = self.cur.execute('SELECT InWords FROM Words WHERE Numeric IN (%s)' % (','.join('?'*len(lists))), lists).fetchall()
        result = " ".join([x[0] for x in query])
        return result

Im using odbc driver to connect in SQL server. In my database, I have 3 columns, Numeric column ex.1,2,3,4,5, InWords column one,two,three,four,five Now I want to sort the output according to the arrangement of the list entered to be like this: [one,two,three]. I observed that the query doesn't display the output according to the input instead whenever the query finds the word from the list match in database, it will display it. Example I inputed in no particular order the numbers in Numeric column (3,5,1,2,4) and the list variable contains [1,3,2], the sql statement display this output: (three,one,two) instead of [one,three,two].

Answer 1

I don't think you can create such SQL query that returns data in desired order. SQL works on sets, so order is irrevelant. It can be forced by very large UNION but I think you can make query that returns both Numeric, and InWords, store it in Python dict and then order it by this dict:

result = []
dct = {}
str_list = [str(s) for s in lists]
self.cur.execute('SELECT Numeric, InWords FROM Words WHERE Numeric IN (%s)' % (','.join(str_list)))
for row in self.cur.fetchall():
    dct[int(row[0])] = row[1]
for word in lists:
    try:
        result.append(dct[word])
    except KeyError:
        result.append(str(word))
return result

You can also make SQL query for each number. I think about such code:

result = []
for word in lists:
    v = word
    self.cur.execute('SELECT InWords FROM Words WHERE Numeric = %d' % (word))
    for txt in self.cur.fetchall():
        v = txt[0]
    result.append(v)
return result

PS I haven't tested it, it is only an example.