How To Install and Use SQLite on Ubuntu 20.04
The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.
Introduction
SQLite is a free, cross-platform database management system. It is popular for its efficiency and ability to interface with many different programming languages.
In this tutorial you will install SQLite on Ubuntu 20.04. You will then create a database, read data from it, insert items, delete items, and join items from separate tables.
Note: This tutorial includes practical instructions for installing and using SQLite. It does not cover larger conceptual issues and production concerns, such as when one should, or should not, consider using a SQLite database. For an excellent overview of popular relational databases and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3
Module in Python 3.
Prerequisites
To complete this tutorial, you will need:
- A local machine or server running Ubuntu 20.04. To set up a server, including a non-root sudo user and a firewall, you can create a DigitalOcean Droplet running Ubuntu 20.04 and then follow our Initial Server Setup Guide.
Step 1 — Installing SQLite on Ubuntu 20.04
To install the SQLite command-line interface on Ubuntu, first update your package list:
- sudo apt-get update
Now install SQLite:
- sudo apt-get install sqlite3
To verify the installation, check the software’s version:
- sqlite --version
You will receive an output like this:
Output3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
With SQLite installed, you are now ready to create a new database.
Step 2 — Creating a SQLite Database
In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3
command:
sqlite3 sharks.db
This will create a new database named sharks
. If the file sharks.db
already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.
You will receive an output like this:
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Following this, your prompt will change. A new prefix, sqlite>
, now appears:
-
With your Shark database created, you will now create a new table and populate it with data.
Step 3 — Creating a SQLite Table
SQLite databases are organized into tables. Tables store information. To better visualize a table, one can imagine rows and columns.
The rest of this tutorial will follow a common convention for entering SQLite commands. SQLite commands are uppercase and user information is lowercase. Lines must end with a semi-colon.
Now let’s create a table and some columns for various data:
- An ID
- The shark’s name
- The shark’s type
- The shark’s average length (in centimeters)
Use the following command to create the table:
- CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);
Using NOT NULL
makes that field required. We will discuss NOT NULL
in greater detail in the next section.
After creating the table, an empty prompt will return. Now let’s insert some values into it.
Inserting Values into Tables
In SQLite, the command for inserting values into a table follows this general form:
INSERT INTO tablename VALUES(values go here);
Where tablename
is the name of your table, and values
are within parentheses.
Now insert three rows of VALUES
into your sharks
table:
- INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
- INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
- INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);
Because you earlier specified NOT NULL
for each of the variables in your table, you must enter a value for each.
For example, try adding another shark without setting its length:
- INSERT INTO sharks VALUES (4, "Faiza", "Hammerhead Shark");
You will receive this error:
OutputError: table sharks has 4 columns but 3 values were supplied
In this step you created a table and inserted values into it. In the next step you will read from your database table.
Step 4 — Reading Tables in SQLite
In this step, we will focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.
To view your table with all of the inserted values, use SELECT
:
- SELECT * FROM sharks;
You will see the previously inserted entries:
Output1|Sammy|Greenland Shark|427
2|Alyoshka|Great White Shark|600
3|Himari|Megaladon|1800
To view an entry based on its id
(The values we set manually), add the WHERE
command to your query:
- SELECT * FROM sharks WHERE id IS 1;
This will return the shark whose id
equals 1
:
Output1|Sammy|Greenland Shark|427
Let’s take a closer look at this command.
- First, we
SELECT
all (*
) values from our database,sharks
. - Then we look at all
id
values. - Then we return all table entries where
id
is equal to1
.
So far you have created a table, inserted data into it, and queried that saved data. Now you will update the existing table.
Step 5 — Updating Tables in SQLite
In the following two sections you will first add a new column into your existing table and then update existing values in the table.
Adding Columns to SQLite Tables
SQLite allows you to change your table using the ALTER TABLE
command. This means that you can create new rows and columns, or modify existing rows and columns.
Use ALTER TABLE
to create a new column. This new column will track each shark’s age in years:
- ALTER TABLE sharks ADD COLUMN age integer;
You now have a fifth column, age
.
Updating Values in SQLite Tables
Using the UPDATE
command, add new age
values for each of your sharks:
- UPDATE sharks SET age = 272 WHERE id=1;
- UPDATE sharks SET age = 70 WHERE id=2;
- UPDATE sharks SET age = 40 WHERE id=3;
Output1|Sammy|Greenland Shark|427|272
2|Alyoshka|Great White Shark|600|70
3|Himari|Megaladon|1800|40
In this step you altered your table’s composition and then updated values inside the table. In the next step you will delete information from a table.
Step 6 — Deleting Information in SQLite
In this step you will delete entries in your table based on the evaluation of an argument.
In the following command you are querying your database and requesting that that it delete all sharks in your sharks
table whose age is less than 200:
- DELETE FROM sharks WHERE age <= 200;
Typing SELECT * FROM sharks;
will verify that Alyoshka
and Himari
, who were each less than 200 years old, were deleted. Only Sammy
the Greenland Shark remains:
Output1|Sammy|Greenland Shark|427|272
Step 7 — Joining Information in SQLite
Let’s imagine that we had two tables: our current sharks
table and an endangered
table. Now what if the endangered
table had an id
value that mapped to the id
s in your sharks
table, and it also had a status
value that indicated each shark’s conservation status?
If you wanted to query data from both tables, you could use one of SQLite’s four join commands:
INNER JOIN
OUTER JOIN
LEFT JOIN
CROSS JOIN
Let’s create that second table and then use INNER JOIN
to join some data.
First, create your endangered
table:
- CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
- INSERT INTO endangered VALUES (1, "near threatened");
Now join your tables:
SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
Your output will look like this:
Output1|Sammy|Greenland Shark|427|272|1|near threatened
Note that the output also includes the id
value from endangered
. You can specify desired output with a more explicit command:
- SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
This time the output excludes the second id
value:
Output1|Sammy|Greenland Shark|427|272|near threatened
You have now successfully joined information from multiple tables.
Conclusion
SQLite is a useful tool for database management. One can quickly create a database and manipulate it with various commands. Following this tutorial, you now have a basic understanding of SQLite and you are prepared dive deeper into this database management system.
For an excellent overview of Relational Databases systems and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3
Module in Python 3.
For specific help with SQLite’s syntax, the official documentation is another excellent resource.
Originally posted on DigitalOcean Community Tutorials
Author: Gareth Dwyer