DF to Azure

Python module for fast upload of pandas DataFrame to Azure SQL Database using automatic created pipelines in Azure Data Factory.

Supported Python versions: 3.11, 3.12, and 3.13.

Introduction

The purpose of this project is to upload large datasets using Azure Data Factory combined with an Azure SQL Server. In steps the following process kicks off:

1. The data will be uploaded as a .csv file to Azure Blob storage.
2. A SQL table is prepared based on pandas DataFrame types, which will be converted to the corresponding SQLAlchemy types.
3. A pipeline is created in datafactory for uploading the .csv from the Blob storage into the SQL table.
4. The pipeline is triggered, so that the .csv file is bulk inserted into the SQL table.

How it works

Based on the following attributes, it is possible to bulk insert your dataframe into the SQL Database:

from df_to_azure import df_to_azure

df_to_azure(df=df, tablename="table_name", schema="schema", method="create")

1. df: dataframe you wish to export
2. tablename: desired name of the table
3. schema: desired sql schema
4. method: option for "create" "append" or "upsert"
5. id_field: id field of the table. Necessary if method is set to "upsert"

Important: the csv's are uploaded to a container called dftoazure, so create this in your storage account before using this module.

Upsert / create or append

It is possible to upsert the SQL table with (new) records, if present in the dataframe you want to upload. Based on the id_field, the SQL table is being checked on overlapping values. If there are new records, the "old" records will be updated in the SQL table. The new records will be uploaded and appended to the current SQL table.

Settings

The default authentication path is passwordless:

• Python Azure SDK clients use DefaultAzureCredential.
• Local development can use az login.
• Deployed Python workloads can use a managed identity.
• Data Factory linked services use the Data Factory managed identity by default.

You should not need SQL passwords, storage account keys, or storage connection strings for the default setup. When legacy values are present, the package uses a fixed fallback order instead of auth-mode environment variables.

Parquet

Since version 0.6.0, functionality for uploading dataframe to parquet is supported. simply add argument parquet=True to upload the dataframe to the Azure storage container parquet. The arguments tablename and schema will be used to create a folder structure. if parquet is set to True, the dataset will not be uploaded to a SQL database.

# Azure subscription and Data Factory settings
subscription_id=""
rg_name=""
rg_location="westeurope"
df_name=""

# Storage account used for temporary SQL upload parquet files and parquet=True uploads.
# Use this for passwordless storage auth. If you use a storage connection string instead,
# ls_blob_account_name is not required.
ls_blob_account_name=""

# Azure SQL Database
SQL_SERVER="<server-name>.database.windows.net"
SQL_DB=""

The lowercase names above are the original names and remain supported. Uppercase aliases are also accepted, for example SUBSCRIPTION_ID, RG_NAME, RG_LOCATION, DF_NAME, LS_BLOB_ACCOUNT_NAME, and LS_BLOB_ACCOUNT_KEY.

The authentication rule is the same for direct Python connections and the Data Factory linked services: explicit credentials win, and without them the package is passwordless. To go passwordless, simply do not set the password, key, and connection-string variables.

• Storage: AZURE_STORAGE_CONNECTION_STRING → LS_BLOB_ACCOUNT_NAME + LS_BLOB_ACCOUNT_KEY → LS_BLOB_ACCOUNT_NAME with DefaultAzureCredential (Python) / managed identity (ADF).
• SQL: SQL_USER + SQL_PW → passwordless: DefaultAzureCredential access token over ODBC (Python) / managed identity (ADF). The ADF SQL linked service uses the user-assigned managed identity referenced by DF_TO_AZURE_ADF_CREDENTIAL_NAME when set, otherwise the system-assigned managed identity.
• Azure management clients always use DefaultAzureCredential.

For a user-assigned managed identity on Data Factory, first create a credential in the Data Factory itself (Manage → Credentials) that references the identity, then set the name of that credential — not the identity's client ID or resource ID:

DF_TO_AZURE_ADF_CREDENTIAL_NAME=""

Azure permissions for passwordless auth

There are two identities involved in a normal SQL upload:

1. The identity running Python.
2. The managed identity of the Azure Data Factory that runs the copy pipeline.

For local development, sign in first:

az login

Grant the local developer identity:

• Azure permissions to create or update Data Factory pipelines, datasets, and linked services. Data Factory Contributor on the Data Factory is usually enough when create=False; resource group Contributor is needed when using create=True.
• Storage Blob Data Contributor on the storage account or the relevant containers.
• A contained Azure SQL user and database permissions for creating schemas, tables, and upsert procedures.

Example SQL permissions for the local developer or deployed Python host identity:

CREATE USER [user-or-managed-identity-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user-or-managed-identity-name];
ALTER ROLE db_datawriter ADD MEMBER [user-or-managed-identity-name];
ALTER ROLE db_ddladmin ADD MEMBER [user-or-managed-identity-name];

Grant the Data Factory managed identity:

• Storage Blob Data Reader on the storage account or the dftoazure container so ADF can read the staged parquet file.
• An Azure SQL contained user with permission to insert into the target and staging tables.
• EXECUTE permission when using method="upsert" because ADF runs the generated upsert stored procedure.

Example SQL permissions for the Data Factory system-assigned managed identity:

CREATE USER [your-data-factory-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-data-factory-name];
ALTER ROLE db_datawriter ADD MEMBER [your-data-factory-name];
GRANT EXECUTE TO [your-data-factory-name];

Azure SQL must have a Microsoft Entra admin configured before CREATE USER ... FROM EXTERNAL PROVIDER works.

For user-assigned managed identity on Data Factory, assign the identity to the factory, create a Data Factory credential for it, and set DF_TO_AZURE_ADF_CREDENTIAL_NAME to that credential name.

Passwordless SQL connections require a Microsoft ODBC Driver for SQL Server version that supports access-token authentication. Use the newest available ODBC Driver 18 where possible.

Cross-tenant databases

A Data Factory in a different subscription is fine with managed identity when it is still in the same tenant as the Azure SQL server. When the Data Factory and Azure SQL server are in different tenants, managed identity may not be a valid SQL principal in the target tenant. In that case, set SQL_USER and SQL_PW; the package will use those credentials for both direct Python SQL setup and the ADF SQL linked service. No extra auth-mode variables are required.

Maintained by Zypp:

• Melvin Folkers
• Erfan Nariman

Support:

For support on using this module, you can reach us at hello@zypp.io

Testing

The auth tests are unit tests and run without any Azure resources or environment variables:

pytest df_to_azure/tests/test_auth.py

The rest of the test suite are integration tests: they create real pipelines, blob containers, and SQL tables in Azure. To run them, point the environment variables from the Settings section at an Azure environment you own:

SUBSCRIPTION_ID=""
RG_NAME=""
RG_LOCATION="westeurope"
DF_NAME=""
LS_BLOB_ACCOUNT_NAME=""
SQL_SERVER="<server-name>.database.windows.net"
SQL_DB=""

Authenticate (for example with az login, see the permissions section above), then run:

pytest df_to_azure

Tip: put the variables in a local .env file (git-ignored) and run uv run --env-file .env pytest df_to_azure.