循环遍历SQL中的表并每次更新一行

Question

并且在尝试弄清楚如何更新SQLite数据库中的多行时遇到很多麻烦。

有效地，我在我收集的数据库上找到一个位置，并通过Google地图运行以获取经度和纬度。一般来说它的工作，但循环失败！

它这样做了一次，获得符合条件并完成的第一行，我无法弄清楚它为什么不继续下去！谁能帮忙？下面的脚本：

# coding=utf-8 
import urllib 
import sqlite3 
import json 
conn = sqlite3.connect('ArchDailyProjects.sqlite') 
cur = conn.cursor() 

#Google Prep 
ServiceUrl="https://maps.googleapis.com/maps/api/geocode/json?" 
FimDoURL="&key=????????????????????????????????" #I have the key right, this part works fine 

#cur.execute('SELECT * FROM Lugares') 
#print type(cur) 
#print cur 
#row=cur.fetchone() 
for row in cur.execute('SELECT * FROM LugareS'): 
    print 'Entramos no While' 
    Loc_id = str(row[0]) 
    Loc_Name = str(row[1]) 
    Loc_Lat = row[2] 
    print Loc_Name 
    if Loc_Lat is None: 
     print Loc_Name 
     print Loc_Lat 
     print "Buscando "+Loc_Name+" no Google Maps" 
     try: 
      Url = ServiceUrl + urllib.urlencode({"sensor": "false", "address": Loc_Name}) + FimDoURL 
      Uh = urllib.urlopen(Url) 
      Dados = Uh.read() 
      try: js = json.loads(str(Dados)) 
      except: js = None 
     except: continue 
     if "status" not in js or js["status"] != "OK": 
      print "===== Beeehhhh!!! Não conseguimos encontrar essa cidade====" 
      print Dados 
      continue 
     else: 
      Loc_FormatedAdress = js["results"][0]["formatted_address"] 
      Loc_Lat = js["results"][0]["geometry"]["location"]["lat"] 
      Loc_Lon = js["results"][0]["geometry"]["location"]["lng"] 
      print Dados 
     print 'Endereço Google: ', Loc_FormatedAdress 
     print 'Latitude: ', Loc_Lat 
     print 'Longitude: ', Loc_Lon 
     cur.execute('''UPDATE Lugares SET Latitude= ?, Longitude=?, GoogleLoc=? WHERE id= ? 
     ''', (Loc_Lat, Loc_Lon, Loc_FormatedAdress, Loc_id)) 
     #row=cur.fetchone() 
    else: #row=cur.fetchone() 
     continue 
conn.commit() 

谢谢你们！

Answer 1

for row in cur.execute('SELECT * FROM LugareS'): 
    ... 
     cur.execute('''UPDATE Lugares SET Latitude= ?, Longitude=?, GoogleLoc=? WHERE id= ? 

您正在对同一个游标对象执行不同的查询; UPDATE没有任何结果行。

遍历之前，简单地读取所有数据：

cur.execute('SELECT id, Name FROM Lugares WHERE Latitude IS NULL') 
empty_rows = cur.fetchall() 
for row in empty_rows: 
    ...