Dramatically improve your database insert speed with a simple upgrade

4 levels of creating blazingly fast Python database connections

your python script after being upgraded with fast_executemany (Image by NASA on Unsplash)

Uploading data to your database is easy with Python. Load your data into a Pandas dataframe and use the dataframe.to_sql() method. But have you ever noticed that the insert takes a lot of time when working with large tables? We don’t have time to sit around, waiting for our queries to finish!

Image based on François Leblanc on leblancfg.com

With a few tweaks you can make inserts A LOT faster. Compare the write times of the brown (default to_sql() method) with the green bar (our goal). Also notice that the vertical axis is on a logarithmic scale! At the end of this article you’ll be able to perform lightning fast database operations. Ready? Lets go!

Goals and steps

We’ll work towards the superfast insertion method in two steps. The first part focusses on how to properly connect to our database, the second part will explore 4 ways to insert data in ascendingly fast order.

1. Connecting to our database