First Interaction with SQLite

In the previous article we have discussed about how to install SQLite. Whether you have installed it for Linux, Windows, or Mac OS X, there should be a working SQLite. If you don’t have it yet, please proceed to installation first depend on you system.

In this article we will discuss about SQLite and cover following topics:

• Create a new SQLite database called “xathrya.db”.
• Create “projects” table with three fields 1) Project ID 2) Name and 3) Deadline
• Insert 5 records into the project tables.
• Verify the records
• Delete a record
• Update a record
• Exit SQLite3
• Reopen database

Actually the operating system is not the matter here, means you can use SQLite on any operating system (whether Linux, Windows, or Mac OS X). This article should be generic enough to cover all of them.

Create a New SQLite Database

Like any SQL DBMS, data is stored on a database. However, SQLite will store all the information (tables, and metadatas) on a single file instead of creating differet files.

In this section we will create a new database with filename xathrya.db. Use following command:

sqlite3 xathrya.db

You would be prompted by something like this:

SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

At this point, we have entered the SQLite’s command shell. Using this shell we can enter SQL command and manage our database.

Create Table

Creating a table in SQLite is like using any Relational DMBS. The underlying language is similiar, SQL.

Now let’s create a table “projects” having three fields:

1. Project ID, denoted by field id as integer.
2. Name, denoted by field name as varchar
3. Deadline, denoted as deadline as datetime

The command (within SQLite shell) would be:

create table projects(id integer primary key,name varchar(20),deadline date);

If you see on current working directory, we have newly created file xathrya.db as result of our command.

Insert Record

To insert a record we use familiar SQL command

insert into tableT values(list of values);

Which in our case the the tableT would be projects and the list of values will be the value of id,name,deadline respectively. Let’s insert 5 records there.

insert into projects values(101,'GSM Tracker using Raspberry Pi','2013-08-01');
insert into projects values(102,'GUNDAM OS','2014-08-01');
insert into projects values(103,'Project Mikan','2015-08-01');
insert into projects values(104,'Project Freedom','2016-08-01');
insert into projects values(105,'Low Orbit Unmanned Plane using Arduino','2017-08-01');

Verify Record

Let’s see whether our records has been saved by SQLite by invoking following command:

select * from projects;<br>

And you should be given following result.

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2014-08-01
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01
105|Low Orbit Unmanned Plane using Arduino|2017-08-01

Delete a Record

To delete a record, for example on with id 105, use following command:

delete from projects where id=105;<br>

Now let’s verify the tables. At this point, SQLite should give you following result:

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2014-08-01
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01

Update a Record

SQLite can be used to update an individual record. For example we want to change project with id 102 and set the deadline to 2013-07-02. Use following command;

update projects set deadline='2013-07-02' where id=102;<br>

Now verify the table. The output should be like this:

101|GSM Tracker using Raspberry Pi|2013-08-01
102|GUNDAM OS|2013-07-02
103|Project Mikan|2015-08-01
104|Project Freedom|2016-08-01

Exit

To exit, press CTRL+D (CTRL and D at same time).

Reopen Database

When we create a database, it is nothing but a file. To access an existing database and query the records, do the following. i.e When you do “sqlite3 xathrya.db”, if the database doesn’t exist it’ll create it. If it already exists, it’ll open it. As simple as that 😀

sqlite3 xathrya.db

If you dont believe it, try to see the tables projects.

sqlite, tools