Skip to main content

Edit a table

Use this recipe to read, edit, and write back data in a Unity Catalog table using the Databricks SQL Connector.

Code snippet​

app.py
from functools import lru_cache
from databricks import sql
from databricks.sdk.core import Config
import pandas as pd

cfg = Config()

@lru_cache(maxsize=1)
def get_connection(http_path):
    return sql.connect(
        server_hostname=cfg.host,
        http_path=http_path,
        credentials_provider=lambda: cfg.authenticate,
    )

def read_table(table_name: str, conn) -> pd.DataFrame:
    with conn.cursor() as cursor:
        cursor.execute(f"SELECT * FROM {table_name}")
        return cursor.fetchall_arrow().to_pandas()

def insert_overwrite_table(table_name: str, df: pd.DataFrame, conn):
    with conn.cursor() as cursor:
        rows = list(df.itertuples(index=False))
        values = ",".join([f"({','.join(map(repr, row))})" for row in rows])
        cursor.execute(f"INSERT OVERWRITE {table_name} VALUES {values}")

http_path_input = "/sql/1.0/warehouses/xxxxxx"
table_name = "catalog.schema.table"
conn = get_connection(http_path_input)
df = read_table(table_name, conn)
# Edit the dataframe
insert_overwrite_table(table_name, df, conn)
info

This sample uses Pythons's lru_cache. Adapt the caching behavior to fit your specific use case.

Resources​

Permissions​

Your app service principal needs the following permissions:

  • MODIFY on the Unity Catalog table
  • CAN USE on the SQL warehouse

See Unity Catalog privileges and securable objects for more information.

Dependencies​

requirements.txt
databricks-sdk
databricks-sql-connector
pandas
dash