测试派 Give testers a home.

Pymysql操作mysql数据库原来如此简单

一、mysql安装

• 官网：https://www.mysql.com/

二、pymysql安装

• pip install pymysql

三、pymysql包引入

• import pymysql

四、pymysql使用

• 建立连接
  • 创建数据库连接
    db=pymysql.connect(host='localhost',port=3306,user='user_name',passwd='password',db='test_db')
  • 创建游标
    cur=db.cursor()
• 基本使用
  • 创建表
  • 新增数据
  • 修改数据
  • 代码演示
    import pymysql
    db=pymysql.connect(host='localhost',port=3306,user='root',db='test_db')
    cur=db.cursor()
    cur.execute(sql)
    db.commit()
    db.close()
  • 查询数据

    • 返回参数格式控制

      • 方法一
        db=pymysql.connect(host='localhost',port=3306,user='root',db='test_db',cursorclass = pymysql.cursors.DictCursor)
      • 方法二
        cur=db.cursor(cursor = pymysql.cursors.DictCursor)

    • 返回查询到的第一个结果
      result=cur.fetchone()

    • 查询到的第一个结果放入list中返回
      result=cur.fetchmany()

    • 将查询到的全部结果放入list中返回
      result = cur.fetchall()

    • 代码演示
      import pymysql
      db=pymysql.connect(host='localhost',port=3306,user='root',db='test_db',cursorclass=pymysql.cursors.DictCursor)
      cur=db.cursor()
      cur.execute("select * from student")# 返回查询到的第一个结果

      #result=cur.fetchone()# 返回查询到的第一个结果，放入list

      #result=cur.fetchmany()
      result = cur.fetchall()
      print(result)
      db.close()

• 推荐使用
  • 代码演示
    with pymysql.connect(host='localhost',port=3306,user='root',db='test_db') as db:
    cur = db.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute("select * from student")
    result = cur.fetchall()
    print(result)
• 封装使用

  • 代码演示
    import pymysql
    class TestDemo:
    def init(self):
    self.db=pymysql.connect(host='localhost',port=3306,user='root',db='test_db')
    self.cur=self.db.cursor(cursor=pymysql.cursors.DictCursor)

    def test01(self):
    self.cur.execute("select * from student")
    result=self.cur.fetchall()
    print(result)
    self.db.close()

• 异常处理
  • 回滚操作
    import pymysql
    import traceback
    class TestDemo:
    def init(self):
    self.db=pymysql.connect(host='localhost',port=3306,user='root',db='test_db')
    self.cur=self.db.cursor(cursor=pymysql.cursors.DictCursor)
    def test01(self):
    try:
    self.cur.execute("select * from student")
    result=self.cur.fetchall()
    print(result)
    except Exception as e:
    print(e,traceback.print_exc())
    self.db.rollback()
    finally:
    self.db.close()
• 注意点
  • 创建数据库连接时，port参数的数据类型为int类型，否则会报错
  • 创建、修改、删除操作一定要提交
  • 操作完数据库一定要记得关闭连接
  • pymysql有个版本不支持with语法

五、数据准备

• 创建表
  CREATE TABLE student (
  student_id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  name varchar(50) DEFAULT NULL COMMENT '姓名',
  age varchar(50) DEFAULT '' COMMENT '年龄',
  sex varchar(50) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (student_id)
  ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

  CREATE TABLE score (
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  student_id varchar(50) NOT NULL COMMENT '学号',
  subject varchar(50) DEFAULT '' COMMENT '学科',
  score varchar(50) DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (id)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• 插入数据
  insert into student(name,age,sex) values
  ("小明","17","男"),
  ("小红","18","男"),
  ("小花","19","男"),
  ("大黄","16","男");

  insert into score(student_id,subject,score) values
  ("1001","语文","90"),
  ("1002","语文","95"),
  ("1003","语文","110"),
  ("1004","语文","130"),
  ("1001","数学","90"),
  ("1002","数学","95"),
  ("1003","数学","110"),
  ("1004","数学","130"),
  ("1001","英语","90"),
  ("1002","英语","95"),
  ("1003","英语","110"),
  ("1004","英语","130");