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!

Answer 1

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()