十四、python使用MySQL数据库

（一）pymsql模块

---

pymsql是Python3中操作MySQL的模块，其使用方法和py2的MySQLdb几乎相同。

1、模块安装 

pip install pymysql 

2、使用模块

 步骤：

1. 导入 pymysql库。
2. 调用 pymysql.connect() 方法建立与数据库的连接。
   1. 在 connect() 方法中，传入数据库的主机名、用户名、密码和数据库名称等连接参数。

      import pymysql
      
      # 创建数据库连接
      connection = pymysql.connect(
          host='localhost',  # 数据库主机地址
          user='your_username',  # 数据库用户名
          password='your_password',  # 数据库密码
          database='your_database',  # 数据库名称
          charset='utf8mb4',  # 字符集
          cursorclass=pymysql.cursors.DictCursor  # 可选: 以字典形式返回结果
      )

3. 创建游标：游标（Cursor）是用于执行 SQL 语句的对象，在连接数据库后，你需要创建一个游标对象来执行 SQL 语句。

   # 创建游标对象
   cursor = connection.cursor()
   #更改获取数据结果的数据类型,默认是元组,可以改为字典等:
   conn.cursor(cursor=pymysql.cursors.DictCursor)
   

4. 执行SQL语句：使用游标对象的 execute() 方法：

   1. 对于查询操作，可以使用 fetchone()、fetchmany(size) 或 fetchall() 方法来获取查询结果

      # 执行 SQL 查询
      sql = "SELECT * FROM your_table"
      cursor.execute(sql)
      
      # 获取查询结果
      results = cursor.fetchall()
      for row in results:
          print(row)

   2. 插入、更新和删除数据：执行完这些操作后，需要调用连接对象的 commit() 方法来提交事务，才会生效。

      # 执行插入数据的 SQL 语句
      insert_sql = "INSERT INTO users (name, age) VALUES ('Alice', 30)"
      cursor.execute(insert_sql)
      
      # 提交事务
      connection.commit()

   3. 批量插入数据：使用游标对象的 executemany() 方法，它允许一次性执行多条 SQL 语句，从而提高插入效率。

      # 准备要插入的数据
      users = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]
      
      # 执行批量插入的 SQL 语句
      insert_sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
      cursor.executemany(insert_sql, users)
      
      # 提交事务
      connection.commit()

5. 关闭游标与连接：在操作完成后，不要忘记关闭游标和连接以释放资源。

   # 关闭游标
   cursor.close()
   # 关闭数据库连接
   connection.close()
   

6. 注意事项：

   1. 在执行完数据库操作后，务必关闭游标和连接，以释放资源。
   2. 处理数据库时，可以使用异常处理（try/except）来捕获和处理错误，进行调用数据库连接的rollback()方法回滚。
   3. 确保在连接数据库时使用正确的主机、用户名、密码和数据库名称。

import pymysql

# 获取连接数据库对象
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='python04')
# 获取数据库游标，SQL语句通过这个游标来执行
cursor = conn.cursor()
#更改获取数据结果的数据类型,默认是元组,可以改为字典等:conn.cursor(cursor=pymysql.cursors.DictCursor)
# 编写SQL语句的字符串赋给变量
sql = 'select * from person;'
# 将sql语句的变量传给游标sursor的execute方法执行
ret = cursor.execute(sql)
print(ret)  # 4，查询或修改影响的行数
# 获取查询结果（游标会移动到下一条数据）,结果默认是元组
data_first = cursor.fetchone()  # 获取第一个条数据，取出后后游标向后移动，再取数据从第二条开始
print('fetchone:', data_first)  # fetchone: (1, 'alex', 18, 'yiz', '1b', 'yuan', 98, '123447800')
data_mid_many = cursor.fetchmany(2)  # 从游标位置开始获取指定参数条数据
print('fetchmany(2):', data_mid_many)  # fetchmany(2): ((2, 'zs', 19, 'yiz', '1b', 'lh', 98, '12344780000000111111111'), (3, 'ls', 20, 'yiz', '1b', 'sh', 98, '1234478002222222200000111111111'))
data_last_all = cursor.fetchall()  # 从游标位置开始获取全部剩余数据
print('fetchall:', data_last_all)  # fetchall: ((4, 'xy', 24, 'erz', '3b', 'xl', 68, '123447800我是谁谁是我'),)
# 移动游标位置
# 相对当前游标位置移动
cursor.scroll(-1, mode='relative')  # 正数向下移动（前进），负数向上移动（后退）
data_current = cursor.fetchone()
print('游标回退一个位置后fetchone:', data_current)  # 游标回退一个位置后fetchone: (4, 'xy', 24, 'erz', '3b', 'xl', 68, '123447800我是谁谁是我')
# 绝对位置移动
data_current = cursor.scroll(2, mode='absolute')
data_current = cursor.fetchone()
print('游标设置绝对位置2后fetchone:', data_current)  # 游标设置绝对位置2后fetchone:(3, 'ls', 20, 'yiz', '1b', 'sh', 98, '1234478002222222200000111111111')
# 提交数据库
conn.commit()
# 关闭游标
cursor.close()
# 关闭数据库连接
conn.close()

3、事务

• 事务是一组作为单一逻辑工作单元执行的操作，这些操作要么全部成功，要么全部失败。
• 在 pymysql中，可以通过连接对象来管理事务。
• 步骤：

1. 建立连接‌：
   首先，需要建立与 MySQL 数据库的连接。

2. ‌开启事务‌：

   1. MySQL 默认是自动提交模式的，即每条独立的 SQL 语句都是一个事务，执行完毕后会自动提交。

   2. 如果想要手动控制事务，需要关闭自动提交模式，可以通过设置 autocommit 属性为 False 来关闭自动提交。

3. ‌执行 SQL 语句‌：
   使用游标对象执行要在事务中执行的 SQL 语句。

4. ‌提交或回滚事务‌：

   • 如果要提交事务，使得所有更改都生效，那么调用连接对象的 commit() 方法。
   • 如果要撤销事务中的所有更改，那么调用连接对象的 rollback() 方法。

5. ‌关闭连接‌：
   最后，不要忘记关闭游标和连接以释放资源。

import pymysql

# 建立与数据库的连接
connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database',
    charset='utf8mb4'
)

try:
    # 关闭自动提交模式
    connection.autocommit = False
    
    # 创建游标对象
    cursor = connection.cursor()
    
    # 执行 SQL 语句
    cursor.execute("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')")
    cursor.execute("UPDATE your_table SET column1 = 'new_value' WHERE column2 = 'some_value'")
    
    # 提交事务
    connection.commit()
    
except Exception as e:
    # 如果发生异常，则回滚事务
    connection.rollback()
    print(f"Transaction failed: {e}")
    
finally:
    # 关闭游标和连接
    cursor.close()
    connection.close()