This section will cover MySQL database administration using Python. You already know Python has various modules for mysql database administration. So, we will learn about the MySQLdb module here. The mysqldb module is an interface for MySQL database server and is used to provide Python database API.

Let's learn how to install MySQL and a Python mysqldb package. For this, run the following command in your Terminal:

$ sudo apt install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account:

• The following code is used for checking for the mysqldb package to install:

$ apt-cache search MySQLdb

• And the following is for installing the Python interface for MySQL:

$ sudo apt-get install python3-mysqldb

• Now, we will check if mysql is installed properly or not. For this, run the following command in Terminal:

student@ubuntu:~$ sudo mysql -u root -p

Once the command runs, you will get the following output:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
 


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql>

By running sudo mysql -u root -p, you will get the mysql console. There are some commands used for listing databases and tables, and using the database to store our work. We will see them one by one:

• This is for listing all the databases:

show databases;

• And this is for using the database:

use database_name;

Whenever we come out of the MySQL console and log in again after some time, we must use the use database_name; statement. The purpose of using this command is that our work will be saved in our database. We can understand this in detail with the following examples:

• The following code is used for listing all the tables:

show tables;

These are the commands we use for listing databases, using the database, and listing the tables.

Now, we will create a database using a create database statement in the mysql console. Now, open the mysql console using mysql -u root -p, then enter your password, which you entered while installing, and press Enter. Next, create your database. In this section, we are going to create a database named test and we will use this database throughout this section:

student@ubuntu:~/work/mysql_testing$ sudo mysql -u root -p

Output:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 16
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.


mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.10 sec)
 
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> use test;
Database changed
mysql>

First, we listed all the databases using show databases. Next, we created our database test using the create database statement. Again, we executed show databases to find whether our database is created or not. Our database is now created. Next, we used that database to store the work we are doing.

Now, we are going to create a user and grant the privileges to that user. Run the following commands:

mysql> create user 'test_user'@'localhost' identified by 'test123';
Query OK, 0 rows affected (0.06 sec)

mysql> grant all on test.* to 'test_user'@'localhost';
Query OK, 0 rows affected (0.02 sec)
 
mysql>

We created a test_user user; the password for that user is test123. Next, we grant all the privileges to our test_user user. Now, come out of the mysql console by running a quit; or exit; command.

Now, we are going to see some examples for getting a database version, creating a table, inserting some data into the table, updating the data, and deleting the data.