To create a data base

#to create a database
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","")
prepare a cursor object using cursor() method
c = db.cursor()
sql="create database student;"
execute SQL query using execute() method.
c.execute(sql)
#disconnect from server
db.close()

To create a table in the above database

#to create a table in an existing database
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
c = db.cursor()
sql="""
create table stud(
roll int(5),
name char(10),
per int(5));"""
#execute SQL query using execute() method.
c.execute(sql)
#disconnect from server
db.close()

To insert a record in the above create table

#to insert data into the existing table in an existing database
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
c = db.cursor()
r='101'
n='sumit'
p='87'
try:
    # execute SQL query using execute() method.
    c.execute("insert into stud (roll,name,per) values (%s,%s,%s)",(r,n,p))
    #to save the data
    db.commit()
    print("Record Saved: 1 Record Inserted")
except:
    db.rollback()
#disconnect from server
db.close()

2nd Method to insert record in the table By taking input from the user

#take input for the details and thwn save the record in the databse
#to insert data into the existing table in an existing database
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
c = db.cursor()
r='101'
n='sumit'
p='87'
r=int(input("Enter roll no "))
n=input("Enter name ")
p=int(input("Enter per "))
try:
    # execute SQL query using execute() method.
    c.execute("insert into stud (roll,name,per) values (%s,%s,%s)",(r,n,p))
    #to save the data
    db.commit()
    print("Record saved")
except:
    db.rollback()
#disconnect from server
db.close()

To display all the record from the table

#display the records from a table
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
try:
    c = db.cursor()
    sql='select * from stud;'
    c.execute(sql)
    countrow=c.execute(sql)
    print("number of rows : ",countrow)
    #data=C.fetchone()
    data=c.fetchall()
    #print(data)
    for eachrow in data:
        print(eachrow)
except:
    db.rollback()
#disconnect from server
db.close()

2nd method to display all the record from table

#display the records from a table
#field by field
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
try:
    c = db.cursor()
    sql='select * from stud;'
    c.execute(sql)
    countrow=c.execute(sql)
    print("number of rows : ",countrow)
    #data=a.fetchone()
    data=c.fetchall()
    #print(data)
    print("Roll No   Name   Per")
    for eachrow in data:
        r=eachrow[0]
        n=eachrow[1]
        p=eachrow[2]
        # Now print fetched result
        print(r,"   ",n,"   ",p)
except:
    db.rollback()
#disconnect from server
db.close()

To search a record from the table using a numeric field (roll)

#searching a record by roll no
#display the records from a table
#field by field
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
try:
    z=0
    roll=int(input("Enter roll no to search "))
    c = db.cursor()
    sql='select * from stud;'
    c.execute(sql)
    countrow=c.execute(sql)
    print("number of rows : ",countrow)
    #data=a.fetchone()
    data=c.fetchall()
    #print(data)
    for eachrow in data:
        r=eachrow[0]
        n=eachrow[1]
        p=eachrow[2]
        # Now print fetched result
        if(r==roll):
            z=1
            print(r,n,p)
    if(z==0):
        print("Record is not present")
except:
    db.rollback()
#disconnect from server
db.close()

To delete a record from the tabl;e

#searching a record by roll no and deleting it
#display the records from a table
#field by field
import pymysql
#Open database connection
#conn=pymysql.connect(host='localhost',user='root',password='',db='test')
db = pymysql.connect("localhost","root","","student")
#prepare a cursor object using cursor() method
try:
    z=0
    roll=int(input("Enter roll no to delete "))
    c = db.cursor()
    sql='select * from stud;'
    c.execute(sql)
    countrow=c.execute(sql)
    print("number of rows : ",countrow)
    #data=a.fetchone()
    data=c.fetchall()
    #print(data)
    for eachrow in data:
        r=eachrow[0]
        n=eachrow[1]
        p=eachrow[2]
        # Now print fetched result
        if(r==roll):
            z=1
            print(r,n,p)
    if(z==0):
        print("Record is not present")
    else:
        r=str(roll)
        sql1="delete from student where roll='r'";
        print(sql1)
        c.execute(sql1)
        db.commit()
except:
    db.rollback()
#disconnect from server
db.close()