sqlite3 CLI can often be a good alternative to python-based solutions when the operations we are performing is easy to express directly with SQL.
.tablesare prefixed with a dot.
select * from AllstarFullend with a semicolon
Useful commands are:
.tables: Shows all tables in the db.
.timer ON: times how long an operation takes between
.schemashows the schema of all the tables in the db
We can dump files to csv via the following syntax
.mode CSV .headers on .output table.csv
We can run an SQL script from the command line using the following syntax
sqlite3 database.db < sql_script.sql
After dropping a table, you may have to use
VACUUM; to reclaim the space that the dropped table was using.
When dealing with tables that are large compared to the space on disk it’s possible to get an error that says:
Error: near line 41: database or disk is full even though the disk isn’t full and even though the database is far smaller than the 120TB size limit.
This could be because either 1) the temporary file directory is filling up or 2) the database is corrupted.
In the case of (1), the solution is either to tell SQLite to put everything in ram via
sqlite> prgrama temp_store = 2;. If you don’t have enough ran for the operation, you can change the temporary directory with the following code:
sqlite> pragma temp_store = 1; sqlite> pragma temp_store_directory = '/spacious/hdd/data';
In the case of (2) hope is still not lost. Search the internet for ways to repair a corrupt SQLite database.
SQLite is much easier to work with than other RBDMS because it is serverless, requires few external libraries, and requires no configuration.
I think of SQLite as being the Python of databases. If you are using an RBDMS for a project, you should have a good reason not to use SQLite. On websites it can handle 100k hits/day with ease and 500k is probably a more realistic upper bound.
SQLite website offers a few cases when SQLite probably is not the best choice.