Running a Sqlite3 Script from Command Line

Question

I am writing a shell script to scp a project and part of it involves transferring some important tables in my database.

I have the following stored in the file SQLTableTransfer:

.o MyTable1.sql;
.dump MyTable1;

.o MyTable2.sql;
.dump MyTable2;

.o MyTable3.sql;
.dump MyTable3;

And took a chance on

$ sqlite3 SQLTableTransfer

But this just opened the Sqlite3 shell. What is the correct way to run a script like this from the command line?

Answer 1

The parameter you give to the sqlite3 program is the database file name.

To execute commands from a file, you must redirect the input to that file:

$ sqlite3 mydatabase.db < SQLTableTransfer

or tell it to read from that file:

$ sqlite3 mydatabase.db ".read SQLTableTransfer"

Answer 2

You can get a list of the spatial tables as follows:

echo "SELECT f_table_name FROM geometry_columns;" | spatialite -noheader -silent your_db.sqlite

Answer 3

For the lazy who want to just dump this into their .bashrc:

### Execute an sqlite3 file on a given db
sql3-exec () {
  # TODO: write a  --help flag that doubles as error handling
  # TODO: Ensure that $1 is a db; Else display --help
  # TODO: Ensure that $2 is a .sql file; Else display --help
  # TODO: Probably store a backup (or at least a flag)...
  sqlite3 $1 ".read $2"
  true
}

Answer 4

For Windows CLI, assuming your database is loaded:

sqlite> .read C:\\somesubdir\\some.sql