Navigation

Azure Storage Table Operations with Python

Introduction

This sample demonstrates common operations for interacting with Azure Table Storage using the Python SDK. Azure Table Storage is a NoSQL key-attribute store that can be used for storing large amounts of unstructured and semi-structured data. It is a cost-effective service for cloud applications.

We will cover creating tables, inserting, querying, updating, and deleting entities, as well as performing batch operations.

Prerequisites

  • An Azure subscription. If you don't have one, create a free account.
  • An Azure Storage Account. You can create one via the Azure portal.
  • Python 3.7+ installed on your system.
  • The connection string for your Azure Storage Account. You can find this in the "Access keys" section of your storage account in the Azure portal.

Installation

To get started, install the necessary Azure Table Storage SDK for Python:

Installation Command 
pip install azure-data-tables

Create Table

This example shows how to create a new table in Azure Table Storage.

Python Code 

from azure.data.tables import TableServiceClient

# Replace with your actual connection string
CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
TABLE_NAME = "MySampleTable"

def create_table_if_not_exists(connection_string, table_name):
    try:
        table_service_client = TableServiceClient.from_connection_string(connection_string)
        table_client = table_service_client.create_table(table_name)
        print(f"Table '{table_name}' created successfully.")
        return table_client
    except Exception as e:
        # Handle cases where the table might already exist
        if "The table already exists" in str(e):
            print(f"Table '{table_name}' already exists.")
            return table_service_client.get_table_client(table_name)
        else:
            print(f"Error creating table: {e}")
            return None

if __name__ == "__main__":
    table_client = create_table_if_not_exists(CONNECTION_STRING, TABLE_NAME)
    if table_client:
        print(f"Connected to table: {table_client.table_name}")

Insert Entity

Learn how to insert a new entity into the table.

Python Code 

from azure.data.tables import TableServiceClient, EdmType

# Assume table_client is already created and available from the previous step

def insert_sample_entity(table_client):
    if not table_client:
        print("Table client is not available. Cannot insert entity.")
        return

    entity = {
        "PartitionKey": "Users",
        "RowKey": "user123",
        "Name": "Alice Wonderland",
        "Email": "alice@example.com",
        "Age": 30,
        "IsActive": True,
        "CreatedOn": "2023-10-27T10:00:00Z"
    }

    try:
        created_entity = table_client.create_entity(entity)
        print(f"Entity inserted successfully: {created_entity}")
    except Exception as e:
        print(f"Error inserting entity: {e}")

if __name__ == "__main__":
    # Assuming you have a table_client from the create_table_if_not_exists function
    # Replace with your actual connection string and table name
    CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
    TABLE_NAME = "MySampleTable"
    table_service_client = TableServiceClient.from_connection_string(CONNECTION_STRING)
    table_client = table_service_client.get_table_client(TABLE_NAME)
    
    insert_sample_entity(table_client)

Query Entities

Retrieve entities from the table with various filtering options.

Python Code 

from azure.data.tables import TableServiceClient, EdmType

# Assume table_client is already created and available

def query_entities(table_client):
    if not table_client:
        print("Table client is not available. Cannot query entities.")
        return

    print("\n--- All Entities ---")
    try:
        all_entities = list(table_client.list_entities())
        for entity in all_entities:
            print(entity)
    except Exception as e:
        print(f"Error querying all entities: {e}")

    print("\n--- Entities with PartitionKey 'Users' ---")
    try:
        # Using OData filter for PartitionKey
        entities_by_partition = list(table_client.list_entities(
            filter="PartitionKey eq 'Users'"
        ))
        for entity in entities_by_partition:
            print(entity)
    except Exception as e:
        print(f"Error querying by PartitionKey: {e}")

    print("\n--- Entities older than 25 ---")
    try:
        # Using OData filter with numeric comparison
        # Note: Storing date as string can be tricky for comparison.
        # For proper date filtering, use EdmType.DATE_TIME or datetime objects.
        # Let's query by Age for demonstration
        entities_by_age = list(table_client.list_entities(
            filter="Age ge 25"
        ))
        for entity in entities_by_age:
            print(entity)
    except Exception as e:
        print(f"Error querying by Age: {e}")

if __name__ == "__main__":
    # Assuming you have a table_client from the create_table_if_not_exists function
    # Replace with your actual connection string and table name
    CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
    TABLE_NAME = "MySampleTable"
    table_service_client = TableServiceClient.from_connection_string(CONNECTION_STRING)
    table_client = table_service_client.get_table_client(TABLE_NAME)
    
    # Ensure at least one entity exists before querying
    # insert_sample_entity(table_client) # Uncomment if you need to insert first
    query_entities(table_client)

