4

I'm writing a bash script where I'm using mysql query in that script where the format which I'm getting is quite different.

Query:

root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" 
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |   2.00 | Nestle      |  2.00 |
|       2 | Cheese     |   2.50 | Amul        |  6.00 |
|       3 | Chips      |  25.00 | Lays        |  3.00 |
|       4 | Coke       |   5.00 | Coke Cola   |  3.50 |
|       5 | Engage     |   5.00 | Deo         |  3.50 |
|       6 | Engage     |   5.00 | Deo         |  3.50 |
|       7 | Ear phones |   4.00 | Skull Candy | 32.30 |
+---------+------------+--------+-------------+-------+

formatted using column -t command the output format is getting aligned

root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" | column -t
item_id  item_name  weight  brand   price
1        Milk       2.00    Nestle  2.00
2        Cheese     2.50    Amul    6.00
3        Chips      25.00   Lays    3.00
4        Coke       5.00    Coke    Cola   3.50
5        Engage     5.00    Deo     3.50
6        Engage     5.00    Deo     3.50
7        Ear        phones  4.00    Skull  Candy  32.30

Bash Script the bash script which I tried using the above command

root@debian:~# cat test
#!/bin/bash

while read -r output;
do
    echo $output | awk '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d |column -t)

output

root@debian:~# ./test
Nestle
Amul
Lays
Coke
Deo
Deo
4.00

But the Expected Output:

Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy

Yeah! you can say use select brand from items. This is for example in real time I'm using quite different command.

Any Hint or Help ?

  • You're explicitly excluding brand with your call to sed 1d which strips the header from the output. Try removing that from the pipeline. Also, for the love of science, why are you running random bash scripts as root? – DopeGhoti Jun 1 '16 at 22:35
  • @DopeGhoti edited. And its a test machine Nothing to worry. – Prasanna Ranganthan Jun 1 '16 at 22:38
  • Tell awk that your field separator is a tab (^I). The problem is that Ear phones is looking like two fields because a space (by being a non-endline whitespace character) is by default a field separator. – DopeGhoti Jun 1 '16 at 22:45
  • @DopeGhoti I tried that too getting a blank output – Prasanna Ranganthan Jun 1 '16 at 22:49
2

output of mysql is tab delimited. column replaces tabs with spaces breaking your input.

#!/bin/bash

while read -r output;
do
    # use double quotes with "$output" to avoid conversion
    # of tabs to spaces and set awk's Field Separator to "\t"
    echo "$output" | awk -F"\t" '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d)

test run:

root@c2:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;"
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |      2 | Nestle      |     2 |
|       2 | Cheese     |    2.5 | Amul        |     6 |
|       3 | Chips      |     25 | Lays        |     3 |
|       4 | Coke       |      5 | Coke Cola   |   3.5 |
|       5 | Engage     |      5 | Deo         |   3.5 |
|       6 | Engage     |      5 | Deo         |   3.5 |
|       7 | Ear phones |      4 | Skull Candy |  32.3 |
+---------+------------+--------+-------------+-------+
root@c2:~# ./test.sh 
Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy
root@c2:~# 
  • I got the output as blank enteries – Prasanna Ranganthan Jun 1 '16 at 23:31
  • try to comment out | awk... to see what you get. – adonis Jun 1 '16 at 23:38
1

Use mysql with the -t flag, to get the output you get from a query in mysql directly as your bash output.

Afterwards you can use sed or awk to clean up the delimiters, to remove the horizontal lines and the |'s.

0

You don't have to use column tool as your table is really done. Try using awk with the common delimiter |.

You can do the following in your echo line

echo "$output" | awk -F'|' '{print $5}'

So it ends as

#!/bin/bash

while read -r output;
do
    echo $output | awk '{print $5}'
    #do something
    done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed '1,2d')
0

This is a very dirty and hacky solution, but you can use the dot (.) as the separator since you have floating point numbers on each side. You can call awk as follows

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); print a}'

in the script and it shall print only the brand.

There will be extraneous spaces in the middle of the brand names but you can extend the awk script to cope with that as well:

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); b=gensub(/\s+/," ","g",a); print b}'

awk's gensub function is a very powerful tool for such kinds of problems. Although this specific example is dirty, i believe, that knowing how to use gensub (the manual is quite decent) will allow you to solve similar problems with ease.


The above is GNU awk (gawk) specific. Old school unix systems (AIX, HP-UX) have no gawk. Funny enough, for some unknown reason, debian also has no gawk by default. debian has mawk as the default awk installed. Here is a mawk version:

awk -F '.' '{gsub(/^[0-9]*\s*|\s*[0-9]*$/,"",$2); gsub(/\s+/," ",$2); print $2}'

mawk has not gensub, it only has gsub (global substitution). gsub is vastly inferior to gensub because it does not return its substitution. If you are using debian and need awk often i strongly recommend installing gawk (it is in the main repo).

  • root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" -s | column -t | awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); b=gensub(/\s+/," ","g",a); print b}' awk: line 2: function gensub never defined awk: line 2: function gensub never defined root@debian:~# I'm getting this error – Prasanna Ranganthan Jun 1 '16 at 23:35
  • Jolly dee... debian has mawk as the default awk. Still doable but even dirtier. As i mentioned in the edited answer, i strongly recommend installing the GNU awk. – grochmal Jun 2 '16 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.