Sqlite bash script tutorial on Ubuntu Linux


sqlite bash
Here I will show you how to use the lightweight Sqlite database from the command line using bash scripts. This is just a short introduction to get you started, and you should already be familiar with Linux and scripting before you start.
The scripts below can give your bash scripts access to data that would otherwise have been very cumbersome. Sqlite is quite powerful and once you get started you will see no end to the problems it can solve.

Installation

If you haven’t already installed Sqlite, install it with apt-get.

$ sudo apt-get install sqlite3

And to see the power of sqlite, run the script to find the id of the person with the name Tom.

Set up some test data

First we will create a small test database so that the scripts we make will output something.
The following few commands will make a database file called mydatabase.sqlite and add a few rows of data.

$ sqlite3 mydatabase.sqlite "CREATE TABLE person ( id int, name varchar(30), phone varchar(30) );"
sqlite3 mydatabase.sqlite "INSERT INTO person VALUES (1, 'Jim', '123446223');\
INSERT INTO person VALUES (2, 'Tom', '232124303');\
INSERT INTO person VALUES (3, 'Bill', '812947283');\
INSERT INTO person VALUES (4, 'Alice', '351246233');"

Run a quick test to make sure everything is working.

$ sqlite3 mydatabase.sqlite "SELECT name from person where id=3;"
Bill

If you got the output above, you are ready to create the bash scripts to do the same.

Creating the bash scripts

The next few lines create the actual scripts. These could be created using a text editor but for simplicty can be created using a few one line commands.
The first bash script will find the name of the person by id, by passing in the id as argument $1 to the script.

First create the script with the following command.
$ echo -e '#!/bin/bash\nsqlite3 mydatabase.sqlite "SELECT name from person where id=$1;"\n' > person_by_id.sh

Lets look inside the file and see what it looks like.

$ cat person_by_id.sh
#!/bin/bash
sqlite3 mydatabase.sqlite "SELECT name from person where id=$1;"

Now that we can see what it will do, make it executable.

$ chmod u+x person_by_id.sh

To find the name of the person with the id 3, run the script with 3 as the argument.

$ ./person_by_id.sh 3
Bill

The second script is a little more complicated, because the sql WHERE clause for the column “name” needs to be inside single quotes and because of the shell escaping, there is a bit of work required to make the one line script creation work. You don’t need to worry about this if you just create the scripts directly in a text editor, but for now just follow along and just enter the lines below.

$ echo -e '#!/bin/bash\nsqlite3 mydatabase.sqlite "SELECT id from person where name='"'"'$1'"'"';"\n' > personid_by_name.sh

Lets look inside the files and see what it looks like.

$ cat personid_by_name.sh
#!/bin/bash
sqlite3 mydatabase.sqlite "SELECT id from person where name='$1';"

Now make the script executable.

$ chmod u+x personid_by_name.sh

And to see the power of sqlite, run the script to find the id of the person with the name Tom.

$ chmod u+x personid_by_name.sh
$ ./personid_by_name.sh Tom
2

Conclusion

The above should be enough to get you started. You should now look at the Sqlite documentation and see what other data manipulation is possible.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s