Database Interaction with Shell Scripts

Introduction to Database Interaction

Shell scripts can interact with databases for tasks such as querying data, updating records, and performing maintenance operations. This tutorial explores various methods to interact with databases using shell scripts.

Using MySQL with Shell Scripts

MySQL is a popular relational database management system. Here’s how you can interact with MySQL using shell scripts:

Executing SQL Queries

#!/bin/bash

# MySQL connection details
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"

# SQL query
SQL_QUERY="SELECT * FROM table_name;"

# Execute query
mysql -u"$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "$SQL_QUERY"
                

This script connects to a MySQL database and executes a SQL query to fetch all records from a specified table.

Inserting Data

#!/bin/bash

# MySQL connection details
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"

# Data to insert
DATA="('John Doe', 'john.doe@example.com')"

# SQL query
SQL_QUERY="INSERT INTO users (name, email) VALUES $DATA;"

# Execute query
mysql -u"$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "$SQL_QUERY"
                

This script inserts new data into the users table of a MySQL database.

Using SQLite with Shell Scripts

SQLite is a lightweight relational database. Here’s an example of interacting with SQLite using shell scripts:

Executing SQL Queries

#!/bin/bash

# SQLite database file
DB_FILE="database.sqlite"

# SQL query
SQL_QUERY="SELECT * FROM table_name;"

# Execute query
sqlite3 "$DB_FILE" "$SQL_QUERY"
                

This script connects to an SQLite database file and executes a SQL query to retrieve all records from a specified table.

Conclusion

Shell scripting provides flexibility in interacting with databases like MySQL and SQLite, allowing you to automate database tasks effectively. Customize scripts according to your database environment and requirements.