Member-only story
Python to SQL — UPSERT Safely, Easily and Fast
Lightning-fast insert and/or update with Python
When you upsert data into a table, you update records that already exist and insert new ones. After reading this article, you’ll be able to connect your Python application to a database and upsert data lightning fast. We’ll go through a few practical examples that demonstrate various ways to do this. They are all geared towards handling large sets of data; focusing on high speed and data validity. Let’s code!
P.S. We’ll use SQL Server for this example but the techniques used are applicable to a wide range of databases.
1. Setup and Preparations
We have a website that sells various musical articles. Every hour we receive a file from all of our suppliers with their current inventory; all of the articles they sell plus information about stock and prices.
In this example, we’ll focus on the file of one supplier. Our goal is to upsert; to insert all new articles and update ones that already exist. Maybe they have a new stock value or a different price. As a bonus, we’ll check out how we can delete items that get taken out of stock.
1.1 Database prep
Let’s first create a table in the database that we can upsert into.
As you can see, our little webshop is not selling a lot of things. That’s okay, though, it’ll keep this article nice and concise. The most important thing to notice is the SKU column. This is the stock-keeping unit; a number that is unique for every product.
1.2 Python — loading data
Let’s say our suppliers send us new CSV-files with their article updates. We have written a Python program that loads the file as soon as it detects a new one. Now the goal is to load the CSV and then upsert it into the database. Keep in mind that it is not required to read your data from a CSV; any data structure will do. First, we’ll load the CSV:
