37 lines
1008 B
Python
37 lines
1008 B
Python
|
|
# Learn how to update database
|
|
|
|
import sqlite3
|
|
|
|
def get_db_connection():
|
|
conn = sqlite3.connect('database.db')
|
|
conn.row_factory = sqlite3.Row
|
|
return conn
|
|
|
|
conn = get_db_connection()
|
|
|
|
# Grab a random row from frames table and pretend it is new
|
|
cur = conn.cursor()
|
|
cur.execute("SELECT [from], id, created_unix FROM frames ORDER BY RANDOM() LIMIT 1;")
|
|
rows = cur.fetchall()
|
|
results = dict(rows[0])
|
|
values = ', '.join('"%s"' % w for w in results.values())
|
|
|
|
|
|
# Build query
|
|
# "from" is wrappedin [] because it is a reserved word and using '' doesn't work.
|
|
query3 = "INSERT INTO stations ([from], frames_id, last_heard_unix, count) \
|
|
VALUES("+values+", 1) \
|
|
ON CONFLICT([from]) \
|
|
DO UPDATE SET count = count + 1;"
|
|
|
|
# example https://stackoverflow.com/a/50718957/2152245
|
|
# query2 = "INSERT INTO stations ([from], frames_id, last_heard_unix, count) \
|
|
# VALUES('KC9TZN-8', 4068, 1687623864, 1) \
|
|
# ON CONFLICT([from]) \
|
|
# DO UPDATE SET count = count + 1;"
|
|
|
|
conn.execute(query3)
|
|
conn.commit()
|
|
conn.close()
|