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

BitTornado library forked by me on GitHub I just forked and committed some patches to the BitTornado library on GitHub. https://github.com/charlesnagy/BitTornado Right now I only committ...
Python MySQLdb vs mysql-connector query performanc... There are a lot of python driver available for MySQL and two stand out the most. The one, traditionally everybody's choice, sort of industrial standar...
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.