Quickstart: Build a API for Table app with Python SDK and Azure Cosmos DB
APPLIES TO:
Table
This quickstart shows how to access the Azure Cosmos DB API for Table from a Python application. The Azure Cosmos DB for Table is a schemaless data store allowing applications to store structured NoSQL data in the cloud. Because data is stored in a schemaless design, new properties (columns) are automatically added to the table when an object with a new attribute is added to the table. Python applications can access the Azure Cosmos DB for Table using the Azure Data Tables SDK for Python package.
Prerequisites
The sample application is written in Python3.6, though the principles apply to all Python3.6+ applications. You can use Visual Studio Code as an IDE.
If you don't have an Azure subscription, create a free account before you begin.
Sample application
The sample application for this tutorial may be cloned or downloaded from the repository https://github.com/Azure-Samples/msdocs-azure-tables-sdk-python-flask. Both a starter and completed app are included in the sample repository.
git clone https://github.com/Azure-Samples/msdocs-azure-tables-sdk-python-flask.git
The sample application uses weather data as an example to demonstrate the capabilities of the API for Table. Objects representing weather observations are stored and retrieved using the API for Table, including storing objects with additional properties to demonstrate the schemaless capabilities of the API for Table.
1 - Create an Azure Cosmos DB account
You first need to create an Azure Cosmos DB Tables API account that will contain the table(s) used in your application. This can be done using the Azure portal, Azure CLI, or Azure PowerShell.
Log in to the Azure portal and follow these steps to create an Azure Cosmos DB account.
2 - Create a table
Next, you need to create a table within your Azure Cosmos DB account for your application to use. Unlike a traditional database, you only need to specify the name of the table, not the properties (columns) in the table. As data is loaded into your table, the properties (columns) will be automatically created as needed.
In the Azure portal, complete the following steps to create a table inside your Azure Cosmos DB account.
3 - Get Azure Cosmos DB connection string
To access your table(s) in Azure Cosmos DB, your app will need the table connection string for the CosmosDB Storage account. The connection string can be retrieved using the Azure portal, Azure CLI or Azure PowerShell.
4 - Install the Azure Data Tables SDK for Python
After you've created an Azure Cosmos DB account, your next step is to install the Microsoft Azure Data Tables SDK for Python. For details on installing the SDK, refer to the README.md file in the Data Tables SDK for Python repository on GitHub.
Install the Azure Tables client library for Python with pip:
pip install azure-data-tables
5 - Configure the Table client in .env file
Copy your Azure Cosmos DB account connection string from the Azure portal, and create a TableServiceClient object using your copied connection string. Switch to folder 1-strater-app
or 2-completed-app
. Then, add the value of the corresponding environment variables in .env
file.
# Configuration Parameters
conn_str = "A connection string to an Azure Cosmos DB account."
table_name = "WeatherData"
project_root_path = "Project abs path"
The Azure SDK communicates with Azure using client objects to execute different operations against Azure. The TableServiceClient
object is the object used to communicate with the Azure Cosmos DB for Table. An application will typically have a single TableServiceClient
overall, and it will have a TableClient
per table.
self.conn_str = os.getenv("AZURE_CONNECTION_STRING")
self.table_service = TableServiceClient.from_connection_string(self.conn_str)
6 - Implement Azure Cosmos DB table operations
All Azure Cosmos DB table operations for the sample app are implemented in the TableServiceHelper
class located in helper file under the webapp directory. You will need to import the TableServiceClient
class at the top of this file to work with objects in the azure.data.tables
SDK package.
from azure.data.tables import TableServiceClient
At the start of the TableServiceHelper
class, create a constructor and add a member variable for the TableClient
object to allow the TableClient
object to be injected into the class.
def __init__(self, table_name=None, conn_str=None):
self.table_name = table_name if table_name else os.getenv("table_name")
self.conn_str = conn_str if conn_str else os.getenv("conn_str")
self.table_service = TableServiceClient.from_connection_string(self.conn_str)
self.table_client = self.table_service.get_table_client(self.table_name)
Filter rows returned from a table
To filter the rows returned from a table, you can pass an OData style filter string to the query_entities
method. For example, if you wanted to get all of the weather readings for Chicago between midnight July 1, 2021 and midnight July 2, 2021 (inclusive) you would pass in the following filter string.
PartitionKey eq 'Chicago' and RowKey ge '2021-07-01 12:00 AM' and RowKey le '2021-07-02 12:00 AM'
You can view related OData filter operators on the azure-data-tables website in the section Writing Filters.
When request.args parameter is passed to the query_entity
method in the TableServiceHelper
class, it creates a filter string for each non-null property value. It then creates a combined filter string by joining all of the values together with an "and" clause. This combined filter string is passed to the query_entities
method on the TableClient
object and only rows matching the filter string will be returned. You can use a similar method in your code to construct suitable filter strings as required by your application.
def query_entity(self, params):
filters = []
if params.get("partitionKey"):
filters.append("PartitionKey eq '{}'".format(params.get("partitionKey")))
if params.get("rowKeyDateStart") and params.get("rowKeyTimeStart"):
filters.append("RowKey ge '{} {}'".format(params.get("rowKeyDateStart"), params.get("rowKeyTimeStart")))
if params.get("rowKeyDateEnd") and params.get("rowKeyTimeEnd"):
filters.append("RowKey le '{} {}'".format(params.get("rowKeyDateEnd"), params.get("rowKeyTimeEnd")))
if params.get("minTemperature"):
filters.append("Temperature ge {}".format(params.get("minTemperature")))
if params.get("maxTemperature"):
filters.append("Temperature le {}".format(params.get("maxTemperature")))
if params.get("minPrecipitation"):
filters.append("Precipitation ge {}".format(params.get("minPrecipitation")))
if params.get("maxPrecipitation"):
filters.append("Precipitation le {}".format(params.get("maxPrecipitation")))
return list(self.table_client.query_entities(" and ".join(filters)))
Insert data using a TableEntity object
The simplest way to add data to a table is by using a TableEntity
object. In this example, data is mapped from an input model object to a TableEntity
object. The properties on the input object representing the weather station name and observation date/time are mapped to the PartitionKey
and RowKey
properties respectively which together form a unique key for the row in the table. Then the additional properties on the input model object are mapped to dictionary properties on the TableEntity object. Finally, the create_entity
method on the TableClient
object is used to insert data into the table.
Modify the insert_entity
function in the example application to contain the following code.
def insert_entity(self):
entity = self.deserialize()
return self.table_client.create_entity(entity)
@staticmethod
def deserialize():
params = {key: request.form.get(key) for key in request.form.keys()}
params["PartitionKey"] = params.pop("StationName")
params["RowKey"] = "{} {}".format(params.pop("ObservationDate"), params.pop("ObservationTime"))
return params
Upsert data using a TableEntity object
If you try to insert a row into a table with a partition key/row key combination that already exists in that table, you will receive an error. For this reason, it is often preferable to use the upsert_entity
instead of the create_entity
method when adding rows to a table. If the given partition key/row key combination already exists in the table, the upsert_entity
method will update the existing row. Otherwise, the row will be added to the table.
def upsert_entity(self):
entity = self.deserialize()
return self.table_client.upsert_entity(entity)
@staticmethod
def deserialize():
params = {key: request.form.get(key) for key in request.form.keys()}
params["PartitionKey"] = params.pop("StationName")
params["RowKey"] = "{} {}".format(params.pop("ObservationDate"), params.pop("ObservationTime"))
return params
Insert or upsert data with variable properties
One of the advantages of using the Azure Cosmos DB for Table is that if an object being loaded to a table contains any new properties then those properties are automatically added to the table and the values stored in Azure Cosmos DB. There is no need to run DDL statements like ALTER TABLE to add columns as in a traditional database.
This model gives your application flexibility when dealing with data sources that may add or modify what data needs to be captured over time or when different inputs provide different data to your application. In the sample application, we can simulate a weather station that sends not just the base weather data but also some additional values. When an object with these new properties is stored in the table for the first time, the corresponding properties (columns) will be automatically added to the table.
To insert or upsert such an object using the API for Table, map the properties of the expandable object into a TableEntity
object and use the create_entity
or upsert_entity
methods on the TableClient
object as appropriate.
In the sample application, the upsert_entity
function can also implement the function of insert or upsert data with variable properties
def insert_entity(self):
entity = self.deserialize()
return self.table_client.create_entity(entity)
def upsert_entity(self):
entity = self.deserialize()
return self.table_client.upsert_entity(entity)
@staticmethod
def deserialize():
params = {key: request.form.get(key) for key in request.form.keys()}
params["PartitionKey"] = params.pop("StationName")
params["RowKey"] = "{} {}".format(params.pop("ObservationDate"), params.pop("ObservationTime"))
return params
Update an entity
Entities can be updated by calling the update_entity
method on the TableClient
object.
In the sample app, this object is passed to the upsert_entity
method in the TableClient
class. It updates that entity object and uses the upsert_entity
method save the updates to the database.
def update_entity(self):
entity = self.update_deserialize()
return self.table_client.update_entity(entity)
@staticmethod
def update_deserialize():
params = {key: request.form.get(key) for key in request.form.keys()}
params["PartitionKey"] = params.pop("StationName")
params["RowKey"] = params.pop("ObservationDate")
return params
Remove an entity
To remove an entity from a table, call the delete_entity
method on the TableClient
object with the partition key and row key of the object.
def delete_entity(self):
partition_key = request.form.get("StationName")
row_key = request.form.get("ObservationDate")
return self.table_client.delete_entity(partition_key, row_key)
7 - Run the code
Run the sample application to interact with the Azure Cosmos DB for Table. The first time you run the application, there will be no data because the table is empty. Use any of the buttons at the top of application to add data to the table.
Selecting the Insert using Table Entity button opens a dialog allowing you to insert or upsert a new row using a TableEntity
object.
Selecting the Insert using Expandable Data button brings up a dialog that enables you to insert an object with custom properties, demonstrating how the Azure Cosmos DB for Table automatically adds properties (columns) to the table when needed. Use the Add Custom Field button to add one or more new properties and demonstrate this capability.
Use the Insert Sample Data button to load some sample data into your Azure Cosmos DB Table.
Select the Filter Results item in the top menu to be taken to the Filter Results page. On this page, fill out the filter criteria to demonstrate how a filter clause can be built and passed to the Azure Cosmos DB for Table.
Clean up resources
When you are finished with the sample application, you should remove all Azure resources related to this article from your Azure account. You can do this by deleting the resource group.
A resource group can be deleted using the Azure portal by doing the following.
Next steps
In this quickstart, you've learned how to create an Azure Cosmos DB account, create a table using the Data Explorer, and run an app. Now you can query your data using the API for Table.
Feedback
Submit and view feedback for