代码之家 › 专栏 › 技术社区 › Chris

MySQL更新报告错误,我遗漏了什么?

mysql python

Chris · 技术社区 · 14 年前

我注意到一些关于使用python的MySQL更新的帖子,但似乎仍然没有得到正确的答案。

def updateEmployee(employee):
    print employee["firstName"]+" "+employee["lastName"]+" "+employee["nameN"]
    cursor = db.cursor()
    if(len(employee["nameN"]) > 0):
        cursor.execute("""
                UPDATE `employee` 
                SET `employeeID`=%s, `parentID`=%s, `firstName`=%s, `title`=%s, `locCode`=%s,

(25个)

                WHERE  `employee`.`nameN`=%s 
                """, (employee["employeeID"], employee["parentID"], employee["firstName"], employee["title"], employee["locCode"], employee["nameN"]))
        if(cursor.rowcount > 0 ):
            print "updated"
        else:
            c.execute("""
                INSERT INTO `employee`
                (`employeeID`, `parentID`, `nameN`, `firstName`, `alias`, `lastName`, `title`, `department`, `phone`, `areaMission`, `leadershipStyle`, `employeeType`, `coreFunc1`, `coreFunc2`, `coreFunc3`, `address`, `locCode`,`posOrg`)
                VALUES 
                (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (employee["employeeID"], employee["parentID"], employee["nameN"], employee["firstName"], "", employee["lastName"], employee["title"], "", employee["phone"], '', '', '', '', '', '', '', employee["locCode"], ''))

            if(c.rowcount > 0):
                db.commit()
                print "inserted"
            else:
                print "insert failed"
    else:
        print "missing nameN"

[chris@apps ~]$ ./adjust.py
Linda Adam adam.804
Traceback (most recent call last):
  File "./adjust.py", line 89, in <module>
    processCSV(fileName)
  File "./adjust.py", line 14, in processCSV
    updateEmployee(employee)
  File "./adjust.py", line 25, in updateEmployee
    WHERE  `employee`.`nameN`=%s """, (employee["employeeID"], employee["parentID"], employee["firstName"], employee["title"], employee["locCode"], employee["nameN"]))
  File "/usr/lib/python2.5/site-packages/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.5/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE  `employee`.`nameN`='adam.804'' at line 3")

看来我在“WHERE子句”处多了一个“WHERE子句”,但我不明白这是怎么回事?

{
    'employeeID': '1111', 
    'firstName': 'Linda', 
    'title': 'Systems Manager', 
    'nameN': 'adam.804', 
    'lastName': 'Adam', 
    'locCode': 'TNC', 
    'phone': '555-555-5555', 
    'parentID': '2222', 
    'room': ''
}

1 回复 | 直到 14 年前

Ike Walker 14 年前

我想你的WHERE子句前面多了一个逗号。

更新应如下所示:

update foo
set a = 1,
b = 2
where type = 0

update foo
set a = 1,
b = 2,
where type = 0