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

Stream cypher (encrypt) with digital envelope in P... Generating. storing and keeping inventory of hundreds of terabyte large database backups is a challenge by itself which we do on daily basis. It's eve...
Python error: AttributeError: __exit__ I just had a 30 minutes worth of debugging on a stupid mistake I made. I wanted to check the free space on the target server before I actually start t...
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 (...