2021-05-22

pymysql操作MySQL数据库

1. 安装pymysql
   在Python中可使用pymysql与MySQL进行交互

pip install pymysql

2. 了解Python中with（上下文管理器）语法使用
   在python中，我们使用文件常常会遇到文件打开代码后没有关闭指令或者文件发生异常的问题，这时我们可以使用python中with语句，with 语句适用于对资源进行访问的场合，确保不管使用过程中是否发生异常都会执行必要的“清理”操作，释放资源。
   具体用法可参考with语法
3. 利用pymysql创建一张表

connect = mysql.connect(host="localhost",  # 用户ip
                        user="输入用户名",  # 用户名
                        passwd="输入密码",  # 密码
                        database='db03',  # 需要进行操作的数据库
                        charset='utf8')   # 选择字符集
# 创建一个雇员表emp
sql = "CREATE TABLE emp ("  \
            "id INT PRIMARY KEY,"  \
            "`name` VARCHAR(32),"  \
            "age INT,"  \
            "gender ENUM('male', 'female')) CHARSET utf8"

with connect:
    with connect.cursor() as cursor:
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交修改内容
            connect.commit()
            print('成功！')
        except:
            connect.rollback()
            print('失败！')

4. 插入单条数据

with connect:
    with connect.cursor() as cursor:
        sql = "INSERT INTO `emp`(`id`, `name`, `age`, `gender`)" \
              "VALUES(%s, '%s', %s, '%s')" % \
              (1, '王昭君', 18, 'female')
        try:
            cursor.execute(sql)
            # 提交修改内容
            connect.commit()
            print('成功！')
        except:
            # 发生错误，回滚
            connect.rollback()
            print('失败！')

    with connect.cursor() as cursor:
        sql = "SELECT * FROM `emp`"
        cursor.execute(sql)
        # 返回所有记录
        result = cursor.fetchall()
        print(result)

5. 插入多条数据

sql = "INSERT INTO `emp`(`id`, `name`, `age`, `gender`)" \
      "VALUES(%s, %s, %s, %s)"  # 插入单条时 (%s, '%s', %s, '%s')

values = [(2, '张三', 19, 'male'),
          (3, '王二', 25, 'male'),
          (4, '西施', 20, 'female'),
          (5, '貂蝉', 21, 'female')]

with connect:
    with connect.cursor() as cursor:
        try:
            cursor.executemany(sql, values)
            connect.commit()
            print('插入成功！')
        except:
            connect.rollback()
            print('插入失败！')

    with connect.cursor() as cursor:
        sql = "SELECT * FROM `emp`"
        cursor.execute(sql)
        # 返回所有记录
        result = cursor.fetchall()
        print(result)

初次写博客，水平有限，希望大家多多指正。
参考文档：https://pymysql.readthedocs.io/en/latest/user/examples.html