数据库课堂笔记5（MySQL与Python交互）

最新推荐文章于 2024-04-22 10:17:13 发布

yuuisei

最新推荐文章于 2024-04-22 10:17:13 发布

阅读量193

点赞数

分类专栏：课堂笔记文章标签： mysql python big data

本文链接：https://blog.csdn.net/yuuisei/article/details/119978928

版权

课堂笔记专栏收录该内容

41 篇文章 2 订阅

订阅专栏

MySQL与Python交互

在这里插入图片描述

准备数据

准备数据
要求：创建一个商品 goods 数据表

-- 创建数据库
CREATE DATABASE 'mb' CHARSET=uft8;
-- 创建数据表
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);

效果图如下：
在这里插入图片描述
这是为了分表做铺垫

分表（初步认识）

分表是因为当一张表的数据量比较多时，但是我们只需要查询其中的某个字段数据，就会导致查询效率降低，所以此处我们需要使用到分表。当然后面会细讲。
在这里插入图片描述

商品分类表
- 创建商品分类表

--创建分类表的结构（id和分类名字两个字段）
create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

将商品表中的商品种类查询并插入到商品分类表里

1、查询分组后的cate_name （在goods表中查询分组）

SELECT cate_name FROM goods GROUP BY cate_name;

下图显示了goods表中按照品牌分组后的样式
在这里插入图片描述
2、如何将从goods表中查询出来的数据插入goods_cate表中

--goods_cate 后面跟 需要插入的字段名字，这边要将goods表中的品牌名字插入到goods_cate分表中的name字段。
-- 之后接我们所查询到的语句（即第一步语句）
INSERT INTO goods_cate(name) SELECT cate_name FROM goods GROUP BY cate_name;

下图就是在goods_cate分表中插入了goods表中的品牌信息。
在这里插入图片描述
注意：不用values，而是使用需要插入的字段+查询语句。

3、但是这样的数据会发现在goods表中的id和goods_cate分表中的id不对应，外键就会无法连接。
所以我们需要修改goods表中的id。

要求：
将goods表中的cate_name的值修改成为goods_cate表对应的id
在这里插入图片描述

-- 两张表进行内连接
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

下图是内连接修改后效果
在这里插入图片描述

安装pymysql

在Windows操作系统上安装
Python3:pip install pymysql
Python2:pip install MySQLdb

Ubuntu安装：https://www.jianshu.com/p/d84cdb5e6273

Python操作MySQL步骤

在这里插入图片描述

导入库

第一种方式：直接导入库，再访问方法。

import pymysql
# 连接数据库
pymysql.connect()

第二种方式：从pymysql库中导入库的所有方法和属性，再访问方法。

from pymysql import *
connect()

Connection 对象

用于建立与数据库的连接
创建对象：调用 connect() 方法

connect的参数：

参数host：连接的mysql主机，如果本机是’localhost’【本地的默认是‘127.0.0.1’】
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称，简写为db
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8

conn=connect(参数列表)

# 方法1
import pymysql

con = pymysql.connect(host = '127.0.0.1',port=3306,db='python-01',user='root',password = 'root',charset = 'utf8')

# 方法2
from pymysql import *

conn = connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

Cursor对象

用于执行sql语句,使用频度最高的语句为select、insert、update、delete
获取Cursor对象:调用Connection对象的cursor()方法【通过连接对象调用该方法】

要求：继之前python代码后获取游标对象。

cs = conn.cursor()

execut语句

execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句

要求：通过游标对象执行sql语句

sql = "select * from goods;"
cs.execute(sql)

将这个执行代码返回得到的数字代表受影响的记录数量（行数）
在这里插入图片描述

得到数据，类似文件读写操作

即在上一步执行sql语句后，从受影响的行数中得到数据。

查询一条：fetchone()

fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组

print(cs.fetchone())

下图中：id为1，还是一个元组。第二次调用就是id为2的数据

查询多条（指定查询行数）：fetchmany（查询行数）

fetchmany（查询行数）执行查询时，获取结果集的指定行数，一行构成一个元组，再将这些元组装入一个元组返回。

print(cs.fetchmany(2))

下图中查询了2条。显示了两条
在这里插入图片描述

获取全部：fetchall()

fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回。

print(cs.fetchall())

在这里插入图片描述

注意：

这个和我们的文本读写操作一致，只能一条条向下，不可逆。所以从读取全部是从第5条开始。之前的四条已经被获取完毕了。

当获取完毕后再获取，则会返回为None。

关闭游标

游标变量名.close()

cs.close()

关闭连接

连接变量名.close()

conn.close()

注意

先关闭游标，再关闭连接

练习

练习1
封装DB类

# 导入库
from pymysql import *

# 建立类
class mydb（object）：
	# 需要在对象初始化的时候，自动连接数据库（init方法）
	def __init__(self):
		self.my_conn()
	
	# 连接数据库,同时避免数据报错就停止执行问题
	def my_conn(self):
		try：
			self.conn = connect(
				host = '127.0.0.1',
				port=3306,
				db='mb',
				user='root',
				password = 'root',
				charset = 'utf8'
			)
		except Exceotion as e：
			print(e)
		
	# 获取单条数据的方法：
	def get_one(self):
		sql = "select * from goods;"
		# 通过连接对象调用获取游标
		cs = self.conn.cursor()
		# 执行数据库
		cs.execute(sql)
		# 查询获取数据
		res = cs.fetchone()
		# 关闭游标
		cs.close()
		#返回查询结果
		return res
	
	# 为了防止再次调用查询出现报错（连接被关闭了），可以在外部关闭连接。
	# def close_conn(self):
		# self.conn.close()
	
	# 定义：代码执行完毕后，自动关闭连接（del方法）。
	def __del__(self):
		self.conn.close()
	
