Now, we are going to create a table and we will insert some data into it. For that, create a create_insert_data.py script and write the following content in it:

import MySQLdb as mdb

con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test')
with con_obj:
            cur_obj = con_obj.cursor()
            cur_obj.execute("DROP TABLE IF EXISTS books")
            cur_obj.execute("CREATE TABLE books(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100))")
            cur_obj.execute("INSERT INTO books(Name) VALUES('Harry Potter')")
            cur_obj.execute("INSERT INTO books(Name) VALUES('Lord of the rings')")
            cur_obj.execute("INSERT INTO books(Name) VALUES('Murder on the Orient Express')")
            cur_obj.execute("INSERT INTO books(Name) VALUES('The adventures of Sherlock Holmes')")
            cur_obj.execute("INSERT INTO books(Name) VALUES('Death on the Nile')")

print("Table Created !!")
print("Data inserted Successfully !!")

Run the script and you will get the following output:

student@ubuntu:~/work/mysql_testing$ python3 create_insert_data.py

Output:
Table Created !!
Data inserted Successfully !!

To check whether your table is created successfully or not, open your mysql console and run the following commands:

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

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 6
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>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
+----------------+
1 row in set (0.00 sec)

You can see that your table books is created.