Part of: Automation and the command line

Python: a SharePoint list into Postgres or SQL Server

A tested Python script that copies a SharePoint list into Postgres or SQL Server with Microsoft Graph delta: paging, deltaLink storage, Retry-After and deletes.

Relational databasesAutomationDesktopCommand line

“Can you get the Projects list into Postgres? Just a quick Python script.” The first version took an afternoon. It used office365-rest-python-client, pulled every item, and wrote the lot into a table with DROP and INSERT. Six months on, the list is ten times the size and the nightly job takes most of the night. Some mornings it stops with a 429. Items people deleted in SharePoint are still in the table because nothing ever told the script they had gone, and the finance team has started asking why the totals do not match the list.

The fix is to read only what changed. Microsoft Graph has a delta function for list items that returns every item once, then just the changes since your last call, deletes included. This post gives you a complete script that uses it, run against Postgres 16 and SQL Server 2022 with simulated Graph responses, and links every Graph behaviour it relies on to Microsoft Learn. It ends with what the script still leaves to you, because a script that copies one list is a different job from one that keeps twenty lists correct for years.

Which Python library should read a SharePoint list?

Three libraries come up when you search, and all of them are current on PyPI.

office365-rest-python-client (3.3.0) is a community library maintained by Vadim Gremyachev. Its ClientContext talks to the SharePoint REST API and covers lists, files and site administration. For app-only access, SharePoint REST requires a certificate, and the older Azure ACS app secrets stopped working on 2 April 2026, which is why many scripts written before then now fail at sign-in. It suits a one-off export well, since it pages through items for you.

msgraph-sdk (1.65.0) is Microsoft’s generated SDK for Graph, installed with the instructions on Learn. It is async, it exposes the delta call as graph_client.sites.by_site_id(...).lists.by_list_id(...).items.delta.get(), and its retry handler already honours Retry-After. It brings a large dependency tree for one endpoint.

Plain httpx (0.28.1) with msal (1.39.0) for the token is what this post uses. Every request and every header is visible, so you can see exactly where paging, throttling and deletes are handled, and you can port the logic to the SDK later if you prefer it.

What the script needs before it runs

You need an app registration in Microsoft Entra ID with a certificate uploaded, and an application permission that lets it read the list. The listItem delta page names Sites.Read.All as its least-privileged application permission. If your admin would rather not grant read access to every site in the tenant, the Selected scopes let them consent Sites.Selected and then grant the app read access to one site, which is the route SPSync’s own setup takes for unattended runs.

The script also needs the site id and the list id. Get the site with GET /sites/{hostname}:/{server-relative-path}, for example /sites/contoso.sharepoint.com:/sites/projects, then list its lists with GET /sites/{site-id}/lists?$select=id,displayName and copy the id you want.

Everything secret comes from the environment: the tenant id, client id, the path to the PFX file and its password, the two ids and the Postgres connection string. Nothing secret is written into the file, so it can sit in git. MSAL Python takes the certificate as a client credential, and versions from 1.29 read a PFX file directly.

The whole script for Postgres

Save the script to a file and run it with uv. The block at the top is inline script metadata, so uv installs the pinned versions on the first run. Python 3.11 or later is enough.

Terminal window
uv run sync_list.py
# /// script
# requires-python = ">=3.11"
# dependencies = ["msal==1.39.0", "httpx==0.28.1", "psycopg[binary]==3.3.6"]
# ///
"""Copy one SharePoint list into Postgres, then keep it current with Graph delta.


Every setting comes from the environment, never from this file:
SP_TENANT_ID, SP_CLIENT_ID, SP_CERT_PFX, SP_CERT_PASSWORD, SP_SITE_ID, SP_LIST_ID, PG_DSN.
"""


import os
import time
from datetime import UTC, datetime


import httpx
import msal
import psycopg
from psycopg.types.json import Jsonb


GRAPH = "https://graph.microsoft.com/v1.0"
USER_AGENT = "NONISV|Contoso|list-sync/1.0"  # SharePoint's format for decorated traffic


DDL = """
CREATE TABLE IF NOT EXISTS sp_items (
    list_id    text NOT NULL,
    item_id    text NOT NULL,
    etag       text,
    modified   timestamptz,
    fields     jsonb,
    deleted_at timestamptz,
    PRIMARY KEY (list_id, item_id)
);
CREATE TABLE IF NOT EXISTS sp_sync_state (
    list_id    text PRIMARY KEY,
    delta_link text NOT NULL
);
"""


