Scribd
Upload
4 views34 pages

Dbms and Python File Final

The document contains SQL code for creating and managing a Student table, including operations for inserting, updating, deleting, and querying data. It also includes Python code for file operations, stack operations, and MySQL database connectivity for managing student records. The Python functions cover tasks such as creating text and binary files, reading and writing CSV files, and performing CRUD operations on a MySQL database.

Uploaded by

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

Dbms and Python File Final

The document contains SQL code for creating and managing a Student table, including operations for inserting, updating, deleting, and querying data. It also includes Python code for file operations, stack operations, and MySQL database connectivity for managing student records. The Python functions cover tasks such as creating text and binary files, reading and writing CSV files, and performing CRUD operations on a MySQL database.

Uploaded by

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

source code(dbms)

-- ========================================
-- PART 1: CREATE TABLES AND INSERT DATA
-- ========================================

-- Create Student Table


CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Grade VARCHAR(2),
City VARCHAR(50)
);

-- Insert 15 records into Student table


INSERT INTO Student VALUES (1, 'Raj Kumar', 18, 'A', 'Mumbai');
INSERT INTO Student VALUES (2, 'Priya Sharma', 17, 'B', 'Delhi');
INSERT INTO Student VALUES (3, 'Amit Patel', 19, 'A', 'Ahmedabad');
INSERT INTO Student VALUES (4, 'Sneha Gupta', 18, 'C', 'Bangalore');
INSERT INTO Student VALUES (5, 'Arjun Singh', 17, 'B', 'Pune');
INSERT INTO Student VALUES (6, 'Neha Verma', 19, 'A', 'Chennai');
INSERT INTO Student VALUES (7, 'Vikram Rao', 18, 'B', 'Hyderabad');
INSERT INTO Student VALUES (8, 'Pooja Jain', 17, 'C', 'Kolkata');
INSERT INTO Student VALUES (9, 'Rahul Mehta', 19, 'A', 'Jaipur');
INSERT INTO Student VALUES (10, 'Kavya Reddy', 18, 'B', 'Mumbai');
INSERT INTO Student VALUES (11, 'Sanjay Nair', 17, 'A', 'Delhi');
INSERT INTO Student VALUES (12, 'Divya Shah', 19, 'C', 'Surat');
INSERT INTO Student VALUES (13, 'Karan Kapoor', 18, 'B', 'Lucknow');
INSERT INTO Student VALUES (14, 'Anjali Das', 17, 'A', 'Patna');
INSERT INTO Student VALUES (15, 'Rohan Desai', 19, 'B', 'Nagpur');
-- ========================================
-- PART 2: ALTER TABLE OPERATIONS
-- ========================================

-- (I) Add new attribute to Student table


ALTER TABLE Student ADD Email VARCHAR(100);

-- Modify data type


ALTER TABLE Student MODIFY City VARCHAR(100);

-- Drop attribute
ALTER TABLE Student DROP COLUMN Email;

-- ========================================
-- PART 3: UPDATE TABLE
-- ========================================

-- Update single record


UPDATE Student SET Grade = 'A' WHERE StudentID = 2;

-- Update multiple records


UPDATE Student SET Age = 20 WHERE Grade = 'A';

-- Update with calculation on Student table


UPDATE Student SET Age = Age + 1 WHERE Grade = 'B';

-- Update with calculation (increase age by 2 for students from Mumbai)


UPDATE Student SET Age = Age + 2 WHERE City = 'Mumbai';

-- ========================================
-- PART 4: ORDER BY (Ascending/Descending)
-- ========================================

-- Display data in ascending order


SELECT * FROM Student ORDER BY Name ASC;

-- Display data in descending order by Age


SELECT * FROM Student ORDER BY Age DESC;

-- Order by multiple columns


SELECT * FROM Student ORDER BY Grade ASC, Age DESC;

-- ========================================
-- PART 5: DELETE TUPLES
-- ========================================

-- Delete specific record


DELETE FROM Student WHERE StudentID = 15;

-- Delete based on condition


DELETE FROM Student WHERE Age < 18;

-- ========================================
-- PART 6: GROUP BY with Aggregate Functions
-- ========================================

-- COUNT: Count students by Grade


SELECT Grade, COUNT(*) AS StudentCount
FROM Student
GROUP BY Grade;

