Monday, 26 November 2012

Getting started with Databases on the Pi with SQLite

Sooner or later one of your projects is going to need to store some data; and what better place to store that data than a database?  This post is going to run through the basics of what you will need to do to get started with databases on your Raspberry Pi.  To do so I am going to make use of a database technology called SQLite.

SQLite is an excellent choice of database technologies to get started with on the Pi for a number of reasons:
  • It is incredibly easy to install.
  • Python comes with built in support for connecting to it.
  • It is free and open source.
  • Databases are stored in a single file which you can directly connect to from your code, without the need of a running server process.
  • The library is small.
  • And yet a serious product being used in many well known products such as Mozilla's Firefox web browser.  Check this page out for a full list.
Obviously SQLite may not be the perfect choice for every project and if you want to read up some more about if it will be suitable for your particular project check out the Appropriate Uses of SQLite page on their website.

But I suspect that SQLite will be a good fit for many projects on the Pi.


I said that it was easy to install so here goes. Boot your Pi up to the command line (or if you are already in the graphical interface open LXTerminal) and type:

sudo apt-get install sqlite3

Wait while it downloads and installs; and you are done.

Creating Your First Database

So now we have installed it, the next step is to create a database.  You'll be pleased to know this is equally easy.  You will remember from above that I said SQLite databases were a single file.  So all we need to decide is what we are going to call that file and where we are going to save it.  For this example we shall call it MyFirstDatabase.db and we'll just save it in the default home folder.  So still on the same command line type:

sqlite3 MyFirstDatabase.db

You don't need to worry about any configuration options because there are none.  You are greeted by the sqlite command prompt

and that is it; you have created your first database and are connected to it.  There is one slight caveat to this.  SQLite has not yet actually created the database file, so if you were to exit SQLite now and look for MyFirstDatabase.db you wouldn't find it.  Why?  Well we haven't created any content for our database yet and with no content what do we actually have?  So let us start adding some content.

Creating Your First Table

In SQLite, data is stored in data tables which conceptually look much like data tables you would encounter in the real world:

First we need to create the table and then we need to add the data into that table.  To do both of these tasks we are going to use a language called SQL.

"Wait a second..." I hear you say.  "I thought we were already using SQLite, now we need another SQL?  And what's the difference?"

OK, good question.  SQL stands for "Structured Query Language" and it is a language much like Python is a language.  Though not strictly a programming language in the traditional sense, but specifically tailored for querying data.  SQLite is a program which let's you run SQL commands, much like the Python editor on your desktop will let you run a Python program, but also provides the whole mechanism of what to do with those commands.  SQL is found in industry applications such as SQL Server, Oracle, MySQL and many others.  Every program has a slightly different flavour of SQL, but the basics will be the same across all of them and well worth your time to learn.

 To create the table as shown type the below at the sqlite> prompt and press enter:

CREATE TABLE fruit ( name TEXT, calories INT, price NUMERIC);

As a helpful note, all commands in this blog post in blue text are SQL commands and should be entered at the sqlite> prompt.  Any commands in red text should be entered at the standard Linux command line $ prompt.

Don't forget the semicolon on the end.  If you do (as I always seem to) just put it on the next line and press enter. That's it, you've just created a table called fruit.

So what did we do?

Well fruit is the name of the table that we created and what we will use to refer to the table when we want to put data in and get data out of it.  name, calories and price are the names of the three columns;  TEXT, INT and NUMERIC are the datatypes for the corresponding columns.

For anyone who has used any other SQL database engines in the past it is worth noting here that SQLite uses a dynamic data type system.  This means you can actually store any type of data in any column.  The data types that you define when you create the table are merely recommended data types and do not enforce the type of data which can be stored in a particular column.  A more detailed explanation can be found on the SQLite website here.

Now if we exit SQLite we will find that it has created MyFirstDatabase.db.  Type


and then


and we can see our newly created database sitting happily in the filesystem:

Inserting Data

Well now we have a table the next step is to get some data into it and again we are going to do this using SQL.

Reconnect to the database with the same command as before

sqlite3 MyFirstDatabase.db

Then to insert the first row of data you can see in the table above we type

INSERT INTO fruit values("Raspberry", 60, 4.99);

repeat for the next two rows

INSERT INTO fruit values("Apple", 52, 0.79);
INSERT INTO fruit values("Orange", 85, 2.5);