UPSERT = """
INSERT INTO sp_items (list_id, item_id, etag, modified, fields, deleted_at)
VALUES (%s, %s, %s, %s, %s, NULL)
ON CONFLICT (list_id, item_id) DO UPDATE SET etag = EXCLUDED.etag,
    modified = EXCLUDED.modified, fields = EXCLUDED.fields, deleted_at = NULL
"""




class MsalAuth(httpx.Auth):
    """Adds a Graph token to each request. MSAL serves it from its cache until it
    is close to expiry, so a long first load never runs on an expired token."""


    def __init__(self) -> None:
        self.app = msal.ConfidentialClientApplication(
            os.environ["SP_CLIENT_ID"],
            authority=f"https://login.microsoftonline.com/{os.environ['SP_TENANT_ID']}",
            client_credential={
                "private_key_pfx_path": os.environ["SP_CERT_PFX"],
                "passphrase": os.environ.get("SP_CERT_PASSWORD"),
            },
        )


    def auth_flow(self, request):
        result = self.app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
        if "access_token" not in result:
            raise RuntimeError(result.get("error_description", "no token"))
        request.headers["Authorization"] = f"Bearer {result['access_token']}"
        yield request




def get(graph: httpx.Client, url: str) -> httpx.Response:
    """GET that waits out 429 and 503 for as long as Retry-After asks."""
    for attempt in range(8):
        response = graph.get(url)
        if response.status_code not in (429, 503):
            return response
        retry_after = response.headers.get("Retry-After", "")
        time.sleep(int(retry_after) if retry_after.isdigit() else 2**attempt)
    response.raise_for_status()
    return response




class ResyncRequired(Exception):
    def __init__(self, location: str | None) -> None:
        self.location = location




def read_delta(graph: httpx.Client, url: str) -> tuple[list[dict], str]:
    """Follow @odata.nextLink to the last page, which carries @odata.deltaLink."""
    items: list[dict] = []
    while True:
        response = get(graph, url)
        if response.status_code == 410:  # token no longer usable
            raise ResyncRequired(response.headers.get("Location"))
        response.raise_for_status()
        page = response.json()
        items.extend(page["value"])
        if "@odata.nextLink" in page:
            url = page["@odata.nextLink"]
        else:
            return items, page["@odata.deltaLink"]




def is_deleted(item: dict) -> bool:
    """listItem delta marks a deleted item with a `deleted` facet; other Graph
    delta APIs use `@removed`. Accept either."""
    return "deleted" in item or "@removed" in item




def sync() -> None:
    site, list_id = os.environ["SP_SITE_ID"], os.environ["SP_LIST_ID"]
    list_url = f"{GRAPH}/sites/{site}/lists/{list_id}/items"
    headers = {"User-Agent": USER_AGENT}
    with (
        psycopg.connect(os.environ["PG_DSN"]) as db,
        httpx.Client(auth=MsalAuth(), headers=headers, timeout=60) as graph,
    ):
        db.execute(DDL)
        # One run per list at a time; the lock ends with the transaction.
        db.execute("SELECT pg_advisory_xact_lock(hashtext(%s))", (list_id,))
        row = db.execute(
            "SELECT delta_link FROM sp_sync_state WHERE list_id = %s", (list_id,)
        ).fetchone()
        full = row is None
        start = row[0] if row else f"{list_url}/delta?$expand=fields"
        try:
            items, delta_link = read_delta(graph, start)
        except ResyncRequired as gone:
            full = True
            items, delta_link = read_delta(
                graph, gone.location or f"{list_url}/delta?$expand=fields"
            )


        now = datetime.now(UTC)
        live: list[str] = []
        for item in items:  # later copies of an item overwrite earlier ones
            if "fields" not in item and not is_deleted(item):  # refetch by id
                response = get(graph, f"{list_url}/{item['id']}?$expand=fields")
                if response.status_code == 404:
                    item = {"id": item["id"], "deleted": {"state": "deleted"}}
                else:
                    item = response.raise_for_status().json()
            if is_deleted(item):
                db.execute(
                    "UPDATE sp_items SET deleted_at = %s WHERE list_id = %s"
                    " AND item_id = %s AND deleted_at IS NULL",
                    (now, list_id, item["id"]),
                )
                continue
            live.append(item["id"])
            db.execute(
                UPSERT,
                (
                    list_id,
                    item["id"],
                    item.get("eTag"),
                    item.get("lastModifiedDateTime"),
                    Jsonb(item["fields"]),
                ),
            )


        if full:  # a full read also shows which rows SharePoint no longer has
            db.execute(
                "UPDATE sp_items SET deleted_at = %s WHERE list_id = %s"
                " AND deleted_at IS NULL AND NOT (item_id = ANY(%s))",
                (now, list_id, live),
            )
        db.execute(
            "INSERT INTO sp_sync_state VALUES (%s, %s) ON CONFLICT (list_id)"
            " DO UPDATE SET delta_link = EXCLUDED.delta_link",
            (list_id, delta_link),
        )
    # Leaving the block commits the rows and the new deltaLink together.




