Python with MySQL Connectivity: Database & Table [Exercises]

Python with MySQL Connectivity: Database & Table [Exercises]

Q1. The code given below inserts the following record in the table Employee:

Empid – integer Name – string salary-float

Note the following to establish connectivity between Python and MYSQL:

·  Username is root

·  Password is tiger

·  The table exists in a MYSQL database named Empolyee.

·  The details (Empid, Name, salary) are to be accepted from the user.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the command that inserts the record in the table Employee.

Statement 3- to add the record permanently in the database

import mysql.connector

from mysql.connector import Error

connection = mysql.connector.connect(host='localhost', database='Employee', user='root', password='tiger')

cursor=_______________________#STATEMENT1

empid=int(input("enter Empid"))

name=input("enter name")

salary=float(input("ENTER SALARY"))

result = __________________________#STATEMENT2

___________________________________#STATEMENT3

SOLUTION:

                      STATEMENT 1: connection.cursor()

                      STATEMENT 2: cursor.execute("Insert into Employee values (%s,%s,%s)", (empid,name,salary))

                      STATEMENT 3: connection.commit()

Q2. The code given below reads the following record from the table named Employee and displays only those records who have Salary greater than 25000:

Note the following to establish connectivity between Python and MYSQL:

 Username is root

 Password is tiger

 The table exists in a MYSQL database named Employee.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the query that extracts records of those Employees who have salary greater than 25000.

Statement 3- to read the complete result of the query (records whose salary greater than 25000) into the object named records, from the table Employee in the database.

import mysql.connector

connection = mysql.connector.connect(host='localhost',database='Employee', user='root', password='tiger')

cursor=________________________#STATEMENT1

_________________________________________#STATEMENT2

records = _____________________________#STATEMENT3

for row in records:

     print("Empid",row[0],end=" ")

     print("name",row[1],end=" ")

     print("salary",row[2],end=" ")

     print()

SOLUTION: 

                 STATEMENT 1: connection.cursor()

                 STATEMENT 2: data=cursor.execute("SELECT * FROM EMPLOYEE WHERE SALARY>25000")

                 STATEMENT 3: records=data.fetchall() 

Q3. The code given below inserts the following record in the table Employee:

EmpNo – integer Name – string

Department – string Salary – integer

Note the following to establish connectivity between Python and MYSQL:

 Username is root

 Password is brick

 The table exists in a MYSQL database named organization.

 The details (EmpNo, Name, Department and Salary) are to be accepted from the user.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the command that inserts the record in the table Student.

Statement 3- to add the record permanently in the database

import mysql.connector as mysql

def sql_data():

       con=mysql.connect(host="localhost",user="root", password="brick", database="organization")

       mycursor=_______________________                      #Statement 1

       eno=int(input("Enter Employee number :: "))

       name=input("Enter Name :: ")

       dept=input("Enter Department name :: ")

       sal=int(input("Enter Salary :: "))

       query="insert into student values({},'{}',{},{})".format(eno,name,dept,sal)

       _______________________                                         #Statement 2

       _______________________                                         # Statement 3

      print("Data Added successfully")

SOLUTION: 

                 STATEMENT 1: con.cursor()

                 STATEMENT 2: data=cursor.execute(query)

                 STATEMENT 3: con.commit() 

 

Q4. The code given below reads the following record from the table named items and displays only those records who have price greater than 100:

ItemNo –integer

Name – string

Price – integer

Note the following to establish connectivity between Python and MYSQL:

 Username is root

 Password is epic

 The table exists in a MYSQL database named store.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the query that extracts records of items with price greater than 100.

Statement 3 - to read the complete result of the query  into the object named data, from the table student in the database.

import mysql.connector as mysqlcon

def sql_data():

     con=mysqlcon.connect(host="localhost",user="root", password="epic",database="store")

     mycursor= ______________________                         #Statement1

     print("Items with price greater than 100 are :")

     ________________________                                         #Statement2

     data= ____________                                                        #Statement3

     for i in data:

          print(i)

