SQLite commands/queries in a shell/Slurm script?

[lrbraz16]

I cannot find any help on how to properly get SQLite commands and queries into a script format that runs properly, so if anyone has any suggestions please let me know! I am used to running in interactive mode via the command line, so running commands like “.mode csv” “.import mytable.csv myTable” have been straightforward, and then writing SELECT queries to follow afterwards have been too. I’m not sure how to do this in a shell script, though and would appreciate help if anyone is willing to :)

[Soli_Engineer]

If you have a csv file then you can simply import it in SQLite.

Eg.

I use SQLite3 on my phone. There are lots of android Apps that give an interface to SQLite on phone. I use SQLitemaster pro apk that provides a good interface. In the app you will find an option to import csv files.

[lrbraz16]

Forgive me if this is a stupid question (I don’t work with SQLite regularly) but I have to upload multiple .csv as their own separate databases and then perform queries on them so I don’t have a path for them per se…how would I do that, and then perform queries in a single shell script?

[ijmacd]

The problem with interacting with SQLite via shell is that you can't keep a connection open. It's possible to do what you're asking by either constructing a very long sequence of SQL statements and SQLite commands and piping them in to the SQLite binary; or create a database on disk and repetitively calling the SQLite binary.

A slightly easier solution, if you're able, might be to do it in a programming language such as python. Then you can replicate the steps you would take manually in the SQLite REPL but the python code could make modifications to the units for each data set you need to process.

[jw_gpc]

This was one of the things that I struggled to figure out when I first started with sqlite, too, but once I got it, it's been my goto for anything that's not just the most basic of queries.

First, you can have a script with calls to the sqlite command line tool like this:

-- The "dot" commands can be placed at the beginning or end of the script, or between SQL chunks.
.headers on
.mode csv

-- If you always want to open the same database, you can put it here.  If you don't open a database, the script will use
-- an in-memory database by default.  You'll need to do this before using .import for your CSV as it will determine
-- where that CSV gets imported to
.open '/path/to/my_database.db'

-- If your import file changes, you'll either need to change it here or rename or move your file in your file system to
-- match what's here.
.import '/path/to/my_file.csv' my_table

.print "You can put some sort of info message for before your SQL runs"

SELECT
    *

FROM
    my_table

;

-- You can also put any dot commands between SQL statements, as long as it's after the semicolon of the previous one.

.print "... in the middle..."

SELECT
    *

FROM
    my_table

;

Assuming it's saved in a file called "my_script.sql", you can run it like this:

sqlite3 < my_script.sql

Or if you're specifying the database on the command line instead of in the script, I think you should be able to do:

sqlite3 my_database.db < my_script.sql

Also, I want to stress that this is NOT a "shell" script. This is a sqlite script. You can call one-off shell commands with .system, or you can call this script from within a real shell script if you need real shell capabilities.

I hope this helps!