- 如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决
- 外键约束:对数据的有效性进行验证
- 关键字: foreign key,只有 innodb数据库引擎 支持外键约束
- 对于已经存在的数据表 如何更新外键约束
- 让 cate_id 成为外键
把 goods商品里面的最后一条数据删掉，不然后面后面外键的设置会失败
delete from goods where id=22;
- 设置 cate_id 成为外键
alter table goods add foreign key (cate_id) references good_cates(id);
- 这个时候我们如果在插入往 goods里面插入一条包含 cate_id = 12 的就会报错了，因为 12 在 good_cates 表里找不到
insert into goods (name,cate_id,price) values('LaserJet Pro P1606dn 黑白激光打印机', 12, '华硕','1849');
- 报错信息：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (
jing_dong.goods, CONSTRAINTgoods_ibfk_1FOREIGN KEY (cate_id) REFERENCESgood_cates(id))
mysql>
- 1.7、如何在创建数据表的时候就设置 外键约束 呢?，注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致
- 创表，设置外键
create table goods( id int primary key auto_increment not null, name varchar(40) default '', price decimal(5,2), cate_id int unsigned, brand_id int unsigned, is_show bit default 1, is_saleoff bit default 0, foreign key(cate_id) references goods_cates(id), foreign key(brand_id) references goods_brands(id) );
- 如何取消外键约束：
需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
提示： 在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
- 1.8、上面我们已经完成了 表
good_cates的操作,下面我们来快速的创建一下good_brands表
- (1)、通过create...select来创建数据表并且同时写入记录,一步到位
在创建数据表的时候一起插入数据
create table good_brands ( id int unsigned primary key auto_increment, name varchar(40) not null) select brand_name as name from goods group by brand_name;
注意：需要对brand_name 用as起别名，否则name字段就没有值
- (2)、同步数据
通过goods_brands数据表来更新goods数据表
update goods as g inner join good_brands as b on g.brand_name=b.name set g.brand_name=b.id;
- (3)、修改表结构
- 查看 goods 的数据表结构,会发现 cate_name 和 brand_name对应的类型为 varchar 但是存储的都是数字
desc goods;
- 通过alter table语句修改表结构(下面是可以同事修改另两个字段的，cate_name我们已经修改过)
alter table goods change cate_name cate_id int unsigned not null,change brand_name brand_id int unsigned not null;
alter table goods change brand_name brand_id int unsigned not null;
- (4)、brand_id 设置为 goods表的 外键
alter table goods add foreign key (brand_id) references good_brands(id);
- (5)、取消外键约束
需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
二、python安装pymysql 与 Python 操作
- 2.1、在终端安装
pymysql： pip3 install pymysql - 2.2、Python 中操作 MySQL 步骤
- (1)、引入模块：在py文件中引入pymysql模块
from pymysql import *
- (2)、Connection 对象
- 用于建立与数据库的连接
- 创建对象：调用
connect()方法
conn=connect(参数列表) 例如： conn = connect(host='域名或者IP',port=3306,database='数据库名',user='用户名',password='密码',charset='utf8')
- 参数host：连接的mysql主机，如果本机是'localhost'，服务器的话就是：域名或者IP
- 参数port：连接的mysql主机的端口，默认是3306
- 参数database：数据库的名称
- 参数user：连接的用户名
- 参数password：连接的密码
- 参数charset：通信采用的编码方式，推荐使用utf8
- (3)、对象的方法
- close()关闭连接
- commit()提交
- cursor()返回Cursor(游标)对象，用于执行sql语句并获得结果
- (4)、Cursor对象
- 用于执行sql语句，使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象：调用Connection对象的cursor()方法
cs1=conn.cursor()
- (5)、对象的方法
- close()关闭
- execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
count = cs1.execute('select * from goods') # 返回是表中数据的个数
- fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
- fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
- fetchmany(数量)执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回，也就是元组套元组
- (6)、对象的属性
- rowcount只读属性，表示最近一次execute()执行后受影响的行数
- connection获得当前连接对象
- 2.3、python操作sql：
查、增、改、删数据
- 共用代码
from pymysql import * def main(): # 创建Connection连接 conn = connect(host='ironman.ren',port=3306,database='jing_dong',user='root',password='456123love',charset='utf8') # 获得Cursor(游标)对象 cursor = conn.cursor() """"增删改查的代码""" # 关闭Cursor对象 cursor.close() # 关闭Connection对象 conn.close()
- (1)、查询数据
查询数据库某个表的全部数据个数
count = cursor.execute('select * from goods') print("总数量count=%d"%count) 打印结果：21
- 查询数表goods里面所有的信息：
fetchall()
cursor.execute('select * from goods') print(cursor.fetchall())
- 查询数表goods里面部分的信息：
fetchmany(数量)
print(cursor.fetchmany(3))
- 查询数表goods里面一条信息：
fetchone()
print(cursor.fetchone())
- (2)、增加数据：执行insert语句，并返回受影响的行数：添加一条数据
count = cursor.execute('insert into goods values(0,"笨蛋 超极本",5,4,4000,0,0)') # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交 conn.commit()
- (3)、改：更新数据
count = cursor.execute('update goods set name = "超级笨蛋 超极本" where name = "笨蛋 超极本" ') conn.commit()
- (4)、删：数据
count = cursor.execute('delete from goods where name="超级笨蛋 超极本" ') conn.commit()
- 提示： 除了
查询外，增、改、删都需要conn.commit()conn.commit(): 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
- 2.4、python操作goods表：所有数据、品牌分类、所有的分类
from pymysql import * class JD(object): def __init__(self): # 1、创建连接 self.connection = connect(host='ironman.ren', port=3306, database='jing_dong', user='root', password='456123love', charset='utf8') # 2、获取cursor(游标)对象 self.cursor = self.connection.cursor() def __del__(self): # 5、关闭游标 self.cursor.close() # 6、关闭数据库连接 self.connection.close() @staticmethod def print_munu(): print("京东商品查询") print("1.所有商品") print("2.所有商品分类") print("3.所有商品品牌分类") return input("请输入功能对应的序号：") def execute_sql(self,sql): count = self.cursor.execute(sql) print(count) """ 所有数据、品牌分类、所有的分类 """ for item in self.cursor.fetchall(): print(item) def select_run(self): while True: num = self.print_munu() if num == "1": # 所有商品 self.show_all_items() elif num == "2": # 所有商品分类 self.show_all_cates() elif num == "3": # 所有商品品牌分类 self.show_all_brand() def show_all_items(self): # 3、使用游标对象 # 3.1、查询这个good表 sql = 'select * from goods' self.execute_sql(sql) def show_all_brand(self): # 3.2、查询这个good表 sql = 'select * from good_brands' self.execute_sql(sql) def show_all_cates(self): # 3.3、查询这个good表 sql = 'select * from good_cates' self.execute_sql(sql) def main(): # 1、创建京东对象 jd = JD() # 2、调用查询 jd.select_run() if __name__ == "__main__": main()
三、参数化(sql防注入)
- 3.1、我们来看一个用户搜索商品的例子，根据用户输入的信息进行查询
from pymysql import * class JD(object): def __init__(self): # 1、创建连接 self.connection = connect(host='ironman.ren', port=3306, database='jing_dong', user='root', password='456123love', charset='utf8') # 2、获取cursor(游标)对象 self.cursor = self.connection.cursor() def __del__(self): # 5、关闭游标 self.cursor.close() # 6、关闭数据库连接 self.connection.close() def select_commodity(self): while True: select_content = input("请输入搜索的商品内容：") params = [select_content] self.cursor.execute("select * from goods where name = %s",params) for item in self.cursor.fetchall(): print(item) def main(): jd = JD() jd.select_commodity() if __name__ == "__main__": main()
- 3.2、提一下 3.1中的
def select_commodity(self):方法里面的sql拼接的问题,上面用的安全方式
- 安全方式：把搜索的内容放到数组里面，在
execute()里面，如果要是有多个参数，需要进行参数化，那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 ，sql语句会自动完成，不需要我们来做
select_content = input("请输入搜索的商品内容：") # 构造参数列表 params = [select_content] self.cursor.execute("select * from goods where name = %s",params)
- 不安全方式：如果用户输入：' ' or 7=7,就会查出你数据库所有的数据，假如用户使用的删除操作，很可能把这个表给你全删了😆，sql注入防不胜防
select_content = input("请输入搜索的商品内容：") sql = "select * from goods where name = %s"% select_content self.cursor.execute(sql)
提示：sql语句的参数化，可以有效防止sql注入
友情提示：不要通过sql注入做坏事哦
