bikevis-sqlite3-A-reset.py

import sqlite3
 
conn = sqlite3.connect("sessions.db")
 
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS sessions")
cursor.execute("""
               CREATE TABLE IF NOT EXISTS sessions(
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  bike TEXT,
                  date TEXT,
                  timestart INTEGER,
                  timeend INTEGER,
                  duration INTEGER,
                  meters INTEGER
               )
""")

conn.close()

mod('../main').init()

bikevis-sqlite3-B-insert.py

import sqlite3

def insert_session(bike, date, timestart, timeend, duration, meters):

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()

    cursor.execute("INSERT INTO sessions(bike, date, timestart, timeend, duration, meters) VALUES (?, ?, ?, ?, ?, ?)", (bike, date, timestart, timeend, duration, meters))

    conn.commit()
    conn.close()

bikevis-sqlite3-C-select.py

import sqlite3
import datetime as DT


def print_all():

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()
 
    cursor.execute("SELECT * FROM sessions")
    print(cursor.fetchall())

    conn.close()
    return

def print_list():

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()
 
    print("\nList of all the records in the table:\n")
    for row in cursor.execute("SELECT * FROM sessions"):
        print(row)

    conn.close()
    return

def sum_total():

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()
 
    cursor.execute("SELECT SUM(meters) FROM sessions")
    result = cursor.fetchone()

    print ("\nTotal sum of meters:")
    val = 0 if result[0] is None else result[0]
    print(val)

    conn.close()

    return val


def sum_days(n):

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()
    cursor.execute("SELECT SUM(meters) FROM sessions WHERE date >= ? AND date <= ? ", (DT.date.today() - DT.timedelta(days=n), DT.date.today()))
    result = cursor.fetchone()

    print ("\nSum of meters last {} days:".format(n))
    val = 0 if result[0] is None else result[0]
    print(val)

    conn.close()

    return val

def sum_month():
    return sum_days(30)

def sum_week():
    return sum_days(7)

def sum_day():

    conn = sqlite3.connect("sessions.db")
    cursor = conn.cursor()

    cursor.execute("SELECT SUM(meters) FROM sessions WHERE date LIKE ? ", (DT.datetime.now().date(),))
    result = cursor.fetchone()

    print ("\nSum of meters this day:")
    val = 0 if result[0] is None else result[0]
    print(val)

    conn.close()

    return val

bikevis-sqlite3-D-delete-today.py

import sqlite3
import datetime as DT

conn = sqlite3.connect("sessions.db")
cursor = conn.cursor()

cursor.execute("DELETE FROM sessions WHERE date LIKE ? ", (DT.datetime.now().date(),))

print ("\nDeleted todays sessions\n")

conn.commit()
conn.close()

mod('../main').init()