One of the scripts that uses bash to read and write CSV file data to a MySQL database

Scene:

A CSV file that contains student details for multiple departments requires inserting the contents of a file into a data table, ensuring that each system generates a separate ranking list.

The data for the Learning Information table Studentdata.csv file is as follows:

1,navin m,98,cs2,kavya n,70,cs3,nawaz O,80,cs4,hari s,80,ec5,alex m,50,ec6,neenu J,70,EC7,Bob A,30,EC8,Anu M,90,AE9, Sruthi,89,ae10,andrew,89,ae

Scripting ideas:

There are two ways to deal with this problem, from the shell script point of view, can be solved with bash tools such as Sort,awk, or a SQL database data table can also be solved.

You need to write 3 scripts to create a database and a data table, insert student data into a data table, read from a datasheet, and display processed data.

Note: The user,pass,socket variables of MySQL data connection in the script below are the connections in my test environment, which can be modified according to the circumstances when using the script;
In addition to the actual import of the CSV format and the number of rows, you can control the database name, table name, column name in the script to modify, so that these three scripts can solve this kind of problem.

Script one, the script to create the database and the data table is as follows:

#!/bin/bash#filename:create_db.sh#use:create MySQL database and tablesuser= "root" pass= "123456" socket= "/data/ Mysqldata/3306/mysql.sock "Mysql-u $USER-p$pass-s $SOCKET <<eof 2>/dev/nullcreate database students; eof[$?-eq 0] && echo Created DB | | echo DB already existmysql-u $USER-p$pass-s $SOCKET students <<eof 2>/dev/nullcreate table students (ID Int,na Me varchar (+), Mark Int,dept varchar (4)); eof[$?-eq 0] && echo Created Table Students | | echo Table students already existmysql-u $USER-p$pass-s $SOCKET students <<eofdelete from students; Eof

script two, the script to insert data into the data table is as follows:

#!/bin/bash#filename : write_to_db.sh#use : read data from csv files  and insert into mysql dbuser= "root" pass= "123456" socket= "/data/mysqldata/3306/ Mysql.sock "If [ $# -ne 1 ];then    echo $0 datafile     echo    exit 2fidata=$1while read line;do     oldifs= $IFS     ifs=,    values= ($line)      values[1]= "\" ' echo ${values[1]} | tr  '   '   ' # '   ' \ ' "     values[3]= "\" ' echo ${values[3]} ' \ ""     query= ' echo ${values[@]}  | tr  '  # '   ',  '   '     ifs= $oldIFS                                                    mysql -u  $USER  -p$PASS -S  $SOCKET  students <<eofinsert  into students values ($query); eofdone<  $dataecho  wrote data into db

Script three, query the database script as follows:

#!/bin/bash#filename:read_db.sh#use:read data from MySQL dbuser= "root" pass= "123456" socket= "/data/mysqldata/3306/ Mysql.sock "depts= ' mysql-u $USER-p$pass-s $SOCKET Students <<eof | Tail-n +2select Distinct dept from students; EOF ' for D in $depts;d oecho Department: $dresult = "' Mysql-u $USER-p$pass-s $SOCKET students <<eofset @i:=0;select @i:[email protected]+1 as Rank,name,mark from students where dept= ' $d ' ORDER by Mark Desc; EOF ' "echo" $result "Echodone

The script executes the results in order as follows:

# chmod +x create_db.sh write_to_db.sh read_db.sh# ./create_db.sh                                                DB already existTable students already  exist# # ./write_to_db.sh studentdata.csv       wrote  data into db# # ./read_db.sh                          department :  CSrank    name    mark1        Navin M 982       Nawaz O 803        kavya  n 70department : ecrank    name    mark1        hari s  802       neenu j  703       Alex M  504        Bob A   30Department : AErank    name     mark1       Anu M   902        Sruthi  893       Andrew   89

Confirm the post-insert results in the database:

mysql> mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  students           | |  test               |+-------- ------------+5 rows in set  (0.00 sec) mysql> mysql> use students; reading table information for completion of table and column  Namesyou can turn off this feature to get a quicker startup  with -adatabase changedmysql> mysql> show tables;+--------------------+|  tables_in_students |+--------------------+| students           |+---------- ----------+1 row in set  (0.00 sec) mysql> mysql> select *  from students;+------+---------+------+------+| id   | name     | mark | dept |+------+---------+------+------+|    1 |  navin m |   98 | cs   | |     2 | Kavya N |   70 | CS    ||     3 | Nawaz O |   80 | CS    ||     4 | Hari S  |   80 | EC    | |     5 | Alex M  |   50 | EC    | |     6 | neenu j |   70 | ec   | |     7 | Bob A   |   30 | EC    | |     8 | Anu M   |   90 | AE    | |     9 | Sruthi  |   89 | AE    ||    10 | andrew  |   89 | ae   |+--- ---+---------+------+------+10 rows in set  (0.00 sec) mysql>

This article is from the "Yumushui column" blog, be sure to keep this source http://yumushui.blog.51cto.com/6300893/1688928

One of the scripts that uses bash to read and write CSV file data to a MySQL database