Scripting SQLite with dot commands
I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.
The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script.
First Method:
sqlite3 database.db < commands.txt
Second Method:
#!/bin/bash --
sqlite3 -batch $1 <<"EOF"
CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import logfile.log log_entry
EOF
And then on the command line:
import.sh database.db
As an alternative, which I consider simpler, you can just use the .read
command. From the command line of your OS (terminal in Mac OS X), type:
sqlite3 yourdatabase.db ".read script_full_of_dot_commands"
It will start sqlite3
, use the mentioned database, perform all of the commands in script_full_of_dot_commands
, and leave.
For instance, let's assume you have the following files:
script_full_of_dot_commands: A file with both .commands
and SQL commands
:
.read file.sql
.separator ","
.import file.csv t
.headers on
.separator "|"
SELECT name, a FROM t WHERE a < 2 ;
SELECT count(*) FROM t2;
file.sql: A file with just SQL commands.
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS t2;
CREATE TABLE t (a integer PRIMARY KEY, name varchar);
CREATE TABLE t2 (b integer PRIMARY KEY);
file.csv: a data file to fill a table
1,One
2,Two
3,Three
4,Four
The result of performing
sqlite3 yourdatabase.db ".read script_full_of_dot_commands"
is
name|a
One|1
count(*)
0
Check also: Running a Sqlite3 Script from Command Line from StackOverflow.