-- SUM: Total age by Grade


SELECT Grade, SUM(Age) AS TotalAge
FROM Student
GROUP BY Grade;

-- AVG: Average age by Grade


SELECT Grade, AVG(Age) AS AverageAge
FROM Student
GROUP BY Grade;

-- MIN: Minimum age by City


SELECT City, MIN(Age) AS MinimumAge
FROM Student
GROUP BY City;

-- MAX: Maximum age by City


SELECT City, MAX(Age) AS MaximumAge
FROM Student
GROUP BY City;

-- Multiple aggregate functions together


SELECT Grade,
COUNT(*) AS StudentCount,
MIN(Age) AS MinAge,
MAX(Age) AS MaxAge,
AVG(Age) AS AvgAge,
SUM(Age) AS TotalAge
FROM Student
GROUP BY Grade;
output(DBMS)
Source code(python)
# ============================================
# TEXT FILE OPERATIONS
# ============================================

# 1. Create text file with 3 user inputs


def create_myfile():
with open("[Link]", "w") as f:
print("Enter 3 lines of text:")
for i in range(3):
line = input(f"Line {i+1}: ")
[Link](line + "\n")
print("File '[Link]' created successfully!")

# 2. Count uppercase, lowercase, and digits in [Link]


def count_characters_in_merge():
try:
with open("[Link]", "r") as f:
content = [Link]()
upper = sum(1 for c in content if [Link]())
lower = sum(1 for c in content if [Link]())
digits = sum(1 for c in content if [Link]())

print(f"Uppercase letters: {upper}")


print(f"Lowercase letters: {lower}")
print(f"Digits: {digits}")
except FileNotFoundError:
print("Error: [Link] file not found!")

# 3. Count total lines and lines starting with A, B, C


def count_lines_abc():
try:
with open("[Link]", "r") as f:
lines = [Link]()
total_lines = len(lines)
count_a = sum(1 for line in lines if [Link]().startswith('A'))
count_b = sum(1 for line in lines if [Link]().startswith('B'))
count_c = sum(1 for line in lines if [Link]().startswith('C'))

print(f"Total lines: {total_lines}")


print(f"Lines starting with 'A': {count_a}")
print(f"Lines starting with 'B': {count_b}")
print(f"Lines starting with 'C': {count_c}")
except FileNotFoundError:
print("Error: [Link] file not found!")

# 4. Count occurrences of a specific word


def count_word_occurrences():
filename = input("Enter filename: ")
word = input("Enter word to search: ")
try:
with open(filename, "r") as f:
content = [Link]()
count = [Link]().count([Link]())
print(f"'{word}' appears {count} times in {filename}")
except FileNotFoundError:
print(f"Error: {filename} not found!")

# 5. Replace spaces with dashes


def replace_spaces_with_dash():
input_file = input("Enter input filename: ")
output_file = input("Enter output filename: ")
try:
with open(input_file, "r") as f:
content = [Link]()

content = [Link](" ", "-")


with open(output_file, "w") as f:
[Link](content)

print(f"Spaces replaced with dashes. Saved to {output_file}")


except FileNotFoundError:
print(f"Error: {input_file} not found!")

# ============================================
# BINARY FILE OPERATIONS
# ============================================

import pickle

# 1. Create binary file with student records


def create_student_binary():
students = []
n = int(input("How many students? "))

for i in range(n):
print(f"\nStudent {i+1}:")
roll = int(input("Enter roll number: "))
name = input("Enter name: ")
[Link]({"roll": roll, "name": name})

with open("[Link]", "wb") as f:


[Link](students, f)

print("\nData saved to [Link]")

# Display the data


with open("[Link]", "rb") as f:
data = [Link](f)
print("\n--- Student Records ---")
for student in data:
print(f"Roll: {student['roll']}, Name: {student['name']}")

# 2. Search record by roll number


def search_student_by_roll():
roll = int(input("Enter roll number to search: "))

try:
with open("[Link]", "rb") as f:
students = [Link](f)
found = False
for student in students:
if student['roll'] == roll:
print(f"Student found: {student['name']}")
found = True
break

if not found:
print("Record not found!")
except FileNotFoundError:
print("Error: [Link] file not found!")

# 3. Update student name by roll number


