sqlite3-cli-manager

This tool is designed to interact with SQLite databases via command-line interface. Other than just issuing the sql staments by yourself, you can let AI generate sql statements based on your prompt.

Pre-requisite

• Python>=3.12 (optional)

Installation and Usage

Installation

• Clone repo and install requirements

git clone https://github.com/Simatwa/sqlite3-cli-manager.git
cd sqlite3-cli-manager
pip install -r requirements.txt

Alternatively, you can download standalone executables for your system from here.

Usage

$ python manager.py --help

Usage: manager.py [OPTIONS] COMMAND [ARGS]...

  Interact with SQLite databases via command-line interface

Options:
  --version  Show the version and exit.
  --help     Show this message and exit.

Commands:
  execute       Run sql statements against database [AUTO-COMMITS]
  interactive   Execute sql statements interactively
  show-columns  List columns for a particular table
  show-tables   List tables contained in the database

Execute

• The execute command accepts multiple sql statements and run each against the database before auto-commiting the changes.

$ python manager.py execute --help

Usage: sqlite-manager execute [OPTIONS] DATABASE

  Run sql statements against database [AUTO-COMMITS]

Options:
  -s, --sql TEXT  Sql statements  [required]
  -j, --json      Stdout results in json format
  -q, --quiet     Do not stdout results
  --help          Show this message and exit.

$ sqlite-manager execute <path-to-sqlite3-database> -s "<sql-statement>"

For example:

$ python manager execute test.db -s "select * from linux"

┏━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ Index ┃ Col. 1 ┃ Col. 2 ┃ Col. 3    ┃ Col. 4 ┃ Col. 5 ┃ Col. 6     ┃ Col. 7     ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│   0   │ 1      │ Parrot │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:22:13   │ 13:22:13   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   1   │ 2      │ Kali   │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:22:21   │ 13:22:21   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   2   │ 3      │ Ubuntu │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:48:18   │ 13:48:18   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   3   │ 4      │ Fedora │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:48:49   │ 13:48:49   │
└───────┴────────┴────────┴───────────┴────────┴────────┴────────────┴────────────┘

---

Interactive

• The interactive command launches a recursive prompt that takes in sql statements and proceed to run them against the database.

$ python manager.py interactive --help

Usage: manager.py interactive [OPTIONS] DATABASE

  Execute sql statements interactively

Options:
  -c, --color TEXT           Results font color
  -j, --json                 Stdout results in json format
  -a, --auto-commit          Enable auto-commit
  -C, --disable-coloring     Stdout prompt text in white font color
  -S, --disable-suggestions  Do not suggest sql statements
  -N, --new-history-thread   Start a new history thread
  --help                     Show this message and exit.

$ sqlite-manager execute <path-to-sqlite3-database> -s "<sql-statement>"

For example:

$ python manager.py interactive test.db

Welcome to interactive sqlite3-db manager.
Run help or h <command> for usage info.
Repo : https://github.com/Simatwa/sqlite3-cli-manager
 
╰─>select * from Linux
┏━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ Index ┃ Col. 1 ┃ Col. 2 ┃ Col. 3    ┃ Col. 4 ┃ Col. 5 ┃ Col. 6     ┃ Col. 7     ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│   0   │ 1      │ Parrot │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:22:13   │ 13:22:13   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   1   │ 2      │ Kali   │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:22:21   │ 13:22:21   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   2   │ 3      │ Ubuntu │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:48:18   │ 13:48:18   │
├───────┼────────┼────────┼───────────┼────────┼────────┼────────────┼────────────┤
│   3   │ 4      │ Fedora │ community │ None   │ 1      │ 2024-11-07 │ 2024-11-07 │
│       │        │        │           │        │        │ 13:48:49   │ 13:48:49   │
└───────┴────────┴────────┴───────────┴────────┴────────┴────────────┴────────────┘
╭─[Smartwa@localhost](smartBet2.db)~[🕒16:55:56-💻00:00:03-⚡-3.9s] 
╰─>select * from 
                  select * from Linux  
                  select * from sqlite_schema
                  select * from sqlite_temp_schema

---

Show-columns

• The show-columns command lists the columns for a particular table in the database.

$ python manager.py show-columns --help

Usage: sqlite-manager show-columns [OPTIONS] DATABASE TABLE

  List columns for a particular table

Options:
  -j, --json  Stdout results in json format
  --help      Show this message and exit.

$ python manager.py show-columns <path-to-sqlite3-database> <table-name>"

For example:

$ python manager.py show-columns test.db linux

┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━┓
┃ Index ┃ Col. 1 ┃ Col. 2        ┃ Col. 3        ┃ Col. 4 ┃ Col. 5       ┃ Col. 6 ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━┩
│   0   │ 0      │ id            │ INTEGER       │ 0      │ None         │ 1      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   1   │ 1      │ distro        │ TEXT          │ 1      │ None         │ 0      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   2   │ 2      │ org           │ TEXT          │ 0      │ 'community'  │ 0      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   3   │ 3      │ logo          │ BLOB NULLABLE │ 0      │ None         │ 0      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   4   │ 4      │ is_maintained │ BOOLEAN       │ 0      │ 1            │ 0      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   5   │ 5      │ updated_on    │ TIMESTAMP     │ 0      │ CURRENT_TIM… │ 0      │
├───────┼────────┼───────────────┼───────────────┼────────┼──────────────┼────────┤
│   6   │ 6      │ created_at    │ TIMESTAMP     │ 1      │ CURRENT_TIM… │ 0      │
└───────┴────────┴───────────────┴───────────────┴────────┴──────────────┴────────┘

---

Show-tables

• The show-tables command lists the available tables across the entire database.

$ python manager.py show-tables --help

Usage: manager.py show-tables [OPTIONS] DATABASE

  List tables contained in the database

Options:
  -j, --json  Stdout results in json format
  --help      Show this message and exit.

$ python manager.py show-tables <path-to-sqlite3-database>

For example:

$ python manager.py show-tables test.db

┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ Index ┃ Col. 1 ┃ Col. 2             ┃ Col. 3 ┃ Col. 4 ┃ Col. 5 ┃ Col. 6 ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│   0   │ main   │ sqlite_sequence    │ table  │ 2      │ 0      │ 0      │
├───────┼────────┼────────────────────┼────────┼────────┼────────┼────────┤
│   1   │ main   │ Linux              │ table  │ 7      │ 0      │ 0      │
├───────┼────────┼────────────────────┼────────┼────────┼────────┼────────┤
│   2   │ main   │ sqlite_schema      │ table  │ 5      │ 0      │ 0      │
├───────┼────────┼────────────────────┼────────┼────────┼────────┼────────┤
│   3   │ temp   │ sqlite_temp_schema │ table  │ 5      │ 0      │ 0      │
└───────┴────────┴────────────────────┴────────┴────────┴────────┴────────┘

Contrubutions

Contributions are always welcoming. Consider implementing new feature or fixing my bad code.

ToDo

1. Let AI generate sql statements based on user's prompt.