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

user_8118499 0
Viewed 9069

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!

sqlite python
2 Answers

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.

user_53249680
Related