CosmosDB and Python3: how to query?

Question

I am using CosmosDB (Azure documentDB) in my project, written in Python 3.

I have been looking for a while now, but I cannot find out how to query my table. I have seen some example code, but I do not see an example of how to query... all I can do is get all documents (not ideal when my DB is > 80GB).

The GitHub repo shows a very tiny set of operations for database and collections: https://github.com/Azure/azure-documentdb-python/blob/master/samples/CollectionManagement/Program.py

And the following SO post shows how to read all documents... but not how to perform querying such as "WHERE = X;"

I'd really appreciate it if someone can point me in the right direction, and possibly supply an example showing how to run queries.

Peter Pan · Accepted Answer · 2017-06-26 09:19:31Z

5

Based on my understanding, I think you want to know how to perform a SQL-like query using Python to retrieve documents on Azure CosmosDB of DocumentDB API, please refer to the code below from here.

A query is performed using SQL

# Query them in SQL
query = { 'query': 'SELECT * FROM server s' }    

options = {} 
options['enableCrossPartitionQuery'] = True
options['maxItemCount'] = 2

result_iterable = client.QueryDocuments(collection['_self'], query, options)
results = list(result_iterable);

print(results)

The above code is using the method QueryDocuments.

Any concern, please feel free to let me know.

Update: Combine with my sample code for the other SO thread you linked, as below.

from pydocumentdb import document_client

uri = 'https://ronyazrak.documents.azure.com:443/'
key = '<your-primary-key>'

client = document_client.DocumentClient(uri, {'masterKey': key})

db_id = 'test1'
db_query = "select * from r where r.id = '{0}'".format(db_id)
db = list(client.QueryDatabases(db_query))[0]
db_link = db['_self']

coll_id = 'test1'
coll_query = "select * from r where r.id = '{0}'".format(coll_id)
coll = list(client.QueryCollections(db_link, coll_query))[0]
coll_link = coll['_self']

query = { 'query': 'SELECT * FROM server s' }    
docs = client.QueryDocuments(coll_link, query)
print list(docs)

edited Jun 26, 2017 at 9:19

answered Jun 26, 2017 at 9:07

Peter Pan

24.2k4 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pookie Over a year ago

Thanks, can you please define collection for completeness? I have an existing collection, I am not creating one.

Peter Pan Over a year ago

@pookie Please see my update, it combined with my sample code for the other SO thread you linked. Is it you want?

pookie Over a year ago

Thanks, this worked with some modifications: need to include the options in the QueryDocuments call, changed print to Python3, list does not work (causes program to hang). I instead used: constraint = 'something' query = "SELECT * FROM r WHERE r. <column_name>='{0}'".format(constraint) docs = client.QueryDocuments(coll_link, query, options) for doc in docs: print(doc) I could not edit your answer as it kept telling me that there was code not formatted correctly (it was a lie!)

mp252 Over a year ago

I am following this same procedure however I cannot get my query to work, I use an order by clause like so; SELECT * FROM c ORDER BY c._ts desc

Bojan Krkic Over a year ago

Do not put raw params into the query. If it comes from user input it's an easy SQLi. Use parameters instead: github.com/Azure/azure-sdk-for-python/blob/main/sdk/cosmos/…

Anurag · Accepted Answer · 2019-10-10 11:06:17Z

1

query = 'SELECT * FROM c'
docs = list(client.QueryItems(coll_link,query))

QueryDocuments has been replaced with QueryItems.

answered Oct 10, 2019 at 11:06

Anurag

11710 bronze badges

Comments

Boyang · Accepted Answer · 2018-03-20 21:06:02Z

0

I have a similar problem recently. You can fetch blocks (not entire query set) by calling fetch_next_block().

query = "select * from c"
options = {'maxItemCount': 1000, 'continuation': True}
q = db_source._client.QueryDocuments(collection_link, query, options)
block1 = q.fetch_next_block()
block2 = q.fetch_next_block()

answered Mar 20, 2018 at 21:06

Boyang

1031 silver badge6 bronze badges

1 Comment

pookie Over a year ago

Cool! You might want to put that into a generator, though!

Faitus Joseph · Accepted Answer · 2024-05-09 18:47:10Z

In order to read the data from the CosmosDB and filter the data you can use "CosmosDB Python SDK". You can install that using the command.

pip install azure-cosmos

Below are the steps to read and filter the data using Python:

Create a Cosmos DB client
Read the data from the container (function "read_item")
Filter the data by using the Python script (example: if item_response['id'] == 2:)

from azure.cosmos.aio import CosmosClient as cosmos_client
from azure.cosmos import PartitionKey
import asyncio

endpoint = "your URI"
key = "Your Public Key"

database_name = 'your DB name'
container_name = 'your container name'

async def read_items(container, items_to_read):
    for dept in items_to_read:
        item_response = await container.read_item(item=dept['id'], partition_key=dept['name'])
        if item_response['id'] == 2:
              print(f"Id {item_response['id']} Name {item_response['name']} location {item_response['location']}")

async def run_sample():
        await read_items(container, dept_items_to_read)

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(run_sample())

Collectives™ on Stack Overflow

CosmosDB and Python3: how to query?

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related