Although there are always multiple ways to solve a single problem not always the most elegant is the best performant. Python gives a perfect tool to check primitives (or even a bit more complex) structure’s speed. This comes really handy when trying to figure out of a loop or a map is more effective. Or using itertools for example.
SQL statement preparation for IN type queries
One common question which comes up is how to make a query in python for MySQL where we have IN filters. For example:
|
1 |
SELECT * FROM my_table WHERE id in (1,2,3,4); |
This is trivial if you know the number of ids.
|
1 |
cur.execute("SELECT * FROM my_table WHERE id in (%s, %s, %s, %s)", (1,2,3,4)) |
But what if you don’t?
String formatting
You can use string formatting and pushing your structured list into the query.
|
1 |
cur.execute('SELECT * FROM my_table WHERE id IN (%s)' % ', '.join(map(str, ids))) |
But that’s far from an optimal solution. You haven’t checked your ids if that’s really a list if integers. You haven’t sanitised your input. Of course you could do this in your application logic but why do something manually which works perfectly in the MySQLDb modul.
Generate the query with placeholders
We will generate this query string in python and pass the proper arguments to execute.
|
1 |
"SELECT * FROM my_table WHERE id in (%s, %s, %s, %s)" |
Do to this we can any of the followings:
|
1 2 3 4 |
import itertools _q = ", ".join(list(map(lambda x: "%s", [1,2,3,4,5,6,7,8,9,10]))) _q = ", ".join(["%s"] * len([1,2,3,4,5,6,7,8,9,10])) _q = ", ".join(itertools.repeat("%s", len([1,2,3,4,5,6,7,8,9,10])) |
Which is the best?
This is when timeit comes to play.
|
1 2 3 4 5 6 |
>>> timeit.timeit('", ".join(["%s"] * len([1,2,3,4,5,6,7,8,9,10]))', number=1000000) 0.9124858340001083 >>> timeit.timeit('", ".join(list(map(lambda x: "%s", [1,2,3,4,5,6,7,8,9,10])))', number=1000000) 2.351792852001381 >>> timeit.timeit('", ".join(itertools.repeat("%s", len([1,2,3,4,5,6,7,8,9,10])))', number=1000000) 1.2630212270014454 |
Recent comments