Use bash to read and write csv file data to one of the scripts of MySQL database,

Use bash to read and write csv file data to one of the scripts of MySQL database,

Zookeeper

Scenario:
There is a csv file containing student details of multiple departments. You need to insert the file content into a data table to ensure that each department generates a separate ranking list.

The data in the studentdata.csv file of the learning information table 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

Script ideas
There are two ways to solve this problem. From the perspective of shell scripts, you can use bash tools such as sort and awk, or use a SQL database data table.
The following three scripts are required to create a database and a data table, insert student data into the data table, read data from the data table, and display the processed data.

Note:In the following script, the user, pass, socket and other variables for mysql DATA connection are connections in my test environment. You can modify the variables as needed when using the script;
In addition, you can modify the database name, table name, and column name in the script for different csv format and number of rows to solve this problem.

Script 1: the script for creating a database and a 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,name varchar(100),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 2: the script for inserting data into a 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 3: the script for querying the database is 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;doecho Department : $dresult="`mysql -u $USER -p$PASS -S $SOCKET students <<EOFSET @i:=0;select @i:=@i+1 as rank,name,mark from students where dept="$d" order by mark desc;EOF`"echo "$result"echo done

The script execution result is 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 insert result 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 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> 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.