CODEWITHSUNDEEP
pythonsqlsqlitepython-3.4
Steve
Steve

Reputation: 505

Issue with including variable in SQL statement - in Python 3.4

I am attempting to calculate the distance between pairs of coordinates and update a table column with the results.

The calculation of the distance between each pair of coordinates is working fine when I print the results from the loop (see screenshot) .

However there is an issue with the distance added to the table (see screenshot)- all the rows have the distance of 15886, the distance between the points (-36.8090839, 174.718904) and (0,0).

It appears I have an issue with how my UPDATE Command is being constructed- specifically the adding of the variable for the coordinates (lat and long) sourced from the table .

Can someone point out what I am doing wrong?

I'm new to python.

enter image description here

import _sqlite3,  time,  datetime, gpxpy.geo
conn = _sqlite3.connect('SpatialDB')
c = conn.cursor()

lat1 = -36.8090839
lon1 = 174.718904

c.execute('Select * FROM Media')
data = c.fetchall()
for row in data:
    lat2 = row[1]
    lon2 = row[2]
    dist = gpxpy.geo.haversine_distance(lat1, lon1, lat2, lon2)/1000
    SQLCommand =("UPDATE Media SET LastQueried = Current_Timestamp, Distance  = ?");
    value =  [dist]
    c.execute(SQLCommand, value)
    print(dist)

conn.commit()
c.close()
conn.close()

Upvotes: 0

Views: 57

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

distance value is different for each row in the for loop. Because the update statement is in the for loop, each time the distance column (for all rows) gets updated to the current row's value as there is no where clause to specify which row to update. Hence you see the same value in the distance column (the last value of distance from the for loop).

Assuming business is unique for each row, use it as a where clause so the corresponding row gets updated.

import _sqlite3,  time,  datetime, gpxpy.geo
conn = _sqlite3.connect('SpatialDB')
c = conn.cursor()

lat1 = -36.8090839
lon1 = 174.718904

c.execute('Select * FROM Media')
data = c.fetchall()
for row in data:
    lat2 = row[1]
    lon2 = row[2]
    busns= row[0]
    dist = gpxpy.geo.haversine_distance(lat1, lon1, lat2, lon2)/1000
    c.execute("UPDATE Media SET LastQueried = ?, Distance  = ? WHERE Business = ?",(Current_Timestamp,dist,busns))

conn.commit()
c.close()
conn.close()

Upvotes: 1

Related Questions