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

news/2024/11/22 19:19:48/

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 pymysqlcon = 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 pymysqlclass 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:breakelif num == 5:self.addcat()else:print("输入有误，请重新输入")# 定义一个静态方法@staticmethoddef 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');"%namesql = "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 pymysqldef 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
      • 前一条没问题的，提交

http://www.ppmy.cn/news/180609.html

相关文章

little white 的电脑DIY历程（小小白自摸教程）

little white 的电脑DIY历程（小小白自摸教程）

希望更多的人尝试自己diy电脑，因为这真的很有趣！ 本文章讲解了升级一个笔记本电脑和组装一个台式机的历程，过程摸索艰难，所有操作都是第一次且一直查资料，欢迎按照我的思路自己尝试一下！ 文章目录 前言一、…
阅读更多...
MySQL 07 - SQL语句四

MySQL 07 - SQL语句四

SQL语句四 1.自关联 可以简单的理解为自己与自己进行连接查询。 比如: 一张areas 表里面有省市区，我们需要在通过这一张表查询某省对应的所有市。 select * from areas as p inner join areas as c on p.id c.pid having p.name 湖南;2. 外键 2.1外键介绍 M…
阅读更多...
3MySQL数据库-连接python-实现DB类 - 全栈式开发30

3MySQL数据库-连接python-实现DB类 - 全栈式开发30

MySQL与Python交互 一、关系图二、python实现操作数据库（一）connect链接数据库conn.close()conn.cursor()conn.commit()conn.rollback() 实现回滚 （二）cursor()获取光标cs.close()cs.execute()cs.fetchone()cs.fetchmany()cs.fetc…
阅读更多...
鲁大师Q1季度电脑硬件报告：intel 12代酷睿笔记本霸榜，显卡小幅降价

鲁大师Q1季度电脑硬件报告：intel 12代酷睿笔记本霸榜，显卡小幅降价

鲁大师2022年Q1季报消费级电脑硬件排行数据包含2022.01.01—2022.03.31日鲁大师PC正式版测试数据。为减少小众产品（服务器）冲击榜单，使得榜单更具参考价值，数据采样选取测试个数超过1000以上，发布日期3年以内的产品&am…
阅读更多...
MySQL学习| python与MySQL交互

MySQL学习| python与MySQL交互

上一篇博客主要讲了SQL的环境和语法，这一篇主要学习的是python和MySQL如何交互。 先搬上数据，作为准备 -- 创建 某宝 数据库 CREATE DATABASE mb CHARSETutf8;USE mb;CREATE TABLE goods(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,NAME VAR…
阅读更多...
老毛桃重启计算机没反应,老毛桃怎么用

老毛桃重启计算机没反应,老毛桃怎么用

1. 老毛桃u盘启动盘制作好后怎么用 老毛桃u盘启动盘安装系统步骤： 1.将制作好的老毛桃启动u盘插入电脑版USB插口，然后开启权电脑，待屏幕上出现开机画面后按快捷键进入到老毛桃主菜单页面，接着移动光标选择“【02】运行老毛桃Win8PE防蓝屏版(新电脑)”，按回车键确认 2.登录…
阅读更多...
宏基因组实战4. 基因注释Prokka

宏基因组实战4. 基因注释Prokka

前情提要 如果您在学习本教程中存在困难，可能因为缺少背景知识，建议先阅读本系统前期文章 宏基因组分析理论教程微生物组入门圣经宏基因组分析实操课程1背景知识-Shell入门与本地blast实战2数据质控fastqc, Trimmomatic, MultiQC, khmer3组装拼接MEGAHIT…
阅读更多...
Nature综述：鸟枪法宏基因组-从取样到数据分析——2万字带你系统入门宏基因组实验和分析

Nature综述：鸟枪法宏基因组-从取样到数据分析——2万字带你系统入门宏基因组实验和分析

文章目录 NBT：鸟枪法宏基因组-从取样到数据分析热心肠日报摘要正文图1. 宏基因组分析流程概述附图1. 用于规划宏基因组学研究的示例工作流程附框1. 实验设计中的问题和解决方案鸟枪法宏基因组学研究方案设计样本采集和DNA提取表1. 在测序之前富集微生物细胞和DNA方法…
阅读更多...
最新文章

Copyright @ 2022~2023