How to use Table storage in Python

This guide shows you how to perform common Azure Table storage scenarios in Python using the Azure Cosmos DB Table SDK for Python. The scenarios covered include creating and deleting a table, and inserting and querying entities.

While working through the scenarios in this tutorial, you may want to refer to the Python API reference.

What is Table storage

Azure Table storage stores large amounts of structured data. The service is a NoSQL datastore which accepts authenticated calls from inside and outside the Azure cloud. Azure tables are ideal for storing structured, non-relational data. Common uses of Table storage include:

  • Storing TBs of structured data capable of serving web scale applications
  • Storing datasets that don't require complex joins, foreign keys, or stored procedures and can be denormalized for fast access
  • Quickly querying data using a clustered index
  • Accessing data using the OData protocol and LINQ queries with WCF Data Service .NET Libraries

You can use Table storage to store and query huge sets of structured, non-relational data, and your tables will scale as demand increases.

Table storage concepts

Table storage contains the following components:

Tables storage component diagram

  • URL format: Code addresses tables in an account using this address format:
    http://<storage account>.table.core.windows.net/<table>

    You can address Azure tables directly using this address with the OData protocol. For more information, see OData.org.

  • Storage Account: All access to Azure Storage is done through a storage account. See Azure Storage Scalability and Performance Targets for details about storage account capacity.
  • Table: A table is a collection of entities. Tables don't enforce a schema on entities, which means a single table can contain entities that have different sets of properties. The number of tables that a storage account can contain is limited only by the storage account capacity limit.
  • Entity: An entity is a set of properties, similar to a database row. An entity can be up to 1MB in size.
  • Properties: A property is a name-value pair. Each entity can include up to 252 properties to store data. Each entity also has three system properties that specify a partition key, a row key, and a timestamp. Entities with the same partition key can be queried more quickly, and inserted/updated in atomic operations. An entity's row key is its unique identifier within a partition.

For details about naming tables and properties, see Understanding the Table Service Data Model.

Create an Azure Cosmos DB account

  1. In a new browser window, sign in to the Azure portal.
  2. Click New > Databases > Azure Cosmos DB.

    The Azure portal Databases pane

  3. In the New account page, enter the settings for the new Azure Cosmos DB account.

    Setting Suggested value Description
    ID Enter a unique name Enter a unique name to identify this Azure Cosmos DB account. Because documents.azure.com is appended to the ID that you provide to create your URI, use a unique but identifiable ID.

    The ID can contain only lowercase letters, numbers, and the hyphen (-) character, and it must contain 3 to 50 characters.
    API SQL (DocumentDB) The API determines the type of account to create. Azure Cosmos DB provides four APIs to suits the needs of your application: Gremlin (graph), MongoDB, SQL (DocumentDB), and Table (key-value), each which currently require a separate account.

    Select SQL (DocumentDB) because in this quickstart you are creating a document database that is queryable using SQL syntax.

    Learn more about the DocumentDB API
    Subscription Your subscription Select Azure subscription that you want to use for this Azure Cosmos DB account.
    Resource Group Enter the same unique name as provided above in ID Enter a new resource-group name for your account. For simplicity, you can use the same name as your ID.
    Location Select the region closest to your users Select geographic location in which to host your Azure Cosmos DB account. Use the location that's closest to your users to give them the fastest access to the data.
    Enable geo-redundancy Leave blank This creates a replicated version of your database in a second (paired) region. Leave this blank.
    Pin to dashboard Select Select this box so that your new database account is added to your portal dashboard for easy access.

    Then click Create.

    The new account blade for Azure Cosmos DB

  4. The account creation takes a few minutes. During account creation the portal displays the Deploying Azure Cosmos DB tile.

    The Azure portal Notifications pane

    Once the account is created, the Congratulations! Your Azure Cosmos DB account was created page is displayed.

Install the Azure Storage SDK for Python

Once you've created an Azure Cosmos DB account, your next step is to install the Azure Cosmos DB Table SDK for Python. For details on installing the SDK, refer to the README.rst file in the Table SDK for Python repository on GitHub.

Create a table

To work with the Azure Table service in Python, you must import the TableService module. Since you are working with Table entities, you also need the Entity class. Add this code near the top your Python file to import both:

from azure.cosmosdb.table import TableService, Entity

Create a TableService object, passing in your storage account name and account key. Replace myaccount and mykey with your account name and key, and call create_table to create the table in Azure Storage.

table_service = TableService(account_name='myaccount', account_key='mykey')

table_service.create_table('tasktable')

Add an entity to a table

To add an entity, you first create an object that represents your entity, then pass the object to the TableService.insert_entity method. The entity object can be a dictionary or an object of type Entity, and defines your entity's property names and values. Every entity must include the required PartitionKey and RowKey properties, in addition to any other properties you define for the entity.

