Mysql Update Syntax Error Python

Question

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

    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

feco · Accepted Answer · 2012-05-07 17:20:57Z

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

answered May 7, 2012 at 17:20

feco

4,4535 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

eamon1234 Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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

feco Over a year ago

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 '

Collectives™ on Stack Overflow

Mysql Update Syntax Error Python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related