def update_student_name():
roll = int(input("Enter roll number to update: "))

try:
with open("[Link]", "rb") as f:
students = [Link](f)

found = False
for student in students:
if student['roll'] == roll:
new_name = input("Enter new name: ")
student['name'] = new_name
found = True
break

if found:
with open("[Link]", "wb") as f:
[Link](students, f)
print("Record updated successfully!")
else:
print("Record not found!")
except FileNotFoundError:
print("Error: [Link] file not found!")

# 4. Delete a record from binary file


def delete_student_record():
roll = int(input("Enter roll number to delete: "))

try:
with open("[Link]", "rb") as f:
students = [Link](f)

original_length = len(students)
students = [s for s in students if s['roll'] != roll]

if len(students) < original_length:


with open("[Link]", "wb") as f:
[Link](students, f)
print("Record deleted successfully!")
else:
print("Record not found!")
except FileNotFoundError:
print("Error: [Link] file not found!")

# ============================================
# CSV FILE OPERATIONS
# ============================================
import csv

# 1. Read entire data from [Link]


def read_entire_csv():
try:
with open("[Link]", "r") as f:
reader = [Link](f)
print("\n--- All Records ---")
for row in reader:
print(row)
except FileNotFoundError:
print("Error: [Link] file not found!")

# 2. Search record by admission number


def search_by_admission_number():
adm_no = input("Enter admission number: ")

try:
with open("[Link]", "r") as f:
reader = [Link](f)
found = False
for row in reader:
if row[0] == adm_no:
print(f"\nRecord found:")
print(f"Admission No: {row[0]}")
print(f"Name: {row[1]}")
print(f"Class: {row[2]}")
print(f"Section: {row[3]}")
print(f"Marks: {row[4]}")
found = True
break

if not found:
print("Record not found!")
except FileNotFoundError:
print("Error: [Link] file not found!")

# 3. Add/Insert records in [Link]


def insert_csv_record():
roll = input("Enter roll number: ")
name = input("Enter name: ")
class_name = input("Enter class: ")

with open("[Link]", "a", newline='') as f:


writer = [Link](f)
[Link]([roll, name, class_name])

print("Record added successfully!")

# 4. Copy data from [Link] to [Link]


def copy_csv_file():
try:
with open("[Link]", "r") as source:
with open("[Link]", "w", newline='') as dest:
reader = [Link](source)
writer = [Link](dest)
for row in reader:
[Link](row)

print("Data copied from [Link] to [Link] successfully!")


except FileNotFoundError:
print("Error: [Link] file not found!")

# 5. Display students with marks > 80


def display_high_scorers():
try:
with open("[Link]", "r") as f:
reader = [Link](f)
print("\n--- Students with marks > 80 ---")
found = False
for row in reader:
if len(row) >= 3 and row[2].isdigit():
if int(row[2]) > 80:
print(f"Roll: {row[0]}, Name: {row[1]}, Marks: {row[2]}")
found = True

if not found:
print("No students found with marks > 80")
except FileNotFoundError:
print("Error: [Link] file not found!")

# ============================================
# STACK OPERATIONS
# ============================================

class Stack:
def __init__(self):
[Link] = []

def push(self, item):


[Link](item)
print(f"{item} pushed to stack")

def pop(self):
if self.is_empty():
print("Stack is empty! Cannot pop.")
return None
return [Link]()

def peek(self):
if self.is_empty():
print("Stack is empty!")
return None
return [Link][-1]

def is_empty(self):
return len([Link]) == 0

def display(self):
if self.is_empty():
print("Stack is empty!")
else:
print("Stack contents (top to bottom):")
for item in reversed([Link]):
print(item)

def stack_menu():
s = Stack()

while True:
print("\n--- Stack Operations Menu ---")
print("1. Push")
print("2. Pop")
print("3. Peek")
print("4. Display")
print("5. Exit")

choice = input("Enter your choice: ")

if choice == '1':
item = input("Enter item to push: ")
[Link](item)
elif choice == '2':
item = [Link]()
if item:
print(f"Popped: {item}")
elif choice == '3':
item = [Link]()
if item:
print(f"Top element: {item}")
elif choice == '4':
[Link]()
elif choice == '5':
print("Exiting...")
break
else:
print("Invalid choice!")