This example creates a dictionary object representing an entity, then passes it to the insert_entity method to add it to the table:

task = {'PartitionKey': 'tasksSeattle', 'RowKey': '001', 'description' : 'Take out the trash', 'priority' : 200}
table_service.insert_entity('tasktable', task)

This example creates an Entity object, then passes it to the insert_entity method to add it to the table:

task = Entity()
task.PartitionKey = 'tasksSeattle'
task.RowKey = '002'
task.description = 'Wash the car'
task.priority = 100
table_service.insert_entity('tasktable', task)

PartitionKey and RowKey

You must specify both a PartitionKey and a RowKey property for every entity. These are the unique identifiers of your entities, as together they form the primary key of an entity. You can query using these values much faster than you can query any other entity properties because only these properties are indexed.

The Table service uses PartitionKey to intelligently distribute table entities across storage nodes. Entities that have the same PartitionKey are stored on the same node. RowKey is the unique ID of the entity within the partition it belongs to.

Update an entity

To update all of an entity's property values, call the update_entity method. This example shows how to replace an existing entity with an updated version:

task = {'PartitionKey': 'tasksSeattle', 'RowKey': '001', 'description' : 'Take out the garbage', 'priority' : 250}
table_service.update_entity('tasktable', task)

If the entity that is being updated doesn't already exist, then the update operation will fail. If you want to store an entity whether it exists or not, use insert_or_replace_entity. In the following example, the first call will replace the existing entity. The second call will insert a new entity, since no entity with the specified PartitionKey and RowKey exists in the table.

# Replace the entity created earlier
task = {'PartitionKey': 'tasksSeattle', 'RowKey': '001', 'description' : 'Take out the garbage again', 'priority' : 250}
table_service.insert_or_replace_entity('tasktable', task)

# Insert a new entity
task = {'PartitionKey': 'tasksSeattle', 'RowKey': '003', 'description' : 'Buy detergent', 'priority' : 300}
table_service.insert_or_replace_entity('tasktable', task)

Tip

The update_entity method replaces all properties and values of an existing entity, which you can also use to remove properties from an existing entity. You can use the merge_entity method to update an existing entity with new or modified property values without completely replacing the entity.

Modify multiple entities

To ensure the atomic processing of a request by the Table service, you can submit multiple operations together in a batch. First, use the TableBatch class to add multiple operations to a single batch. Next, call TableService.commit_batch to submit the operations in an atomic operation. All entities to be modified in batch must be in the same partition.

This example adds two entities together in a batch:

from azure.cosmosdb.table import TableBatch
batch = TableBatch()
task004 = {'PartitionKey': 'tasksSeattle', 'RowKey': '004', 'description' : 'Go grocery shopping', 'priority' : 400}
task005 = {'PartitionKey': 'tasksSeattle', 'RowKey': '005', 'description' : 'Clean the bathroom', 'priority' : 100}
batch.insert_entity(task004)
batch.insert_entity(task005)
table_service.commit_batch('tasktable', batch)

Batches can also be used with the context manager syntax:

task006 = {'PartitionKey': 'tasksSeattle', 'RowKey': '006', 'description' : 'Go grocery shopping', 'priority' : 400}
task007 = {'PartitionKey': 'tasksSeattle', 'RowKey': '007', 'description' : 'Clean the bathroom', 'priority' : 100}

with table_service.batch('tasktable') as batch:
    batch.insert_entity(task006)
    batch.insert_entity(task007)

Query for an entity

To query for an entity in a table, pass its PartitionKey and RowKey to the TableService.get_entity method.

task = table_service.get_entity('tasktable', 'tasksSeattle', '001')
print(task.description)
print(task.priority)

Query a set of entities

You can query for a set of entities by supplying a filter string with the filter parameter. This example finds all tasks in Seattle by applying a filter on PartitionKey:

tasks = table_service.query_entities('tasktable', filter="PartitionKey eq 'tasksSeattle'")
for task in tasks:
    print(task.description)
    print(task.priority)

Query a subset of entity properties

You can also restrict which properties are returned for each entity in a query. This technique, called projection, reduces bandwidth and can improve query performance, especially for large entities or result sets. Use the select parameter and pass the names of the properties you want returned to the client.

The query in the following code returns only the descriptions of entities in the table.

Note

The following snippet works only against the Azure Storage. It is not supported by the storage emulator.

tasks = table_service.query_entities('tasktable', filter="PartitionKey eq 'tasksSeattle'", select='description')
for task in tasks:
    print(task.description)

Delete an entity

Delete an entity by passing its PartitionKey and RowKey to the delete_entity method.

table_service.delete_entity('tasktable', 'tasksSeattle', '001')

Delete a table

If you no longer need a table or any of the entities within it, call the delete_table method to permanently delete the table from Azure Storage.

table_service.delete_table('tasktable')

Next steps