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:

String formatting

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

Python

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.

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.

Categories

About Charles Nagy

Database specialist

Automation expert

System engineer

Software architect

“An expert is a man who has made all the mistakes which can be made, in a narrow field.” - Niels Bohr

Don't be afraid to fail, to test, to experiment. This is what teaches you things you cannot learn from books. Everything written, told, heard are things somebody already know. If you want to be better in something you have to do things that nobody did before and push those boundaries as much as you can.