# ============================================
# MySQL-Python CONNECTIVITY
# ============================================

import [Link]

def get_connection():
try:
conn = [Link](
host="localhost",
user="root",
password="your_password", # Change this
database="school" # Change this
)
return conn
except [Link] as e:
print(f"Error connecting to MySQL: {e}")
return None

def insert_student():
conn = get_connection()
if not conn:
return
cursor = [Link]()

roll = int(input("Enter roll number: "))


name = input("Enter name: ")
marks = float(input("Enter marks: "))

query = "INSERT INTO student (roll, name, marks) VALUES (%s, %s, %s)"
[Link](query, (roll, name, marks))
[Link]()

print("Record inserted successfully!")


[Link]()
[Link]()

def update_student():
conn = get_connection()
if not conn:
return

cursor = [Link]()

roll = int(input("Enter roll number to update: "))


name = input("Enter new name: ")
marks = float(input("Enter new marks: "))

query = "UPDATE student SET name=%s, marks=%s WHERE roll=%s"


[Link](query, (name, marks, roll))
[Link]()

if [Link] > 0:
print("Record updated successfully!")
else:
print("Record not found!")

[Link]()
[Link]()

def delete_student():
conn = get_connection()
if not conn:
return

cursor = [Link]()

roll = int(input("Enter roll number to delete: "))

query = "DELETE FROM student WHERE roll=%s"


[Link](query, (roll,))
[Link]()

if [Link] > 0:
print("Record deleted successfully!")
else:
print("Record not found!")

[Link]()
[Link]()

def display_students():
conn = get_connection()
if not conn:
return

cursor = [Link]()

query = "SELECT * FROM student"


[Link](query)

records = [Link]()
if records:
print("\n--- Student Records ---")
for record in records:
print(f"Roll: {record[0]}, Name: {record[1]}, Marks: {record[2]}")
else:
print("No records found!")

[Link]()
[Link]()

def mysql_menu():
while True:
print("\n--- Student Database Operations ---")
print("1. Insert Record")
print("2. Update Record")
print("3. Delete Record")
print("4. Display All Records")
print("5. Exit")

choice = input("Enter your choice: ")

if choice == '1':
insert_student()
elif choice == '2':
update_student()
elif choice == '3':
delete_student()
elif choice == '4':
display_students()
elif choice == '5':
print("Exiting...")
break
else:
print("Invalid choice!")
# ============================================
# MAIN MENU
# ============================================

def main():
while True:
print("\n" + "="*50)
print("MAIN MENU")
print("="*50)
print("TEXT FILE OPERATIONS:")
print("1. Create [Link] with 3 user inputs")
print("2. Count characters in [Link]")
print("3. Count lines starting with A, B, C")
print("4. Count word occurrences")
print("5. Replace spaces with dashes")

print("\nBINARY FILE OPERATIONS:")


print("6. Create and display student records")
print("7. Search student by roll number")
print("8. Update student name")
print("9. Delete student record")

print("\nCSV FILE OPERATIONS:")


print("10. Read entire CSV")
print("11. Search by admission number")
print("12. Insert CSV record")
print("13. Copy CSV file")
print("14. Display high scorers (marks > 80)")

print("\nSTACK OPERATIONS:")
print("15. Stack menu")

print("\nMYSQL OPERATIONS:")
print("16. MySQL database menu")
print("\n0. Exit")
print("="*50)

choice = input("Enter your choice: ")

if choice == '1':
create_myfile()
elif choice == '2':
count_characters_in_merge()
elif choice == '3':
count_lines_abc()
elif choice == '4':
count_word_occurrences()
elif choice == '5':
replace_spaces_with_dash()
elif choice == '6':
create_student_binary()
elif choice == '7':
search_student_by_roll()
elif choice == '8':
update_student_name()
elif choice == '9':
delete_student_record()
elif choice == '10':
read_entire_csv()
elif choice == '11':
search_by_admission_number()
elif choice == '12':
insert_csv_record()
elif choice == '13':
copy_csv_file()
elif choice == '14':
display_high_scorers()
elif choice == '15':
stack_menu()
elif choice == '16':
mysql_menu()
elif choice == '0':
print("Thank you for using the program!")
break
else:
print("Invalid choice! Please try again.")

if __name__ == "__main__":
main()
output(python)

You might also like