0

I've been following this previous answer, however I still get a syntax error:

Stack Overflow Answer

    cursor.execute ("""
   UPDATE tblTableName
   SET Year=%s, Month=%s, Day=%s, Hour=%s, Minute=%s
   WHERE Server=%s
   """, (Year, Month, Day, Hour, Minute, ServerID))

My code is:

def postToMySQL(data,fieldname,table,col):
if fieldname == "Year":
    sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES (%s)"
    c.execute(sql, data)
else:
    c.execute ("""
   UPDATE %s
   SET US=%s
   WHERE ID=%s
    """, (table, data, col))

The table then looks like:

Stack Overflow Answer

The syntax error is:

_mysql_exceptions.ProgrammingError: (1064....near ''OilProvedReservesHistory' SET US = '36.533' WHERE ID=1' at line 1

Can you spot the error? ?Thanks

Community
  • 1
  • 1
eamon1234
  • 1,555
  • 3
  • 19
  • 38

1 Answers1

1

It should be like this, without the quotes

SET US = '36.533'

Can you try this:

UPDATE %s
   SET US=%s
   WHERE ID=%s
feco
  • 4,384
  • 5
  • 27
  • 44
  • Cheers. I tried that before posting, but neglected to check that the error is different. It's now throwing up: TyperError: not all agruments converted during string formatting – eamon1234 May 07 '12 at 17:24
  • Scratch that, removing fieldname from the list still gives the error: ''OilProvedReservesHistory'\n\t SET US = '36.533'\n\t WHERE ID=1'. Is it something to do with the \n\t business? – eamon1234 May 07 '12 at 17:28
  • I don't think the \n\t matters because I tried putting it all on one line and the error becomes: ''OilProvedReservesHistory' SET US='36.533' WHERE ID=1' at line 1 – eamon1234 May 07 '12 at 17:31
  • 1
    Ah, thanks to feco, but it works if I put in the table name explicitly ie: else: c.execute (""" UPDATE OilProvedReservesHistory SET US=%s WHERE ID=%s """, (data, col)) Can someone explain why this is the case? Do I have to do the brute force string concatenation that I have done like this each time? sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES (%s)" – eamon1234 May 07 '12 at 17:45
  • There is a ' after 'OilProvedReservesHistory, be careful using the ' because it means a varchar in SQL, try to concatenate your string before entering into SQL, and make sure there a no orphan ' – feco May 07 '12 at 18:01