CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
LemusThelroy
LemusThelroy

Reputation: 303

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...

Upvotes: 2

Views: 9467

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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
)

Upvotes: 4

Related Questions