Problem using where condition in sql string to pass in python to cosmosdb

Question

def run_query(ENDPOINT,KEY, unix_timestamp,token,container_name):
        client = CosmosClient(url=ENDPOINT, credential=KEY)
        database = client.get_database_client(database_name)
        container = database.get_container_client(container_name)
        general_query = "SELECT * FROM @container_name c WHERE c.RowKey>'@RowKey'"
        parameters = [
            {"name": "@PartitionKey", "value": str(token)},
            {"name": "@RowKey", "value": int(unix_timestamp)},
            {"name": "@database_name", "value": str(database_name)},
            {"name": "@container_name", "value": str(container_name)}
        ]

        for param in parameters:
            general_query = general_query.replace(param['name'], str(param['value']))
        items_tmp = list(container.query_items(query=general_query, 
            partition_key=token  # specifying the partition key
            ))
        return len(items_tmp)>0

the following python method works in case the

general_query = "SELECT * FROM @container_name c"

the moment any where condition like this is inserted

general_query = "SELECT * FROM @container_name c WHERE c.RowKey>'@RowKey'"

it doesn't work.

(I even tried >'0', which covers all cases and it doesn't work)

(I also tried >0, without quotes and it doesn't work)

Don't put a variable inside quotes. WHERE c.RowKey > @RowKey — Barmar
– Barmar, Commented Aug 26, 2024 at 15:10
fyi you don't need to pass any special container name - call it whatever you want in your query, as it's just a placeholder; your query is always executed against the container referenced by the container object. You're making things more complicated than it needs to be - just SELECT * FROM c WHERE... will work — David Makogon
– David Makogon, Commented Aug 26, 2024 at 19:18
Aside from @container_name name as parameter, you don't need to provide @database_name or @PartitionKey. And aside from all that, you really should edit your question to provide a minimal reproducible example, including some sample data, as well as exact query (not showing the substitution strings), expected vs actual results, errors, etc. Right now it's too ambiguous. — David Makogon
– David Makogon, Commented Aug 27, 2024 at 1:03

Balaji · Accepted Answer · 2024-09-02 05:18:00Z

Thanks for your suggestion @Barmar. As per his suggestion you no need to put quotes '@RowKey' in WHERE condition. It works fine without quotes as you can see in the below output.

def run_query(ENDPOINT, KEY, unix_timestamp, partition_key_value, container_name):

    print("executing query")
    client = CosmosClient(url=ENDPOINT, credential=KEY)

    database = client.get_database_client(database_name)
    container = database.get_container_client(container_name)

    general_query = "SELECT * FROM c WHERE c.RowKey > @RowKey"

    parameters = [
        {"name": "@RowKey", "value": str(unix_timestamp)}
    ]

    items_tmp = list(container.query_items(
        query=general_query, 
        parameters=parameters,
        partition_key=partition_key_value  
    ))

    return len(items_tmp) > 0

result = run_query(ENDPOINT, KEY, unix_timestamp, partition_key_value, container_name)

print("Items found:", result)

Output:

executing query
Items found: True

Collectives™ on Stack Overflow

Problem using where condition in sql string to pass in python to cosmosdb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related