Loop Through an Table in SQL and Update a row every time

Question

and having lots of trouble trying to figure out how I can update several rows in a SQLite data base.

Efectively I am getting an location on a Database I gathered, and running through Google maps to get the Latitude and Longitude. In general its working, but the loop fails!

It does it once, gets the first line that meet criteria and finish, and I can´t figure it out why it´s not keep going!! Can anyone help? The script below:

# 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()

Thank you guys!

Answer 1

If the file is large, you may not want to load the entire database into memory with "fetchall" but read only one row at a time, and update entries on the go. You can do this by creating two cursors.

import sqlite3 as sq3
conn = sq3.connect(db_name)
cur = conn.cursor()
cur2 = conn.cursor()

for row in cur.execute('SELECT * FROM Table' ):
    cur2.execute('''UPDATE Table SET variable = ? WHERE id= ?''', (variable, id))

works fine.

Answer 2

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

You are executing a different query on the same cursor object; the UPDATE does not have any result rows.

Simply read all the data before looping over it:

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