How to INSERT INTO SQLite database using Bash?

Question

I have created a database - tasks.db - with SQLite. This database has one table - todo - with the following fields : id (pk), date (NOW with trigger), project, duedate, status, description

To enter a new row in SQLite from the command line, I have to write :

sqlite3 tasks.db "insert into todo (project,duedate,status,description) values (2010-11_18,'Home','Urgent','Call the plumber');"

which is a rather long and error-prone process. So I decided to "automate" it with a shell script (bsq) which runs as follows :

#!/bin/sh
echo "What project ?"
read Proj
echo "For when ?"
read Due
echo "What status ?"
read Stat
echo "What to do ?"
read Descr

echo sqlite3 tasks.db "insert into todo (project,duedate,status,description) values ('$Proj',$Due,'$Stat','$Descr');"

… and nothing happens when I run : sh bsq. The sequence appears then brings me back to the prompt.

Where did I go wrong or what did I omit (ENTER ? but how do I do that ?)?

Thanks for your help.

What do you expect? You call echo sqlite ... it won't invoke sqlite. Please format the source properly. Use the icons above the textarea. — khachik
– khachik, Commented Nov 11, 2010 at 7:55
Is the last echo correct? Or are you executing sqlite3 directly? It works fine here; echoing the command at the end. — falstro
– falstro, Commented Nov 11, 2010 at 7:56
Also: In sh '$Proj' is not treated as a variable. Use "$Proj" — khachik
– khachik, Commented Nov 11, 2010 at 7:57
@khachik; incorrect.. the single quotes are within double quotes, so they have no meaning and will be used as is. — falstro
– falstro, Commented Nov 11, 2010 at 7:59

khachik · Accepted Answer · 2010-11-11 08:03:11Z

35

#!/bin/sh
echo "What project ?"
read Proj
echo "For when ?"
read Due
echo "What status ?"
read Stat
echo "What to do ?"
read Descr

echo "im gonna run" sqlite3 tasks.db "insert into todo \
     (project,duedate,status,description) \
     values (\"$Proj\",$Due,\"$Stat\",\"$Descr\");"
sqlite3 tasks.db "insert into todo (project,duedate,status,description) \
         values (\"$Proj\",$Due,\"$Stat\",\"$Descr\");"

answered Nov 11, 2010 at 8:03

khachik

28.8k10 gold badges63 silver badges98 bronze badges

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

4 Comments

ThG Over a year ago

it worked perfectly, of course. Thanks a lot (and this time, I did not forget to ACCEPT the answer...)

Florian F Over a year ago

please note: this does not take care of escaping

jia103 Over a year ago

What do I have to do to handle escaping?

MaXi32 Over a year ago

To handle escaping don't use sqlite. That is the best answer I found so far within few posts.

RonJohn · Accepted Answer · 2025-03-15 19:52:52Z

1

printf, a subprocess and a pipe works for me with sqlite 3.26.0

(printf "INSERT INTO atable VALUES ('%s', '%s', '%s');" $foo $bar $baz) | sqlite3 some.db

That will probably eliminate the need to escape double quotes.

answered Mar 15 at 19:52

RonJohn

4533 gold badges9 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to INSERT INTO SQLite database using Bash?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related