Python & MySQL Cheatsheet

Cheatsheet Content

Python Programming - File Operations Read file line by line, display words with '#': def process_file_words(filename): with open(filename, 'r') as f: for line in f: words = line.strip().split() print('#'.join(words)) # Example usage: # with open('sample.txt', 'w') as f: # f.write("This is a sample text.\nAnother line here.") # process_file_words('sample.txt') Count vowels/consonants/case in file: def analyze_file_characters(filename): vowels = "aeiouAEIOU" consonants = "bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ" v_count = c_count = upper_count = lower_count = 0 with open(filename, 'r') as f: content = f.read() for char in content: if char.isalpha(): if char in vowels: v_count += 1 elif char in consonants: c_count += 1 if char.isupper(): upper_count += 1 elif char.islower(): lower_count += 1 print(f"Vowels: {v_count}, Consonants: {c_count}") print(f"Uppercase: {upper_count}, Lowercase: {lower_count}") # analyze_file_characters('sample.txt') Remove lines with 'a' from file: def remove_lines_with_char(input_filename, output_filename, char_to_remove='a'): with open(input_filename, 'r') as infile, open(output_filename, 'w') as outfile: for line in infile: if char_to_remove not in line: outfile.write(line) # remove_lines_with_char('sample.txt', 'filtered.txt', 'a') Python Programming - Binary Files Create/Search binary file (name/roll): import pickle def create_binary_file(filename, data): with open(filename, 'wb') as f: pickle.dump(data, f) def search_binary_file(filename, roll_num): with open(filename, 'rb') as f: data = pickle.load(f) for entry in data: if entry['roll'] == roll_num: print(f"Name: {entry['name']}") return print("Data not found") # data = [{'roll': 1, 'name': 'Alice'}, {'roll': 2, 'name': 'Bob'}] # create_binary_file('students.dat', data) # search_binary_file('students.dat', 1) # search_binary_file('students.dat', 3) Update marks in binary file: import pickle def update_marks_binary_file(filename, roll_num, new_marks): data = [] with open(filename, 'rb') as f: data = pickle.load(f) found = False for entry in data: if entry['roll'] == roll_num: entry['marks'] = new_marks found = True break if found: with open(filename, 'wb') as f: pickle.dump(data, f) print(f"Marks for Roll {roll_num} updated to {new_marks}") else: print(f"Student with Roll {roll_num} not found.") # data = [{'roll': 1, 'name': 'Alice', 'marks': 80}, {'roll': 2, 'name': 'Bob', 'marks': 75}] # create_binary_file('student_marks.dat', data) # update_marks_binary_file('student_marks.dat', 1, 90) Python Programming - Random & Functions Dice simulation: import random def roll_dice(): roll = random.randint(1, 6) print(f"You rolled a {roll}") if roll == 1: print("Bad luck!") elif roll == 6: print("Great roll!") # Add more messages as per number # roll_dice() Generate strong password: import random import string def generate_strong_password(): length = 10 upper = random.sample(string.ascii_uppercase, 2) digit = random.choice(string.digits) special = random.choice('!@#$%^&*()') remaining_chars = random.sample(string.ascii_letters + string.digits + '!@#$%^&*()', length - len(upper) - 1 - 1) password_list = upper + [digit] + [special] + remaining_chars random.shuffle(password_list) password = "".join(password_list) print(f"Generated Password: {password}") # generate_strong_password() Stack operations on dictionary keys (marks > 75): def process_student_marks(students_dict): stack = [] for name, marks in students_dict.items(): if marks > 75: stack.append(name) print("Students with marks > 75 (pushed to stack):") while stack: print(f"Popped: {stack.pop()}") # students = {"Alice": 85, "Bob": 70, "Charlie": 92, "David": 65} # process_student_marks(students) CSV for user-id/password, search: import csv def create_csv(filename, data): with open(filename, 'w', newline='') as csvfile: fieldnames = ['userid', 'password'] writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writeheader() writer.writerows(data) def search_csv(filename, userid): with open(filename, 'r', newline='') as csvfile: reader = csv.DictReader(csvfile) for row in reader: if row['userid'] == userid: print(f"Password for {userid}: {row['password']}") return print(f"User ID '{userid}' not found.") # user_data = [{'userid': 'user1', 'password': 'pass1'}, {'userid': 'user2', 'password': 'pass2'}] # create_csv('users.csv', user_data) # search_csv('users.csv', 'user1') # search_csv('users.csv', 'user3') Most common words in a text file: from collections import Counter import re def find_most_common_words(filename, num_words=5): with open(filename, 'r') as f: text = f.read().lower() words = re.findall(r'\b\w+\b', text) word_counts = Counter(words) print(f"Most {num_words} common words: {word_counts.most_common(num_words)}") # with open('emails.txt', 'w') as f: # f.write("Hello world, this is a test. world hello test.") # find_most_common_words('emails.txt') Calculate grade from marks: def calculate_grade(marks_list): total_marks = sum(marks_list) average_marks = total_marks / len(marks_list) if average_marks >= 90: grade = 'A' elif average_marks >= 80: grade = 'B' elif average_marks >= 70: grade = 'C' elif average_marks >= 60: grade = 'D' else: grade = 'F' print(f"Total Marks: {total_marks}, Average Marks: {average_marks:.2f}, Grade: {grade}") # calculate_grade([85, 90, 78, 92, 88]) Menu-driven program (factorial, min, max, avg, sum): import math def factorial(n): return math.factorial(n) def list_operations(numbers): print(f"Max: {max(numbers)}") print(f"Min: {min(numbers)}") print(f"Sum: {sum(numbers)}") print(f"Average: {sum(numbers) / len(numbers):.2f}") def menu_program(): while True: print("\n--- Menu ---") print("1. Factorial") print("2. List Operations (Min, Max, Sum, Avg)") print("3. Exit") choice = input("Enter your choice: ") if choice == '1': num = int(input("Enter a number for factorial: ")) print(f"Factorial of {num} is {factorial(num)}") elif choice == '2': nums_str = input("Enter numbers (space-separated): ") nums = [int(n) for n in nums_str.split()] list_operations(nums) elif choice == '3': break else: print("Invalid choice. Please try again.") # menu_program() Fibonacci Series: def fibonacci_series(n): a, b = 0, 1 series = [] while a Function to count word occurrence: def FindWord(STRING, SEARCH): count = STRING.lower().split().count(SEARCH.lower()) print(f"The word '{SEARCH}' occurs {count} times.") # FindWord("Learning history helps to know about history with interest in history", "history") Function to check same-length strings: def are_same_length(str1, str2): return len(str1) == len(str2) # print(are_same_length("hello", "world")) # print(are_same_length("hi", "there")) Calculate mean, mode, median: from collections import Counter def analyze_sequence(numbers): # Mean mean = sum(numbers) / len(numbers) # Median sorted_numbers = sorted(numbers) n = len(sorted_numbers) if n % 2 == 0: median = (sorted_numbers[n // 2 - 1] + sorted_numbers[n // 2]) / 2 else: median = sorted_numbers[n // 2] # Mode counts = Counter(numbers) max_count = max(counts.values()) mode = [key for key, value in counts.items() if value == max_count] print(f"Numbers: {numbers}") print(f"Mean: {mean:.2f}") print(f"Median: {median}") print(f"Mode: {mode}") # analyze_sequence([1, 2, 3, 4, 4, 5, 5, 5, 6]) # analyze_sequence([1, 2, 3, 4, 5, 6]) Count even/odd numbers in a tuple: def count_even_odd_tuple(tup): even_count = 0 odd_count = 0 for num in tup: if num % 2 == 0: even_count += 1 else: odd_count += 1 print(f"Tuple: {tup}") print(f"Even numbers: {even_count}") print(f"Odd numbers: {odd_count}") # my_tuple = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) # count_even_odd_tuple(my_tuple) Database Management - SQL Queries Q.1 Student Table Operations -- Create table CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50), Class VARCHAR(10), Marks INT ); -- Insert data INSERT INTO Student (RollNo, Name, Class, Marks) VALUES (1, 'Alice', 'XII', 85), (2, 'Bob', 'XI', 70), (3, 'Charlie', 'XII', 92); -- ALTER table to add new attribute ALTER TABLE Student ADD COLUMN Email VARCHAR(100); -- ALTER table to modify data type ALTER TABLE Student MODIFY COLUMN Marks DECIMAL(5,2); -- ALTER table to drop attribute ALTER TABLE Student DROP COLUMN Email; -- UPDATE table to modify data UPDATE Student SET Marks = 90 WHERE RollNo = 1; -- ORDER By to display data in ascending / descending order SELECT * FROM Student ORDER BY Name ASC; SELECT * FROM Student ORDER BY Marks DESC; -- DELETE to remove tuple(s) DELETE FROM Student WHERE RollNo = 2; -- GROUP BY and find min, max, sum, count, average SELECT Class, MIN(Marks), MAX(Marks), SUM(Marks), COUNT(*), AVG(Marks) FROM Student GROUP BY Class; Q.2 ACTIVITY and COACH Tables Tables Structure: CREATE TABLE ACTIVITY ( ACode VARCHAR(10) PRIMARY KEY, ActivityName VARCHAR(50), ParticipantsNum INT, PrizeMoney INT, ScheduleDate DATE ); CREATE TABLE COACH ( PCode VARCHAR(10) PRIMARY KEY, Name VARCHAR(50), ACode VARCHAR(10), FOREIGN KEY (ACode) REFERENCES ACTIVITY(ACode) ); Insert Data: INSERT INTO ACTIVITY (ACode, ActivityName, ParticipantsNum, PrizeMoney, ScheduleDate) VALUES ('1001', 'Relay 100X4', 16, 10000, '2004-01-23'), ('1002', 'High Jump', 10, 12000, '2003-12-12'), ('1003', 'Shot Put', 12, 8000, '2004-02-14'), ('1005', 'Long Jump', 12, 9000, '2004-01-01'), ('1008', 'Discuss Throw', 10, 15000, '2004-03-19'); INSERT INTO COACH (PCode, Name, ACode) VALUES ('1', 'Ahmed Hussain', '1001'), ('2', 'Ranvinder', '1008'), ('3', 'Janila', '1001'), ('4', 'Naaz', '1003'); SQL Queries: Create tables and insert data: (Done above) Display activity names and Acodes in descending order: SELECT ActivityName, ACode FROM ACTIVITY ORDER BY ACode DESC; Display sum of prizemoney for each ParticipantsNum: SELECT ParticipantsNum, SUM(PrizeMoney) FROM ACTIVITY GROUP BY ParticipantsNum; Display coach's name and ACodes in ascending order of ACode: SELECT Name, ACode FROM COACH ORDER BY ACode ASC; Display content of Activity table where ScheduleDate is earlier than '2004-01-01' in ascending order of ParticipantsNum: SELECT * FROM ACTIVITY WHERE ScheduleDate Q.3 STUDENT Table Queries Table Structure: CREATE TABLE STUDENT ( RollNo INT PRIMARY KEY, Name VARCHAR(50), Class VARCHAR(10), DOB DATE, Gender CHAR(1), City VARCHAR(50), Marks INT ); Insert Data: INSERT INTO STUDENT (RollNo, Name, Class, DOB, Gender, City, Marks) VALUES (1, 'Nanda', 'X', '1995-06-06', 'M', 'Agra', 551), (2, 'Saurabh', 'XII', '1993-05-07', 'M', 'Mumbai', 462), (3, 'Sanal', 'XI', '1994-05-06', 'F', 'Delhi', 400), (4, 'Trisla', 'XII', '1995-08-08', 'F', 'Mumbai', 450), (5, 'Store', 'XII', '1995-10-08', 'M', 'Delhi', 369), (6, 'Marisla', 'XI', '1994-12-12', 'F', 'Dubai', 250), (7, 'Neha', 'X', '1995-12-08', 'F', 'Moscow', 377), (8, 'Nishant', 'X', '1995-06-12', 'M', 'Moscow', 489); SQL Queries: Display records in alphabetical order by name: SELECT * FROM STUDENT ORDER BY Name ASC; Display Class, DOB and City where marks are between 450 and 551: SELECT Class, DOB, City FROM STUDENT WHERE Marks BETWEEN 450 AND 551; Display Name, Class and total number of students who have secured more than 450 marks, class wise: SELECT Name, Class, COUNT(RollNo) OVER (PARTITION BY Class) AS TotalStudentsInClassWithHighMarks FROM STUDENT WHERE Marks > 450 ORDER BY Class, Name; -- Or if you want count of students > 450 marks per class -- SELECT Class, COUNT(*) FROM STUDENT WHERE Marks > 450 GROUP BY Class; Increase marks of all students by 20 whose class is "XII": UPDATE STUDENT SET Marks = Marks + 20 WHERE Class = 'XII'; Display records where marks are greater than 350 and city is Delhi: SELECT * FROM STUDENT WHERE Marks > 350 AND City = 'Delhi'; Q.4 PRODUCT and BRAND Tables Queries Tables Structure: CREATE TABLE PRODUCT ( PCode VARCHAR(10) PRIMARY KEY, PName VARCHAR(50), UPrice INT, Rating INT, BID VARCHAR(10) ); CREATE TABLE BRAND ( BID VARCHAR(10) PRIMARY KEY, BName VARCHAR(50) ); Insert Data: INSERT INTO PRODUCT (PCode, PName, UPrice, Rating, BID) VALUES ('P01', 'Shampoo', 120, 6, 'M03'), ('P02', 'Toothpaste', 54, 8, 'M02'), ('P03', 'Soap', 25, 7, 'M03'), ('P04', 'Toothpaste', 65, 4, 'M04'), ('P05', 'Soap', 38, 5, 'M05'), ('P06', 'Shampoo', 245, 6, 'M05'); INSERT INTO BRAND (BID, BName) VALUES ('M02', 'Dant Kanti'), ('M03', 'Medimix'), ('M04', 'Pepsodent'), ('M05', 'Dove'); SQL Queries: Display product name and brand name: SELECT P.PName, B.BName FROM PRODUCT P JOIN BRAND B ON P.BID = B.BID; Display structure of PRODUCT table: DESCRIBE PRODUCT; -- Or SHOW COLUMNS FROM PRODUCT; Display average rating of Medimix and Dove brands: SELECT AVG(P.Rating) FROM PRODUCT P JOIN BRAND B ON P.BID = B.BID WHERE B.BName IN ('Medimix', 'Dove'); Display name, price, and rating of products in descending order of rating: SELECT PName, UPrice, Rating FROM PRODUCT ORDER BY Rating DESC; Update price of Shampoo Unit price to 150: UPDATE PRODUCT SET UPrice = 150 WHERE PName = 'Shampoo'; Q.5 EMPLOYEE and DEPARTMENT Tables Queries Tables Structure: CREATE TABLE EMPLOYEE ( EMPID INT PRIMARY KEY, NAME VARCHAR(50), DOB DATE, DEPTID VARCHAR(10), DESIG VARCHAR(50), SALARY INT ); CREATE TABLE DEPARTMENT ( DEPTID VARCHAR(10) PRIMARY KEY, DEPTNAME VARCHAR(50), FLOORNO INT ); Insert Data: INSERT INTO EMPLOYEE (EMPID, NAME, DOB, DEPTID, DESIG, SALARY) VALUES (120, 'Alisha', '1978-01-23', 'D001', 'Manager', 75000), (123, 'Nitin', '1977-10-10', 'D002', 'AO', 59000), (129, 'Navjot', '1971-07-12', 'D003', 'Supervisor', 40000), (130, 'Jimmy', '1980-12-30', 'D004', 'Sales Rep', 52000), (131, 'Faiz', '1984-04-06', 'D001', 'Dep Manager', 65000); INSERT INTO DEPARTMENT (DEPTID, DEPTNAME, FLOORNO) VALUES ('D001', 'Personal', 4), ('D002', 'Admin', 10), ('D003', 'Production', 1), ('D004', 'Sales', 3); SQL Queries: Display average salary of all employees, department wise: SELECT D.DEPTNAME, AVG(E.SALARY) AS AverageSalary FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTID = D.DEPTID GROUP BY D.DEPTNAME; Display name and respective department name of each employee whose salary is more than 50000: SELECT E.NAME, D.DEPTNAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTID = D.DEPTID WHERE E.SALARY > 50000; Display names of employees whose salary is more than 50000, in alphabetical order: SELECT NAME FROM EMPLOYEE WHERE SALARY > 50000 ORDER BY NAME ASC; Display DEPTID from EMPLOYEE table without repetition: SELECT DISTINCT DEPTID FROM EMPLOYEE; Using python-DB (MySQL) - Operations Setup (assuming MySQLdb or mysql.connector): import mysql.connector # Replace with your MySQL credentials db_config = { 'host': 'localhost', 'user': 'your_user', 'password': 'your_password', 'database': 'your_database_name' # For Q1, this will be 'Employee' } def execute_query(query, params=None, fetch=False): conn = None cursor = None try: conn = mysql.connector.connect(**db_config) cursor = conn.cursor() cursor.execute(query, params) if fetch: return cursor.fetchall() conn.commit() except mysql.connector.Error as err: print(f"Error: {err}") finally: if cursor: cursor.close() if conn: conn.close() return None Q.1 Employee Database Operations Create database 'Employee' and table 'emp': # First, connect to MySQL without specifying a database to create it # Then update db_config to connect to 'Employee' # CREATE DATABASE Employee; # After creating DB, update db_config['database'] = 'Employee' # Then run: query_create_table = """ CREATE TABLE IF NOT EXISTS emp ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ); """ execute_query(query_create_table) Insert 5 records: records_to_insert = [ (101, 'John Doe', 'HR', 60000), (102, 'Jane Smith', 'IT', 75000), (103, 'Peter Jones', 'Finance', 62000), (104, 'Alice Brown', 'HR', 58000), (105, 'Bob White', 'IT', 80000) ] for record in records_to_insert: execute_query("INSERT INTO emp (id, name, department, salary) VALUES (%s, %s, %s, %s)", record) Display all records: rows = execute_query("SELECT * FROM emp", fetch=True) if rows: for row in rows: print(row) Search record for one employee: def search_employee(emp_id): row = execute_query("SELECT * FROM emp WHERE id = %s", (emp_id,), fetch=True) if row: print(f"Employee found: {row[0]}") else: print(f"Employee with ID {emp_id} not found.") # search_employee(102) # search_employee(999) Q.2 Student Table Operations (MySQL) # Assuming db_config is set to 'School' database # CREATE DATABASE School; # db_config['database'] = 'School' query_create_student_table = """ CREATE TABLE IF NOT EXISTS Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50), Class VARCHAR(10), Marks INT ); """ execute_query(query_create_student_table) student_data = [ (1, 'Amit', 'XII', 95), (2, 'Bhavna', 'XI', 88), (3, 'Chirag', 'XII', 98), (4, 'Deepa', 'XI', 75), (5, 'Esha', 'XII', 91) ] for data in student_data: execute_query("INSERT INTO Student (RollNo, Name, Class, Marks) VALUES (%s, %s, %s, %s)", data) # a. Display details of all students print("All Students:") rows = execute_query("SELECT * FROM Student", fetch=True) if rows: for row in rows: print(row) # b. Display details of students who got maximum marks print("\nStudents with Max Marks:") max_marks_query = "SELECT * FROM Student WHERE Marks = (SELECT MAX(Marks) FROM Student);" rows = execute_query(max_marks_query, fetch=True) if rows: for row in rows: print(row) # c. Find the total marks of all students print("\nTotal Marks of all students:") total_marks = execute_query("SELECT SUM(Marks) FROM Student", fetch=True) if total_marks: print(total_marks[0][0]) Q.3 Create 'Doctor' database and 'details' table: # CREATE DATABASE Doctor; # db_config['database'] = 'Doctor' query_create_doctor_table = """ CREATE TABLE IF NOT EXISTS details ( id INT PRIMARY KEY, name VARCHAR(50), specialty VARCHAR(50), phone VARCHAR(15), UNIQUE(phone) # Assuming phone is unique for simplicity as primary key is id ); """ execute_query(query_create_doctor_table) doctor_records = [ (1, 'Dr. Sharma', 'Cardiologist', '111-222-3333'), (2, 'Dr. Verma', 'Pediatrician', '444-555-6666'), (3, 'Dr. Singh', 'Neurologist', '777-888-9999'), (4, 'Dr. Khan', 'Dermatologist', '123-456-7890'), (5, 'Dr. Gupta', 'Oncologist', '098-765-4321') ] for record in doctor_records: execute_query("INSERT INTO details (id, name, specialty, phone) VALUES (%s, %s, %s, %s)", record) Q.4 Create 'Personal' table and display sorted by age: # CREATE DATABASE PersonalDB; # db_config['database'] = 'PersonalDB' query_create_personal_table = """ CREATE TABLE IF NOT EXISTS Personal ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT ); """ execute_query(query_create_personal_table) names_ages = [ ('Ram', 30), ('Sita', 25), ('Gopal', 35), ('Meera', 28), ('Krishna', 40) ] for name, age in names_ages: execute_query("INSERT INTO Personal (name, age) VALUES (%s, %s)", (name, age)) print("\nPersonal records sorted by age (descending):") rows = execute_query("SELECT * FROM Personal ORDER BY age DESC", fetch=True) if rows: for row in rows: print(row) Q.5 Python program to integrate MySQL with Python (Updating records for Personal table): # This builds on Q.4. Assuming 'Personal' table exists and db_config points to 'PersonalDB' def update_personal_record(name_to_update, new_age): query = "UPDATE Personal SET age = %s WHERE name = %s;" execute_query(query, (new_age, name_to_update)) print(f"Updated age for {name_to_update} to {new_age}") # Example usage: # update_personal_record('Ram', 32) print("\nPersonal records after update:") rows = execute_query("SELECT * FROM Personal", fetch=True) if rows: for row in rows: print(row)