将包含多个项目的列表插入到mysql数据库中

I have a list like this:

我有一个这样的列表:

list = [('name1', 'id1', 'created_at1'),('name2', 'id2', 'created_at2'),('name3', 'id3', 'created_at3')]

And I want to put it into a Mysql Database using MySQLdb that it looks like this in the end:

我想把它放到一个使用MySQLdb的Mysql数据库中,它最终看起来像这样:

Name   ID    Created_at
name1  id1   created_at1
name2  id2   created_at2
name3  id3   created_at3

I tried something like:

我试过类似的东西:

for s in list:
   var_string = ', '.join('?' * len(s))
   query_string = 'INSERT INTO TweetsTest VALUES (%s);' % s
   cursor.execute(query_string, varlist)

But it does not work. How can I achieve this?

但它不起作用。我怎样才能做到这一点?

1 个解决方案

#1

This should do it:

这应该这样做:

sql = ("INSERT INTO TweetsTest "
       "(Name, ID, Created_at) VALUES ")

for item in list:
    sqlRow = "('%s', '{1}', '{2}'), ".format(item[1], item[2]) % item[0]
    sql += sqlRow

sql = sql[:-2] + ";"
cursors.execute(sql)

All the above is implying that those 3 fields are all string based, thus the quotes in the for loop. For all numeric fields - remove the quotes.

以上所有都暗示这3个字段都是基于字符串的,因此for循环中的引号。对于所有数字字段 - 删除引号。

#1