UPDATE and psycopg2

Community
1

Hi everyone,
a question for Python with psycopg2:
in this case:

try:
    with self.connection.cursor() as cursor:
        cursor.execute(query)

If query is an UPDATE operation, can I get a consistent ‘cursor.rowcount’ value?

tnx

2

Hey @BepTheWolf ,

Unfortunately, UPDATE queries on WAL tables will not return a true count, its a limitation. Perhaps you can share a bit more about your use case and we can see if there’s an alternate fix?

Though inconvenient, assuming the data is static, you could run a filter query first to identify rows to change, and then check them after the UPDATE to ensure the counts match and the rows were updated.

We also prefer to upsert data if possible, rather than UPDATE, since UPDATE can invalidate materialised views.

3

Hi, UPSERT is giving me problems because it replaces non-existent fields with null. I’m waiting for the MERGE version.
I’d like to use the update, and if the update hasn’t made any changes, then use the insert.
In practice, it would be like using UPSERT, but without changing non-existent fields to NULL.

Something like:

try:
    with self.connection.cursor() as cursor:
        cursor.execute(query)
        if not cursor.rowcount:
            cursor.execute(query_insert)
4

Indeed, dedup merge would help you a lot here. The only way to do it with current upserts is to RMW i.e read the row, merge it client side, then write it back. Not ideal, but we should ship dedup merge in the next few months which will help you long term.

1 Like
5

Ok, that’s what I’m doing now. Thanks.