Simple example access of sql database of can frames.

cansqlquery.py

import sqlite3
import numpy as np

logfile = "output.db"

conn = sqlite3.connect(logfile)
c = conn.cursor()


c.execute("SELECT COUNT() FROM messages")
print("Number of messages: ", c.fetchone()[0])

c.execute("SELECT ts FROM messages LIMIT 1")
start_time = c.fetchone()[0]


def show_id_counts():
    id_counts = {}
    for m in c.execute("SELECT * FROM messages"):
        if m[1] in id_counts:
            id_counts[m[1]] += 1
        else:
            id_counts[m[1]] = 1


    print("ID    | Count |     Avg Freq | Avg DLC ")
    for id in sorted(id_counts):
        if id_counts[id] > 5:
            timestamps = []
            dlcs = []
            for frame in c.execute("SELECT ts, dlc FROM messages WHERE arbitration_id=?", (id,)):
                timestamps.append(frame[0])
                dlcs.append(frame[1])

            print('0x{:<4x}|{:6d} | {:12.3f} | {}'.format(
                id,
                id_counts[id],
                1000 * np.diff(np.array(timestamps)).mean(),
                np.array(dlcs).mean()))
        else:
            print('0x{:<4x}|{:6d}'.format(id, id_counts[id]))



def get_raw_data_for_id(id):
    for m in c.execute("SELECT * FROM messages WHERE arbitration_id=?", (id,)):
        yield m[0], m[6]