Confirmation that a postgres 'update' query worked in python

Question

I've written my first 'update' query in python, while it seems correct, I'm not sure how to receive back the output to confirm it worked..

This is supposed to load a CSV file and replace the values in the first column with those in the second:

def main():
    try:
        conn=psycopg2.connect("dbname='subs' user='subs' host='localhost' password=''")
    except:
        print "I am unable to connect to the database."
        sys.exit()

    with open("dne.txt", "r+") as f:
        for line in f:
            old = line.split(',')[0].strip()
            new = line.split(',')[1].strip()
            cur = conn.cursor()
            cur.execute("UPDATE master_list SET subs = '{0}' WHERE subs = '{1}';".format(new, old))
            conn.commit()
            results = cur.fetchall()
            for each in results:
                print str(each)


if __name__=="__main__":
    main()

I thought the results (UPDATE 1 for each change?) would come back as a tuple, but I got an error instead:

psycopg2.ProgrammingError: no results to fetch

I'm not sure if this means my query just didn't work and there were no updates, or if I can't use fetchall() like I'm trying to.

Any feedback or suggestions welcome!

have you tried commit your data before fetching? I mean cur.commit() I'm not sure if it's useful in your case but sometimes not commiting is the problem. — Lafexlos
– Lafexlos, Commented Jan 22, 2014 at 8:30
@ Lafexlos - cur.commit() AttributeError: 'psycopg2._psycopg.cursor' object has no attribute 'commit' — some1
– some1, Commented Jan 22, 2014 at 8:32
no problem. unfortunately, that gave the same error as the original post — some1
– some1, Commented Jan 22, 2014 at 8:35
one more line of code. cur.execute(select * from table_name) than results = cur.fetchall() — Lafexlos
– Lafexlos, Commented Jan 22, 2014 at 8:38

leancz · Accepted Answer · 2016-12-01 08:42:53Z

11

The UPDATE statement won't return any values as you are asking the database to update its data not to retrieve any data.

By far the best way to get the number of rows updated is to use cur.rowcount. This works with other drivers too, like with Psycopg2 for Postgresql it's the same syntax.

cur.execute("UPDATE master SET sub = ('xyz') WHERE sub = 'abc'")
print(cur.rowcount)

A more roundabout way of checking the update is by running a SELECT against the table after updating it; you should get the data returned. In my example below the first SELECT will return the row(s) where the update will happen. The second SELECT after the update should then return no rows as you have already updated all fields. The third SELECT should return the rows you have updated, plus any that already existed with the 'xyz' value.

import sqlite3

def main():
    try:
        conn=sqlite3.connect(":memory:")
        cur = conn.cursor()
        cur.execute("create table master(id text, sub text)")
        cur.execute("insert into master(id, sub) values ('1', 'abc')")
        cur.execute("insert into master(id, sub) values ('2', 'def')")
        cur.execute("insert into master(id, sub) values ('3', 'ghi')")
        conn.commit()
    except:
        print("I am unable to connect to the database.")
        sys.exit()

    cur.execute("select id, sub from master where sub='abc'")
    print(cur.fetchall())
    cur.execute("UPDATE master SET sub = ('xyz') WHERE sub = 'abc'")
    conn.commit()
    cur.execute("select id, sub from master where sub='abc'")
    print(cur.fetchall())
    cur.execute("select id, sub from master where sub='xyz'")
    print(cur.fetchall())

if __name__=="__main__":
    main()

edited Dec 1, 2016 at 8:42

answered Jan 22, 2014 at 9:54

leancz

6885 silver badges21 bronze badges

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

4 Comments

some1 Over a year ago

thanks. so there is no way to get a direct response back after performing an update query?

leancz Over a year ago

For sqlite there is a "rowcount" attribute on the cursor which records the number of affected rows from the last update: print(cur.rowcount) This seems to be true for Postgres as well

some1 Over a year ago

adding in the the print(cur.rowcount) basically did it, it printed "1" 48 times, which was the number of rows in the csv file.

Le Droid Over a year ago

@leancz cur.rowcount is a far better answer, you should formulate it that way in your main message. Way faster than another select. It works with other drivers too, like with Psycopg2 for Postgresql it's the same syntax.

Zaffer · Accepted Answer · 2020-11-03 18:51:00Z

3

In PostgreSQL 9.5 or later you can add RETURNING * to end your query that then returns the modified rows.

PostgreSQL docs: https://www.postgresql.org/docs/9.5/dml-returning.html

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

answered Nov 3, 2020 at 18:51

Zaffer

1,8882 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

Confirmation that a postgres 'update' query worked in python

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest