sqlite dot commands variable expansion like bash

Question

I have a loop in bash that sets some pathnames as variables.

Within that loop I want to perform some sqlite commands based on these variables.

for example:

sqlitedb="/Users/Documents/database.db"
for mm in 01 02 03; do
filename1="A:/data1-${mm}.csv"
filename2="D:/data2-${mm}.csv"
sqlite3 "$sqlitedb" #create new file, it is a temporary file.  no problem with this command.
.mode csv
.import "$filename1" data_1 #try to import the first data file.  This command doesn't work
.import "$filename2" data_2 #try to import the second data file.  This command doesn't work
# now do some important sql stuff which joins these files.
.quit
rm -f "$sqlitedb" #remove old file, ready for the next loop
done

Clearly, SQLITE doesn't know about my BASH variables. What is the best way to set variables, loop through files, etc within sqlite3?

If it helps, I'm using WSL ubuntu 18.04

This will not work as written, because `sqlite3` will read its commands from standard input (generally the Terminal) rather than the script file. You'd need something like a [here-document](https://stackoverflow.com/questions/2953081/how-can-i-write-a-heredoc-to-a-file-in-bash-script) to give input to the `sqlite3` command, and shell variables *are* substituted in here-documents (unless the delimiter is quoted). Please post a [minimal example that actually shows the problem](https://stackoverflow.com/help/minimal-reproducible-example). — Gordon Davisson, Jun 01 '20 at 03:43

score 1 · Accepted Answer · answered Jun 01 '20 at 08:04

1

You need a heredoc, as mentioned in comments:

for mm in 01 02 03; do
    filename1="A:/data1-${mm}.csv"
    filename2="D:/data2-${mm}.csv"
    sqlite3 -batch -csv <<EOF
.import "$filename1" data_1
.import "$filename2" data_2
-- Do stuff with the tables
EOF
done

(If you leave off a filename, sqlite uses a in-memory database so you don't need a manual temporary database file unless you have a lot of data to store)

answered Jun 01 '20 at 08:04

Shawn

47,241
3
26
60

checked and works perftecly. I was not aware of the `-batch` switch, but it seems just what I need. I still don't understand why the variable expansion works, even in the heredoc; the `.import` command is part of sqlite3 interpreter, so I didn't expect it to be aware of shell variables. Is it because interactive mode is switched off via the `-batch` switch? – Tim Jun 02 '20 at 01:10

sqlite dot commands variable expansion like bash

1 Answers1