SOLUTION: 

                 STATEMENT 1: con.cursor()

                 STATEMENT 2: mycursor.execute("SELECT * FROM ITEMS WHERE PRICE>100")

                 STATEMENT 3: data=mycursor.fetchall() 

Q5. Virat has created a table named TRAVELS in MySQL:

Tour_ID – string

Destination – String

Geo_Cond– String

Distance – integer (In KM)

       Note the following to establish connectivity between Python and MYSQL:

 Username is root

 Password is bharat

 The table TRAVELS exists in a MYSQL database named TOUR.

 The details Tour_ID, Destination, Geo_Cond and Distance are to be accepted from the user.

Virat wants to display All Records of TRAVELS relation whose Geographical condition is hilly area and distance less than 1000 KM. Help Virat to write program in python.

SOLUTION: 

import mysql.connector as mysqlcon

def sql_data():

     con=mysqlcon.connect(host="localhost",user="root", password="bharat", database="TOUR")

     mycursor= con.cursor()                                                                     #Statement1

     print("Geographical condition is hilly area and distance less than 1000 KM :")

     mycursor.execute("SELECT Tour_ID, Destination, Geo_Cond, Distance FROM Travels WHERE Geo_Cond='hilly' and Distance <1000")#Statement2

     data= mycursor.fetchall()                                                                  #Statement3

     for i in data:

          print(i)

Q6. Aarya has created a table named Emp in MySQL:

EmpNo – integer

EmpName – string

Age– integer

Salary – integer

      Note the following to establish connectivity between Python and MYSQL:

 Username - root

 Password - tiger

 Host - localhost

 The Emp table exists in a MYSQL database named company.

 The details of Emp table (EmpNo, EmpName, Age and Salary)

      Aarya wants to display All Records of Emp relation whose age is greater than 55. Help Aarya to write program in python.

SOLUTION: 

import mysql.connector

def sql_data():

     con=mysql.connector.connect(host="localhost",user="root", password="tiger", database="COMPANY")

     mycursor= con.cursor()                                                                     

     mycursor.execute("SELECT * FROM Emp WHERE Age>55')

     data= mycursor.fetchall()                                                                  

     for i in data:

          print(i)

     con.close()

Q7. Complete the following database connectivity program by writing missing statements and perform  the given query.                                                                                                                                

import  ______________________________ as sqltor                              # 1

mycon = sqltor.________( host = “localhost”, user = “root”, passwd = “123”, database = “medicine” )                                                                                          #2

cursor = mycon.cursor( )

cursor.execute(______________________ )                                              #3

data = cursor._________________                                                             #4

for rec in data:

        print ( rec )

mycon.close( )

a.       Complete the statement #1,by writing the name of library / package need to import for database connectivity.

b.      Complete the statement #2, write the name of method require to create connection between python and mysql.

c.       Complete the statement #3, write the query to display those drug records which price is between the 50 to 100 from table DRUG.

     d. Complete the statement #4, to retrieve all records from the result set.

SOLUTION: 

                 STATEMENT 1: mysql.connector

                 STATEMENT 2: connect

                 STATEMENT 3: cursor.execute("SELECT * FROM DRUG WHERE PRICE BETWEE 50 AND 100")

                 STATEMENT 4: data=cursor.fetchall() 

Q8. Vihaan wants to write a program in Python to create the following table named “EMP” in MYSQL database, ORGANISATION:

Eno (Employee No )- integer , Ename (Employee Name) - string

Edept (Employee Department)-string, Sal (salary)-integer

Note the following to establish connectivity between Python and MySQL:

Username – root , Password – admin , Host - localhost

Help Vihaan to write the program in Python to Alter the above table with new column named Bonus integer.

SOLUTION: 

import mysql.connector

con=mysql.connector.connect(host="localhost",user="root", password="admin", database="ORGANISATION")

     mycursor= con.cursor()                                                                     

     mycursor.execute("ALTER TABLE EMP ADD COLUMN BONUS INT')

     con.commit()

     con.close()

Q9. Complete the following database connectivity program by writing the missing statements and performing the given query

import ______________ as mysql                                                                                                 # statement 1

con=mysql. ---------(host=’localhost’,user=’root’,passwd=’123’ ,database=’student’)         # statement 2

cursor=con.cursor( )

cursor.execute(____________________)                                                                        # statement 3

data=cursor. -----------------------------------                                                                      # statement 4

for rec in data:

print(rec)

con.close( )

a) Complete the statement 1 by writing the name of package need to be imported for database connectivity.

b) Complete the statement 2 by writing the name of method require to create connection between Python and mysql.

