4

Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?

I'm building and repeatedly rebuilding an SQLite database and need to type in roughly twenty four dot command statements every time I rebuild the database. It would be really nice if I could put those commands in a script and have SQLite read them.

Is there a way to put dot commands ( not SQL statements ) into a script and have SQLite run them?

I'm on Mac OS X using bash.

4

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
2

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 .commandsand 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.

  • For anyone following in my footsteps.. I initially could not get .read to work. The problem for me was the script file path had spaces in it. It's easier to use the pipe symbol (cmd < script). But you can also escape every \ and " with \\ and \" respectively and get this to work too. – JGFMK May 15 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.