CODEWITHSUNDEEP
pythonpython-3.xsqlite
Yannis G
Yannis G

Reputation: 63

using a For loop in python3 to create a 3 columns x 10 rows table in sqlite3

I am using a For loop to give values to three variables distance, cty, ctry. Then I want to store the values in a table in sqlite3. Unfortunately the program stores only the first row of values of the For loop in the sqlite3 database: 

14004   GOROKA  PAPUA NEW GUINEA

Code:

from math import sin, cos, sqrt, atan2, radians
import sqlite3

R = 6373.0

conn = sqlite3.connect('global_airports_sqlite.db')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Ports')
cur.execute('''
CREATE TABLE Ports ( Delta INTEGER, City TEXT, Country TEXT )''')
coords= 'SELECT lat_decimal, lon_decimal, city, country FROM airports ORDER   
BY id ASC LIMIT 10'
for cord in cur.execute(coords):
 lat2 = radians(cord[0])
 lon2 = radians(cord[1])
 cty = cord[2]
 ctry = cord[3]
 dlon = lon2 - lon1
 dlat = lat2 - lat1
 a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
 c = 2 * atan2(sqrt(a), sqrt(1 - a))
 distance = int(R * c)
 cur.execute('INSERT INTO Ports (Delta, City, Country) VALUES (?,?,?)',
 (distance, cty, ctry))
 conn.commit()

How can I make it store all 10 values in my three columns: Delta, City, Country?

Upvotes: 1

Views: 351

Answers (2)

Barmar
Barmar

Reputation: 780974

You need to use two different cursors, one for the SELECT query and another for the INSERT. When you do the first INSERT query, the cursor no longer holds the remaining results of the SELECT query, so the loop ends.

from math import sin, cos, sqrt, atan2, radians
import sqlite3

R = 6373.0

conn = sqlite3.connect('global_airports_sqlite.db')
cur = conn.cursor()
cur2 = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Ports')
cur.execute('''
CREATE TABLE Ports ( Delta INTEGER, City TEXT, Country TEXT )''')
coords= 'SELECT lat_decimal, lon_decimal, city, country FROM airports ORDER   
BY id ASC LIMIT 10'
for cord in cur.execute(coords):
     lat2 = radians(cord[0])
     lon2 = radians(cord[1])
     cty = cord[2]
     ctry = cord[3]
     dlon = lon2 - lon1
     dlat = lat2 - lat1
     a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
     c = 2 * atan2(sqrt(a), sqrt(1 - a))
     distance = int(R * c)
     cur2.execute('INSERT INTO Ports (Delta, City, Country) VALUES (?,?,?)',
     (distance, cty, ctry))
     conn.commit()

Upvotes: 1

Yikang Luo
Yikang Luo

Reputation: 186

i am not sure about sqlite3 package, I am using pymysql personally; however, I think the flow should be the same;

Thus, I think before you start your for loop, probably you need to fetch your data first?

...
cur.execute('DROP TABLE IF EXISTS Ports')
cur.execute('''
CREATE TABLE Ports ( Delta INTEGER, City TEXT, Country TEXT )''')
coords= 'SELECT lat_decimal, lon_decimal, city, country FROM airports ORDER BY id ASC LIMIT 10')
cur.execute(coords)
data = cur.fetchall()
for records in data:
  ...

Give it a try

Upvotes: 2

Related Questions