我正在尝试在Python 2.7中使用MySQLdb将值插入SQL表的列中。我在将列表插入1列的命令中遇到问题。

我有一个名为“ name”的简单表,如下所示:

+--------+-----------+----------+--------+
| nameid | firstname | lastname | TopAdd |
+--------+-----------+----------+--------+
| 1      | Cookie    | Monster  |        |
| 2      | Guy       | Smiley   |        |
| 3      | Big       | Bird     |        |
| 4      | Oscar     | Grouch   |        |
| 5      | Alastair  | Cookie   |        |
+--------+-----------+----------+--------+


这是我创建表格的方式:

CREATE TABLE `name` (
  `nameid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `TopAdd` varchar(40) NOT NULL,
  PRIMARY KEY (`nameid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8


这是我填充表格的方式:

INSERT INTO `test`.`name`
(`firstname`,`lastname`)
VALUES
("Cookie","Monster"),
("Guy","Smiley"),
("Big","Bird"),
("Oscar","Grouch"),
("Alastair","Cookie");


免责声明:上述MySQL示例的原始来源为here

这是我创建名为TopAdd的新列的方法:

ALTER TABLE name ADD TopAdd VARCHAR(40) NOT NULL


现在,我有5个值的列表,我想将该列的值插入到TopAdd列中。这是清单。

vals_list = ['aa','bb','cc','dd','ee']


这是我尝试过的(循环内的UPDATE语句):

vals = tuple(vals_list)
for self.ijk in range (0,len(self.vals)):
            self.cursor.execute ("UPDATE name SET TopAdd = %s WHERE 'nameid' = %s" % (self.vals[self.ijk],self.ijk+1))


我收到以下错误消息:

Traceback (most recent call last):
  File "C:\Python27\mySQLdbClass.py", line 70, in <module>
    [Finished in 0.2s with exit code 1]main()
  File "C:\Python27\mySQLdbClass.py", line 66, in main
    db.mysqlconnect()
  File "C:\Python27\mySQLdbClass.py", line 22, in mysqlconnect
    self.cursor.execute ("UPDATE name SET TopAdd = %s WHERE 'nameid' = %s" % (self.vals[self.ijk],self.ijk+1))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'aa' in 'field list'")


有没有一种方法可以将这些值通过循环或直接作为列表插入到列中?

最佳答案

尝试这个:

vals_list = ['aa','bb','cc','dd','ee']


对于i,enumerate(vals_list)中的j:

 self.cursor.execute(("UPDATE test.name SET TopAdd = '%s' WHERE nameid = %s" % (str(j),int(i+1))

10-07 19:33
查看更多