- [Narrator] Hi and welcome to the next video
of the last section, Writing and Reading
the MySQL Database from Bash.
In the previous video, we've seen how to schedule with Cron.
In this video, we'll see how to read and write
to the MySQL database from Bash.
MySQL is a widely used database management system
used to manage databases for the storage systems
for applications that are written in languages
such as PHP, Python, C++, and so on.
Accessing and manipulating MySQL databases
from shell scripts is also interesting,
as we can write scripts to store contents
from a text file or comma separated values into tables
and interact with the MySQL database to read
and manipulate data.
For example, we can read all the email addresses
stored in a guestbook's program database
by running a query from the shell script.
Let's take this example problem.
I have a CSV file containing details of students.
I need to insert the contents of the file
to a database table.
From this data, I need to generate a separate rank list
for each department.
In order to handle MySQL databases, you should have
MySQL Server, MySQL Client Packages installed
on your system.
These tools do not come with Linux distribution by default.
As MySQL comes with a username and password
for authentication, you should set a username
and password while installing the MySQL server.
Let's see how to do it.
The preceding problem can be solved using Bash utilities,
Sort, Org, and so on.
Alternatively we can also solve it by using
an SQL database's table.
We'll write three scripts for the purpose of creating
database and table, inserting student data into the table,
and reading and displaying process data from the table.
Create the database and table script as follows.
The script for inserting data into the table is as follows.
The query from the database is as follows.
The data for the input CSV file, studentdata.csv,
is as follows.
Execute the scripts in the following sequence.
We'll now see the explanation of the preceding scripts
one by one.
The first script create_db.sh is used to create a database
called Students, and a table called Students inside it.
We need the MySQL username and password to access
and modify the data in the DBMS.
The variables USER and PASS are used to store
the username and password.
The MySQL command is used for MySQL manipulations.
The MySQL command can specify the username
by using minus u, and the password by using
minus p password.
The other command argument for the MySQL command
is the database name.
If a database name is specified as an argument
to the MySQL command, it will use that
for database operations.
Else we have to explicitly specify in the SQL query
about which databases to be used with the
use database name query.
The MySQL command accepts the queries to be executed
through standard input, standard in.
The convenient way of supplying multiple lines
through standard in is by using the EOF method.
The text that appears between EOF and EOF
is passed to MySQL as standard input.
In the create database query, we have redirected
standard error to slash dev slash null
in order to prevent displaying an error message.
Also, in the table creation query, we have redirected
standard error to dev null to ignore any errors that occur.
Then we check the exit status for MySQL command
by using the exit status variable, dollar question mark,
to know if a table or database already exists.
If the database or table already exists,
a message is displayed to notify that.
Else, we'll create them.
The next script, write_to_db.sh accepts a file name
of the studentdata.csv file.
We read each line of the CSV file by using the while loop.
So in each iteration, a line with comma separated values
will be received.
We then need to formulate the values in the line
to an SQL query.
For that, the easiest way to store that data
in the comma separated line is by using an array.
We know that an array assignment is in the form
array equals val one, val two, val three.
Here the space character is the internal
field separator, IFS.
We have a line with comma separated values,
hence by changing the IFS to a comma,
we can easily assign values to the array.
IFS equals comma.
The data items in the comma separated value line
are ID, name, mark, and department.
ID and mark are integer values, whereas name
and department are strings.
Strings must be quoted.
Also the name can contain space characters.
Space can conflict with the internal field separator,
hence we should replace the space in the name
with a character, hash, and replace it later
after formulating the query.
In order to quote the strings, the values in the array
are reassigned prefix and suffix with slash.
The TR is used to substitute space in the name to hash.
Finally, the query is formed by replacing
the space character with a comma,
and replacing hash with a space.
And this query is executed.
This third script, read_db.sh is used to find out
the department and print the ranked list of students
for each department.
The first query is used to find distinct
names of departments.
We use a while loop to iterate through each department
and run the query to display student details
in the order of highest marks.
Set at I equals zero is an SQL construct
used to set the variable I equals zero.
On each row it's incremented and is displayed
as the rank of the student.
Awesome.
We've successfully learned how to write and read
the MySQL Database from Bash.
In the next video, we'll learn about
user administration script.