How to use the command "sqlite3" (with examples)
The command “sqlite3” is the command-line interface to SQLite 3, which is a self-contained file-based embedded SQL engine. It allows users to interact with a SQLite database through an interactive shell or execute SQL statements against a database directly from the command line.
Use case 1: Start an interactive shell with a new database
Code:
sqlite3
Motivation: Starting an interactive shell with a new database allows users to create a new SQLite database from scratch and immediately start interacting with it. This is useful when you want to create a new database and quickly add tables, insert data, or run SQL queries.
Explanation: In this use case, the command “sqlite3” is used without any arguments. This opens an interactive shell with a new in-memory SQLite database. The user can then execute SQLite commands directly in the shell.
Example output:
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>
Use case 2: Open an interactive shell against an existing database
Code:
sqlite3 path/to/database.sqlite3
Motivation: Opening an interactive shell against an existing database allows users to connect to an existing SQLite database and perform various operations on it. This is useful when you want to access an existing database, modify its schema or data, or run queries against it.
Explanation: In this use case, the command “sqlite3” is used with the argument “path/to/database.sqlite3”, which specifies the path to the existing SQLite database file. This opens an interactive shell connected to the specified database.
Example output:
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>
Use case 3: Execute an SQL statement against a database and then exit
Code:
sqlite3 path/to/database.sqlite3 'SELECT * FROM some_table;'
Motivation: Executing an SQL statement against a database and then exiting allows users to quickly retrieve data or perform operations on an existing database without the need to enter into an interactive shell. This is useful when you want to automate certain tasks or incorporate SQLite commands in a shell script.
Explanation: In this use case, the command “sqlite3” is used with the argument “path/to/database.sqlite3”, which specifies the path to the existing SQLite database file, and the SQL statement “SELECT * FROM some_table;”. This executes the provided SQL statement against the specified database and then exits, returning the result of the statement to the standard output.
Example output:
column1 column2 column3
-------- -------- --------
value1 value2 value3
Conclusion:
The “sqlite3” command provides a flexible and convenient way to interact with SQLite databases from the command line. Whether you need to start an interactive shell with a new database, open an interactive shell against an existing database, or execute SQL statements directly against a database, the “sqlite3” command has got you covered. Its simplicity and versatility make it a valuable tool for working with SQLite databases efficiently.