Update Entity

Modify an existing entity in the table.

Python Code 

from azure.data.tables import TableServiceClient, UpdateMode

# Assume table_client is already created and available

def update_sample_entity(table_client):
    if not table_client:
        print("Table client is not available. Cannot update entity.")
        return

    entity_to_update = {
        "PartitionKey": "Users",
        "RowKey": "user123",
        "Email": "alice.wonderland@example.com",
        "Age": 31,
        "IsActive": False
    }

    try:
        # Use UpdateMode.REPLACE to replace the entire entity
        # Use UpdateMode.MERGE to update specific properties
        updated_entity = table_client.update_entity(entity_to_update, update_mode=UpdateMode.MERGE)
        print(f"Entity updated successfully: {updated_entity}")
    except Exception as e:
        print(f"Error updating entity: {e}")

if __name__ == "__main__":
    # Assuming you have a table_client from the create_table_if_not_exists function
    # Replace with your actual connection string and table name
    CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
    TABLE_NAME = "MySampleTable"
    table_service_client = TableServiceClient.from_connection_string(CONNECTION_STRING)
    table_client = table_service_client.get_table_client(TABLE_NAME)

    # Ensure the entity exists before updating
    # insert_sample_entity(table_client) # Uncomment if you need to insert first
    update_sample_entity(table_client)

Delete Entity

Remove an entity from the table.

Python Code 

from azure.data.tables import TableServiceClient

# Assume table_client is already created and available

def delete_sample_entity(table_client):
    if not table_client:
        print("Table client is not available. Cannot delete entity.")
        return

    partition_key = "Users"
    row_key = "user123"

    try:
        table_client.delete_entity(partition_key, row_key)
        print(f"Entity with PartitionKey '{partition_key}' and RowKey '{row_key}' deleted successfully.")
    except Exception as e:
        print(f"Error deleting entity: {e}")

if __name__ == "__main__":
    # Assuming you have a table_client from the create_table_if_not_exists function
    # Replace with your actual connection string and table name
    CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
    TABLE_NAME = "MySampleTable"
    table_service_client = TableServiceClient.from_connection_string(CONNECTION_STRING)
    table_client = table_service_client.get_table_client(TABLE_NAME)

    # Ensure the entity exists before deleting
    # insert_sample_entity(table_client) # Uncomment if you need to insert first
    delete_sample_entity(table_client)

Batch Operations

Perform multiple operations (insert, update, delete) in a single batch request for efficiency.

Python Code 

from azure.data.tables import TableServiceClient, UpdateMode, TableEntity

# Assume table_client is already created and available

def perform_batch_operations(table_client):
    if not table_client:
        print("Table client is not available. Cannot perform batch operations.")
        return

    entities_to_add = [
        {"PartitionKey": "Orders", "RowKey": "orderA", "Amount": 150.50, "Status": "Shipped"},
        {"PartitionKey": "Orders", "RowKey": "orderB", "Amount": 200.00, "Status": "Pending"}
    ]

    entity_to_update = {
        "PartitionKey": "Users",
        "RowKey": "user123",
        "Age": 32 # Example update
    }

    row_key_to_delete = "user123"

    batch_operations = []

    # Add insert operations
    for entity_data in entities_to_add:
        batch_operations.append(("create", entity_data))

    # Add update operation
    batch_operations.append(("update", entity_to_update, UpdateMode.MERGE))

    # Add delete operation
    # Deletions require the PartitionKey and RowKey
    batch_operations.append(("delete", {"PartitionKey": "Users", "RowKey": row_key_to_delete}))

    try:
        # The create_batch method expects a list of tuples: (operation_type, entity_data, [UpdateMode])
        # For delete, it's (operation_type, partition_key_row_key_dict)
        results = table_client.execute_batch(batch_operations)
        print("Batch operations executed successfully.")
        for result in results:
            print(result)
    except Exception as e:
        print(f"Error during batch operations: {e}")

if __name__ == "__main__":
    # Assuming you have a table_client from the create_table_if_not_exists function
    # Replace with your actual connection string and table name
    CONNECTION_STRING = "YOUR_AZURE_STORAGE_CONNECTION_STRING"
    TABLE_NAME = "MySampleTable"
    table_service_client = TableServiceClient.from_connection_string(CONNECTION_STRING)
    table_client = table_service_client.get_table_client(TABLE_NAME)

    # Ensure entities exist for update/delete, or just run batch
    # insert_sample_entity(table_client) # Ensure 'user123' exists if you plan to update/delete it
    
    perform_batch_operations(table_client)