Shell Script to Perform Database Operations

In this article, we will be creating a shell script to perform various database operations. We will be using the MySQL database management system and the MySQL command-line client for our examples. However, the concepts and techniques discussed in this article can be applied to other database management systems as well.

Before we begin, make sure that you have the following prerequisites:

• A MySQL server set up and running
• The MySQL command-line client installed on your machine
• A MySQL user account with the necessary privileges to perform the operations you want to include in your script

Make sure that your MySQL server is running you can start your server by command:

$ service mysql start

You can see below the status is running and my server is active:

Creating the Script

We will start by creating a new file called db_ops.sh using a text editor of your choice. The first thing we will do in our script is to store the MySQL username and password in separate variables. This will make it easier to modify these values later on if needed.

#!/bin/bash

# MySQL login details

MYSQL_USER=”user”

MYSQL_PASSWORD=”password”

Next, we will define a function to execute a MySQL query and print the results to the terminal. This function will take two arguments: the query to execute and the MySQL database to use.

# Function to execute a MySQL query and print the results

execute_query() {

    local query=$1

    local database=$2

    mysql -u “$MYSQL_USER” -p”$MYSQL_PASSWORD” “$database” -e “$query”

}

With these basic building blocks in place, we can now start adding the database operations we want to include in our script. Let’s start by creating a function to create a new database.

# Function to create a new database

create_database() {

    local database=$1

    execute_query “CREATE DATABASE $database”

}

We can now create a function to create a new table in a database. This function will take three arguments: the name of the database, the name of the table, and the table schema.

# Function to create a new table in a database

create_table() {

    local database=$1

    local table=$2

    local schema=$3

    execute_query “CREATE TABLE $table ($schema)” “$database”

}

We can also create functions to insert, update, and delete data from a table. These functions will take similar arguments as the create_table function, with the addition of the data to insert, update, or delete.

# Function to insert data into a table

insert_data() {

    local database=$1

    local table=$2

    local data=$3

    execute_query “INSERT INTO $table VALUES ($data)” “$database”

}

# Function to update data in a table

update_data() {

    local database=$1

    local table=$2

    local data=$3

    local condition=$4

    execute_query “UPDATE $table SET $data WHERE $condition” “$database”

}

# Function to delete data from a table

delete_data() {

    local database=$1

    local table=$2

    local condition=$3

    execute_query “DELETE FROM $table WHERE $condition” “$database”

}

Finally, we can create a function to display the data stored in a table. This function will take two arguments: the name of the database and the name of the table.

# Function to display data from a table

display_data() {

    local database=$1

    local table=$2

    execute_query “SELECT * FROM $table” “$database”

}

Now that we have all the functions in place, we can use them in the main body of our script to perform the desired database operations. For example, the following script creates a new database called “employees”, creates a table called “employee_data” with the specified schema, inserts some data into the table, updates a record, and displays the updated data.

# Create a new database

create_database “employees”

# Create a table with the specified schema

create_table “employees” “employee_data” “id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), salary INTEGER”

# Insert data into the table

insert_data “employees” “employee_data” “1, ‘John Smith’, 50000”

insert_data “employees” “employee_data” “2, ‘Jane Doe’, 60000”

# Update a record

update_data “employees” “employee_data” “salary=70000” “id=2”

# Display the data

display_data “employees” “employee_data”

Steps to create and execute a bash script

Step 1: Open a terminal window.

Step 2: Use the nano or vi command to open a new blank file in a text editor.

nano myscript.sh

Step 3: Type the bash commands that you want to include in the script.

Step 4: Save the file by pressing CTRL + X, then Y, and then Enter.

Step 5: Make the script executable by running the chmod command: 

chmod +x myscript.sh

Step 6: Run the script by typing its name at the command prompt, followed by any arguments that the script expects:

./myscript.sh

Script:

Output:

You can see in the above image the script is being executed successfully and the output is printed in the terminal.

Conclusion

In this article, we have seen how to create a shell script to perform various database operations using the MySQL database management system. We have created functions to create a database, create and modify tables, and query data. With these building blocks, you can easily create a script to automate any database-related tasks you might have. With these building blocks, you can easily create a script to automate any database-related tasks you might have. For example, you could create a script to regularly back up your database or populate a database with test data for testing purposes. It is important to note that the concepts and techniques discussed in this article can be applied to other DBMS as well. You will just need to use the appropriate command-line tools and SQL commands for the database you are working with. In summary, shell scripts can be a useful tool for automating database operations and streamlining your workflow. I hope this article has been helpful and has given you some ideas on how you can use shell scripts to manage your databases more efficiently.