DataBase Connection


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32


import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
output = cursor.fetchone()
print ("Database version : %s " % output)

# disconnect from server
db.close()


Create Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Create table as per requirement
sql = """CREATE TABLE STUDENT (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         MARKS FLOAT )"""

cursor.execute(sql)


# disconnect from server
db.close()


Insert Data Into Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO STUDENT(FIRST_NAME,
         LAST_NAME, AGE, MARKS)
         VALUES ('Varshini', 'm', 24, 24)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()



# disconnect from server
db.close()


							
							

Insert Data Into Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO STUDENT(FIRST_NAME, \
       LAST_NAME, AGE, Marks) \
       VALUES ('%s', '%s', '%d', '%d' )" % \
       ('Varshini', 'M', 24, 24)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()



# disconnect from server
db.close()


Retrive The Data From The Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM STUDENT"
       
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      marks = row[3]
      # Now print fetched result
      print ("fname=%s,lname=%s,age=%d,income=%d" % \
             (fname, lname, age, marks ))
except Exception as e:
   print ("unable to fecth data",e)


# disconnect from server
db.close()


Update Data FRom The Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "UPDATE STUDENT SET MARKS = MARKS + 10 WHERE Marks < '%d'" % (50)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Delete Data From The Table


#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","Rain" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = "DELETE FROM Student WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()