Great now our table has three rows of data, but how do we check what is there?

Querying Data

To see what data is in a particular table we use the SQL select command.

SELECT * FROM fruit;

which shows us the three rows of data we just added:

the * just means give me all columns.  Alternatively if we just wanted to see a subset of columns we could have typed

SELECT name, price FROM fruit;

now we just get the name and price columns returned

What if we want to filter a particular subset of rows?  For that we need a where clause

SELECT * FROM fruit WHERE price > 3;

gives us all rows where the price is greater than 3. Or

SELECT * FROM fruit WHERE name = "Apple";

returns just the Apple row of data.

The SELECT statement is really one of the cornerstones of the SQL language and these are just some very basic examples of what you can do with it.

Deleting Data

If we want to remove data from our table then we can use the DELETE command.

which is very similar to the SELECT command so

DELETE FROM fruit WHERE name = "Raspberry";

will delete the Raspberry row


will delete all of the rows in the fruit table.

TIP:  If you want to re-add the data you just deleted, a quick way to do this is to press the up arrow on the command prompt to get back the insert commands you initially used to add the data and just press enter on each one to run that command again.


Look out for my next blog where we will look at how we can connect to our database from Python.  Essential if we want to start making use of databases in our projects.


  1. I want to connect my database through a Python Program but i cannot find your next blog in which you connected the database with python program, can you please give me a direct link to that blog.. Thanks. Ahsan

    1. I'm afraid that post is still a work in progress. Probably be completed next year now I'm afraid. Thanks Adam

  2. Thanks for the post, i'll wait for next one.


    this link might help for python code working with SQLite.

    Good blog post. Helpful

  4. This paragraph will help the internet visitors for building up new web site or even a weblog from start to end. 토토

  5. I savour, lead to I found exactly what I used to be having a look for. You’ve ended my four day long hunt! God Bless you man. Have a nice day. Bye 토토

  6. Thanks for sharing this marvelous post. I m very pleased to read this article.

  7. Such an amazing and helpful post. I really really love it.

  8. I'm so happy to finally find a post with what I want. casino You have inspired me a lot. If you are satisfied, please visit my website and leave your feedback.

  9. I always think about what is. It seems to be a perfect article that seems to blow away such worries. 온카지노 seems to be the best way to show something. When you have time, please write an article about what means!!

  10. From some point on, I am preparing to build my site while browsing various sites. It is now somewhat completed. If you are interested, please come to play with 바카라사이트!!

  11. Great information, thanks for sharing it with us 토토사이트

  12. this piece of writing is pleasant and very informative. 스포츠토토

  13. This paragraph will assist the internet people for creating new website or even a weblog from start to end. 카지노사이트탑

  14. It’s very simple to find out any topic on web as compared to textbooks, as I found this paragraph at this website. 카지노사이트

  15. Hi, yeah this article is actually pleasant and I have learned lot of things from it regarding blogging. thanks. 온라인바둑이

  16. There are also articles on these topics on my blog and I hope you visit once and have a deep discussion!casino api

  17. Pretty useful article. I merely stumbled upon your internet site and wanted to say that I’ve very favored learning your weblog posts. Any signifies I’ll be subscribing with your feed and I hope you publish once additional soon. 메이저사이트

  18. Fairly certain he will have a good read. Thank you for sharing! 카지노

  19. I have joined your feed and look forward to seeking more of your great post. 카지노사이트

  20. I finally found what I was looking for! I'm so happy. 우리카지노

  21. What an interesting story! I'm glad I finally found what I was looking for 메리트카지노.

  22. I accidentally searched and visited your site. I still saw several posts during my visit, but the text was neat and readable. I will quote this post and post it on my blog. Would you like to visit my blog later? keonha cai

  23. First of all, thank you for letting me see this information. I think this article can give me a lot of inspiration. I would appreciate 바카라사이트 if you could post more good contents in the future.

  24. I no uncertainty esteeming each and every bit of it. It is an amazing site and superior to anything normal give. I need to grateful. Marvelous work! Every one of you complete an unfathomable blog, and have some extraordinary substance. Keep doing stunning 메이저사이트순위

  25. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트

  26. First of all, thank you for letting me see this information. I think this article can give me a lot of inspiration. I would appreciate 바카라사이트 if you could post more good contents in the future.

  27. That's a really impressive new idea! 안전한놀이터^ It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.