It isn’t that SQLite itself is not user-friendly for data exploration, just the command-line interface is not.
The reason I say this is because when you are exploring the data, you want to be able to see it in a way that allows you to easily notice patterns, etc. On the command line, the query outputs are often not formatted in a way that is easy to read. You can change the mode to
column, which is fine for smaller query results, but I believe column also has a character limit, so anything in a column that is above that limit will be cut off.
I like to use SQLite on the command line when I don’t have to actually look at the data. When I am looking through it I usually gravitate toward one of three tools:
- DB Browser (codecademy video here)
- when I am only doing SQL and I want to explore/create/edit a new database
- VS Code
- when I am only running SQL queries and doing mild database exploration
- Jupyter Notebooks
- when I am doing data exploration and analysis that will involve Python
If you are doing a project that involves using data from SQL queries and doing analysis, etc., in Python then Jupyter notebooks are the way to go. You can use Python’s sqlite3 module to query the database, then save the query results as Pandas DataFrames, which look great on Jupyter Notebooks. Check it out and see if it fits with your desired workflow!