Using SQLite3 with Python

Our mission is to send a ‘satellite’ around 300m up which will then take a lot of readings and send them down to ground. All these readings must be stored and later accessed for data analysis. To achieve the latter objective, we will be using SQLite3. A C-based lightweight embedded database which is our database of choice. Now you may be wondering why we chose SQLite3 over something more common like Oracle or even mySQL and to keep it simple, we’re lazy. Oracle and mySQL are quite complicated to learn, use and implement and are normally used for massive multi-user projects, they are too complex for a simple project like ours. It simply was not worth the effort. Mind you, using SQLite3 is still a task in itself and in the next few paragraphs, I will be teaching you how to make your very own database and access the data in Python.

How to make the database

The Progammer opened the file. And he said “Let there be a connection.”

Firstly, you’ll have to install the package using your preferred tool of installation and then import the package. Afterwards, we need to create a connection to the database. You can think of the connection as a phone with the database on one end and you (the user) on the other. To tell the database commands, you have to use the connection to ‘phone’ it. This can be done through:

connection = sqlite3.connect("cansat.db") 
//The "cansat.db" acts a phone number

We have a phone line which we can communicate through, but what’s the point of sending messages if there’s nothing on the other end to recieve and execute them. Hence we need to make a cursor object which can be done through:

cursor = connection.cursor()

Now there is a phone and a reciever however we are missing one key thing, tables. You can think of a table as a – wait, why am I explaining tables. To create a table, we can run a command using the cursor like this:

cursor.execute("(CREATE TABLE IF NOT EXISTS imuReadings(dataType STRING, dataValue REAL, Timestamp REAL)"))

This might seem a little bit confusing and a bit shouty (the all-caps is a bit much) so I’m gonna break it down for you. The first four words are self-explanatory (hopefully), create a table if it already does not exist. The ‘imuReadings’ is the name of the table and in our project it holds the accelerometer, gyroscope and magentometer readings. The stuff (I know, very technical language) is the fields of the table (the columns). The lowercase word is the name of the field and the uppercase word is the data-type. Finally, we have a table with function columns. Now we need to add data.

Adding and Reading Data

We have built the framework, so let’s add the data and as famous redstoner Mumbo Jumbo says, “It’s really quite simple.” There is another shouty command we can use:

cursor.execute(cursor.execute("INSERT INTO imuReadings("accelerometer", 823, 1710009925)"))

This is hopefully self-explanatory but to put it simply, we are creating a new entry into the imuReadings table with “acceleromter” as the dataType, 823 as the readings and the 1710009925 as the timestamp.

Now to read the data, there is one final command that again is shouty:

cursor.execute("SELECT dataValue FROM example where Timestamp = 170009925")
data = cursor.fetchall()

The first line of code asks for all the data from the dataValue field if and only if the timestamp is a certain value. This is now stored in a queue so the second line just asks for the items in the queue and stores it. Think of it as a click and collect stall at a shop, you query for the item(s) online and then pick it up at the counter.

Conclusion

Thats it. The basics of SQlite3 (in Python) and how to make your own database in a few short paragraphs. Hope you learned something and goodnight!