2019-09-10 19:20:41 +00:00
|
|
|
|
|
|
|
# from datetime import datetime,timedelta
|
|
|
|
import datetime
|
2019-12-22 10:37:00 +00:00
|
|
|
import logging
|
2019-09-10 19:20:41 +00:00
|
|
|
import sqlite3
|
|
|
|
import csv
|
|
|
|
import sys
|
|
|
|
|
|
|
|
|
|
|
|
def balloonstodb(balloons):
|
|
|
|
con = None
|
|
|
|
|
|
|
|
print("Writing balloons do db")
|
|
|
|
try:
|
|
|
|
con = sqlite3.connect('wsprdb.db')
|
|
|
|
cur = con.cursor()
|
|
|
|
cur.execute('drop table if exists balloons')
|
|
|
|
cur.execute('create table if not exists balloons(name varchar(20), call varchar(10), freq integer, channel integer)')
|
|
|
|
for row in balloons:
|
|
|
|
cur.execute("INSERT INTO balloons VALUES(?,?,?,?)", (row))
|
|
|
|
print(row)
|
|
|
|
data = cur.fetchall()
|
|
|
|
if not data:
|
|
|
|
con.commit()
|
|
|
|
except sqlite3.Error as e:
|
2019-12-22 10:37:00 +00:00
|
|
|
logging.info("Database error: %s",e)
|
2019-09-10 19:20:41 +00:00
|
|
|
except Exception as e:
|
2019-12-22 10:37:00 +00:00
|
|
|
logging.info("Exception in _query:", e)
|
2019-09-10 19:20:41 +00:00
|
|
|
finally:
|
|
|
|
if con:
|
|
|
|
con.close()
|
|
|
|
return
|
|
|
|
|
|
|
|
|
|
|
|
def readballoonsdb():
|
|
|
|
con = None
|
|
|
|
balloons = []
|
|
|
|
data = []
|
|
|
|
|
|
|
|
print("Reading balloons from db")
|
|
|
|
try:
|
|
|
|
con = sqlite3.connect('wsprdb.db')
|
|
|
|
cur = con.cursor()
|
|
|
|
cur.execute('select * from balloons')
|
|
|
|
data = cur.fetchall()
|
|
|
|
for row in data:
|
|
|
|
print(row)
|
|
|
|
balloons.append(list(row))
|
|
|
|
|
|
|
|
if not data:
|
|
|
|
con.commit()
|
|
|
|
except sqlite3.Error as e:
|
|
|
|
print("Database error: %s" % e)
|
|
|
|
except Exception as e:
|
|
|
|
print("Exception in _query: %s" % e)
|
|
|
|
finally:
|
|
|
|
if con:
|
|
|
|
con.close()
|
|
|
|
# print("Loaded balloons:", len(balloons))
|
|
|
|
return balloons
|
|
|
|
|
|
|
|
# Dumps all spots to csv-file
|
|
|
|
def dumpcsv(spotlist):
|
2019-12-22 10:37:00 +00:00
|
|
|
logging.info("Writing spots to csv-file")
|
2019-09-10 19:20:41 +00:00
|
|
|
with open('spots.csv', 'a', newline='') as csvfile:
|
|
|
|
spotswriter = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
|
|
|
|
|
|
|
|
for row in spotlist:
|
|
|
|
row[0] = datetime.datetime.strftime(row[0], '%Y-%m-%d %H:%M')
|
|
|
|
spotswriter.writerow(row)
|
|
|
|
|
|
|
|
csvfile.close()
|
2019-12-22 10:37:00 +00:00
|
|
|
return
|
2019-09-10 19:20:41 +00:00
|
|
|
|
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
# Org-csv
|
2019-09-10 19:20:41 +00:00
|
|
|
# Date Call Frequency SNR Drift Grid dBm W reporter locator dist-km dist-mi
|
|
|
|
# 2018-05-21 19:04,F6HCO,10.140216,-11,1,J19bg,+33,1.995,SM0EPX/RX2,JO89si,1495,929
|
|
|
|
|
|
|
|
# 2018-05-03 13:06:00, QA5IQA, 7.040161, -8, JO53, 27, DH5RAE, JN68qv, 537
|
|
|
|
# 0 1 2 3 4 5 6 7 8
|
|
|
|
|
|
|
|
# 2018-06-14 09:08,QA5IQB,5.288761,-26,0,JO22,+20,DL0HT,JO43jb,266
|
|
|
|
# 0 1 2 3 4 5 6 7 8 9
|
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
# Local-csv
|
|
|
|
# 2018-05-01 02:14,QA5IQA,3.594159,-28,JO53,27,DC5AL-R,JO31lk,353
|
|
|
|
# 2018-05-01 02:14,QA5IQA,3.594153,-21,JO53,27,DF2JP,JO31hh,380
|
2019-09-10 19:20:41 +00:00
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
# timestamp, tx_call, freq real, snr integer, drift integer, tx_loc, power , rx_call, rx_loc, distance
|
2019-09-10 19:20:41 +00:00
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
def readcsv():
|
|
|
|
spots = []
|
|
|
|
with open('spots.csv', newline='') as csvfile:
|
|
|
|
spotsreader = csv.reader(csvfile, delimiter=',', quotechar='|')
|
|
|
|
|
|
|
|
for row in spotsreader:
|
|
|
|
# Time
|
|
|
|
row[0] = datetime.datetime.strptime(row[0], '%Y-%m-%d %H:%M')
|
|
|
|
row[3] = int(row[3])
|
|
|
|
row[4] = int(row[4])
|
|
|
|
|
|
|
|
# Strip "+" from dB
|
|
|
|
row[6] = int(row[6].replace('+',''))
|
|
|
|
row[9] = int(row[9])
|
|
|
|
|
|
|
|
# print(row)
|
|
|
|
spots.append(row)
|
2019-09-10 19:20:41 +00:00
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
csvfile.close()
|
|
|
|
logging.info("Loaded spots: %s", len(spots))
|
|
|
|
#print("First",spots[1:][0])
|
|
|
|
#print("Last",spots[-1:][0])
|
2019-09-10 19:20:41 +00:00
|
|
|
|
2019-12-22 10:37:00 +00:00
|
|
|
return spots
|