Python SDK update query not updating document

Hi,

I am trying to a field inside a couchbase document, using cluster.query with below code :
print(type(cluster))
query = “update some.bucket set status = ‘Not Processed’ where instance in {} and specification.id=‘{}’ and id LIKE ‘%{}%’;”.format(newCycleMonths,cycleSpecification,cycleYear)
print(“Query before exeuting - {}”.format(query))

bucket = cluster.bucket(bucket_name)

result = cluster.query(query,QueryOptions(metrics=True)).execute

This same code works and returns results if the query is select. but update doesn’t work.

What do you mean by “doesn’t work”? Does it throw an exception? Can you show the output from the print(“Query before executing…”) ? Can you show what ‘result’ is after executing? Can you show the same for the SELECT that works?

Modified the code a bit.

cluster = connect_couchbase(endpoint,username,password)

print(type(cluster))
query = "update `somebucket` set status = 'Not Processed' where Instance in {} and specification.id='{}' and id LIKE '%{}%';".format(newCycleMonths,cycleSpecification,cycleYear)
print("Query before exeuting - {}".format(query))
select_query = "select * from `somebucket` where instance in {} and specification.id='{}' and id LIKE '%{}%';".format(newCycleMonths,cycleSpecification,cycleYear)
print("Select Query before exeuting - {}".format(select_query))
bucket = cluster.bucket(bucket_name)


result_select = cluster.query(select_query,QueryOptions(metrics=True))
result_update = cluster.query(query,QueryOptions(metrics=True))
print("Query Result of select query - {}".format(list(result_select)))
print("Query Result of Update query - {}".format(list(result_update)))

PS C:\Users\sourakum> & C:/Users/sourakum/AppData/Local/Programs/Python/Python39/python.exe c:/Users/sourakum/Desktop/opc.py
Enter Cycle Year :2023
Enter cycle months : 2
Enter Cycle Specification : 131
Authenticating…
Connecting to couchbase cluster… couchbase://ilcebcn121.corp.amdocs.com
<class ‘couchbase.cluster.Cluster’>
Query before exeuting - update somebucket set status = ‘in progress’ where instance in [‘2’] and specification.id=‘131’ and id LIKE ‘%2023%’;
Select Query before exeuting - select * from somebucket where instance in [‘2’] and specification.id=‘131’ and id LIKE ‘%2023%’;
Query Result of select query - [Actual Result]
Query Result of Update query -

I tried the same query on couchbase UI, it works there. Maybe I am missing something.

Update via query is not synchronous. Use the query option scanConsistency = request_plus to read-your-own-writes. See the documention for the sdk you are using for details.

It “works” in the UI, because there is a delay of a few seconds between executing the “update” and the “select” which is enough time for the actual update to take place.

Even if I run only update it doesn’t update the document on couchbase.
When I say it doesn’t work, I mean - it doesn’t update the actual document in couchbase.
The same query if executed from UI, updates the doc.

The goal is update a field in the document, via the N1ql query. If this is incorrect or cannot happen, Is there any other way to do it?

query() with an ‘update’ does not return the updated rows.
To see the updated row, execute the select query after the update query, and with the option scanConsistency(QueryScanConsistency.REQUEST_PLUS).

@srvkmr619

The metrics returned from the update query - do they indicate any rows were mutated? Best guess is that the “id LIKE ‘%{}%’” predicate is not matching anything for some reason. Maybe double check that the post-format version of the statement is as you expect?

In the ‘select’, Instance has an upper-case I. In the ‘update’ it has a lower-case i. Not the same.
Although the program output shows both have lower case i. Also the program output shows ‘in progress’ while the code shows ‘Not Processed’. So the output you are showing is not from the code you are showing - which makes it difficult to help.
So it would help to repeat the ‘select’ (with scanConsistency) after the ‘update’ and show the results.