Overview

This page explains how to create, save, and load a simple SQLite database on chip. This tutorial includes two methods for working with SQLite on chip. Method A uses Python's built-in sqlite3 module, which is available when Python is installed. Method B uses the standalone sqlite3 command-line tool, which may require activating an appropriate Conda environment before use. SQLite is a lightweight file-based database. It stores the database in a single .db file and does not require a separate database server such as PostgreSQL or MySQL. This makes it a convenient option for learning database concepts and working with small datasets on chip.

We are using SQLite, which is available on UMBC’s chip cluster:

It is included with Python.
It requires no database server.
It stores all data in a single file.
It is easy for beginners.
It is suitable for learning database concepts on chip.

Let's begin. After logging in, check where you are:

Example:

chip-login1

or

chip-login2

If the hostname contains login, you are on a login node.

Which node should I use?

Task	Recommended Location

Task	Recommended Location

Small SQLite tutorial

Login node

Creating a small test database

Login node

Loading an existing database file

Login node

Importing a small CSV

Login node

Very large database imports

Compute node

Performance benchmarking

Compute node

Large-scale analytics

Compute node

Choose a Storage Location

Many users have a limited home directory quota.

Check your quota:

quota -s

If your home directory is nearly full or over quota, use your PI/project storage.

Example:

cd /umbc/rs/pi_<PI_NAME>/users/$USER

Verify location:

pwd

Example:

/umbc/rs/pi_example/users/username

For chip users, PI/project storage is generally recommended for database files.

Environment Setup

Check for Python

Check Python availability:

which python
python --version

Example:

/usr/bin/python
Python 3.9.25

If Python works, continue.

Verify SQLite Support

Start Python:

python

Then:

import sqlite3
print(sqlite3.sqlite_version)

Example:

3.41.2

If a version number appears, SQLite is available.

Exit Python:

quit()

For most users, no additional environment setup is required. If Python is available and the following command succeeds:

python -c "import sqlite3; print(sqlite3.sqlite_version)"

This tutorial will include two methods:

Method A: Using Python's built-in sqlite3 module
Method B: Using the SQLite command-line tool

Create a Directory

Create and enter a test/desire directory, for example:

mkdir -p db_test
cd db_test

Check that you are in the correct location:

pwd

Example output:

/umbc/rs/pi_mollar/users/shetty1/db_test

Method A: Create, Save, and Load a Database Using Python

Step A1: Check Python and SQLite Support

Run:

which python
python --version
python -c "import sqlite3; print(sqlite3.sqlite_version)"

Example output:

Python 3.9.25
3.34.1

If a SQLite version number appears, Python SQLite support is available.

No Conda environment is required if this command works.

Step A2: Start Python

Run:

python

You should see the Python prompt:

>>>

Important: only type Python code after the >>> prompt. Do not type explanation text into Python.

Step A3: Create a Database

At the Python prompt, type:

import sqlite3

Then:

conn = sqlite3.connect("test_python.db")

Then:

cur = conn.cursor()

This creates or opens a database file named test_python.db.

Step A4: Create a Table

Type:

