How can I execute SpatiaLite commands directly from the command line?

Question

I know I can open a SpatiaLite database and export a CSV from it as the following:

.headers on
.mode csv
.output csvFile.csv
select * from table
.output stdout

However, I'd like to automate the process of exporting CSV files from SpatiaLite with a bash script. Is there any way to execute this same sequence of commands from a shell? Just so I don't need to manually enter the database all the times I want to export data from it?

Alternatively, is there any way of exporting the result of a query inside my SpatiaLite database to a csv file directly? Without the need of using the commands that I used in my example?

Answer 1

In Bash, you can feed a series of lines to the stdin of a process using a "here document":

#!/bin/bash

sqlite3 test.db <<EOF
.headers on
.mode csv
select 1 as x, 2 as y, 3 as z;
EOF

which goes:

$ ./test.sh 
x,y,z
1,2,3

Everything between <<EOF and the EOF is fed to the process, as if you typed it. You can also put variables inside the document and expand them from the script.

#!/bin/bash

firstname=$1

sqlite3 test.db <<EOF
.headers on
.mode csv
select 1 as $firstname, 2 as y, 3 as z;
EOF

then you can do:

$ ./test.sh this
this,y,z
1,2,3

This is all standard Bash programming stuff, nothing special to spatialite here (I'm using sqlite3 but it will work in spatialite, I wasn't sure I had it installed). Consult a bash programming guide for more.

Answer 2

spatialite -header -csv data.sqlite "select * from table" > file.csv