Insert into mysql from Bash script [duplicate]

Question

Utilized a few posts here and Google and friends and I'm trying desperately to get something simple to work. I want to insert data into mysql via a bash script.

#!/bin/bash -x
DB_USER='jhatter';
DB_PASSWD='XXXXXXX';

DB_NAME='ppr';
TABLE='graph';

#Collect Rank Information from flightaware.com
day=$(date +"%m/%d/%y")
time=$(date +"%H:%M:%S")
rank=$(curl -s http://flightaware.com/adsb/stats/user/jdhatter11 | grep -i site-ranking-29371 | grep window | awk '{print $2}' | sed s/,//)

#mysql commands
mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME
INSERT INTO $TABLE (`id `, `day`, `time`, `rank`) VALUES (NULL, "$day", "$time", "$rank");
QUIT;

This script when ran manually stops abrubtly after mysql login. It actually brings the login up on screen and I have been inserted in the database. I'm a huge novice and please go easy if it's blatant, but why doesn't the script proceed to process the INSERT and instead throw me into mysql. I envision this all happening in the background...

the insert query will be a command to the bash script, NOT the mysql client. you have to pass the query as an argument to mysql, or via its stdin. — Marc B
– Marc B, Commented Aug 29, 2016 at 21:02

janos · Accepted Answer · 2016-08-29 21:02:43Z

16

You can pass the commands in a here-document, like this:

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
INSERT INTO $TABLE (\`id\`, \`day\`, \`time\`, \`rank\`) VALUES (NULL, "$day", "$time", "$rank");
EOF

Notice that the ` need to be escaped. I also removed the QUIT command, as it's unnecessary (good tip @Ven, thanks).

Actually, since those column names don't contain special symbols, you don't actually need to quote them, and write the INSERT query a bit simpler, like this:

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
INSERT INTO $TABLE (id, day, time, rank) VALUES (NULL, "$day", "$time", "$rank");
EOF

edited Aug 29, 2016 at 21:02

answered Aug 29, 2016 at 21:00

janos

126k31 gold badges242 silver badges253 bronze badges

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

1 Comment

Joshua Hatter Over a year ago

Wonderful, in the end I feel like I tried 1 million combinations of similar variations. Thank you for the explanation. SOLVED

Barmar · Accepted Answer · 2016-08-29 21:02:34Z

3

You need to use a here-doc if you want to put the input to a program after the program in a script.

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME <<EOF
INSERT INTO $TABLE (\`id\`, \`day\`, \`time\`, \`rank\`) VALUES (NULL, "$day", "$time", "$rank");
EOF

Note that you need to escape backticks, because they're part of shell syntax to insert the output of a command into the command line.

For a one-liner, you could also use the -e option.

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME -e "NSERT INTO $TABLE (\`id\`, \`day\`, \`time\`, \`rank\`) VALUES (NULL, '$day', '$time', '$rank)"

answered Aug 29, 2016 at 21:02

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

Insert into mysql from Bash script [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related