GRADE XII - MYSQL CONNECTIVITY WITH PYTHON

 GRADE XII - MYSQL CONNECTIVITY WITH PYTHON

SOURCE CODE :

#ADD TO DATABASE

import mysql.connector

mydb = mysql.connector.connect(

host="localhost",

user="root",

passwd="root",

database="school" )

mycursor=mydb.cursor()

v1=input("enter GROCERY ID:")

v2=input("enter GROCERY NAME:")

v3=int(input("enter PRICE:"))

v4=int(input("enter QUANTITY:"))

sql='insert into GRO values("%s","%s","%d","%d")'%(v1,v2,v3,v4)

 #print(sql)

mycursor.execute(sql)

mydb.commit()

print("*********Grocery Added To Database Successfully*****")

#TO VIEW DATA

sql="select * from gro"

mycursor.execute(sql)

res=mycursor.fetchall()

print("The AVAILABLE GROCERY details are as follows : ")

print("G_ID    NAME     PRICE   QUANTITY")

print("\n##*************************************** ************ ##")

for x in res:

        print(x)

print("\n##***************************************************  ##")

#DELETION FROM DATABASE

 print("*******************DELETE  FORM **************************")

    en = input("Enter Grocery Id to be deleted :")

    query="select * from gro where ID="+en

    mycursor.execute(query)

    results = mycursor.fetchall()

    if mycursor.rowcount<=0:

        print("\## SORRY! NO MATCHING DETAILS AVAILABLE ##")

    else:

       

        print("**************************************************")

        print('%5s'%"NAME",'%15s'%'PRICE','%12s'%'QUANTITY')

        print("**************************************************")

        for row in results:

            print('%5s' % row[1],'%15s'%row[2],'%15s'%row[3])

    print("-"*50)

    ans = input("Are you sure to delete ? (y/n)")

    if ans=="y" or ans=="Y":

        query="delete from GRO where ID="+en

        mycursor.execute(query)

        mydb.commit()

        print("\n##***************** RECORD DELETED**********************  ##")

        print("\n##***************** ************************************  ##")

    

_____________________________________________________________________________

GRADE - XII

MYSQL-PYTHON CONNECTIVITY

PRACTICE QUESTIONS

1. Write the code to check whether the database has been created or not using Python interface.

2. Write a code to create a table ‘student’ inside the database ‘school’ using Python as the interface.

3. Write a code to check for the created table using Python.

4. How can we add a new column ‘marks’ in the student table?

5. Write the code to insert multiple records into the table student through Python interface.

6. How can we display all the records of a student table in Python?

7. Write the code to display all the records along with the total number of records from the student table using Python shell.

8. Kabir wants to write a program in Python to insert the following record in the table named Student in MYSQL database, SCHOOL:

· rno(Roll number ) - integer

· name(Name) - string

· DOB (Date of birth) – Date

· Fee – float

Note the following to establish connectivity between Python and MySQL:

· Username - root

· Password - tiger

· Host - localhost

a)     The values of fields rno, name, DOB and fee has to be accepted from the user. Help Kabir to write the program in Python.

b)    He also wants to display the records of students whose fee is more than 5000. Help him to write the program in Python.

 

9. A table, named STATIONERY, in ITEMDB database, has the following structure:

Field	Type
itemNo	int(11)
itemName	Varchar(15)
Price	Float
qty	int(11)

 

Write the following Python function to perform the specified operation:

AddAndDisplay(): To input details of an item and store it in the table STATIONERY. The function should then retrieve and display all records from the STATIONERY table where the Price is greater than 120.

Assume the following for Python-Database connectivity:

Host: localhost, User: root, Password: Pencil

 

 

ANSWERS

 

1. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456")
print("Yes, its connected")
mycursor=mydb.cursor()
mycursor.execute("Show Databases")
for i in mycursor:
    print(i)

2. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()
mycursor.execute("Create table student (RollNo int (5) primary key, Name varchar (15), age int, city varchar (20)")

3. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()

mycursor.execute("Show Tables")
for i in mycursor:
    print(i)

4. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()
mycursor.execute("alter table student add (marks int (3)")

 

5. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()
mycursor.execute("insert into student values (1, ‘Pooja’, 21, ‘Ggn’, 99)")

mycursor.execute("insert into student values (2, ‘Viraj’, 21, ‘Ggn’, 99)")

mycursor.execute("insert into student values (3, ‘Myra’, 21, ‘Ggn’, 99)")

mycursor.execute("insert into student values (4, ‘Ankit’, 21, ‘Ggn’, 99)")

mycursor.execute("insert into student values (5, ‘Raman’, 21, ‘Ggn’, 99)")

mydb.commit()

6. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()

mycursor.execute("Select * from student")

myrecords = mycursor.fetchall()
for i in myrecords:
    print(i)

 

7. import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", password="123456", database=”school”)
mycursor=mydb.cursor()

mycursor.execute("Select * from student")

myrecords = mycursor.fetchall()

no_rec=mycursor.rowcount

print(“Total no. of records found are:”, no_rec)
for i in mycursor:
    print(i)

 

8 a)

8. b)

9. def Add_Item():

    import mysql.connector as mycon

    mydb=mycon.connect(host="localhost",user="root", password="Pencil",database="ITEMDB")

    mycur=mydb.cursor()

    no=input("Enter Item Number: ")

    nm=input("Enter Item Name: ")

    pr=input("Enter price: ")

    qty=input("Enter qty: ")                                

    query="INSERT INTO stationery VALUES (“%s”, “%s”, “%s”, “%s”)”% (no, nm, pr, qty)

   print(query)

    mycur.execute(query)

    mydb.commit()

    mycur.execute("select * from stationery where price>120")

    for i in mycur:

        print(i)