import pymysql def connect_to_mysql(): try: # 数据库连接参数 connection = pymysql.connect( host='localhost', # MySQL服务器地址 user='root', # 数据库用户名 password='root', # 数据库密码（请替换为实际密码） database='test_db', # 要连接的数据库名 charset='utf8mb4', # 支持中文 ) print("数据库连接成功！") return connection except Exception as e: print(f"数据库连接失败：{e}") return None def insert_student(name, age, gender, grade): sql = "INSERT INTO students (name, age, gender, grade) VALUES (%s, %s, %s, %s)" cursor.execute(sql, (name, age, gender, grade)) conn.commit() print(f"成功插入学生：{name}") def query_students(): sql = "SELECT * FROM students" cursor.execute(sql) results = cursor.fetchall() # print(results) print("查询结果：") for row in results: print(f"ID: {row[0]}, 姓名: {row[1]}, 年龄: {row[2]}, 性别: {row[3]}, 成绩: {row[4]}") def delete_student(name): sql = "DELETE FROM students WHERE name = %s" affected_rows = cursor.execute(sql, (name,)) conn.commit() if affected_rows > 0: print(f"成功删除学生：{name}") else: print(f"未找到学生 {name}") def update_student(name,new_grade): sql = "update students set grade = %s where name = %s" affected_rows = cursor.execute(sql,(new_grade,name)) conn.commit() if affected_rows > 0: print(f"成功更新学生{name}的成绩为{new_grade}") else: print(f"未找到可以修改的学生{name}") # 主程序测试 if __name__ == "__main__": conn = connect_to_mysql() try: with conn.cursor() as cursor: insert_student("赵六", 20, "女", 88.0) query_students() delete_student("赵六") update_student("李四",99.8) query_students() except Exception as e: print(f"失败：{e}") conn.rollback() finally: conn.close()
