Question

Write a python program to connect python with mysql using database connectivity and perform the following operations on data in database: fetch, update and delete the data.

Answer 1

Answer:

Now we create database using Python in MySQL

import mysql.connector

db_connection = mysql.connector.connect(

host= "localhost",

user= "root",

passwd= "root"

)

# creating database_cursor to perform SQL operation

db_cursor = db_connection.cursor()

# executing cursor with execute method and pass SQL query

db_cursor.execute("CREATE DATABASE my_first_db")

# get list of all databases

db_cursor.execute("SHOW DATABASES")

#print all databases

for db in db_cursor:

print(db)

Answer 2

Code:

import mysql.connector as mysql

con=mysql.connect(host='localhost', user='root', password='root', database='xii B')

cur=con.cursor()

def fetch():

   cur.execute("select * from <tablename>")

   recs=cur.fetchone()   #for fetching rows(or records) one by one

   rec=cur.fetchall()   #for fetching all data together

   print(recs)

   print(rec)

def update():

   cur.execute("select * from <tablename>")

   rec=cur.fetchall()

   x=int(input('Enter Roll No. to be searched: '))  #instead of roll number, put column name through which records will be searched

   y=float(input('Enter Avg. marks to be updated: '))   #instead of avg. marks, put column name which should be updated in that particular row

   for i in rec:

       if i[0]==x:

           cur.execute("update student set avg_marks= %s where Roll_No= %s"%(y,x))

           break

       cur.execute("commit")

def delete():

   cur.execute("delete from <tablename> where name='xyz'") #this can be used to delete a particular row

   cur.execute("alter table <tablename> drop column <columnname>") #this can be used to delete a column    

   cur.execute("drop table <tablename>") #this can be used to delete a whole table... this is a DDL command hence table cannnot be recovered if once deleted.

#Note: you can add 'for' loop in fetchone() function to display the output more beautifully..

hope you will like my answer...