Python Mysql Database connectivity

Python Mysql Database connectivity

Basic Program to insert a record

import mysql.connector

conn = mysql.connector.connect(host = 'localhost', user = 'root', passwd = 'root', database = 'school')

if conn.is_connected():

  id = input("Enter ID: ")

  nm = input("Name: ")

  uid = input("UID: ")

  dob = input("Date of Birth (YYYY-MM-DD: ")
  city = input("City: ")

  sql = "insert into student values('{}','{}','{}','{}','{}')".format(id, nm, uid, dob, city)

  cursor = conn.cursor()

  cursor.execute(sql)

  conn.commit()

else :

  print("Error")

Basic Program to select a record

import mysql.connector

conn = mysql.connector.connect(host='localhost',user='root',passwd='root', database='school')

if conn.is_connected():

     city=input("City: ")

     sql = "select * from student where city='{}'".format(city)

     cursor = conn.cursor()

     cursor.execute(sql)

     rs = cursor.fetchall()

     for x in rs:

         print(x)

else:

     print("Error")

Basic Program to delete a record

import mysql.connector

conn = mysql.connector.connect(host='localhost',user='root',passwd='root', database='school')

if conn.is_connected():

     id=input("ID: ")

     sql = "delete from student where id={}".format(id)

     cursor = conn.cursor()

     cursor.execute(sql)

     conn.commit()

else:

     print("Error")

 Basic Program to update a record

 

import mysql.connector

conn = mysql.connector.connect(host='localhost',user='root',passwd='root', database='school')

if conn.is_connected():

     id=input("ID: ")

     city = input("City: ")

     sql = "update student set city='{}' where id={}".format(city,id)

     cursor = conn.cursor()

     cursor.execute(sql)

     conn.commit()

else:

     print("Error")