SQLite is a powerful and widely used database engine. The sqlite-utils command-line tool provides a convenient way to manipulate SQLite databases in various ways. This article provides examples of different use cases for the sqlite-utils command, demonstrating how to create a database, create tables, insert and select records, delete records, drop tables, and more.

Use case 1: Create a database

Code:

sqlite-utils create-database path/to/database.db

Motivation:

Creating a database is the first step in working with SQLite. This command allows you to create a new SQLite database file at the specified path.

Explanation:

• create-database: This is the command to create a new database.
• path/to/database.db: This is the path where the new database file will be created.

Example OUTPUT:

Database created successfully at path/to/database.db

Use case 2: Create a table

Code:

sqlite-utils create-table path/to/database.db table_name id integer name text height float photo blob --pk id

Motivation:

Tables are used to store structured data in a database. This command allows you to create a new table in the specified database.

Explanation:

• create-table: This is the command to create a new table.
• path/to/database.db: This is the path to the database where the table will be created.
• table_name: This is the name of the table that you want to create.
• id integer name text height float photo blob: These are the columns of the table. Each column is specified with its name and data type.
• --pk id: This specifies the primary key column of the table.

Example OUTPUT:

Table 'table_name' created successfully in database path/to/database.db

Use case 3: List tables

Code:

sqlite-utils tables path/to/database.db

Motivation:

Sometimes it is necessary to get an overview of the tables present in a database. This command allows you to list all the tables in the specified database.

Explanation:

• tables: This is the subcommand to list all the tables in a database.
• path/to/database.db: This is the path to the database from which you want to list the tables.

Example OUTPUT:

Available tables in database path/to/database.db:

- table1
- table2
- table3

Use case 4: Upsert a record

Code:

echo '[ {"id": 1, "name": "Linus Torvalds"}, {"id": 2, "name": "Steve Wozniak"}, {"id": 3, "name": "Tony Hoare"} ]' | sqlite-utils upsert path/to/database.db table_name - --pk id

Motivation:

When working with databases, there is often a need to insert or update records. This command allows you to upsert (insert or update) multiple records into the specified table of the database.

Explanation:

• upsert: This is the subcommand to upsert records.
• path/to/database.db: This is the path to the database in which the table exists.
• table_name: This is the name of the table where you want to upsert the records.
• -: This specifies that the input data will be read from the standard input.
• --pk id: This specifies the primary key column of the table.

Example OUTPUT:

3 records upserted into table 'table_name' in database path/to/database.db

Use case 5: Select records

Code:

sqlite-utils rows path/to/database.db table_name

Motivation:

Fetching records from a table is a common task when working with databases. This command allows you to select all records from the specified table of the database.

Explanation:

• rows: This is the subcommand to select records.
• path/to/database.db: This is the path to the database from which you want to select records.
• table_name: This is the name of the table from which you want to select records.

Example OUTPUT:

Selected records from table 'table_name' in database path/to/database.db:

- id: 1, name: Linus Torvalds
- id: 2, name: Steve Wozniak
- id: 3, name: Tony Hoare

Use case 6: Delete a record

Code:

sqlite-utils query path/to/database.db "delete from table_name where name = 'Tony Hoare'"

Motivation:

Sometimes it is necessary to remove specific records from a table. This command allows you to delete records from the specified table of the database based on a condition.

Explanation:

• query: This is the subcommand to execute a SQL query.
• path/to/database.db: This is the path to the database from which you want to delete records.
• "delete from table_name where name = 'Tony Hoare'": This is the SQL query that deletes records from the table where the name is ‘Tony Hoare’.

Example OUTPUT:

1 record deleted from table 'table_name' in database path/to/database.db

Use case 7: Drop a table

Code:

sqlite-utils drop-table path/to/database.db table_name

Motivation:

Dropping a table is a common operation when you no longer need a table or want to delete all records. This command allows you to drop the specified table from the database.

Explanation:

• drop-table: This is the subcommand to drop a table.
• path/to/database.db: This is the path to the database from which you want to drop the table.
• table_name: This is the name of the table you want to drop.

Example OUTPUT:

Table 'table_name' dropped successfully from database path/to/database.db

Use case 8: Show help information

Code:

sqlite-utils -h

Motivation:

When working with a new command or trying to understand its usage, it is helpful to have access to the command’s documentation. This command allows you to view the help information of the sqlite-utils command.

Example OUTPUT:

Usage: sqlite-utils [OPTIONS] COMMAND [ARGS]...

  Command-line tool used to manipulate SQLite databases in a number of
  different ways.

Options:
  -h, --help  Show this message and exit.

Commands:
  create-database  Create a new SQLite database.
  create-table     Create a new table.
  drop-table       Drop an existing table.
  query            Execute a SQL query.
  rows             Display rows from a table.
  tables           Show all tables in a database.
  upsert           Upsert records into a table.

Conclusion:

The sqlite-utils command-line tool provides a flexible and convenient way to work with SQLite databases. With its various subcommands, you can create databases and tables, insert and select records, delete records, and drop tables. By understanding and utilizing the examples provided in this article, you will be able to effectively use the sqlite-utils command for your SQLite database manipulation needs.