# 定义函数，进行类的实例化
def main（）：
	obj = mydb（）
	# 获取第一条数据
	res = obj.get_one()
	print(res)

# 程序主入口，执行函数
if __name__ == "__mian__":
	main()

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

import pymysql


class mydb(object):
    def __init__(self):
        # 1.连接数据库
        self.conn = pymysql.connect(host='127.0.0.1',
                                    port=3306,
                                    db='mb',
                                    user='root',
                                    password='root',
                                    charset='utf8'
                                    )
        # 2.获取游标
        self.cs = self.conn.cursor()

    # 3.定义run方法来进行输入值
    def run(self):
        while True:
            num = self.print_menu()
            if num == 1:
                self.show_all_goods()
            elif num == 2:
                self.show_all_cate()
            elif num == 3:
                self.show_all_brand()
            elif num == 4:
                break
            elif num == 5:
            	self.addcat()
            else:
                print("输入有误，请重新输入")

    # 定义一个静态方法
    @staticmethod
    def print_menu():
        print("mb shop")
        print("1：查询所有商品")
        print("2：所有商品种类")
        print("3：查询所有品牌")
        print("4：退出")
        # # 暂时还没学到。之后再更新
        # print("5：插入数据")

        num = int(input("请输入功能对应的序号"))
        return num

    # 展示所有的商品
    def show_all_goods(self):
        sql = "select * from goods;"
        # self.cs.execute(sql)
        # res = self.cs.fetchall()
        # for item in res:
        #     print(item)
        self.execute_sql(sql)

    # 展示所有商品的种类
    def show_all_cate(self):
        sql = "select * from goods_cate;"
        # self.cs.execute(sql)
        # res = self.cs.fetchall()
        # for item in res:
        #     print(item)
        self.execute_sql(sql)

    # 提取封装。因为种类和展示所有商品代码重复
    def execute_sql(self, sql):
        self.cs.execute(sql)
        res = self.cs.fetchall()
        for item in res:
            print(item)

    # 显示品牌名字
    def show_all_brand(self):
        sql = "select distinct brand_name from goods;"
        self.execute_sql(sql)
	
	# 插入数据
	def addcat(self):
	  	name = import("请输入新的商品名")
	  	 
    	# sql = "insert into students(name) values ('%s');"%name
		sql = "insert into students(name) values ('%s');"
   		self.cs.execute(sql,(name,))
   		self.conn.commit()

    # 退出
    def __del__(self):
        self.cs.close()
        self.conn.close()


def main():
    obj = mydb()
    obj.run()

if __name__ == '__main__':
    main()

commit（）

第五个插入数据的需求：
需要用到提交事务的函数
commit()：通过连接对象调用方法运行提交事务。
xx.commit()：xx代表连接数据库时候赋值的变量名。

以下代码是插入一条数据：

import pymysql


def coperation_db():
    """
    此时运行代码，会发现数据没有插入表中
    注意：当没有提交事务的时候，执行来插入语句，这个数据实际上在内存中，没有提交，所以id是在自增的。
    注意：修改数据就要提交事务
    :return:
    """
    # 1.连接数据库
    conn = pymysql.connect(host='127.0.0.1',
                           port=3306,
                           db='mb',
                           user='root',
                           password='root',
                           charset='utf8'
                           )
    # 2.获取游标
    cs = conn.cursor()

    # 3.定义sql语句
    sql = "insert into students(name) values ('amy');"

    # 4.执行sql语句
    cs.execute(sql)

    # 5.提交事务commit()方法：通过连接对象调用
    conn.commit()

    # 6.关闭
    cs.close()
    conn.close()


if __name__ == '__main__':
    coperation_db()

注意：

可以插入多条语句
当commit前有个插入代码出错，那么后面正确的代码也不会被插入进去。
异常捕获中：当错误插入数据在第一条那么之后的数据也不会被插入进数据库。但是当插入的错误数据在正确数据之后，那么正确的数据会被插入进数据库。

同时注意：当我们没有执行提交事务，仅仅是插入语句那么这个插入的id会在内存中，即当我们没使用提交事务的时候那条信息占用了id 1，但是数据库并没有数据。当我们使用提交事务再次提交，那么数据库会有数据，但是id不是1而是2。

回滚rollback（）

我们之前知道了提交事务与异常捕获之间的错误先后顺序会导致两种不同的结果。那么当我们需要只要有一个错误（不管先后顺序）都不要插入，就称为回滚。

xx.rollback()：xx代表连接的赋值名称。即通过连接来调用方法。
同commit的异常捕获区别在于：我们在出现异常后不再使用commit 而是rlooback

 try:
        # 2.获取游标
        cs = conn.cursor()

        # 3.定义sql语句
        sql = "insert into students(name) values ('amy');"

        # 4.执行sql语句
        cs.execute(sql)

        # 3.定义sql语句
        sql = "insert into students(name) values ('sei');"

        # 4.执行sql语句
        cs.execute(sql)

        # 5.提交事务commit()方法：通过连接对象调用
        conn.commit()

        # 6.关闭
        cs.close()
        conn.close()
    except Exception as e:
        print(e)
        conn.rollback()

小结

全部插入成功，一个提交事务
执行失败
- 全部失败rollback（）id+1
- 提交一部分commit（）id+1
  - 前一条没问题的，提交

yuuisei

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库课堂笔记5（MySQL与Python交互）

MySQL与Python交互准备数据准备数据-- 创建一个商品 goods 数据表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
复制链接

扫一扫