LemusThelroy LemusThelroy - 7 months ago 25
SQL Question

psycopg2.ProgrammingError: column "none" does not exist - Update Statement

I keep encountering the error "psycopg2.ProgrammingError: column "none" does not exist".

I have read the psycopg2 documentation and looked at other posts on this error, however, many are specific to an insert query rather than an update query.

My code is as follows:-

def send_to_sql(identity,list1,list2,list3):
userid = 30
Fixture_No = None
Home_Side_Score = None
Away_Side_Score = None
conn = psycopg2.connect("dbname='Euro2016' user='postgres' host='localhost' password='##passwordhere##'")
cur = conn.cursor()
cur.execute("""UPDATE "Predictions" SET userid = %r, "Fixture_No" = %r, "Home_Side_Score" = %r, "Away_Side_Score" = %r WHERE "Predictions"."userid" = %r AND "Predictions"."Fixture_No" = %r """ % (userid, Fixture_No, Home_Side_Score, Away_Side_Score, userid, Fixture_No))
conn.commit()
cur.close()
conn.close()


This code works fine if scores and fixture number I am trying to pass are numbers but when a None entry is made, the code falls on its face. Somebody save me...

Answer

Use %s not %r. To check what is being sent to the server use cursor. mogrify:

update = """
    UPDATE "Predictions"
    SET 
        userid = %s, 
        "Fixture_No" = %s, 
        "Home_Side_Score" = %s, 
        "Away_Side_Score" = %s
    WHERE 
        "Predictions"."userid" = %s AND 
        "Predictions"."Fixture_No" = %s
"""
data = (userid, Fixture_No, Home_Side_Score, Away_Side_Score, userid, Fixture_No)
print cur.mogrify(update, data)
cur.execute(update, data)

Still better, use a dictionary to pass the data:

update = """
    UPDATE "Predictions"
    SET
        userid = %(userid)s, 
        "Fixture_No" = %(Fixture_no)s,
        "Home_Side_Score" = %(Home_Side_Score)s,
        "Away_Side_Score" = %(Away_Side_Score)s
    WHERE
        "Predictions"."userid" = %(userid)s AND
        "Predictions"."Fixture_No" = %(Fixture_No)s
"""
data = dict (
    userid = userid,
    Fixture_No = Fixture_No,
    Home_Side_Score = Home_Side_Score,
    Away_Side_Score = Away_Side_Score
)
Comments