四:MySQL系列之Python交互(四)

时间:2024-10-01 18:36:08

  该篇主要介绍MySQL数据库的分表、以及与Python的交互的基本操作等。

一、拆分表操作

  1.1  准备工作

  创建数据库 --> 使用数据库 --> 创建数据表 --- 添加记录

-- 1.创建一个名为淘宝的数据库
create database taobao charset=utf8; -- 2. 使用该数据库进行操作
use taobao; -- 3.创建数据表(商品)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 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
); -- 4.插入记录
- 向goods表中插入数据 insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux )','台式机','宏碁','',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','',default,default);

创建数据库、数据表、插入值

  从上表中:由于当我们需要修改数据表的cate_name 或者brand_name字段中的 "平板电脑" 或者 "苹果" 的名称时,修改起来会显得十分麻烦,并且如果数据十分庞大时,维护起来也会显得十分的麻烦,故我们可以使用拆分表来更好的维护数据。接下来我们介绍一下如何 拆分表;

  

  2.2 拆分表A

  其主要步骤为:

   1、创建分类表A_a

   2、将将分组结果插入表A某个字段中去

   3、同步原表A的数据

   4、修改原表A的结构

   5、为原表A添加 外键

  

1、创建商品分类表    2、将查询结果插入goods_cates表的name字段中

-- 查分表

    -- 1、创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
); -- 查询goods表的分类
select cate_name from goods group by cate_name; --2、将查询结果插入goods_cates表的name字段中
insert into goods_cates (name) select cate_name from goods group by cate_name;

 得到如下的结果:

mysql> select * from goods_cates;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+----+---------------------+
7 rows in set (0.00 sec)

得到goods-cates的结果

  3、通过goods_cates数据表来更新goods表

-- 通过goods_cates数据表来更新goods表

    update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name = c.id;

  4、修改goods表的结构,即修改cate_name 为 cate_id,且数据类型需修改为int

--- 查看 goods 的数据表结构,会发现 cate_name 对应的类型为 varchar 但是存储的都是数字

    alter table goods change cate_name cate_id int unsigned not null,

  5、为goods表增设外键

-- 为表goods_cates添加记录
insert into goods_cates(name) values ('路由器'),('交换机'),('网卡'); -- 为表goods添加记录
insert into goods values(0,'皮皮双肩背包',12,'索尼','',default,default); -- 查询所有商品的详细信息 (通过左连接)
select g.*,c.name from goods as g
left join goods_cates as c on g.cate_id=c.id;d;

  当我们为两个表分别插入值时,goods表中插入的cate_id值可以是任意整数(12)。那么我们怎么阻止这类无效数据的插入呢?这时候就需要外键了。

 什么是外键: 

  • 外键约束:对数据的有效性进行验证

  • 关键字: foreign key,只有 innodb数据库引擎 支持外键约束
-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id); -- 给cate_id 添加外键失败
-- 会出现1452错误 -- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
delete from goods where id=22; -- 再添加外键
alter table goods add foreign key (cate_id) references goods_cates(id);

  当然在实际开发的过程中很少使用外键,因为这会极大的降低表的更新效率。那么我们这么删除外键呢?还有就是在创建表的时候添加外键呢?

 6、创建时设置外键

--- 创建的时候设置外键
foreign key(cate_id) references goods_cates(id)

  外键也是一种约束,通常我们在创建表的时候会指定字段的 名字 数据类型 以及约束;而在设置外键 foreign key 给谁 references 关联谁即可;

 7、删除外键

-- 获取外键约束名称
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods; -- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;

  删除外键也是修改表结构故用alter语句。

二、Python操作MySQL数据库

  首先我们若与MySQL数据库进行交互,需要安装一个模块 pymysql ,安装教程可以百度应该挺多;

  交互流程大致如下图所示:

四:MySQL系列之Python交互(四)

  

即大致流程如下:直接看示例:

from pymysql import connect

# 1、创建conn对象
conn = connect(host= "localhost",port=3306,database="taobao",user="root",password="mysql",charset="utf8",)
# 2、创建cursor 对象
cursor = conn.cursor() # 查询,获取受影响行数
count = cursor.execute("select * from goods;")
print(count)
# 获取数据
cursor.fetchone() # 获取一行数据
cursor.fetchmany(3) # 获取指定行数的数据
cursor.fetchall() # 获取所有行的数据 # 修改
cursor.execute("insert into goods values('皮皮虾我们走',6,'雷神',"6888",0,0);")
conn.commit() # 关闭cursor对象
cursor.close()
# 再关闭conn对象
conn.close()

 解析:其实conn对象负责与数据库之间的通信,例如提交commit,而 cursor对象更像是数据库的数据的操作人员,即可以查询、修改数据库,同时也充当容器的作用将取到的数据存储在自身口袋;

    注:修改数据库需使用conn对象进行提交,关闭时先关闭cursor对象再关闭conn对象;

from pymysql import connect

class JingDong(object):
def __init__(self):
self.conn = connect(host="localhost",port=3306,user="root",password="mysql",database="jing_dong",charset="utf8")
self.cursor = self.conn.cursor() def __del__(self):
self.cursor.close()
self.conn.close() def execute_sql(self,sql):
# 执行sql语句
self.cursor.execute(sql)
for temp in self.cursor.fetchall():
print(temp) def show_all_items(self):
# 显示所有商品
sql = "select * from goods;"
self.execute_sql(sql) def show_cates(self):
# 显示所有分类名称
sql = "select name from goods_cates;"
self.execute_sql(sql) def show_brands(self):
# 显示所有商品的品牌分类
sql = "select name from goods_brands"
self.execute_sql(sql) def change_goods_name(self,good_name,change_name):
lis =[change_name,good_name]
sql = "update goods name set name=%s where name=%s;"
self.cursor.execute(sql,lis)
self.conn.commit() @staticmethod
def print_menu():
print("-----welcome to jingdong---")
print("01-查询所有商品信息")
print("02-查看所有商品的分类")
print("03-查看所有商品的品牌分类")
print("04-修改商品的名称")
print("q-退出选择")
ret = input("请输入功能对应的序号:")
return ret def run(self):
while True:
ret =self. print_menu()
if ret == "":
self.show_all_items()
elif ret == "":
self.show_cates()
elif ret == "":
self.show_brands()
elif ret == "":
good_name=input("请输入要修改商品的名字:")
change_name = input("请输入修改后的名字:")
self.change_goods_name(good_name,change_name)
elif ret == "q":
break
else:
print("您输入的有误,请重新输入") def main():
jd = JingDong()
jd.run()
if __name__ == "__main__":
main()

实例--用户 输入查询数据

  over ~~~ 本篇介绍到此,下篇介绍数据的视图、事务、索引等相关知识