c) Complete the statement 3 by writing the query to display those students record whose mark is between 50 and 90 from table “student”

SOLUTION: 

                 STATEMENT 1: mysql.connector

                 STATEMENT 2: connect

                 STATEMENT 3: cursor.execute("SELECT * FROM STUDENT WHERE MARK BETWEE 50 AND 90")

                 STATEMENT 4: data=cursor.fetchall() 

Q10. Maya has created a table named BOOK in MYSQL database, LIBRARY

BNO(Book number )- integer

B_name(Name of the book) - string

Price (Price of one book) –integer

Note the following to establish connectivity between Python and MySQL: Username – root, Password – writer, Host – localhost.

Maya, now wants to display the records of books whose price is more than 250. Help Maya to write the program in Python

SOLUTION: 

import mysql.connector

con=mysql.connector.connect(host="localhost",user="root", password="writer", database="LIBRARY")

     mycursor= con.cursor()                                                                     

     mycursor.execute("SELECT* FROM BOOK WHERE  PRICE>250')

     data=mycursor.fetchall()

     for row in data:

                       print(row)

     con.close()

Q11. The code given below reads records from the table named student and displays only those records who have marks greater than 75.The structure of a record of table Student is:

RollNo–integer; Name –string; Clas –integer; Marks– integer

Note the following to establish connectivity between Python and MYSQL:

(i) Username is root

(ii) Password is abc

(iii) The table exists in a MYSQL database named school.

(iv) The details(RollNo, Name, Class and Marks) are to be accepted from the user.

Write the following missing statements to complete the code:

Statement 1 – to create the cursor object

Statement 2 – to execute the query that extracts records of those students whose marks are greater than 75.

Statement3-to read the complete result of the query(records whose marks are greater than 75) into the object named data, from the table student in the database.

import mysql.connector as mysql

def sql_data():

con1=mysql.connect(host="localhost",user="root", password="abc",database="school")

mycursor= __________________________                                                                   #Statement 1

print("Students with marks greater than75are:")

________________________________                                                                          #Statement 2

data= ___________________________                                                                          #Statement3

for i in data:

      print(i)

SOLUTION: 

                 STATEMENT 1: con1.cursor()

                 STATEMENT 2: mycursor.execute("SELECT * FROM STUDENT WHERE MARK >75")

                 STATEMENT 3: data=cursor.fetchall() 

            

Q12. Sumitra wants to write a program to connect to MySQL database using python and increase the age of all the students who are studying in class 11 by 2 years.Since she had littleunderstanding of the coding, she left a few blank spaces in her code. Now help her to complete the code by suggesting correct coding for statements 1, 2 and 3.

import _______ as myc                                                                                                # Statement 1
con = myc.connect(host=”locahost”, user=”root”, passwd=””, database=”mydb”)
mycursor = _______                                                                                                           # Statement 2
sql = “UPDATE student SET age = age + 2 WHERE class=’XI’ “
mycursor.execute(sql)

sql = “SELECT * FROM student”
mycursor=con.execute(sql)
result = _________                                                                                                            # Statement 3
for row in result:
       print(row)

Statement 1: The required module to be imported
Statement 2: To initialize the cursor object.
Statement 3: To read all the rows from the cursor object.

SOLUTION: 

                 STATEMENT 1: mysql.connector

                 STATEMENT 2: con.cursor()

                 STATEMENT 3: result=mycursor.fetchall() 

Q13. Pikato wrote a program which he wants to use to connect with MySQL and show the name of the all the record from the table “Student” from the database “School”. You are required to complete the statements so that the code can be executed properly.

import_____.connector____pymysql                                                                   # statement 1

dbcon=pymysql.__(host=”localhost”,user=”root”, _____=”sia@1928”, _____)          # statement 2

if dbcon.isconnected()= =False:

      print(“Error in establishing connection:”)

cur=dbcon…………….()                                                                                        # statement 3

query=”select * from stmaster”

cur.execute(_________)                                                                                         # statement 4

resultset=cur.fetchmany(3)

for row in resultset:

       print(row)

dbcon.______()                                                                                                        # statement 5

SOLUTION: 

                 STATEMENT 1: mysql.connector

                 STATEMENT 2: password

                 STATEMENT 3: cursor()

                 STATEMENT 4: query

                 STATEMENT 5: close() 

Q14. Preety has written the code given below to read the following record from the table named employee and displays only those records who have salary greater than 53500:

Empcode – integer

EmpName – string

EmpSalary – integer

Note the following to establish connectivity between Python and MYSQL:

• Username is root

• Password is root@123

• The table exists in a MYSQL database named management.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the query that extracts records of those employees whose salary are greater than 53500.

Statement 3- to read the complete result of the query (records whose salary are greater than 53500) into the object named data, from the table employee in the database.

import mysql.connector as mysql

def sql_data():

con1=mysql.connect(host="localhost",user="root", password="root@123",

database="management")

mycursor=_______________ #Statement 1

print("Employees with salary greater than 53500 are : ")

_________________________ #Statement2

data=__________________ #Statement 3

for i in data:

       print(i)

print()

SOLUTION: 

                 STATEMENT 1: con1.cursor()

                 STATEMENT 2: mycursor.execute("SELECT * FROM EMPLOYEE WHERE SALARY>53500 ")

                 STATEMENT 3: data=mycursor.fetchall()           

Q15. A table Drug is created in the database Medicine. The details of table are given below.    

        

import  mysql.connector as sqltor                 

mycon = sqltor.connect( ___________________ )                                                     #1

cursor = mycon.cursor( )

cursor.execute(______________________ )                                                                         #2

data = _________________                                                                                      #3

for rec in data:

     print ( rec )

_____________________                                                                                          #4

mycon.close( )

a. Complete the statement #1 to write appropriate missing parameter and values.

      b. Write the  statement #2, to fetch drugName, and price from table Drug in descending order of price.

      c. Complete the statement #3, to fetch only three records from the resultset.

          d. What statement you will write in place of statement #2 to insert one more record in table Drug, as well as in statement #4, to make your changes permanent in the table Drug.

SOLUTION: 

                 STATEMENT 1: host='localhost', user='root' password='root', database='medicine'

                 STATEMENT 2: mycursor.execute("SELECT drugName, price FROM Drug ORDER BY Price ")

                 STATEMENT 3: data=mycursor.fetchmany(3)

                 STATEMENT 4: mycursor.execute("Insert into Drug values ('R1005', '5632', 'Brufin', 23.44, 'OXFORD', 'UNA')")

                                               mycon.commit()

                      

Q16. Consider the table

TRAINER

TID	TNAME	CITY	HIREDATE	SALARY
101	SUNAINA	MUMBAI	1998‐10‐15	90000
102	ANAMIKA	DELHI	1994‐12‐24	80000
103	DEEPTI	CHANDIGARG	2001‐12‐21	82000
104	MEENAKSHI	DELHI	2002‐12‐25	78000
105	RICHA	MUMBAI	1996‐01‐12	95000
106	MANIPRABHA	CHENNAI	2001‐12‐12	69000

The Following program code is used to increase the salary of Trainer SUNAINA by 2000.

Note the following to establish connectivity between Python and MYSQL:

Username is root

Password is system

The table exists in a MYSQL database named Admin.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the command that inserts the record in the table Student.

Statement 3- to add the record permanently in the database

import mysql.connector as mydb

mycon = mydb.connect(host = “localhost”,user = “root”, passwd = “system”,database = “Admin”)

cursor = _______________ #Statement 1

sql = “UPDATE TRAINER SET SALARY = SALARY + 2000 WHERE TNAME = ‘SUNAINA’”

cursor. _______________ #Statement 2

_______________ #Statement 3

mycon.close( )

SOLUTION:

                 STATEMENT 1: mycon.cursor()

                 STATEMENT 2: mycursor.execute(sql)

                 STATEMENT 3: mycon.commit()

Q17. Consider the table

TABLE : GRADUATE

S.NO	NAME	STIPEND	SUBJECT	AVERAGE	DIV
1	KARAN	400	PHYSICS	68	I
2	DIWAKAR	450	COMP Sc	68	I
3	DIVYA	300	CHEMISTRY	62	I
4	REKHA	350	PHYSICS	63	I
5	ARJUN	500	MATHS	70	I
6	SABINA	400	CHEMISTRY	55	II
7	JOHN	250	PHYSICS	64	I
8	ROBERT	450	MATHS	68	I
9	RUBINA	500	COMP Sc	62	I
10	VIKAS	400	MATHS	57	II

The Following program code is used to view the details of the graduate whose subject is PHYSICS.

Note the following to establish connectivity between Python and MYSQL:

Username is root

Password is system

The table exists in a MYSQL database named Admin.

Write the following missing statements to complete the code:

Statement 1 – to import the proper module

Statement 2 – to create the cursor object.

Statement 3- to Close the connection

import ______________ as mydb #Statement 1

mycon = mydb.connect(host = “localhost”,user = “root”,passwd = “system”,database = “Admin”)

cursor = _________________ #Statement 2

sql = “SELECT * FROM GRADUATE WHERE SUBJECT = ‘PHYSICS’ ”

cursor. execute(sql)

mycon.commit ( )

___________________ #Statement 3

 SOLUTION:

                STATEMENT 1: mysql.connector

                 STATEMENT 2: mycon.cursor.execute()

                 STATEMENT 3: mycon.close()

Q18. The code given below accepts the increments MARKS the value of Class by 1 for each student. The structure of a record of table Student is:

RollNo – integer; Name – string; Clas – integer; Marks – integer

Note the following to establish connectivity between Python and MYSQL:

 Username is root, Password is abc

 The table exists in a MYSQL database named school.

Write the following missing statements to complete the code:

import mysql.connector as mysql

def sql_data():

        con1=mysql.connect(host="localhost",user="root",password="abc")

        _______________________________                    #Statement 1

        crsr.execute("use school")

        _______________________________                    #Statement 2

        crsr.execute(querry)

        _______________________________                    # Statement 3

        print("Data updated successfully")

Statement 1 – to create the cursor object.

Statement 2 – to create the query to update the table.

Statement 3- to make the updating in the database permanent

SOLUTION:

                 STATEMENT 1: con1.cursor()

                 STATEMENT 2: mycursor.execute("UPDATE STUDENT SET MARKS=MARKS+1")

                 STATEMENT 3: mycon.commit()

Q19. The code given below reads records from the table named Vehicle and displays only those records which have model later than 2010. The structure of a record of table Vehicle is:

V_ID – integer; Name – string; Model – integer; Price – integer

Note the following to establish connectivity between Python and MYSQL:

 Username is root

 Password is abc

 The table exists in a MYSQL database named Transport.

 The details (RollNo, Name, Clas and Marks) are to be accepted from the user.

Write the following missing statements to complete the code:

Statement 1 – to create the cursor object

Statement 2 – to execute the query that extracts records of those vehicles whose model is greater than 2010.

Statement 3 - to read the complete result of the query into the object tnamed data.

import mysql.connector as mysql

def display():

     con1=mysql.connect(host="localhost",user="root",password="abc", database="Transport")

     __________________________________#Statement 1

     print("Students with marks greater than 75 are : ")

     q="Select * from vehicle where model>2010"

     __________________________________#Statement 2

    data= ________________________ #Statement 3

    for rec in data:

             print(rec)

SOLUTION:

                 STATEMENT 1: con1.cursor()

                 STATEMENT 2: mycursor.execute(q)

                 STATEMENT 3: data=mycursor.fetchall()

Q20. The code given below inserts the following record in the table Book:

B_No – integer           B_Name – string      B_Author – string                Price – Decimal

Note the following to establish connectivity between Python and

MYSQL:

▪ Username is root                                                                            ▪ Password is tiger

▪ The table exists in a MYSQL database named Library.

The details (B_No, B_Name, B_Author and Price) are to be accepted from the user.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the command that inserts the record in the table Book.

Statement 3- to add the record permanently in the database

import mysql.connector as mysql

def sql_data():

conn=mysql.connect(host="localhost", user="root", password="tiger", database="Library")

mycursor=_________________ #Statement 1

B_No=int(input("Enter Book Number : "))

B_Name=input("Enter Book Name : ")

B_Author=input("Enter Author : ")

Price=float(input("Enter Price : "))

sql="insert into Book values({},'{}','{}',{})".format(B_No, B_Name, B_Author, Price)

______________________ #Statement 2

______________________ # Statement 3

print("Data Added successfully")

conn.close()

SOLUTION:

                 STATEMENT 1: conn.cursor()

                 STATEMENT 2: mycursor.execute(sql)

                 STATEMENT 3: mycon.commit()

Q21. The code given below reads and fetches all the records from EMP table having salary more than 25000.

empno - integer, ename- string and salary- integer.

Note the following to establish connectivity between Python and MYSQL:

▪ Username is root                                                                                   ▪ Password is tiger

▪ The table exists in a MYSQL database named company.

Write the following missing statements to complete the code:

Statement 1 – to form the cursor object

Statement 2 – to execute the query that extracts records of those employees having salary more than 25000.

Statement 3- to read the complete result of the query (records whose salary is more than 25000) into the object named data, from the table EMP in the database.

import mysql.connector as mysql

def sql_data():

     conn=mysql.connect(host="localhost", user="root", password= "tiger", database="company")

     mycursor=______________ #Statement 1

     print("Employees with salary more than 25000 are : ")

     try:

           ______________________________ #Statement 2

           resultset=_____________________ #Statement 3

           for row in resultset:

                  print(row)

      except:

           print("Error: unable to fetch data")

      conn.close()

SOLUTION:

                 STATEMENT 1: conn.cursor()

                 STATEMENT 2: mycursor.execute("SELECT * FROM EMP WHERE SALARY>25000 ")

                 STATEMENT 3: resultset=mycursor.fetchall()

Q22. Avni is trying to connect Python with MySQLfor her project.

Help her to write the python statement on the following:-

(i) Name the library, which should be imported to connect MySQL with Python.

(ii) Name the function, used to run SQL query in Python.

(iii) Write Python statement of connect function having the arguments values as :

Host name :192.168.11.111               User : root

Password: Admin                                            Database : MYPROJECT

SOLUTION: 

import mysql.connector

con=mysql.connector.connect(host=192.168.11.111, user="root", password="Admin", database="MYPROJECT")

mycursor= con.cursor()                                                                     

mycursor.execute("SELECT* FROM Library')

data=mycursor.fetchall()

for row in data:

                       print(row)

con.close()

Q23. Note the following to establish connectivity between Python and MYSQL:

 Username is myusername

 Password is mypassword

 The table Customers exists in a MYSQL database named mydatabase

Write the following missing statements to complete the code:

Statement 1 – to create the connection object

Statement2– to create the cursor object

Statement3-To execute the sql query

import mysql.connector

mydb = ________________________________________________________________ # Statement 1

mycursor = _______________________________ # Statement 2

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"

val = ("John", "Highway 21")

mycursor._________________ # Statement 3

mydb.commit()

print(mycursor.rowcount, "record inserted.")

SOLUTION:                   

                STATEMENT 1: mysql.connector.connect(host='localhost', user='myusername' password='mypassword', database='mydatabase')

                 STATEMENT 2: mycursor =mydb.cursor()

                 STATEMENT 3: mycursor.execute(sql, val)