cur.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
)
""")

Expected output will look similar to:

<sqlite3.Cursor object at ...>

Step A5: Verify the Table Was Created

Type:

cur.execute("SELECT name FROM sqlite_master WHERE type='table';")

Then:

print(cur.fetchall())

Expected output:

[('users',)]

Step A6: Insert Sample Records

Type:

cur.executemany("""
INSERT INTO users
VALUES (?, ?, ?)
""", [
    (1, "Alice", "Physics"),
    (2, "Bob", "Chemistry"),
    (3, "Carol", "Computer Science")
])

Step A7: Save the Database

Type:

conn.commit()

This saves the inserted records to the database file.

Step A8: Query the Database

Type:

cur.execute("SELECT * FROM users")

Then:

print(cur.fetchall())

Expected output:

[(1, 'Alice', 'Physics'), (2, 'Bob', 'Chemistry'), (3, 'Carol', 'Computer Science')]

Step A9: Close the Database

Type:

conn.close()

Step A10: Load the Database Again

Type:

conn = sqlite3.connect("test_python.db")

Then:

cur = conn.cursor()

This reloads the saved database file.

Step A11: Verify Saved Data After Loading

Type:

cur.execute("SELECT * FROM users")

Then:

print(cur.fetchall())

Expected output:

[(1, 'Alice', 'Physics'), (2, 'Bob', 'Chemistry'), (3, 'Carol', 'Computer Science')]

If the records appear after reopening the database, the creation, saving, and loading test was successful.

Step A12: Exit Python

Type:

quit()

or press:

Ctrl-D

Step A13: Verify the Database File Exists

Back at the Linux shell prompt, run:

ls -lh test_python.db

Example output:

-rw-r--r--+ 1 username group 8.0K Jun 11 10:00 test_python.db

Method B:

This method does not use Python. It uses the sqlite3 command-line tool.

The sqlite3 command may not be available in the default environment. In one tested chip environment, it was available after activating a Conda environment.

How to activate the conda environment: see this link how to create a virtual environment: Conda Virtual Environments

Step B1: Check Whether `sqlite3` Is Available

Run:

which sqlite3
sqlite3 --version

If sqlite3 is available, continue to Step B3.

If you see:

sqlite3: command not found

or:

no sqlite3 in ...

Then the SQLite command-line tool is not available in your current environment.

Step B2: Activate a Conda Environment If Needed

List available Conda environments:

conda info --envs

Example output:

ddp-hpcf    /umbc/rs/pi_mony/users/Sakurat1/conda_envs/ddp-hpcf
base        /usr/ebuild/installs/software/Anaconda3/2024.02-1

Activate an environment that contains sqlite3.

Example:

conda activate ddp-hpcf

Check again:

which sqlite3
sqlite3 --version

Example output:

/umbc/rs/pi_mony/users/Sakurat1/conda_envs/ddp-hpcf/bin/sqlite3
3.51.2

If this works, continue.

Step B3: Create a SQLite Database

Run:

sqlite3 test_cli.db

You should enter the SQLite prompt:

sqlite>

Important: only type SQL commands at the sqlite> prompt.

Step B4: Create a Table

At the sqlite> prompt, type:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);

Press Enter.

Step B5: Insert Sample Records

Type:

INSERT INTO users VALUES (1, 'Alice', 'Physics');

Then:

INSERT INTO users VALUES (2, 'Bob', 'Chemistry');

Then:

INSERT INTO users VALUES (3, 'Carol', 'Computer Science');

SQLite saves these changes to the database file automatically.

Step B6: Query the Database

Type:

SELECT * FROM users;

Expected output:

1|Alice|Physics
2|Bob|Chemistry
3|Carol|Computer Science

Step B7: Exit SQLite

Type:

.quit

You should return to the Linux shell prompt.

Step B8: Verify the Database File Exists

Run:

ls -lh test_cli.db

Example output:

-rw-r--r--+ 1 username group 8.0K Jun 11 10:10 test_cli.db

Step B9: Load the Database Again

Run:

sqlite3 test_cli.db

This loads the existing database file.

At the sqlite> prompt, query the table again:

SELECT * FROM users;

Expected output:

1|Alice|Physics
2|Bob|Chemistry
3|Carol|Computer Science

This confirms the database was saved and loaded successfully.

Exit SQLite:

.quit

You have successfully created, saved, and loaded a SQLite database on chip using either Python's built-in sqlite3 module or the sqlite3 command-line tool. You also verified that the database file persists after closing and reopening it.

Overall Tested Operations

Operation	Python Method	SQLite CLI Method

Operation	Python Method	SQLite CLI Method
Create database file	`sqlite3.connect("test_python.db")`	`sqlite3 test_cli.db`
Create table	`CREATE TABLE` through Python	`CREATE TABLE` at `sqlite>` prompt
Insert records	`cur.executemany(...)`	`INSERT INTO ...`
Save records	`conn.commit()`	Auto-saved by SQLite CLI
Close database	`conn.close()`	`.quit`
Load database	Reconnect with `sqlite3.connect(...)`	Reopen with `sqlite3 test_cli.db`
Query saved records	`SELECT * FROM users`	`SELECT * FROM users;`

Database Creation, Saving, and Loading on chip

Overview

Which node should I use?

Choose a Storage Location

Environment Setup

Check for Python

Verify SQLite Support

Create a Directory

Method A: Create, Save, and Load a Database Using Python

Step A1: Check Python and SQLite Support

Step A2: Start Python

Step A3: Create a Database

Step A4: Create a Table

Step A5: Verify the Table Was Created

Step A6: Insert Sample Records

Step A7: Save the Database

Step A8: Query the Database

Step A9: Close the Database

Step A10: Load the Database Again

Step A11: Verify Saved Data After Loading

Step A12: Exit Python

Step A13: Verify the Database File Exists

Method B:

Step B1: Check Whether sqlite3 Is Available

Step B2: Activate a Conda Environment If Needed

Step B3: Create a SQLite Database

Step B4: Create a Table

Step B5: Insert Sample Records

Step B6: Query the Database

Step B7: Exit SQLite

Step B8: Verify the Database File Exists

Step B9: Load the Database Again

Overall Tested Operations

Step B1: Check Whether `sqlite3` Is Available