# Database APIs The process monitor is detecting events with timestamps. The next logical step is to **persist these events** rather than just printing them. That means building the **storage module** (`db.py`) with SQLite. 2 options in my mind 1. store as events - pid, app, start/ stop, timestamp (only inserts) 2. create new record at open - pid, app, start time, update stop time on stop based on pid and not sttopped (based on NULL stop time) **Option 1 (event log)** — Simpler to write (inserts only), but querying “how long was Chrome open?” requires matching start/stop pairs afterwards. **Option 2 (session record)** — Slightly more complex (insert + update), but gives you duration directly. The risk: if the app or machine crashes, you’ll have orphan rows with NULL stop times. **Option 1 has less locking risk** — it’s insert-only, so each write is a quick, independent transaction. Option 2 requires a SELECT to find the matching row, then an UPDATE, which holds the lock longer. With Option 1 you can pair start/stop events in a SQL query when generating reports (e.g., using a self-join on PID). Want to start building the events table? Process monitor output Opened processes: 2026-02-21 16:20:08-{(874, 'sleep')} Closed processes: 2026-02-21 16:20:23-{(870, 'top')} Opened processes: 2026-02-21 16:21:08-{(875, 'top')} Closed processes: 2026-02-21 16:21:23-{(875, 'top')} HOw can I import a module from ../\ ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 The quickest way is to add that folder to `sys.path`: ``` python import sys sys.path.insert(0, '../folder_name') import config as cf ``` Replace `folder_name` with the actual folder. If `config.py` is directly in the parent directory, just use `'..'` instead. I would like to use the fastlite library for SQLlite related code. Documentation available at https://fastlite.answer.ai/ ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Let me check out the fastlite docs so I can help you use it effectively.
read_url(url=‘https://fastlite.answer.ai/’) ``` json { "id": "toolu_01VjsUoGPqmkidDPRwzKecNN", "call": { "function": "read_url", "arguments": { "url": "https://fastlite.answer.ai/" } }, "result": "\u2026e ](./index.html)\\n\\n * [ __ ](https://github.com/AnswerDotAI/fastlite)\\n\\n\\n\\n__\\n\\n 1. [fastlite](./index.html)\\n\\n\\n\\n * [ fastlite](./index.html)\\n\\n * [ Source](./core.html)\\n\\n * [ Test Insert Operations](./test_insert.html)\\n\\n * [ Test Update Operations](./test_update.html)\\n\\n * [ Test Upsert Operations](./test_upsert.html)\\n\\n\\n\\n\\n## On this page\\n\\n * Install\\n * Overview\\n * Dataclass support\\n * Core design\\n * Manipulating data\\n * .insert()\\n * .update()\\n * .delete()\\n * Multi-field primary keys\\n * Diagrams\\n * Importing CSV/TSV/etc\\n * Database.import_file\\n\\n\\n\\n * [__Edit this page](https://github.dev/AnswerDotAI/fastlite/blob/main/nbs/index.ipynb)\\n * [ __Report an issue](https://github.com/AnswerDotAI/fastlite/issues/new)\\n * [ __View source](https://github.com/AnswerDotAI/fastlite/blob/main/nbs/index.ipynb)\\n\\n\\n\\n## Other Formats\\n\\n * [ __CommonMark](index.html.md)\\n\\n\\n\\n# fastlite\\n\\nA bit of extra usabi\u2026" } ```
🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Nice choice! fastlite makes SQLite much more pleasant to work with. To get started with your events table, you’d use something like: ``` python db = database("snooper.db") ``` Then you can define your events table schema using a class. Have a go at defining an [`Event`](https://sayanbanerjee32.github.io/snooper_pkg/db.html#event) class with the fields we discussed (`pid`, `app`, `event_type`, `timestamp`) and create the table using `db.create(Event)`. let me start from DB creation again. I have deleted the file in back end ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Fresh start! You’ve got the `db` object ready. Now try defining your [`Event`](https://sayanbanerjee32.github.io/snooper_pkg/db.html#event) dataclass and creating the table: ``` python from dataclasses import dataclass @dataclass class Event: pid: int app: str event_type: str timestamp: str id: int = None ``` Then create the table with `db.create(Event)`. Give it a go! ------------------------------------------------------------------------ source ### Event ``` python def Event( args:VAR_POSITIONAL, kwargs:VAR_KEYWORD ): ``` *The class container for process monitoring* now? ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Table’s created! Now try inserting a test event to make sure it works: ``` python events = db.t.event events.insert(Event(pid=874, app='sleep', event_type='start', timestamp='2026-02-21 16:20:08')) ``` lets wrap this in a function ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Sure! You’ll want a function that takes the event details and inserts into the table. Something like: ``` python def log_event(pid, app, event_type, timestamp): ... ``` Have a go at filling in the body — you’ve already got the insert working above so it’s just wrapping it up. ------------------------------------------------------------------------ source ### log_event ``` python def log_event( pid:int, # process identifier app:str, # application name event_type:str, # start of stop timestamp:str, # time of event ): ``` *Creates the database if it does not exist and inserts a record* ``` python log_event(pid=874, app='sleep', event_type='stop', timestamp='2026-02-21 16:20:38') ``` how do I check for records in the table? ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 Try: ``` python events() ``` That returns all rows from the table. You can also use `events(limit=5)` to limit results. how do I write a query to so that start and stop event for a combination of pid and app can be extracted as a single row with duration calculated? ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 You can do this with a **self-join** — join the `events` table to itself, matching `start` events with `stop` events on the same `pid` and `app`. SQLite has a `julianday()` function that’s useful for calculating time differences. The general shape is: ``` sql SELECT s.pid, s.app, s.timestamp AS start_time, e.timestamp AS stop_time FROM events s JOIN events e ON s.pid = e.pid AND s.app = e.app WHERE s.event_type = 'start' AND e.event_type = 'stop' ``` You can run raw SQL in fastlite with `db.execute(sql).fetchall()`. Try this query first, then we can add the duration calculation using `julianday()`. how to calculate the duration here? ##### 🤖Reply🤖 🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠🧠 The duration came out as `0` because SQLite is subtracting two text strings. You need to convert them to numeric values first using `julianday()`: ``` sql (julianday(e.timestamp) - julianday(s.timestamp)) * 86400 AS duration_seconds ``` `julianday()` returns days, so multiplying by 86400 (seconds in a day) gives you duration in seconds. Try replacing your duration line with that. ------------------------------------------------------------------------ source ### get_processes ``` python def get_processes( ): ``` *Returns application usage with start time, stop time and duration in seconds* ``` python get_processes() ```