if __name__ == "__main__":
    sync()

Run it once and it reads the whole list. Run it again from cron or a scheduled task and it reads only what changed since the last run.

How does Graph delta paging work for list items?

The first call to /items/delta has no token. Our post on Graph list item delta, tokens and missed deletes explains how those tokens behave in practice; this section sticks to what the script does with them. The listItem delta page says the service then returns pages of items, each ending in either an @odata.nextLink or an @odata.deltaLink, and that you keep calling the next link until a delta link appears. That delta link is the bookmark you store for the next run. Graph’s paging guidance adds one rule the script follows: request the whole URL you were given, and never pull the token out to build a request of your own. The $expand=fields from the first call travels inside those links, so later pages and later runs still carry the column values.

Where you store the delta link matters more than how you read it. The script writes it to sp_sync_state in the same Postgres transaction as the rows. If the run dies halfway, neither is committed and the next run starts again from the old link. Graph also warns that the same item can appear more than once in one feed, and that you should keep the last copy you see, which the upsert loop does by processing items in order.

Delta also costs less. Microsoft’s SharePoint throttling page charges a delta request with a token at 1 resource unit, against 2 resource units for each page of the first read, which has no token, so a nightly run on a quiet list costs a fraction of the full export.

Retry-After and 429 responses

When SharePoint decides an app is sending too much, Graph answers 429 with a Retry-After header in seconds. Graph’s throttling guidance is to wait exactly that long and retry, and to fall back to exponential backoff only when the header is missing. The SharePoint page explains why waiting less backfires: throttled requests still count against the app’s limits, so an early retry earns a longer wait. The get function waits out 429 and 503 for the time the header gives, up to eight attempts, then raises.

The same SharePoint page asks callers to decorate their traffic with a User-Agent of the form NONISV|CompanyName|AppName/Version, and says decorated traffic is prioritised. The script sets it on every request. Put your organisation’s name in place of Contoso.

How does the script see deleted items?

Graph’s delta overview describes removed objects as an id plus an @removed annotation. List items are reported differently. The response example on the listItem delta page shows a deleted item as its id with a deleted facet whose state is deleted, and SPSync’s own reader has seen that shape from SharePoint, with an empty fields object. A script that only looks for @removed treats every deleted list item as an update with no values. is_deleted checks for both, and the row stays in the table with deleted_at set, so last month’s report still adds up.

A stored token can also stop working. The listItem delta page says the service may answer 410 Gone with a Location header holding a fresh link, for example after a long gap between runs, and that you should then compare the full enumeration with your local copy. The script does that comparison: on a 410 it follows the new link as a full read, and once every live item has been seen it marks any row that was not returned as deleted.

One defensive step comes from SPSync’s own reader. If a delta row arrives without its fields, the script fetches that item by id instead of storing an empty row, and treats a 404 on that fetch as a delete.

Python SharePoint list to SQL Server: what changes

Swap psycopg for Microsoft’s mssql-python driver (1.16.0), which the Learn quickstart says needs no external dependencies on Windows and a few system libraries on Linux and macOS. The table, the upsert, the lock, the prune and the commit all change. The table stores fields as nvarchar(max) with an ISJSON check, and the upsert becomes a MERGE:

DDL = """
IF OBJECT_ID('dbo.sp_items') IS NULL CREATE TABLE dbo.sp_items (
    list_id    nvarchar(64)  NOT NULL,
    item_id    nvarchar(64)  NOT NULL,
    etag       nvarchar(200) NULL,
    modified   datetimeoffset NULL,
    fields     nvarchar(max) NULL CHECK (ISJSON(fields) = 1),
    deleted_at datetimeoffset NULL,
    PRIMARY KEY (list_id, item_id)
);
IF OBJECT_ID('dbo.sp_sync_state') IS NULL CREATE TABLE dbo.sp_sync_state (
    list_id    nvarchar(64)  PRIMARY KEY,
    delta_link nvarchar(max) NOT NULL
);
"""


