RChX1dBR

1from myconnection import connect_to_mysql
2from icecream import ic
3
4def create_database(cursor, database_name):
5    query = "CREATE DATABASE IF NOT EXISTS %s"
6    cursor.execute(query % (database_name,))
7
8def drop_table(cursor, table_name):
9    query = "DROP TABLE IF EXISTS %s"
10    cursor.execute(query % (table_name,))
11
12def rename_table(cursor, old_table_name, new_table_name):
13    query = "RENAME TABLE %s TO %s"
14    cursor.execute(query % (old_table_name, new_table_name))
15
16def create_table(cursor, table_name, table_str):
17    query = "CREATE TABLE %s (%s)"
18    cursor.execute(query % (table_name, table_str))
19
20def create_student(cursor, name, age):
21    query = "INSERT INTO students (name, age) VALUES (%s, %s)"
22    cursor.execute(query, (name, age))
23
24def read_students(cursor):
25    query = "SELECT * FROM students"
26    cursor.execute(query)
27    return cursor.fetchall()
28
29def update_student(cursor, student_id, new_name, new_age):
30    query = "UPDATE students SET name=%s, age=%s WHERE id=%s"
31    cursor.execute(query, (new_name, new_age, student_id))
32
33def delete_student(cursor, student_id):
34    query = "DELETE FROM students WHERE id=%s"
35    cursor.execute(query, (student_id,))
36
37def main():
38    config = {
39        "host": "127.0.0.1",
40        "user": "root",
41        "password": "",
42        "database": "schooldb",
43    }
44
45    cnx = connect_to_mysql(config, attempts=3)
46
47    if cnx and cnx.is_connected():
48        with cnx.cursor() as cursor:
49            # Create the database if it doesn't exist
50            create_database(cursor, 'schooldb')
51
52            # Drop the table if it exists
53            drop_table(cursor, 'customers')
54
55            # Create a new table
56            create_table(cursor, 'customers', 'id int primary key auto_increment, name varchar(20) not null')
57
58            # Rename the table (optional)
59            # rename_table(cursor, 'customers', 'new_customers')
60
61            # Create a new student
62            create_student(cursor, "Aung Win Htut", 20)
63
64            # Read all students
65            print("All students:")
66            students = read_students(cursor)
67            for student in students:
68                print(student)
69
70            # Update a student
71            update_student(cursor, 1, "New Student", 25)
72
73            # Read all students after update
74            print("\nAll students after update:")
75            students = read_students(cursor)
76            for student in students:
77                print(student)
78
79            # Delete a student
80            delete_student(cursor, 1)
81
82            # Read all students after delete
83            print("\nAll students after delete:")
84            students = read_students(cursor)
85            for student in students:
86                print(student)
87
88        cnx.commit()
89        cnx.close()
90    else:
91        print("Could not connect to the database")
92
93if __name__ == "__main__":
94    main()
95