### Python连接MySQL数据库的操作方法 在Python中，可以通过多种方式实现与MySQL数据库的交互。以下是基于`mysql-connector-python`和`PyMySQL`两种常用库的具体操作教程。 #### 使用 `mysql-connector-python` 这是由MySQL官方提供的连接器，支持标准的DB-API接口[^1]。以下是一个完整的示例： ```python import mysql.connector from mysql.connector import Error try: # 建立连接 connection = mysql.connector.connect( host='localhost', port=3306, user='root', password='your_password', database='your_database_name' ) if connection.is_connected(): db_info = connection.get_server_info() print(f"Connected to MySQL Server version {db_info}") # 创建游标对象 cursor = connection.cursor() # 查询所有数据库名 cursor.execute("SHOW DATABASES;") records = cursor.fetchall() print("List of databases:") for row in records: print(row) except Error as e: print(f"Error while connecting to MySQL: {e}") finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed.") ``` 上述代码展示了如何通过`mysql-connector-python`建立到MySQL服务器的连接，并查询当前存在的数据库列表。 --- #### 使用 `PyMySQL` 另一种广泛使用的库是`PyMySQL`，它适用于Python 3.x版本[^2]。下面是一段类似的示例代码： ```python import pymysql.cursors try: # 建立连接 connection = pymysql.connect( host='localhost', port=3306, user='root', password='your_password', database='your_database_name', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) with connection.cursor() as cursor: # 查询所有数据库名 sql = "SHOW DATABASES;" cursor.execute(sql) result = cursor.fetchall() print("List of databases:") for row in result: print(row['Database']) except Exception as e: print(f"An error occurred: {e}") finally: connection.close() print("Connection closed.") ``` 此代码片段说明了如何利用`PyMySQL`完成基本的数据库连接、执行SQL语句以及关闭资源的过程[^3]。 --- #### 处理事务 当涉及多条SQL语句时，为了保障数据一致性和完整性，通常会启用事务管理机制[^4]。例如，在更新记录的同时插入新数据： ```python connection.autocommit(False) # 关闭自动提交模式 try: with connection.cursor() as cursor: # 更新某员工收入减少100元 update_sql = "UPDATE EMPLOYEE SET INCOME = INCOME - 100 WHERE LAST_NAME = %s" cursor.execute(update_sql, ('Doe',)) # 插入一条新的员工记录 insert_sql = """ INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s) """ cursor.execute(insert_sql, ('Jane', 'Doe', 28, 'F', 3500)) connection.commit() # 提交事务更改 except Exception as e: connection.rollback() # 出现异常则回滚 print(f"Transaction failed and rolled back due to: {e}") finally: connection.close() ``` 以上代码实现了事务控制逻辑，确保即使发生错误也能保持数据库状态一致性。 ---