Embed MySQL in shell script

Question

How do I insert data into a table using MySQL using shell script? The maximum number of records could change based on a parameter. Should I use a for loop in shell script embedded with MySQL?

I have attached my attempt below, and its not inserting the record. Can somebody review it and correct me please?

#!/bin/bash

mysql -u root -p"" << EOF

use school;

EOF

echo -n "Enter numebr of Students to enroll"

read i

echo -n "Enter marks"

read marks

for (( i=1; i‹= 10; i++ ))

do   

for((sub=1; sub‹= 8; sub++ ))

do

echo "Enter subject name"

read subject

if [$marks=y | y<=100 | y>=0] then

<< EOF

insert into students('id$i',$subject,$marks)

EOF

echo Record Inserted

Ravi Dhoriya ツ · Accepted Answer · 2014-05-16T14:25:41.190

0

You can use mysql command in your shell script to execute query as shown below:

$./script.sh 10

limit=$1
for(i=1;i<limit;i++){

    //input value for $v1 
    //input value for $v2
    //input value for $v3

    mysql --host=database_host_name --user=mysql_user --password=mysql_user_password your_db_name << EOF
    insert into your_table (col1,col2,col3) values('$v1','$v2','$v3');
    EOF
}

NOTE

Don't forgot to quote your variable with single quotes (for varchar field)

edited May 16 '14 at 14:25

answered May 16 '14 at 14:13

Ravi Dhoriya ツ

4,435
8
37
48

Thankyou by the way... Generate and insert records to the table according to the following criteria. a. Student reg_id should be like "idx". "x" should be start from 1. Ex: id1, id2..... id138 b. Every student should have marks only for 8 subjects. (Optional subjects : Art, Computer, literature) c. The maximum number of records should be able to change using a parameter. d. The subject marks can be vary from 0 to 100. here i need to insert a variable actually.. not a normal insert. thats y i asked how to insert a variable to database??? – harzyne May 16 '14 at 14:18
You need to quote your variable as its string to be inserted in database. so, change your query to `insert into students('id$i','$subject',$marks);` – Ravi Dhoriya ツ May 16 '14 at 14:24
Nope, actually you can quote all values being inputed in `INSERT` statement of `MySQL`. It will be inserted to database as an `INT` if its defined as an `INT` column and as a string if its defined as a `varchar` column. I always used to quote my values while inserting. – Ravi Dhoriya ツ May 16 '14 at 14:28
thanku so much :) " limit=$1 "what does it mean? initializing to value one?? – harzyne May 17 '14 at 15:15
Nope, `$1` represents command-line argument (given while executing script). So it will have value 10 if you execute script like, `user@machine$./script.sh 10` – Ravi Dhoriya ツ May 17 '14 at 15:22
$0 will be having script name, $1 argument 1, $2 argument 2 and so on... [read more..](http://how-to.wikia.com/wiki/How_to_read_command_line_arguments_in_a_bash_script) – Ravi Dhoriya ツ May 17 '14 at 15:23
Is your problem solved? If it helped then you should mark it as an answer by clicking on "Tick-mark" left hand side of the answer. So that this answer can be useful in future for someone else :) – Ravi Dhoriya ツ May 17 '14 at 15:25
can you give an example script to use inner for loops (2 loops) with 2 variables and to insert them to mysql?? And when i use < – harzyne May 17 '14 at 15:36
Well, I'm not expert in shell scripts. But its because you can't have HERE document nested. You need to do proper indentation` as shown in [this answer](http://stackoverflow.com/questions/13254077/is-nesting-of-here-document-possible-in-a-unix-bash-script) – Ravi Dhoriya ツ May 17 '14 at 15:47

Embed MySQL in shell script

1 Answers1