MySQL学习| python与MySQL交互

上一篇博客主要讲了SQL的环境和语法，这一篇主要学习的是python和MySQL如何交互。
先搬上数据，作为准备

-- 创建 某宝 数据库
CREATE DATABASE `mb` CHARSET=utf8;

USE `mb`;

CREATE TABLE goods(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  NAME VARCHAR(150) NOT NULL,
  cate_name VARCHAR(40) NOT NULL,
  brand_name VARCHAR(40) NOT NULL,
  price DECIMAL(10,3) NOT NULL DEFAULT 0,
  is_show TINYINT NOT NULL DEFAULT 1,
  is_saleoff TINYINT NOT NULL DEFAULT 0
);

-- 插入数据
INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);


-- 1.创建分类表
CREATE TABLE goods_cates(
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	NAME VARCHAR(40) NOT NULL
);

-- 2. 给分类表添加分类名称
-- 2.1 商品表cate_name 字段 进行 分组
SELECT cate_name FROM goods GROUP BY cate_name;

-- 2.2 查询出来的结果怎么插入到 goods_cates 表里面
-- 注意：此处不是使用的values 而是 目标表的 字段名 + 查询结果。
INSERT INTO goods_cates (NAME) SELECT cate_name FROM goods GROUP BY cate_name;

SELECT * FROM goods;

-- 3.需要将 goods 表里面的 cate_name 换成 对应 的 id
UPDATE goods g INNER JOIN goods_cates c ON g.`cate_name`=c.`name` SET g.`cate_name`=c.`id`;

（1）pymysql安装

pip install pymysql

（2）python操作MySQL的步骤

import pymysql

"""
1. 连接数据库
参数 host ：连接的 mysql 主机，如果本机是 'localhost'
参数 port ：连接的 mysql 主机的端⼝，默认是 3306
参数 database ：数据库的名称
参数 user ：连接的⽤户名
参数 password ：连接的密码
参数 charset ：通信采⽤的编码⽅式，推荐使⽤ utf8
2. 构建游标对象
cs = conn.cursor()
3.获取数据
cs.fetchone()、cs.fetchmany()、cs.fetchall()
4. 关闭游标
cs.close()
5.断开连接
conn.close()
"""

try:
    # 1。连接数据库
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="mb", charset="utf8")

    # 2.获取cursor游标对象
    cs = conn.cursor()

    # 3.通过游标对象 执行 sql语句 有返回值 且为受影响记录的行数
    r = cs.execute("select * from goods;")
    print(r)  # r = 21

    # 4.获取数据
    print(cs.fetchone())    # 第一条数据
    print(cs.fetchone())    # 第二条数据

    # 4.1 获取多条  size=None 默认获取一条 返回((),)
    # print(cs.fetchmany())  # 没有加数字，默认为获取一条
    print(cs.fetchmany(2))  # 获取size条

    # 4.2 获取全部
    print(cs.fetchall())     # 5-21
    print(cs.fetchone())     # 当 获取完毕 再查询 返回 为None

    # 5.关闭
    # 5.1 关闭游标
    cs.close()

except Exception as e:
    print("Error %d:%s" % (e.args[0], e.args[1]))

（3）利用python对MySQL进行增删改查

"""
数据 增改删 -->数据修改
MyISAM  --> 不需要提交事务就可以修改数据
Innodb  -->  修改它的表数据 进行提交事务
conn.commit()提交事务

如果说，多条sql语句执行，只要有一条报错，就都不要插入了。
conn.rollback() 实现回滚

使用面向对象完成商品查询
• 输入1：查询所有商品
• 输入2：所有商品种类
• 输入3：查询所有品牌
• 输入4：退出
• 输入5：插入数据
"""

import pymysql


class MB(object):
    def __init__(self):
        self.conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="mb", charset="utf8")
        self.cs = self.conn.cursor()

    @staticmethod
    def print_menu():
        print("-----mb shop-----")
        print("输入1：查询所有商品")
        print("输入2：所有商品种类")
        print("输入3：查询所有品牌")
        print("输入4：退出")
        print("输入5：添加商品数据")

        num = input("请输入数字：")
        return num

    def run(self):
        while True:
            num = self.print_menu()
            if num == '1':
                self.show_all_goods()
            elif num == '2':
                self.show_all_cates()
            elif num == '3':
                self.show_all_brands()
            elif num == '4':
                break
            elif num == '5':
                self.add_cate()
            else:
                print("输入有误，请重新输入")

    def execute_sql(self, sql):
        self.cs.execute(sql)
        res = self.cs.fetchall()
        for data in res:
            print(data)

    # 输入1：查询所有商品
    def show_all_goods(self):
        sql = "select * from goods;"
        self.execute_sql(sql)

    # 输入2：所有商品种类
    def show_all_cates(self):
        sql = "select * from goods_cates;"
        self.execute_sql(sql)

    # 输入3：查询所有品牌
    def show_all_brands(self):
        sql = "select distinct brand_name from goods"
        self.execute_sql(sql)

    # 输入5：插入数据
    def add_cate(self):
        name = input("请输入新商品的分类名字")
        sql = f"insert into goods_cates (name) values ('{name}')"
        self.cs.execute(sql)
        self.conn.commit()

    def __del__(self):
        self.cs.close()
        self.conn.close()


def main():
    mb = MB()
    mb.run()


if __name__ == '__main__':
    main()