Python with MySQL (Database connectivity)

DATE:08/10/2024
S.No. NAME OF PRACTICAL

Write a program to connect Python with MySQL using database connectivity and
perform the following operations on data in database BookShop:
Create the table Book and insert records.

1 CREATE A TABLE
Table: Book

BookNo BookName Price Author Publisher

import mysql.connector as sql

db = sql.connect(
SOUR host="localhost",
CE user="root",
CODE
password="root",
)

def create():
cursor = db.cursor()
cursor.execute('DROP DATABASE IF EXISTS
bookshop')
cursor.execute('CREATE DATABASE bookshop')
print('Database creation has been successful')

cursor.execute('USE bookshop')
cursor.close()
cursor = db.cursor()
cursor.execute('use bookshop')
create_table_query = """
CREATE TABLE IF NOT EXISTS Book (
BookNo INT AUTO_INCREMENT PRIMARY
KEY,
BookName VARCHAR(255) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Author VARCHAR(255) NOT NULL,
Publisher VARCHAR(255) NOT NULL
)
"""
cursor.execute(create_table_query)
print("Table 'Book' created successfully!")
cursor.close()
db.close()
def insert():

OUTP Database creation has been successful

UT
Table 'Book' created successfully
2 INSERT THE DATA
SOUR
CE
CODE import mysql.connector as sql

db = sql.connect(
host="localhost",
user="root",
password="root"
)

def insert():
cursor = db.cursor()
cursor.execute('USE bookshop')
i = int(input('How many books to append? '))
for x in range(i):
a = int(input('Enter book number: '))
b = input("Enter book name: ")
c = int(input("Enter price: "))
d = input("Enter Author Name: ")
e = input("Enter Publisher: ")

insert_query = """
INSERT INTO Book (BookNo, BookName,
Price, Author, Publisher)
VALUES (%s, %s, %s, %s, %s)
"""
cursor.execute(insert_query, (a, b, c, d, e))
print(a, 'Inserted successfully')
db.commit()
cursor.close()
db.close()

print("Inserting process done")

insert()
OUTP
How many books to append? 4
UT
Enter book number: 1
Enter book name: The 100 Rules Of The Sigma
Male
Enter price: 5000
Enter Author Name: He Who Howls
Enter Publisher: Dupinder Records
1 Inserted successfully
Enter book number: 2
Enter book name: A Beginner's Guide To Taxi
Driving
Enter price: 300
Enter Author Name: Dupinder Kuttanad
Enter Publisher: The Hippie AutoMobile Publishing
House
2 Inserted successfully
Enter book number: 3
Enter book name: How to be Invisible like Me
Enter price: 4000
Enter Author Name: Cuckshey
Enter Publisher: NPC Books and Services
3 Inserted successfully
Enter book number: 4
Enter book name: How to Win A Lottery
Enter price: 5000
Enter Author Name: Lottery Winner Baiju
Enter Publisher: Shetty Distillation
4 Inserted successfully
Inserting process done
Python with MySQL (Database connectivity)

DATE: 15/10/2024
S.No. NAME OF PRACTICAL

Write a program to connect Python with MySQL using database connectivity and perform the
following operations on data in database BookShop and table Book.

3 FETCH THE DATA

SOURC
FETCH.py
E
CODE
import mysql.connector as sql

def fetch():
db = sql.connect(
host="localhost",
user="root",
password="root",
database="bookshop"
)

cursor = db.cursor()
cursor.execute('SELECT * FROM book')
data = cursor.fetchall()

bookno = 'BookNo'.ljust(10)
bookname = 'BookName'.ljust(50)
price = 'Price'.ljust(10)
author = 'Author'.ljust(30)
publisher = 'Publisher'.ljust(30)

print(bookno,bookname,price,author,publisher)
print('-'*1)

for i in data:
print(str(i[0]).ljust(10),i[1].ljust(50),str(i[2]).ljust(10),i[3].ljust(30),i[4]
.ljust(30))

cursor.close()
db.close()

fetch()

OUTPU
T
Python with MySQL (Database connectivity)

DATE:18/10/2024
S.No. NAME OF PRACTICAL

Write a program to connect Python with MySQL using database connectivity and
perform the following operations on data in database BookShop and table Book.

4 UPDATE THE RECORD

SOURCE
db = sql.connect(
CODE
host="localhost",
user="root",
password="root"
)

def update():
cursor = db.cursor()
cursor.execute('USE bookshop')

b = int(input('Enter book number to be updated: '))

cursor.execute('SELECT * FROM Book WHERE
BookNo = %s', (b,))
data = cursor.fetchone()

if data is not None:

n = int(input("Enter new book number: "))
m = input('Enter new book name: ')
o = float(input('Enter new price: '))
p = input('Enter new author: ')
q = input('Enter new publisher: ')

update_query = """
UPDATE Book
SET BookNo = %s, BookName = %s, Price = %s,
Author = %s, Publisher = %s
WHERE BookNo = %s
"""
cursor.execute(update_query, (n, m, o, p, q, b))
db.commit()

print(cursor.rowcount, 'Record Updated')

else:
print("No record found with BookNo =", b)

cursor.execute('SELECT * FROM Book')

all_data = cursor.fetchall()

bookno = 'BookNo'.ljust(10)
bookname = 'BookName'.ljust(50)
price = 'Price'.ljust(10)
author = 'Author'.ljust(30)
publisher = 'Publisher'.ljust(30)

print(bookno, bookname, price, author, publisher)

print('-' * 150)

for row in all_data:

print(
str(row[0]).ljust(10),
row[1].ljust(50),
str(row[2]).ljust(10),
row[3].ljust(30),
row[4].ljust(30)
)

cursor.close()
db.close()

update()
OUTPUT
Python with MySQL (Database connectivity)

DATE: 18/10/2024

S.No. NAME OF PRACTICAL

Write a program to connect Python with MySQL using database connectivity and
perform the following operations on data in database BookShop and table Book.

5 DELETE THE DATA

SOURCE
import mysql.connector as ms
CODE

def print_table(cur):
cur.execute('SELECT * FROM book')
data = cur.fetchall()

bookno = 'BookNo'.ljust(10)
bookname = 'BookName'.ljust(50)
price = 'Price'.ljust(10)
author = 'Author'.ljust(30)
publisher = 'Publisher'.ljust(30)

print(bookno, bookname, price, author, publisher)

print('-' * 150)

for i in data:
print(
str(i[0]).ljust(10),
i[1].ljust(50),
str(i[2]).ljust(10),
i[3].ljust(30),
i[4].ljust(30)
)

def delete():
conn = ms.connect(
host='localhost',
user='root',
password='root',
database='bookshop'
)
cur = conn.cursor()

while True:
cur.execute('USE bookshop')
choice = int(input('''Choose an option:
1. Delete the whole record
2. Delete selected record
3. Exit
Enter your choice: '''))

if choice == 1:
cur.execute('DELETE FROM book')
print("All records deleted.")
conn.commit()

elif choice == 2:
record_id = int(input("Enter the ID of the record
to delete: "))
cur.execute('DELETE FROM book WHERE
BookNo = %s', (record_id,))
conn.commit()

print('Record with ID {}
deleted.'.format(record_id))

elif choice == 3:
print('Exiting')
break

else:
print("Invalid choice. Please try again.")
continue

print_table(cur)

cont = input("Do you want to continue deleting

records? (yes/no): ")
if cont.lower() != 'yes':
break

cur.close()
conn.close()

delete()

OUTPUT