### 使用Python和MySQL模块创建宿舍管理系统数据库连接代码 以下内容详细说明如何使用Python中的`mysql-connector-python`模块来连接MySQL数据库，并开发一个宿舍管理系统。 #### 1. 安装依赖库 在开始之前，确保已安装`mysql-connector-python`模块。如果尚未安装，可以通过以下命令完成安装： ```bash pip install mysql-connector-python ``` #### 2. 数据库连接与初始化 以下是用于连接MySQL数据库并初始化宿舍管理系统的代码示例[^1]： ```python import mysql.connector class DormitoryManagementSystem: def __init__(self, host="127.0.0.1", port=3306, user="root", password="", database="dormitory"): self.host = host self.port = port self.user = user self.password = password self.database = database self.conn = mysql.connector.connect( host=self.host, port=self.port, user=self.user, password=self.password, database=self.database ) self.cursor = self.conn.cursor() def setup_database(self): self.cursor.execute(""" CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, dormitory_number VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL ) """) self.cursor.execute(""" CREATE TABLE IF NOT EXISTS dormitories ( number VARCHAR(50) PRIMARY KEY, capacity INT NOT NULL, occupied INT DEFAULT 0 ) """) self.conn.commit() ``` #### 3. 添加学生信息 以下代码展示了如何向数据库中添加学生信息： ```python def add_student(self, name, dormitory_number, phone): try: stmt = "INSERT INTO students (name, dormitory_number, phone) VALUES (%s, %s, %s)" data = (name, dormitory_number, phone) self.cursor.execute(stmt, data) self.conn.commit() return True except mysql.connector.Error as err: print(f"Error: {err}") return False ``` #### 4. 查询学生信息 以下代码实现了查询所有学生信息的功能[^1]： ```python def get_students(self): self.cursor.execute("SELECT * FROM students") return self.cursor.fetchall() ``` #### 5. 更新宿舍容量 以下代码展示了如何更新宿舍的容量信息[^1]： ```python def update_dormitory_occupied(self, dormitory_number, increment=1): try: stmt = "UPDATE dormitories SET occupied = occupied + %s WHERE number = %s" data = (increment, dormitory_number) self.cursor.execute(stmt, data) self.conn.commit() return True except mysql.connector.Error as err: print(f"Error: {err}") return False ``` #### 6. 删除学生信息 以下代码实现了删除特定学生信息的功能： ```python def delete_student(self, student_id): try: stmt = "DELETE FROM students WHERE id = %s" data = (student_id,) self.cursor.execute(stmt, data) self.conn.commit() return True except mysql.connector.Error as err: print(f"Error: {err}") return False ``` --- ### 示例测试代码 以下是一个完整的测试代码示例，展示如何使用上述类来操作宿舍管理系统[^1]： ```python if __name__ == "__main__": # 初始化系统 system = DormitoryManagementSystem(user="your_user", password="your_password", database="dormitory_db") system.setup_database() # 添加学生 system.add_student("Alice", "Dorm A101", "1234567890") system.add_student("Bob", "Dorm A102", "0987654321") # 获取学生列表 students = system.get_students() for student in students: print(student) # 更新宿舍占用情况 system.update_dormitory_occupied("Dorm A101") # 删除学生 system.delete_student(1) ``` ---