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 inserts into MySQL dbuser= "root" pass= "123456" Socke t= "/data/mysqldata/3306/mysql.sock" if [$#-ne 1];then    echo $ datafile    echo    exit 2fidata=$1while Read Line ;d o    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 "echo done

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# #./WR ite_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 MySQL 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 W Ith-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>

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

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