Databases¶

Direct connections with traditional SQL databases aren’t supported. Here’s what you can do about it:

Put a web API in front of your database. You can either build something custom or use an existing solution, e.g., for PostgreSQL, there’s PostgREST. For a hosted solution, see Supabase below.
Use an SQLite database that you download from a network location. Note that this requires to add sqlite3 to requirements.txt.

Supabase¶

Supabase is a hosted Postgres database, which includes PostgREST. They offer a free plan and since the solution is open-source, you could also self-host it.

While Supabase offers Python packages like supabase and postgrest, they currently have a few issues when used with xlwings Lite. While these issues will likely be resolved in the future, for now, you can use the PostgREST interface directly via either requests or aiohttp (httpx is currently having an issue).

Attention

If you use aiohttp, make sure to list also ssl under requirements.txt.

Here is the sample code:

import requests
import aiohttp
import xlwings as xw
from xlwings import func, script


@script
def db_requests(book: xw.Book):
    key = "<SUPABASE_KEY>"  # When supported, should be handled via env var
    url = "https://<PROJECT>.supabase.co/rest/v1/<QUERY>"
    headers = {
        "apikey": key,
        "Authorization": f"Bearer {key}",
    }
    try:
        response = requests.get(url, headers=headers)
        if response.status_code == 200:
            data = response.json()
            print(data)
        else:
            print(f"Error: {response.status_code}")
    except Exception as e:
        print(f"Unexpected error: {e}")


@script
async def db_aiohttp(book: xw.Book):
    key = "<SUPABASE_KEY>"  # When supported, should be handled via env var
    url = "https://<PROJECT>.supabase.co/rest/v1/<QUERY>"
    headers = {
        "apikey": key,
        "Authorization": f"Bearer {key}",
    }
    async with aiohttp.ClientSession() as session:
        try:
            async with session.get(url, headers=headers) as response:
                if response.status == 200:
                    data = await response.json()
                    print(data)
                else:
                    print(f"Error: {response.status}")
        except Exception as e:
            print(f"Unexpected error: {e}")

For example, for <QUERY> you can use a table name to return all records of that table. For details about the query syntax, see PostgREST API.