### PyCharm连接SQL Server实现增删改查示例代码 为了在PyCharm中连接SQL Server并执行基本的CRUD（创建、读取、更新、删除）操作，可以使用`pyodbc`库来处理数据库交互。下面展示了如何配置环境以及编写相应的Python脚本来满足需求。 #### 安装必要的包 首先，在命令行工具里安装所需的第三方模块： ```bash pip install pyodbc pandas ``` #### 创建数据库连接函数 定义一个通用的方法用来获取到SQL Server的数据连接对象: ```python import pyodbc def get_connection(): conn_str = ( r'DRIVER={ODBC Driver 17 for SQL Server};' r'SERVER=your_server_name;' r'DATABASE=student_management_system;' r'UID=your_username;' r'PWD=your_password' ) try: connection = pyodbc.connect(conn_str) return connection except Exception as e: print(f"Error connecting to database: {e}") raise ``` 此段代码尝试建立与SQL Server的安全连接，并返回该连接实例以便后续的操作[^3]。 #### 插入新记录 (Create) 这里展示了一个简单的例子，向学生表(`students`)插入一条新的记录: ```python def insert_student(student_id, name, gender, class_id): with get_connection() as conn: cursor = conn.cursor() query = """ INSERT INTO students (id, name, gender, class_id) VALUES (?, ?, ?, ?); """ params = (student_id, name, gender, class_id) try: cursor.execute(query, params) conn.commit() print("Student inserted successfully.") except Exception as ex: conn.rollback() print(f"Failed to insert student due to error: {ex}") ``` 这段程序接收四个参数作为输入并将它们存入数据库的学生表格内[^2]。 #### 查询现有数据 (Read) 接下来是一个用于检索特定条件下所有匹配条目的功能: ```python def read_students(class_id=None): with get_connection() as conn: cursor = conn.cursor() if not class_id: query = "SELECT * FROM students;" else: query = f"SELECT * FROM students WHERE class_id='{class_id}';" results = [] try: cursor.execute(query) columns = [column[0] for column in cursor.description] rows = cursor.fetchall() for row in rows: result_dict = dict(zip(columns, list(row))) results.append(result_dict) import json print(json.dumps(results, indent=4)) except Exception as er: print(f"An error occurred while reading data from the table: {er}") read_students('specific_class') # Replace 'specific_class' with actual value or leave empty for all records. ``` 上述逻辑会打印出符合条件的结果集，如果未提供具体班级ID，则显示全部学生的信息[^4]。 #### 更新已有信息 (Update) 对于已存在的某位同学资料做适当调整可采用如下方式: ```python def update_student(student_id, updated_data): set_clause = ', '.join([f"{key}=?" for key in updated_data.keys()]) with get_connection() as conn: cursor = conn.cursor() query = f""" UPDATE students SET {set_clause} WHERE id=?; """ values_list = [*updated_data.values(), student_id] try: cursor.execute(query, tuple(values_list)) conn.commit() print("Student information has been updated.") except Exception as err: conn.rollback() print(f"There was an issue updating this record: {err}") ``` 在这个过程中传入待修改字段及其对应的新值即可完成单个学生的属性变更。 #### 删除指定实体 (Delete) 最后介绍怎样安全地移除不再需要的一名学员档案: ```python def delete_student_by_id(student_id): confirmation = input( f"Are you sure you want to permanently remove Student ID#{student_id}? This action cannot be undone! Type Y/N:" ).strip().upper()[0] if confirmation != 'Y': print("Operation cancelled by user.") return with get_connection() as conn: cursor = conn.cursor() query = "DELETE FROM students WHERE id=?;" try: cursor.execute(query, (student_id,)) affected_rows = cursor.rowcount if affected_rows > 0: conn.commit() print(f"The specified student ({student_id}) has now been removed.") else: print("No matching entries found.") except Exception as exc: conn.rollback() print(f"Some problem happened during deletion process: {exc}") ``` 以上就是关于通过PyCharm配合SQL Server来进行简单的学生信息系统维护的一些基础指导。