up vote 0 down vote favorite
share [g+] share [fb]

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

link|improve this question
100% accept rate
feedback

1 Answer

up vote 1 down vote accepted

It should be like this, without the quotes

SET US = '36.533'

Can you try this:

UPDATE %s
   SET US=%s
   WHERE ID=%s
link|improve this answer
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 – user578582 May 7 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? – user578582 May 7 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 – user578582 May 7 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)" – user578582 May 7 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 7 at 18:01
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.