Python timeit – when speed matters – SQL IN query with cursor.execute

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:

This is trivial if you know the number of ids.

But what if you don’t?

String formatting

You can use string formatting and pushing your structured list into the query.

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.

Do to this we can any of the followings:

Which is the best?

This is when timeit comes to play.

 

You might like these too

Sunburnt Solr spatial filter support I've added spatial filtering to the sunburnt library. The feature is now available with filter_spatial function. Example usage Currently this will...
Solr Stats component is available in sunburnt StatsComponent is now available with stats function in the suburnt Solr python client library. More info: http://wiki.apache.org/solr/StatsComponen...
Solr python interface – Sunburnt fork I forked tow (Toby White, gratitude for his great work) sunburnt repository from github because I needed range facet funcionality and the workaround (...

About charlesnagy

I'm out of many things mostly automation expert, database specialist, system engineer and software architect with passion towards data, searching it, analyze it, learn from it. I learn by experimenting and this blog is a result of these experiments and some other random thought I have time to time.
Bookmark the permalink.