36

I see plenty of examples showing how to use the sqlite3 interactive shell, e.g.:

$ sqlite3
$ sqlite3> SELECT * from x;

but I am looking for a way to create a table in a SQLite3 database with a bash script, aka, non-interactively.

For example, the following doesn't seem to work (compare last 2 characters with accepted answer), it remains interactive:

#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n";
Improve this question
4
  • For example create table tbl1(one varchar(10), two smallint); Commented Feb 15, 2017 at 9:51
  • 2
    There is nothing to wake up, SQLite3 does not use a server/daemon. Commented Feb 15, 2017 at 10:02
  • thanks for that info. good to know. on a separate note, my answer actually doesn't work - it's still interactive. Commented Feb 15, 2017 at 10:13
  • 2
    Try moving your final semicolon inside the double quote, instead of outside. Commented Apr 22, 2023 at 2:16

4 Answers 4

Reset to default
37

Looks like it's as simple as

#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n;"

(last 2 characters swapped) from https://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

ok, I confirmed that this works - this is non-interactive, as long as test.db exists
In the last command, you might have intended the semi-colon needs to be inside the quotes, otherwise it's a command to Bash (which, in this case, does nothing).
thanks Paddy, I fixed that semicolon placement
32

While the above should work, I think it is better not to have to invoke sqlite3 multiple times, therefore I think the following is preferable:

#!/bin/sh
sqlite3 test.db <<EOF
create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);
insert into n (f,l) values ('john','smith');
select * from n;
EOF

Note that unless you really need to use bash, you should prefer "/bin/sh" as your shebang, for portability reasons.

Improve this answer

11 Comments

I do not think you should ever prefer /bin/sh as shebang: usually sh is symlinked to bash so that any code that is valid in bash but not in sh will not be noticed until finally someone runs your code on some eon-old platform (Solaris 9, for example) which does really have a difference between sh and bash and it will throw errors.
@Cheiron: Your point about unnoticed bashisms in a script with a /bin/sh shebang is well-taken, but I still disagree. I don't know offhand how common it is in the Linux word to symlink /bin/sh to /bin/bash, but it isn't universal, e.g, in Lubuntu (the only Linux system handy to me at the moment), /bin/sh is symlinked to /bin/dash, not /bin/bash. More importantly, all the world in not Linux. Apart from "eon-old" platforms, modern, very much in use systems, like the one I use all the time, FreeBSD, simply do not have /bin/bash. I stand by my recommendation.
is the FreeBSD argument not an argument for /bin/bash? Dash is not sh, bash is not sh and zsh is not sh, so I think one should pick the shebang one is actually working with. If I create a bash script and put /bin/sh above it, I can not actually test wether or not it runs on sh since /bin/sh is symlinked to /bin/bash on my system, so I should put /bin/bash as shebang, right?
I dont have 30 years of experience, but I don't dare assume that my bash just runs on sh without being able to check. I also do not think that learning your craft is somehow equal to making no mistakes, especially not when tired. I do like portability, but I need at least one test system for every supported platform or I cannot confirm wether or not the code is actually supported everywhere. So as long as I dont have sh, i prefer using /bin/bash as to not give false promises: I can only test for bash so I put /bin/bash.
@MikeS So why can I write /bin/sh as shebang and then use [[? [[ does not exist in sh. So effectively I am lying when sh is the shebang, since it will only run if you symlinked the sh to a bash-like shell. That is my point here: sh is obsolete and confusing at this point so stick to the shell you know it will work on.
|
12

You can disable interactive mode using the -batch option:

sqlite3 -batch test.db "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
Improve this answer

3 Comments

Does not work using cmd internal commands, like as sqlite3 -separator ',' -cmd ".import file.csv' tmp" -batch tmp.db.
@e-info128 not sure if you got the answer to this already but cat << EOF | sqlite3 tmp.db <newline> .import file.csv tmp EOF works for me.
@e-info128 fix your quotes
4

This works too

sqlite3 "${DB_FILE_NAME}" < data_model.sql
sqlite3 "${DB_FILE_NAME}" < dml.sql
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.