Sqlite：从表中更新多个列

Question

问题

我用Python导入了一个csv文件，并创建了一个叫做upload的表。我想用新的upload表中的值来更新Fixture表。我不想插入数据，因为每个fixture都有特定的ID，我不想因为插入而搞乱这些ID。下面是我用插入时能正常工作的代码，但用更新时却不行。有什么建议吗？

Sqlite代码

cursor = conn.execute("select * from upload")

for row in cursor:
    cur.execute("update Fixture \
                set home_score = ?\
                    ,away_score = ?\
                WHERE home_team in ?\
                and away_team in ?",(row[1],row[2],row[0],row[3]))
    conn.commit()
print "update fixture done";

Fixture表

(1, u'2014-06-12', 1, u'Group A', u'Brazil', None, u'Croatia', 1, None, None, None, u'21:00 (ITV)')                                                                       
(2, u'2014-06-13', 11, u'Group A', u'Mexico', None, u'Cameroon', None, None, None, None, u'17:00 (ITV)')                                                                     
(3, u'2014-06-17', 5, u'Group A', u'Brazil', None, u'Mexico', None, None, None, None, u'20:00 (BBC)')                                                                        
(4, u'2014-06-18', 10, u'Group A', u'Cameroon', None, u'Croatia', None, None, None, None, u'23:00 (ITV)')                                                                    
(5, u'2014-06-23', 3, u'Group A', u'Cameroon', None, u'Brazil', None, None, None, None, u'21:00 (ITV)')                                                                      
(6, u'2014-06-23', 9, u'Group A', u'Croatia', None, u'Mexico', None, None, None, None, u'21:00 (ITV)') 

更新数据

u'Cameroon', 1,4, u'Brazil'                                                                   
u'Croatia', 1,3, u'Mexico'

测试

cursor = conn.execute("select * from upload")
for row in cursor:
    print row[1],row[2],row[0],row[3]

测试输出

2 1 Greece Ivory Coast                                                                                                                                                 
1 4 Japan Colombia                                                                                                                                                     
0 0 Costa Rica England                                                                                                                                                 
0 1 Italy Uruguay 

Answer 1

你漏掉了 cursor.fetchall() 这个部分。

for row in cursor.fetchall():
    stmt='''update Fixture set home_score = :home_score,away_score = :away_score WHERE home_team = :home_team and away_team =:away_team''' 
    cur.execute(stmt,dict(home_score =row[1],away_score=row[2],home_team=row[0],away_team =row[3]))
conn.commit()
print "update fixture done";