David's Blog

Web development and programming

How to execute a SQL Query from a Shell Script

MySQL Database

For this example we’re going to use MySQL as database management system. We’re going to operate over testdb database which contains the people table:

people table

And the following tuples:

+----+---------+-----+------------+
| id | name    | age | bornPlace  |
+----+---------+-----+------------+
|  1 | John    |  34 | London, UK |
|  2 | Patrick |  28 | NY, USA    |
+----+---------+-----+------------+

This is the MySQL SQL script for generating the testdb database:

create database testdb;
use testdb;
create table people(id INT, name varchar(100),
                    age INT, bornPlace varchar(100));
insert into people values(1,"John",   34,"London, UK");
insert into people values(2,"Patrick",28,"NY, USA");

 

Understanding execution of SQL Query from Shell

MySQL allows user to execute queries from the shell with MySQL command. The query can be passed as input using a pipe, like in this example:

echo "select name, age, bornPlace FROM people" | mysql -u david --password=1234 --database=testdb

The database system returns the data and the fields names of the table as first row. This is the result:

name    age     bornPlace
John    34      London, UK
Patrick 28      NY, USA

 

Shell script

This is the script for retrieving the registries from the database (download it):

USER_NAME=david
USER_PWD=1234

DB_NAME=test

flag=0
delimiter="a3f4g52"

echo "select name, '"$delimiter"', age, '"$delimiter"', bornPlace FROM people" | \
mysql -u $USER_NAME --password=$USER_PWD --database=$DB_NAME | \
while read row ;
do
  if [ $flag == 0 ]; then
    flag=1
  else
    name=`     echo $row | \
               awk -F "$delimiter" '{print $1}' | \
               sed -e 's/ *$//' | sed 's/^ *//'`
    age=`      echo $row | \
               awk -F "$delimiter" '{print $2}' | \
               sed -e 's/ *$//' | sed 's/^ *//'`
    bornPlace=`echo $row | \
               awk -F "$delimiter" '{print $3}' | \
               sed -e 's/ *$//' | sed 's/^ *//'`

    echo ">$name<"
    echo ">$age<"
    echo ">$bornPlace<"
  fi
done

To can split later the fields returned by the query, in select query is inserted a delimiter between each field to retrieve. This will help us later to split the line up with this delimiter. This is the select query and the result of its execution with mysql command command.

david@david$ echo "select name, 'a3f4g52', age, 'a3f4g52', bornPlace FROM people" | mysql -u david --password=1234 --database=testdb
name    a3f4g52 age     a3f4g52 bornPlace
John    a3f4g52 34      a3f4g52 London, UK
Patrick a3f4g52 28      a3f4g52 NY, USA

The while loop reads each line:

  • if-else sentence with flag variable avoid reading the first row which contains the name of the fields
  • awk command to separate each row by the delimiter.
  • sed command to remove the spaces in beginning and end of the fields with the s command ‘s/regexp/replacement/flags’:
    • Trailing spaces: sed -e ‘s/ *$//’
    • Beginning spaces: sed -e ‘s/^ *//’

And this is the output of the shell script, it shows each field in a new line and cleaned of spaces.

>John<
>34<
>London, UK<
>Patrick<
>28<
>NY, USA<

In this post is presented how to make a select query an retrieve the results. I hopeyou can adapt this simple example to create most complex programs.

Category: MySQL, Shell Script

Your email address will not be published. Required fields are marked *

*