/
Common SQLite Commands and Common Errors

Common SQLite Commands and Common Errors

Command

Description

.help

Display all available SQLite commands.

.tables

List all tables in the database.

.schema

Display the SQL schema (table definitions).

.databases

Show attached databases and file locations.

.headers on

Display column names in query results.

.mode column

Format query results in columns for readability.

.indexes

List indexes defined in the database.

.dump

Export the database contents as SQL statements.

.backup FILE

Create a backup of the database.

.restore FILE

Restore a database from a backup file.

.import FILE TABLE

Import data from a file into a table.

.excel

Open query results in a spreadsheet.

.stats on

Display database performance statistics.

.timer on

Display execution time for SQL statements.

.version

Show SQLite version information.

.quit

Exit SQLite and return to the Linux shell.

Common SQL Queries

Display all records from a table:

SELECT * FROM users;

Display selected columns:

SELECT id, name FROM users;

Count the number of rows:

SELECT COUNT(*) FROM users;

Example Session

sqlite3 test.db
.tables .schema .headers on .mode column SELECT * FROM users; .quit

Common Errors and Troubleshooting

Depending on how users interact with the database on chip, errors may appear in different places:

  • Python errors will appear when running a Python script (python database.py) or while using the interactive Python interpreter.

  • Linux command-line (CLI) errors will appear directly in the terminal when executing commands such as mkdir, cd, pwd, or attempting to use unavailable software.

  • Storage and permission errors may occur when creating or writing database files in locations where you do not have sufficient permissions or when your home directory storage quota is full.

Errors

Error or Message

Where the user would see it

Cause

Solution

Error or Message

Where the user would see it

Cause

Solution

bash: import: command not found

Linux shell/terminal

Python code was typed in the Linux shell

Run python first, then type Python code at the >>> prompt

SyntaxError: invalid syntax

Python prompt

Explanation text or shell commands were typed into Python

Only type Python code at the >>> prompt

which sqlite3 gives SyntaxError

Python prompt

A shell command was typed inside Python

Exit Python using quit(), then run which sqlite3 in the Linux shell

sqlite3: command not found

Linux shell/terminal

SQLite CLI is not available in the current environment

Use Method A or activate a Conda environment that provides sqlite3

EnvironmentNameNotFound

Linux shell/terminal after conda activate ...

Conda environment name does not exist

Run conda info --envs and activate an existing environment

sqlite3.OperationalError: no such table: users

Python script or Python prompt

Table was not created before querying

Run the CREATE TABLE command first

[] when checking tables

Python prompt after querying sqlite_master

Database exists but contains no tables

Create a table first

Data missing after reopening

Python method

Changes were not committed before closing

Run conn.commit() before conn.close()

table users already exists

Python prompt or SQLite CLI

Table was created during a previous test

Delete the old database file or use a new database name

UNIQUE constraint failed

Python prompt or SQLite CLI during insert

Duplicate primary key values were inserted

Use a new database file or unique IDs

disk quota exceeded

Python, SQLite CLI, or Linux shell while writing files

Home directory is full

Use PI/project storage instead of the home directory

unable to open database file

Python prompt or script

Incorrect path or insufficient permissions

Check pwd, ls -ld ., and write permissions

No such file or directory

Linux shell/terminal

Directory path does not exist

Create the directory with mkdir -p

No module(s) found

Linux shell/terminal after module load ...

Requested software module is unavailable

Use Python sqlite3 or an existing Conda environment

Notes for Users:

  • SQLite databases are normal files ending in .db.

  • Using PI/project storage is recommended if the home directory is full.

  • For beginner tests, the login node is acceptable.

  • For large database work, use a compute node.