MySQL_Python/python_mysql_summary.py at master · Akhileswar6/MySQL_Python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
# ---------------------------------------------------------
#  MySQL + Python Full Tutorial
#  Author: Akhileswar Kamale
#  Description: Connect, Create DB, Tables, Insert, Fetch,
#               Update, Delete, Drop using mysql-connector
# ---------------------------------------------------------

import mysql.connector

# ---------------------------------------------------------
# 1️⃣ Connect to MySQL Server
# ---------------------------------------------------------
dataBase = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="Akhil@0109"
)

print("✅ Connected to MySQL Server successfully!")

# ---------------------------------------------------------
# 2️⃣ Create a Database
# ---------------------------------------------------------
cursorObject = dataBase.cursor()
cursorObject.execute("CREATE DATABASE StudentDB")
print("✅ Database 'StudentDB' created successfully!\n")

# ---------------------------------------------------------
# 3️⃣ Connect to the New Database
# ---------------------------------------------------------
dataBase = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="Akhil@0109",
    database="StudentDB"
)
cursorObject = dataBase.cursor()
print("✅ Connected to 'StudentDB' database.\n")

# ---------------------------------------------------------
# 4️⃣ Create a Table
# ---------------------------------------------------------
cursorObject.execute("""
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    branch VARCHAR(50),
    roll_no INT,
    marks INT
)
""")
print("✅ Table 'students' created successfully!\n")

# ---------------------------------------------------------
# 5️⃣ Insert a Single Record
# ---------------------------------------------------------
cursorObject.execute("""
INSERT INTO students (name, branch, roll_no, marks)
VALUES ('Akhileswar', 'CSE', 101, 95)
""")
dataBase.commit()
print("✅ Single record inserted successfully!\n")

# ---------------------------------------------------------
# 6️⃣ Insert Multiple Records
# ---------------------------------------------------------
insert_query = """
INSERT INTO students (name, branch, roll_no, marks)
VALUES (%s, %s, %s, %s)
"""
student_data = [
    ("Rohit", "ECE", 102, 88),
    ("Sneha", "MECH", 103, 91),
    ("Kiran", "CSE", 104, 85),
    ("Divya", "IT", 105, 90)
]
cursorObject.executemany(insert_query, student_data)
dataBase.commit()
print(f"✅ {cursorObject.rowcount} records inserted successfully!\n")

# ---------------------------------------------------------
# 7️⃣ Fetch All Records
# ---------------------------------------------------------
cursorObject.execute("SELECT * FROM students")
result = cursorObject.fetchall()
print("🎓 All Student Records:")
for row in result:
    print(row)
print()

# ---------------------------------------------------------
# 8️⃣ Update a Record
# ---------------------------------------------------------
cursorObject.execute("""
UPDATE students
SET marks = 92
WHERE name = 'Kiran'
""")
dataBase.commit()
print(f"✅ Record updated successfully! Rows affected: {cursorObject.rowcount}\n")

# ---------------------------------------------------------
# 9️⃣ Delete a Record
# ---------------------------------------------------------
cursorObject.execute("DELETE FROM students WHERE name = 'Rohit'")
dataBase.commit()
print(f"🗑️ Record deleted successfully! Rows affected: {cursorObject.rowcount}\n")

# ---------------------------------------------------------
# 🔟 Drop Table
# ---------------------------------------------------------
cursorObject.execute("DROP TABLE students")
print("🧨 Table 'students' deleted successfully!\n")

# ---------------------------------------------------------
# 1️⃣1️⃣ Drop Database
# ---------------------------------------------------------
cursorObject.close()
dataBase.close()

# Reconnect to MySQL Server (not StudentDB)
dataBase = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="Akhil@0109"
)
cursorObject = dataBase.cursor()
cursorObject.execute("DROP DATABASE StudentDB")
print("💥 Database 'StudentDB' deleted successfully!\n")

# ---------------------------------------------------------
# ✅ Close Connection
# ---------------------------------------------------------
dataBase.close()
print("🔒 MySQL connection closed successfully.")