Scribd
Upload
2 views19 pages

Connectivity Programs SQL

The document outlines a Python program for MySQL connectivity that allows users to create a database and a table for student records, insert data, display records, and manage the database. It includes functions for creating and dropping databases, creating tables, inserting records, and displaying data. Additionally, it provides a second program for performing CRUD operations on a 'Student' table with error handling for various operations.

Uploaded by

dasrajdeep341
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views19 pages

Connectivity Programs SQL

The document outlines a Python program for MySQL connectivity that allows users to create a database and a table for student records, insert data, display records, and manage the database. It includes functions for creating and dropping databases, creating tables, inserting records, and displaying data. Additionally, it provides a second program for performing CRUD operations on a 'Student' table with error handling for various operations.

Uploaded by

dasrajdeep341
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Program no.

21

Program Statement: Write a mySQL connectivity program


in Python to
(a)create a database school
(b)create a table students with the specifications - Roll No
integer, STname character(10) in MySQL
and perform the following
operations:
(i)insert two records in it.
(ii) Display the contents of the table.
Source Code
import [Link] as mys
# Establish connection
db = [Link](
host="localhost",
user="root",
passwd="root" # change this to your MySQL password
)
ch = [Link]()
# Function to create database
def create_database():
try:
dbn = input("Enter Database Name: ")
[Link]("CREATE DATABASE {}".format(dbn))
print("Database created successfully!")
except Exception as a:
print("Create Database Error:", a)
# Function to drop database
def drop_database():
try:
dbn = input("Enter Database Name to be dropped: ")
[Link]("DROP DATABASE {}".format(dbn))
print("Database dropped successfully!")
except Exception as a:
print("Drop Database Error:", a)
# Function to create table
def create_table():
try:
[Link]("CREATE TABLE student (roll_no INT, sname
VARCHAR(20))")
print("Table created successfully!")
except Exception as a:
print("Create Table Error:", a)
# Function to insert data
def insert_data():
try:
while True:
tno = int(input("Enter student roll number: "))
tname = input("Enter name of student: ")
[Link]("INSERT INTO student VALUES({},
'{}')".format(tno, tname))
[Link]()
print("Record inserted successfully!")
choice = input("Do you want to add more records (y/n)? ")
if [Link]() != 'y':
break
except Exception as a:
print("Insert Record Error:", a)
# Function to display data
def display_data():
try:
[Link]("SELECT * FROM student")
data = [Link]()
print("\nStudent Records:\n")
print("Roll_No | Name")
for i in data:
print(i)
except Exception as a:
print("Display Record Error:", a)
# Main Menu
while True:
print("\nMain Menu")
print("1. Create Database")
print("2. Drop Database")
print("3. Create Table")
print("4. Insert Record")
print("5. Display Record")
print("6. Exit")
ch1 = int(input("Enter your choice (1–6): "))
if ch1 == 1:
create_database()
elif ch1 == 2:
drop_database()
elif ch1 == 3:
create_table()
elif ch1 == 4:
insert_data()
elif ch1 == 5:
display_data()
elif ch1 == 6:
print("Exiting program...")
break
else:
print("Invalid choice!")
OUTPUT:
Menu
1. Create Database
2. Drop Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6): 1
Enter database Name: work
Database created successfully
Menu
1. Create Database
2. Drop Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6): 3
Table created successfully
Menu
1. Create Database
2. Drop Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6): 4
Enter student roll number: 1
Enter student name: Abhi
Record inserted successfully!
Do you want to add more records (y/n)? y
Enter student roll number: 2
Enter student name: Lily
Record inserted successfully!
Do you want to add more records (y/n)? n
Menu
1. Create Database
2. Dr0p Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6): 5
Student Records
(1, 'Abhi')
(2, 'Lily')
Menu
1. Create Database
2. Drop Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6): 2
Enter Database Name to be dropped: school
Database deleted successfully
Menu
1. Create Database
2. Drop Database
3. Create Table
4. Insert Record
5. Display Entire Data
6. Exit
Enter your choice (1-6):6
Screenshot of source code
Screenshot of output
Program no. 22
Program Statement: Perform all the operations with
reference to table "Student" through My [Link]
connectivity
Source Code
import [Link] as sql
# ---------------- CONNECT DATABASE ----------------
con = [Link](
host="localhost",
user="root", # change this to your MySQL username
passwd="root", # change this to your MySQL password
database="school_sql" # change it as per your database name
)
# ---------------- INSERT RECORD ----------------
def insert_rec():
try:
while True:
rn = int(input("Enter Roll Number: "))
name = input("Enter Name: ")
marks = float(input("Enter Marks: "))
grade = input("Enter Grade: ")
query = "INSERT INTO student VALUES({}, '{}', {},
'{}')".format(rn, name, marks, grade)
cur = [Link]()
[Link](query)
[Link]()
print("Record inserted successfully!")
ch = input("Do you want to add more records? (y/n): ")
if [Link]() != 'y':
break
except Exception as e:
print("Insert Record Error:", e)
# ---------------- UPDATE RECORD ----------------
def update_rec():
try:
rn = int(input("Enter Roll Number to update: "))
marks = float(input("Enter new Marks: "))
grade = input("Enter new Grade: ")
query = "UPDATE student SET marks={}, grade='{}'
WHERE rollno={}".format(marks,
grade, rn)
cur = [Link]()
[Link](query)
[Link]()
print("Record updated successfully!")
except Exception as e:
print("Update Record Error:", e)
# ---------------- DELETE RECORD ----------------
def delete_rec():
try:
rn = int(input("Enter Roll Number to delete: "))
query = "DELETE FROM student WHERE
rollno={}".format(rn)
cur = [Link]()
[Link](query)
[Link]()
print("Record deleted successfully!")
except Exception as e:
print("Delete Record Error:", e)
# ---------------- DISPLAY RECORD ----------------
def display_rec():
try:
cur = [Link]()
[Link]("SELECT * FROM student")
data = [Link]()
print("\nStudent Records:\n")
print("RollNo | Name | Marks | Grade")
print("--------------------------------")
for row in data:
print(row)
except Exception as e:
print("Display Record Error:", e)
# ---------------- MAIN MENU ----------------
while True:
print("\n========== MAIN MENU ==========")
print("1. Insert Record")
print("2. Update Record")
print("3. Delete Record")
print("4. Display Records")
print("5. Exit")
print("================================")
ch = int(input("Enter your choice (1-5): "))
if ch == 1:
insert_rec()
elif ch == 2:
update_rec()
elif ch == 3:
delete_rec()
elif ch == 4:
display_rec()
elif ch == 5:
print("Program exited successfully."
break
else:
print("Wrong option selected! Try again.")
Output
MENU
1. Insert record
2. Update Record
3. Delete Record
4. Display Records
5. Exit
Enter your choice (1-5): 1
Enter Roll Number: 2
Enter Name: PRITHA
Enter Marka: 61
Error: 1062 (23000): Duplicate entry '2' for key
'[Link]"
MENU
1. Insert Record
2. Update Record
3. Delete Record
4. Display Records
5. Exit
Enter your choice (1-5): 1
Enter Roll Number: 6
Enter Name: RIDDHTMA
Enter Marka: 50
Enter Grado: A
Record inserted successfully!
Do you want to add more records? (Y/N):
MENU
1. Insert Record
2. Update Record
3. Delete Records
4. Display Records
5. Exit Enter your choice (1-5): 2
Enter Roll Number to update: 2
Enter now Marks: 09
Error: 1054 (42522): Unknown column “rollno”in 'where
clause’
MENU
1. Insert Record
2. Update Record
3. Delete Record
4. Display Records
5. Exit
Enter your choice (1-5): 2
Enter new Marks: 859
Enter Roll Number to update: 2
Enter new Grade: A
Error: 1054 (42522): Unknown column 'rollno’ in 'where
clause’
MENU
1. Insert Record
2. Update Record
3. Delete Record
4. Display Record
5. Exit
Enter your choice (1-5): 3
Enter Roll Number to delete: 6
Error: 1054 (42522): Unknown column “rolino” in 'where
clause"
MENU
1. Insert Record
2. Update Record
3. Delete Record
4. Display Records
5. Exit
Enter your choice (1-5):4
Student Records
RollNo| Namo Marks | Grade
(2. 'pritha", 27.0, 'D')
(6, RIDDHIMA', 90.0, 'A')
MENU
[Link] Record
[Link] Record.
[Link] Record
4. Display Records
[Link]
Enter your choice (1-5): 5 Program exited successfully.
Screenshot of source code
Screenshot of output

You might also like