Skip to content

sql_database_writer

sqllocks_spindle.fabric.sql_database_writer

Write generated data to SQL databases via pyodbc.

Supports Fabric SQL Database, Fabric Warehouse, Azure SQL Database, and SQL Server with Entra ID (Azure AD) and SQL authentication.

Requires the fabric-sql extra::

pip install sqllocks-spindle[fabric-sql]

Classes

WriteResult dataclass

Result of a database write operation.

FabricSqlDatabaseWriter

Write generated data to SQL databases via pyodbc.

Supports
  • Fabric SQL Database (*.database.fabric.microsoft.com)
  • Fabric Warehouse (*.datawarehouse.fabric.microsoft.com)
  • Azure SQL Database (*.database.windows.net)
  • SQL Server (on-prem or VM)
Authentication methods
  • "cli" — Azure CLI (az login), default for local dev
  • "msi" — Managed Identity, for Fabric Notebooks / Azure VMs
  • "spn" — Service Principal, for CI/CD pipelines
  • "sql" — SQL authentication (username/password), for SQL Server on-prem

Usage::

writer = FabricSqlDatabaseWriter(
    connection_string="Driver={ODBC Driver 18 for SQL Server};Server=...",
    auth_method="cli",
)
writer.write(result, schema_name="dbo", mode="create_insert")
Methods:
test_connection()

Test the database connection. Returns True if successful.

write(result, schema_name='dbo', mode='create_insert', batch_size=5000, table_order=None, on_table_complete=None)

Write all tables from a GenerationResult or dict of DataFrames.

Parameters:

Name Type Description Default
result Any

A GenerationResult or dict[str, DataFrame].

 required
schema_name str

SQL schema prefix (default "dbo").

 'dbo'
mode str

Write mode — one of: - "create_insert": DROP + CREATE + INSERT (full reset) - "insert_only": INSERT into existing tables (no DDL) - "truncate_insert": TRUNCATE + INSERT (keep schema, reset data) - "append": INSERT without truncating (for Day 2 loads)

 'create_insert'
batch_size int

Rows per INSERT batch (default 5000).

 5000
table_order list[str] | None

Explicit table write order. If None, uses GenerationResult.generation_order or dict key order.

 None
on_table_complete Any | None

Optional callback (table_name, row_count) -> None invoked after each table is written. Use for progress reporting.

 None

Returns:

Type Description
WriteResult

WriteResult with per-table row counts and any errors.
write_all(tables, **kwargs)

Write all tables — protocol-compatible alias for write().

Conforms to the SpindleWriter protocol so FabricSqlDatabaseWriter can be used with MultiStoreWriter.

write_table(table_name, df, schema_name='dbo', mode='create_insert', batch_size=5000)

Write a single DataFrame to the database. Returns rows written.

create_ddl(result, schema_name='dbo', dialect='tsql')

Generate CREATE TABLE DDL as a string without executing.

Parameters:

Name Type Description Default
result Any

A GenerationResult or dict[str, DataFrame].

 required
schema_name str

SQL schema prefix.

 'dbo'
dialect str

SQL dialect for DDL generation.

 'tsql'

Returns:

Type Description
str

SQL DDL string for all tables.