Python data loading with Supabase (original) (raw)

Python data loading with Supabase

Python is an excellent choice when it comes to data science. With a wide selection of libraries and built-in analytics tools, you can crunch data with ease, analyze even the most complex datasets, and visualize your results in beautiful charts and graphs.

Supabase is backend-as-a-service built on top of PostgreSQL. It's an excellent choice for building modern data-intensive apps and tooling.

Thanks to our incredible community, Supabase now has a powerful and open source Python SDK. With Supabase and Python, you can automate tasks such as CRUD operations with only a few lines of code. This guide will first create a simple schema in Supabase, then we'll use the Supabase Python SDK to show how you can load sample data.

Before we dive in, let's look at some prerequisites you'll need:

Python version > 3.7
- The SDK only supports version > 3.7. You can download a supported Python version from here.
Python virtual environment
- This is optional, but it will avoid issues of package dependencies and version conflicts. You can find the steps to create a virtual environment here. We will be using PyCharm to harness its venv creation capabilities.
Faker python package
- We will be using the faker-commerce package from the Faker library in Python to generate realistic sample data.

Supabase is built for developers, and you can get started for free using your existing Github account. Once your Supabase account is set up, you will access the Supabase dashboard. From here, go to All Project > New Project.

Give your project a name and set the database password. You can also choose the region and adjust the pricing plan based on the requirements of your project.

Your project will spin up within 2 minutes.

In this example, we'll be creating 2 tables in Supabase:

Vendor (fields are vendor_name, vendor_location, and total_employees)
Product (vendor_id as FK, product_name, price, and total orders)

The database schema will look like the following:

Let us now begin creating the tables. Once you create a project, you will need to go to Table Editor > New Table

Now, you can create a table according to the defined schema.

Click on Save to create your vendor table. Similarly, create the product table.

Before you click Save, you need to set up the foreign key relationship between the Product and Vendor table. To do this, select the button next to “vendor_id”

Select the vendor_id primary key from the “Vendor” table.

Click on Save and you are good to go. You should now see the 2 tables under Table Editor.

Once you have set up the tables and installed the prerequisites, you can now start playing around with the Python SDK. To install the SDK, run the following command:

pip3 install supabase

Ensure that you are running this inside your python virtual environment. This will take a few minutes to complete.

The SDK authentication uses API keys pointing to a project URL. To find out your project URL and APIs, go to Settings > API.

API credentials and project URL can be stored in environment variables. Setting the environment variables in bash/zsh is very simple. All we need to do is run:

`
_10

export =

So for our example we will set them up like this:

`
_10

export SUPABASE_URL=< URL>>

_10

export SUPABASE_KEY=< anon public>>

_10

export SUPABASE_SECRET_KEY=< service_role secret>>

Here is a snippet of the code we will be using to insert random data into our tables:

`
_50

import os

_50

import json

_50

from dotenv import load_dotenv

_50

from supabase import create_client, Client

_50

from faker import Faker

_50

import faker_commerce

_50

def add_entries_to_vendor_table(supabase, vendor_count):

_50

fake = Faker()

_50

foreign_key_list = []

_50

fake.add_provider(faker_commerce.Provider)

_50

main_list = []

_50

for i in range(vendor_count):

_50

value = {'vendor_name': fake.company(), 'total_employees': fake.random_int(40, 169),

_50

'vendor_location': fake.country()}

_50

main_list.append(value)

_50

data = supabase.table('Vendor').insert(main_list).execute()

_50

data_json = json.loads(data.json())

_50

data_entries = data_json['data']

_50

for i in range(len(data_entries)):

_50

foreign_key_list.append(int(data_entries[i]['vendor_id']))

_50

return foreign_key_list

_50

def add_entries_to_product_table(supabase, vendor_id):

_50

fake = Faker()

_50

fake.add_provider(faker_commerce.Provider)

_50

main_list = []

_50

iterator = fake.random_int(1, 15)

_50

for i in range(iterator):

_50

value = {'vendor_id': vendor_id, 'product_name': fake.ecommerce_name(),

_50

'inventory_count': fake.random_int(1, 100), 'price': fake.random_int(45, 100)}

_50

main_list.append(value)

_50

data = supabase.table('Product').insert(main_list).execute()

_50

def main():

_50

vendor_count = 10

_50

load_dotenv()

_50

url: str = os.environ.get("SUPABASE_URL")

_50

key: str = os.environ.get("SUPABASE_KEY")

_50

supabase: Client = create_client(url, key)

_50

fk_list = add_entries_to_vendor_table(supabase, vendor_count)

_50

for i in range(len(fk_list)):

_50

add_entries_to_product_table(supabase, fk_list[i])

_50

main()

To summarize what we have done using this code snippet:

We have inserted 10 random vendors to the table.
For each of the 10 vendors, we have inserted a number of different products

Data can also be viewed directly from the Supabase dashboard. To do this, go to Table Editor > All tables

Note: In case you cannot see any of the data, you should hit the Refresh button.

With Python, data loading into Supabase is easy. It just takes a few easy steps to get started with the Python SDK and Supabase. In the next part of this blog series, we will learn how to visualize the data that we just loaded into Supabase using Metabase. Stay tuned!

If you have any questions please reach out via Twitter or join our Discord.