Suppose you:

• Don't have an IO account yet
• ...or don't have a network near your mouse problem
• ...or would rather keep your mouse data exclusive to hardware you control
• ...or just want a version you can use to build visualizations locally?

Totally cool! Let's talk about SQLite for a minute.

SQWhat?

The first thing you need to know is that SQLite, as the name implies, is a database system that implements a lightweight version of SQL, the Structured Query Language.

As computer languages go, SQL is both an old technology and an incredibly important one. It was first defined back in the 1970s, and it's in use by, among other things, just about every big web site on the planet. The guide you're reading right now, for example, is stored in a relational database queried with SQL.

Most full-featured SQL databases - MySQL/MariaDB, PostgreSQL, etc. - are big, complicated pieces of software that run as server processes and require a healthy dose of know-how to configure. They'll run on the Raspberry Pi, but they might be a lot of overhead for a small project like this one.

SQLite, by contrast, implements only a fraction of the features of bigger systems, but it keeps its data in a single file and the basics of use can be learned in a few minutes.

Installation & Database Setup

And then, from the root of the Adafruit-MouseLogger repo, run ./setup_sqlite.sh:

Now there should be a file called mice.db. You can explore its contents by running sqlite3 mice.db.

The Basic Idea

In an SQLite database, data is stored in rows, in tables containing pre-defined columns. The definition of each table is called a schema. A schema is defined for a given table by using a CREATE statement. You can see the create statement for a given table in SQLite by running .schema tablename, like so:

The setup script defines two tables: One called events, and one called event_types. It also defines a couple of pre-existing event types. You can see these by using a SELECT statement:

The * here is a lot like a wildcard in the shell - it means "all of the fields". If you wanted just the names, of the event types, you could instead write a query like:

If you were going to log a tripping of the motion sensor by hand, you'd write an INSERT like this:

In this statement:

• events (timestamp, value, event_type) specifies the table and the fields you're providing values for
• VALUES (DATETIME(), 1, 1) provides the values
• DATETIME() is a function that returns a string containing the current system date and time
• The first 1 is our value
• The second 1 corresponds to the id of the event type for motion detection

Try running this a few times in a row with some different values (as in the shell, you can press the up arrow to go back to the previous query) and doing a SELECT * FROM events:

Log Some Mouses with Python

mouse.py is written so that if it sees a file called mice.db in the directory it's run from, it'll automatically add event rows. Make sure you've run setup_sqlite.sh, and then run mouse.py and trigger the sensors a few times:

Now, you should be able to open the database with SQLite, and read back these events with SELECT * FROM events:

Suppose you don't really care about the values, but do want to know the timestamp and the name of the event type, instead of just its numeric id? Try this:

SQL is a deep topic, and if you're anything like me, it might take a while for it to really click, but the basics can take you quite a ways.

For more on SQLite, check out this detailed writeup over at Python Module of the Week.