Geek Answers Handbook

Can I run a command line script in SQLite?

Can I run a script with SQL statements and. SQLite Commands?

+5
source share
4 answers

Mixing SQL statements with SQLite .commandscan be a bit complicated:

$ sqlite3 test.db 'create table X(x integer); .dump'
Error: near ".": syntax error

The easiest and most versatile way to handle this is to provide all the SQLite3 command line standard input commands separated by newline characters as you typed them:

$ sqlite3 test.db <<EOF
> CREATE TABLE TEST(X INTEGER);
> .dump
> EOF
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;

Since documents are not available in all shells here, you can use an intermediate file to circumvent this limitation:

$ cat test.sql
CREATE TABLE TEST(X INTEGER);
.dump
$ sqlite3 test.db <test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;
+10
source

This query works for me:

sqlite3 test.db "select * from sometable"
+2
source

. .read , .

script:

sample.sql

DROP TABLE IF EXISTS test;
CREATE TABLE test (id PRIMARY KEY,
                   rand1 REAL DEFAULT (RANDOM()),
                   rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);
.dump

script:

$ sqlite3 test.db '.read sample.sql'
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (id PRIMARY KEY, rand1 REAL DEFAULT (RANDOM()), rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test VALUES(1,-479816953303475072.01,7897905953557517311.9);
INSERT INTO test VALUES(2,3449088292611145728.0,-595585280596709760.01);
COMMIT;
+1
echo 'create table X(x integer); .dump' | sqlite3 test.db
-1
source

More articles:


All Articles