Using MySQL as Database with Python

Sample Application

Create database as library

Create table as book with four columns

1. bookno, int

2. title, varchar, 100

3. author, varchar, 100

4. price, float

Enter some sample records 

Inserting records into database table : savebook.py

#inserting record

import MySQLdb

cn=MySQLdb.connect(host='localhost',user='root',passwd='',database='library',port=3306)

cursor = cn.cursor()

bookno=int(input("Enter book no "))

title=input("Title ")

author=input("Author ")

price=float(input("Price "))

sql="insert into book values ({},'{}','{}',{})".format(bookno,title,author,price)

cursor.execute(sql)

cn.commit()  

cn.close()  

    

Show all records present in the table book: showbooks.py

import MySQLdb
cn=MySQLdb.connect(host='localhost',user='root',passwd='',db='library')
cursor = cn.cursor()
cursor.execute('select * from book')
r=cursor.fetchall()
for b in r:
    print(b[0],b[1],b[2],b[3])
cn.close()




Search a book based on bookno
import MySQLdb
bookno=int(input("Book no to search : "))
cn=MySQLdb.connect(host='localhost',user='root',passwd='',db='library')
cursor = cn.cursor()
sql='select * from book where bookno={}'.format(bookno)
n=cursor.execute(sql)
if n==0:
    print("Sorry! Bookno",bookno,"not found")
else:
    r=cursor.fetchall()
    print("Record found")
    print(r[0][0],r[0][1],r[0][2],r[0][3])

cn.close()   

Updating a book based on bookno

import MySQLdb
bookno=int(input("Book no to update : "))

cn=MySQLdb.connect(host='localhost',user='root',passwd='',db='library')

cursor = cn.cursor()
sql='select * from book where bookno={}'.format(bookno)
n=cursor.execute(sql)
if n==0:
    print("Sorry! Bookno",bookno,"not found")
else:
    r=cursor.fetchall()
    print("Record found")
    print(r[0][0],r[0][1],r[0][2],r[0][3])
    title=input("Enter new title ")
    author=input("Enter new author ")
    price=float(input("Enter the price "))
    sql="update book set title='{}',author='{}',price={} where bookno={}".format(title,author,price,bookno)
    cursor.execute(sql)
    cn.commit()
    print("Record updated successfully")
cn.close()

 

Deleting a record based on bookno

import MySQLdb
bookno=int(input("Book no to delete : "))

cn=MySQLdb.connect(host='localhost',user='root',passwd='',db='library')

cursor = cn.cursor()
sql='delete from book where bookno={}'.format(bookno)
n=cursor.execute(sql)
if n==0:
    print("Sorry! Bookno",bookno,"not found")
else:
    cursor.execute(sql)
    cn.commit()
    print("Record deleted successfully")
cn.close()


Learn Python with Dr B P Sharma
Facebook: https://www.facebook.com/drbpsharma.in
Mobile    : +91 9810849501
Email      : bpsharma@gmail.com