Using the 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.
.tables
are prefixed with a dot. select * from AllstarFull
end with a semicolonUseful commands are:
.tables
: Shows all tables in the db..timer ON
: times how long an operation takes between .timer ON
and .timer OFF
.schema
shows the schema of all the tables in the dbWe 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.
The SQLite
website offers a few cases when SQLite probably is not the best choice.