Easy Steps To Install and Use SQLite on Ubuntu 22.04

By Posted on

Easy Steps To Install and Use SQLite on Ubuntu 22.04

Easy Steps To Install and Use SQLite on Ubuntu 22.04

This guide aims to provide a comprehensive walkthrough on how to Install and Use SQLite on Ubuntu 22.04. SQLite is a relational database management system (RDBMS) known for its speed and minimal dependence on the host environment. It is an embedded database, meaning it’s designed to be integrated directly into applications, offering database functionality without the need for a separate database server installation. This makes it simpler to use and requires less administrative configuration compared to client-server databases.

Follow these steps to Install and Use SQLite on Ubuntu 22.04 to create databases, tables, and more.

Before proceeding, ensure you have a non-root user with sudo privileges on your Ubuntu 22.04 server. If you haven’t already set this up, refer to a guide on initial server setup with Ubuntu 22.04.

SQLite Installation on Ubuntu 22.04

First, update your package index:

sudo apt update

SQLite 3 is the latest version and is available in the default Ubuntu repositories. Install it using:

sudo apt install sqlite3

Verify the installation by checking the version:

sqlite3 --version
**Output**
3.37.2 2022-01-06 13:25:41

Now that SQLite is installed, let’s explore its usage.

SQLite Usage on Ubuntu 22.04

This section will demonstrate how to use SQLite effectively.

Create SQLite Database

To create a new SQLite database, use the following command:

sqlite3 orca.db

This command creates a database file named orca.db. If the file already exists, SQLite will open a connection to it. If it doesn’t exist, SQLite will create it.

The output will be similar to this:

**Output**
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite>

Note: If the orca.db file does not exist, and you exit SQLite without executing any queries, the file will not be created. To ensure the file is created, run an empty query by typing ; and pressing Enter.

Now, let’s create a table within the database.

Create a Table for SQLite Database

Use the CREATE TABLE statement to define a table with columns and data types:

CREATE TABLE orca(id integer NOT NULL, name text NOT NULL, orcatype text NOT NULL, length integer NOT NULL);

This creates a table named orca with columns for id (integer, not null), name (text, not null), orcatype (text, not null), and length (integer, not null).

Next, insert data into the table:

INSERT INTO tablename VALUES(values go here);

Example:

INSERT INTO orca VALUES (1, "olivia", "type1", 427);
INSERT INTO orca VALUES (2, "daniel", "type2", 600);
INSERT INTO orca VALUES (3, "sarra", "type3", 1800);

Note: Since the columns are defined as NOT NULL, you must provide a value for each column when inserting data.

Read SQLite Tables

To retrieve data from the table, use the SELECT statement:

SELECT * FROM orca;

The output will display all rows and columns:

**Output**
1|olivia|type1|427
2|daniel|type2|600
3|sarra|type3|1800

To retrieve specific rows based on a condition, use the WHERE clause:

SELECT * FROM orca WHERE id IS 1;
**Output**
1|olivia|type1|427

Add Columns in SQLite Table

Add a new column to the table using the ALTER TABLE statement:

ALTER TABLE orca ADD COLUMN age integer;

Update the values for the new column:

UPDATE orca SET age = 25 WHERE id=1;
UPDATE orca SET age = 33 WHERE id=2;
UPDATE orca SET age = 35 WHERE id=3;

View the updated table:

SELECT * FROM orca;
**Output**
1|olivia|type1|427|25
2|daniel|type2|600|33
3|sarra|type3|1800|35

Delete a Row in SQLite Table

Delete a row from the table using the DELETE FROM statement with a WHERE clause:

DELETE FROM orca WHERE age <= 30;

This command deletes the row where the age is less than or equal to 30.

The following image will show that olivia is deleted:

[Image of SQLite table after deleting row]

For further details, consult the SQLite Documentation page.

Conclusion

SQLite is a lightweight and user-friendly database system ideal for local data storage and management. Its simplicity and serverless nature make it an excellent choice for various applications. This guide has demonstrated the essential steps to Install and Use SQLite on Ubuntu 22.04.

Alternative Solutions for Managing SQLite Databases on Ubuntu 22.04

While the command-line interface is a powerful way to interact with SQLite, there are alternative methods that offer a graphical user interface (GUI) or programmatic access for enhanced usability and automation.

1. Using a GUI Tool (DB Browser for SQLite)

DB Browser for SQLite is a free, open-source visual tool that allows you to create, design, and edit SQLite databases with a user-friendly interface. It’s particularly useful for those who prefer a visual approach to database management.

  • Installation:

    sudo apt update
sudo apt install sqlitebrowser
  • Usage: After installation, you can launch DB Browser for SQLite from your applications menu. It provides options to create new databases, open existing ones, create and modify tables, insert and edit data, and execute SQL queries visually. This eliminates the need to remember SQL syntax for basic operations. For example, you can right-click on the ‘orca’ table to add new columns, without having to type ALTER TABLE commands. The interface provides prompts and validation to minimize errors.

2. Programmatic Access using Python (sqlite3 module)

For applications that require automated database interactions, using a programming language like Python with the sqlite3 module is an efficient approach. The sqlite3 module provides a Python interface for interacting with SQLite databases.

  • Prerequisites: Python and the sqlite3 module are typically pre-installed on Ubuntu. If not, you can install Python using sudo apt install python3 and ensure the sqlite3 module is available.
  • Code Example:
import sqlite3

# Connect to the database (or create it if it doesn't exist)
conn = sqlite3.connect('orca.db')

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orca (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        orcatype TEXT NOT NULL,
        length INTEGER NOT NULL,
        age INTEGER
    )
''')

# Insert data
data = [(1, "olivia", "type1", 427, 25),
        (2, "daniel", "type2", 600, 33),
        (3, "sarra", "type3", 1800, 35)]

cursor.executemany('INSERT INTO orca VALUES (?, ?, ?, ?, ?)', data)

# Query the database
cursor.execute('SELECT * FROM orca')
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

# Delete a row
cursor.execute('DELETE FROM orca WHERE age <= 30')

# Commit the changes
conn.commit()

# Close the connection
conn.close()

This Python script demonstrates how to connect to an SQLite database, create a table (if it doesn’t exist), insert multiple rows of data, query the database, print the results, delete a row based on a condition, commit the changes, and close the connection. This approach allows you to automate database operations within your applications, making it suitable for tasks such as data processing, reporting, and integration with other systems. Error handling can be implemented with try...except blocks for more robust applications.

By using either DB Browser for SQLite or the Python sqlite3 module, you can manage your SQLite databases more effectively based on your specific needs and preferences, complementing the command-line approach. These alternative solutions can streamline the process of learning to Install and Use SQLite on Ubuntu 22.04.

Related posts: