In this article, we are going to learn how to automate SQL queries by connecting to a server using a shell script. Bash scripting is used for automating things.
Prerequisites
Make sure that mysql
, postgres
, and sqlite
are installed. Ensure that the user is created in MySQL and that you have granted permission to that user.
Write Script:
MySQL queries in script: We are going to write a script called mysql_version.sh
to get the latest version of MySQL:
#!/bin/bash
mysql -u root -pTraining2@^ <<MY_QUERY
SELECT VERSION();
MY_QUERY
Now, we are going to create a script called create_database.sh
to create the database:
#!/bin/bash
mysql -u root -pTraining2@^ <<MY_QUERY
create database testdb;
MY_QUERY
SQLite queries in script: Now, we are going to create a sqlite
database. You can create the sqlite
database by simply writing sqlite3
and a name for the database. For example:
$ sqlite3 testdb
Now, we are going to create a table in the sqlite
console. Enter sqlite3 testdb
and press Enter—you will see the sqlite3 console. Now, write the create table
command to create a table:
$ sqlite3 testdb
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/student/testdb
sqlite> CREATE TABLE bookslist(title text, author text);
sqlite> .tables
bookslist
Postgres queries in scripting: Now, we are going to check the postgresql database version. Here, testdb
is our database name, which is what we created earlier. For that, run the following command:
student@ubuntu:~$ sudo -i -u postgres
postgres@ubuntu:~$ psql
psql (9.5.13)
Type "help" for help.
postgres=# create database testdb;
CREATE DATABASE
postgres=# \quit
postgres@ubuntu:~$ psql testdb
psql (9.5.13)
Type "help" for help.
testdb=# select version();
version
-------------------------------------------------------------------------------------------
PostgreSQL 9.5.13 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)
Now, we are going to create a table. For that, run the following command:
postgres@ubuntu:~$ psql testdb
psql (9.5.13)
Type "help" for help.
testdb=# create table employee(id integer, name text, address text, designation text, salary integer);
CREATE TABLE
testdb=#
How it works
- We are creating bash scripts to check the version of a database and to create a new database. In these scripts, we are using the root user and there is a password present right after
-p
for that user. You can use theroot
user or else you can create a new user, assign a password to it, and use that user in your scripts. - SQLite software provides us with a simple command-line interface. Using this interface, we can manually enter and execute SQL commands. You can list the database using the dot (
.
) operator..databases
and.tables
are used to list all the tables in the database. - In PostgreSQL, first we are changing the user from student to postgres. Then, we enter
psql
to start thepostgres
command-line console. In that console, we must create thetestdb
database. To come out of the console, run the\quit
command. Now, once again, start thetestdb
console by typingpsql testdb
and pressing Enter. Now, create a table in that database.
0 Comments