def __init__(self,
ref,
fetch_from_db=True,
name=None,
region=None,
association=None,
altitude=None,
lat=None,
lon=None):
if fetch_from_db:
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM summits WHERE ref = %s",
(ref, ))
result = cursor.fetchone()
self.ref = ref
self.name = result['name']
self.region = result['region']
self.association = result['association']
self.altitude = result['altitude']
self.lat = result['lat']
self.lon = result['lon']
finally:
connection.close()
else:
self.ref = ref
self.name = name
self.region = region
self.association = association
self.altitude = altitude
self.lat = lat
self.lon = lon
def logbook():
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
s = request.args.get('s', '')
if not s:
query = ''
elif not (' ' in s or '=' in s):
s = s.upper()
c = pymysql.escape_string(s)
query = 'WHERE callsign = "%s" OR callsign LIKE "%%/%s/%%" OR callsign LIKE "%%/%s" OR callsign LIKE "%s/%%"' % (
c, c, c, c)
else:
query = 'WHERE %s' % s #FIXME: escape somehow?
cursor.execute("""SELECT qsos.*,
ms.ref AS ms_ref, ms.name AS ms_name, ms.region AS ms_region, ms.association AS ms_association, ms.altitude AS ms_altitude, ms.lat AS ms_lat, ms.lon AS ms_lon,
s.ref AS s_ref, s.name AS s_name, s.region AS s_region, s.association AS s_association, s.altitude AS s_altitude, s.lat AS s_lat, s.lon AS s_lon
FROM qsos
LEFT JOIN summits AS ms on my_sota_ref = ms.ref
LEFT JOIN summits AS s on sota_ref = s.ref
%s
ORDER BY datetime""" % query)
def logitem(e):
my_summit = Summit.from_joined_db(
e, 'ms_') if e['ms_ref'] else None
summit = Summit.from_joined_db(e, 's_') if e['s_ref'] else None
return (QSO(e), my_summit, summit)
log_items = map(logitem, cursor.fetchall())
return render_template('logbook.html', log_items=log_items, s=s)
finally:
connection.close()
def update_db(download=True):
SUMMIT_DB_FILE = './summitslist.csv'
if download:
# download CSV
url = sqlog.Config.get('sota', 'summit_db_url')
os.makedirs(os.path.dirname(SUMMIT_DB_FILE), exist_ok=True)
r = requests.head(url)
try:
file_last_updated = datetime.datetime.fromtimestamp(
os.path.getmtime(SUMMIT_DB_FILE))
except FileNotFoundError:
file_last_updated = datetime.datetime.fromtimestamp(0)
if 'last-modified' not in r.headers or parsedate(
r.headers['last-modified']) > file_last_updated.replace(
tzinfo=datetime.timezone.utc):
r = requests.get(url, allow_redirects=True, stream=True)
with open(SUMMIT_DB_FILE, 'wb') as f:
yield 'Downloading summits '
for chunk in r.iter_content(1024 * 150):
yield '.'
f.write(chunk)
yield "\n"
# import CSV into MySQL database
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
f = open(SUMMIT_DB_FILE, 'r')
f.readline(
) # skip first line that looks like "SOTA Summits List (Date=29/06/2020)"
yield 'Importing summits '
summits = csv.DictReader(f)
def _summits(n):
global csv_empty
i = 0
for summit in summits:
yield summit
i += 1
if i >= n:
break
cursor.execute("DELETE FROM summits")
connection.commit()
while True:
try:
cursor.executemany(
"""INSERT INTO summits (ref, name, region, association, altitude, lat, lon)
VALUES (%(SummitCode)s, %(SummitName)s, %(RegionName)s, %(AssociationName)s, %(AltM)s, %(Latitude)s, %(Longitude)s)""",
_summits(1000))
yield '.'
except StopIteration:
break
connection.commit()
yield "\n"
finally:
connection.close()
def qso_show(qso_id):
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM qsos WHERE id = '%s'", qso_id)
qso = QSO(cursor.fetchone())
my_summit = Summit(qso.my_sota_ref) if qso.my_sota_ref else None
summit = Summit(qso.sota_ref) if qso.sota_ref else None
return render_template('qso.html',
qso=qso,
my_summit=my_summit,
summit=summit)
except AttributeError:
return "QSO not found", 404
finally:
connection.close()
def summits():
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT COUNT(id) AS qsos, MAX(datetime) as last_activation, summits.ref, summits.name, region, association, altitude, summits.lat, summits.lon
FROM qsos LEFT JOIN summits ON qsos.my_sota_ref = summits.ref
WHERE ref IS NOT NULL
GROUP BY my_sota_ref ORDER BY last_activation""")
def summititem(e):
summit = Summit.from_joined_db(e, '')
return (summit, e['qsos'], e['last_activation'])
summit_items = map(summititem, cursor.fetchall())
return render_template('summits.html', summit_items=summit_items)
finally:
connection.close()
def import_qsos(qsos):
connection = sqlog.mysql_connection()
try:
with connection.cursor() as cursor:
field_keys = ', '.join(map(lambda f: '`%s`' % f, sqlog.qso.fields))
field_values = ', '.join(
map(lambda f: '%%(%s)s' % f, sqlog.qso.fields))
for qso in qsos:
yield qso
cursor.execute(
'INSERT INTO qsos (%s) VALUES (%s)' %
(field_keys, field_values), qso.data)
qso.id = cursor.lastrowid
connection.commit()
except:
yield qso.data
raise
finally:
connection.close()