UPSERT = """
MERGE dbo.sp_items WITH (HOLDLOCK) AS t
USING (SELECT ? AS list_id, ? AS item_id, ? AS etag, ? AS modified, ? AS fields) AS s
    ON t.list_id = s.list_id AND t.item_id = s.item_id
WHEN MATCHED THEN UPDATE SET etag = s.etag, modified = s.modified,
    fields = s.fields, deleted_at = NULL
WHEN NOT MATCHED THEN INSERT (list_id, item_id, etag, modified, fields)
    VALUES (s.list_id, s.item_id, s.etag, s.modified, s.fields);
"""

Inside sync(), connect with db = mssql_python.connect(os.environ["MSSQL_CONNECTION_STRING"]), run each statement on a cursor with ? placeholders, and pass json.dumps(item["fields"]) where Postgres took Jsonb. The advisory lock becomes EXEC sp_getapplock @Resource = ?, @LockMode = 'Exclusive', @LockOwner = 'Transaction'. SQL Server has no array parameter, so the prune after a full read sends the live ids as one JSON string and filters with item_id NOT IN (SELECT value FROM OPENJSON(?)). The driver does not commit for you, so wrap the body in try, call db.commit() at the end and db.rollback() on any exception, which keeps the rows and the delta link moving together.

How do you get a SharePoint list into a pandas DataFrame?

Read it from the copy, where deletes are already handled and no Graph call is made:

import os


import pandas as pd
import psycopg


with psycopg.connect(os.environ["PG_DSN"]) as db:
    rows = db.execute(
        "SELECT item_id, fields FROM sp_items WHERE list_id = %s AND deleted_at IS NULL",
        (os.environ["SP_LIST_ID"],),
    ).fetchall()
df = pd.json_normalize([{"item_id": item_id, **fields} for item_id, fields in rows])

With pandas 3.1, json_normalize gives one column per SharePoint field, typed from the JSON values. For a one-off look at a list without any database, call read_delta(graph, f"{list_url}/delta?$expand=fields") from the script and pass the fields of each item to json_normalize in the same way. That reads the whole list every time, so keep it for exploration.

What the script still leaves to you

The script keeps one list’s rows current. Look inside fields and the gaps show.

A lookup column arrives as ClientLookupId, a number that means something only next to a copy of the Clients list, so every lookup needs its target list synced too and a join written by hand. Person columns work the same way, as a lookup id into the site’s own user list. Multi-choice and multi-person columns arrive as JSON arrays that a report cannot group by until someone splits them out. Lists with many lookups add another constraint: SharePoint documents a list view lookup threshold of 12 join operations per query, so a list near it needs its column selection split across requests and merged by id. Our post on the lookup column threshold covers where that limit bites outside Python too.

Typed columns are the next job. A jsonb column is a safe place to start because a new column in SharePoint cannot break the load. The cost moves to the views you write on top, which need editing each time someone adds, renames or retypes a column, and nothing warns you when they drift.

Then there is trust. Delta tells you what SharePoint reports as changed. SPSync does not rely on it alone and runs a reconcile every seven days by default, re-listing every id and etag, soft-deleting rows SharePoint no longer serves and refetching rows whose etag has moved. A script needs the same pass, plus alerting when a run fails, before anyone should build a report on it.

Keeping the script, or moving to SPSync

Keep the script for one or two small lists, owned by a developer who will maintain it, feeding something that tolerates a missed night. It costs nothing and you can read every line.

SPSync is the alternative once the list count grows or the copy feeds finance. It runs the same delta reading with Retry-After honoured, then writes each list to a typed table keyed on sp_item_id. Lookups become joinable id columns, people resolve through one shared sp_users table, and multi-value columns become child tables. A new column appears on the next run, and a retyped one moves to a _v2 column instead of breaking the load. It is one way: SharePoint stays the system of record and SPSync never writes back. It copies list data and file metadata, not file contents, and SharePoint item permissions are not carried into the copy.

The Postgres guide walks through a first sync, and the Postgres and SQL Server destination pages show the schema each one gets. If the script you are replacing is PowerShell, SPSync against PnP PowerShell and custom scripts lists what each side owns, and what it costs to keep a SharePoint export script running goes through the upkeep job by job. The replacing Data Factory and flows use case covers switching the downstream reports over.

Compared with

Book a call

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment.