python与mysql交互详解

一、数据准备

  1. 数据库准备

    -- 数据准备
    	-- 创建京东数据库
    	create database jing_dong charset=utf8;
    
    	-- 创建一个商品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
    	);
    
    	-- 添加数据
    	insert into goods values(0, 'r518vc 15.6英寸笔记本','笔记本','华硕','3399' , default, default);
    	insert into goods values(0, 'y400 14.0英寸笔记本','笔记本','联想','4999' , default, default);
    	insert into goods values(0, 'g150th 15.6英寸笔记本','游戏本','雷神','8499' , default, default);
    	insert into goods values(0, 'x550c 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 显示屏','显示屏','苹果','2799' , default, default);
    	insert into goods values(0, 'ideacentre c340 20英寸一题电脑','台式机','戴尔','2899' , default, default);
    	insert into goods values(0, 'imac 一体电脑','台式机','苹果','9188' , default, default);
    	insert into goods values(0, 'at7 台式电脑 linux','台式机','宏碁','3699' , default, default);
    	insert into goods values(0, 'z22 工作站','服务器/工作站','惠普','4288' , default, default);
    	insert into goods values(0, 'pow ii 服务器','服务器/工作站','戴尔','5388' , default, default);
    	insert into goods values(0, 'mac pro专业台式电脑','笔记本','苹果','28888' , default, default);
    	insert into goods values(0, 'hmz 头戴显示器','笔记本配件','索尼','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);
    
  2. SQL语句的强化

    -- SQL强化
    	-- 查询笔记本为超极本的
    	select * from goods where cate_name="超极本";
    	-- 只看 name 和 价格
    	select name as 名字, price as 价格 from goods where cate_name="超极本";
    
    	-- 显示商品的种类
    	select cate_name from goods;
    	select distinct cate_name from goods;
    	select cate_name from goods group by cate_name;
    
    	-- 每种商品种类中的名称
    	select cate_name, group_concat(name) from goods group by cate_name;
    
    	-- 求所有商品的平均价格
    	select avg(price) from goods;
    	select round(avg(price), 2) from goods;
    
    	-- 显示每种商品的平均价格
    	select cate_name, avg(price) from goods group by cate_name;
    
    	-- 查询 最贵 最便宜 数量
    	select cate_name, avg(price), max(price), min(price), count(*) from goods group by cate_name;
    
    
    	-- 大于平均价格的
    	select * from goods where price>(select avg(price) from goods);
    
    	-- 查询每种商品当中最贵商品的信息
    	select * 
    	from (select cate_name, max(price) as max_price from goods group by cate_name) as g_new
    	left join goods as g on g.price=g_new.max_price and g.cate_name=g_new.cate_name;
    
    -- 将数据表中的数据拆出来 拆成N张表
    	-- 创建"商品分类表"	
    	create table if not exists goods_cates(
    		id int unsigned primary key auto_increment,
    		name varchar(40) not null 
    	);
    
    	-- 向 商品分类表中存信息
    	select cate_name from goods group by cate_name;  -- 查询分类
    	insert into goods_cates (name) select cate_name from goods group by cate_name;  -- 这个时候不用写valuses
    
    	-- 修改goods中商品的信息 将cate_name 改成另一个表的id
    	update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
    
    
    	-- 修改goods的表结构 用alter change
    	alter table goods change cate_name cate_id int unsigned not null;
    	
    	-- goods添加关联 cate_id --> goods_cates 的id
    	-- 1.添加外键  2.程序里面控制  谈价外键的时候会检查cate_id 和 id 时候有
    	alter table goods add foreign key (cate_id) references goods_cates(id);	
    
    	-- 添加一个品牌表 并向里面添加关联数据
    	-- 擦混国家爱你表并添加数据 添加数据的时候查出来的哪个name要相同
    	create table if not exists goods_brands(
    		id int unsigned primary key auto_increment,
    		name varchar(40) not null
    	) select brand_name as name from goods group by brand_name;
    
    	-- 关联goods 和goods_brands
    	update goods as g inner join goods_brands as b on b.name=g.name set g.brand_name=b.id;
    	-- 修改表结构
    	alter table goods change brand_name brand_id int unsigned not null;
    	-- 添加外键约束
    	alter table goods add foreign key (brand_id) references goods_brands(id);
    
    	-- 删除外键约束
    		--查询结果	
    			-- 	| goods | CREATE TABLE `goods` (
    			--   `id` int unsigned NOT NULL AUTO_INCREMENT,
    			--   `name` varchar(150) NOT NULL,
    			--   `cate_id` int unsigned NOT NULL,
    			--   `brand_id` int unsigned NOT NULL,
    			--   `price` decimal(10,3) NOT NULL DEFAULT '0.000',
    			--   `is_show` bit(1) NOT NULL DEFAULT b'1',
    			--   `is_saleoff` bit(1) NOT NULL DEFAULT b'0',
    			--   PRIMARY KEY (`id`),
    			--   KEY `cate_id` (`cate_id`),
    			--   KEY `brand_id` (`brand_id`),
    			--   CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`),
    			--   CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`)
    			-- ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 |
    		-- 删除外键
    		-- alter table goods drop foreign key 外键名;
    		alter table goods drop foreign key 'goods_ibfk_1';
    
  3. pymysql安装

     pip install pymysql
    

二、pymysql模块的使用

  1. 链接数据库

    conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf8')
    
  2. 获取游标对象

    cs1 = conn.cursor()
    
  3. 执行sql语句

    count = cs1.execute("select * from goods")  # count记录的是受影响的行数
    
  4. 获取返回数据:
    4.1 cs1.fetchone() :获取一条数据–>一个元组
    4.2 cs1.fetchall() # 取出所有数据 元组里面嵌元组
    4.3 cs1.fetchmany(5) : 获取五条数据

  5. 关闭

    cs1.close()  # 关闭游标
    conn.close()  # 关闭连接 
    

三、实例的介绍

1. 面向对象的方法 完成查询

  1. 查询
    from pymysql import connect
    
    
    # 用类完成
    class JD(object):
    	def __init__(self):
    		# 连接数据库
    	    self.conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf8')
    	    # 获取游标对象
    	    self.cursor = self.conn.cursor()
    
    	def __del__(self):
    		# 关闭连接
    		self.conn.close()
    		self.cursor.close()
    
    	@staticmethod
    	def print_menu():
    		# 静态方法没什么值也不用传
    		print("------京东------")
    		print("0. 退出")
    		print("1. 查询所有的商品")
    		print("2. 查询所有的品牌分类")
    		print("3. 查询所有的商品分类")
    		return input("请输入功能所对应的序号:")
    
    	def execute_sql(self, sql):
    		# 输出查询内容
    		# 输出查询内容
    		self.cursor.execute(sql)
    		for item in self.cursor.fetchall():
    			print(item)
    
    	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 run(self):
    		"""运行"""
    		while True:
    			num = self.print_menu()
    			if num == "1":
    				self.show_all_items()
    			elif num == "2":
    				self.show_cates()
    			elif num == "3":
    				self.show_brands()
    			elif num == "0":
    				break
    			else:
    				print("输入错误请重新输入")
    
    
    def main():
    	"""完成整体的控制"""
    	# 1. 创建一个京东商城的对象
    	jd = JD()
    	# 2. 调用这个对象的run方法,让其运行
    	jd.run()
    
    
    if __name__ == "__main__":
    	main()
    
    

2. 添加增加功能

  1. 添加增加的功能
    """增删改查-面向对象"""
    from pymysql import connect
    
    
    # 用类完成
    class JD(object):
    	def __init__(self):
    		# 连接数据库
    	    self.conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf8')
    	    # 获取游标对象
    	    self.cursor = self.conn.cursor()
    
    	def __del__(self):
    		# 关闭连接
    		self.conn.close()
    		self.cursor.close()
    
    	@staticmethod
    	def print_menu():
    		# 静态方法没什么值也不用传
    		print("------京东------")
    		print("0. 退出")
    		print("1. 查询所有的商品")
    		print("2. 查询所有的品牌分类")
    		print("3. 查询所有的商品分类")
    		print("4. 增加商品p品牌种类")
    		return input("请输入功能所对应的序号:")
    
    	def execute_sql(self, sql):
    		# 输出查询内容
    		self.cursor.execute(sql)
    		for item in self.cursor.fetchall():
    			print(item)
    
    	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 add_brands(self):
    		"""增加品牌"""
    		item = input("请输入要添加的品牌:")
    		sql = """insert into goods_brands (name) values ("%s")""" % item
    		self.cursor.execute(sql)
    		# 提交 增删改 必须有这个
    		self.conn.commit()
    
    	def run(self):
    		"""运行"""
    		while True:
    			num = self.print_menu()
    			if num == "1":
    				self.show_all_items()
    			elif num == "2":
    				self.show_cates()
    			elif num == "3":
    				self.show_brands()
    			elif num == "4":
    				self.add_brands()
    			elif num == "0":
    				break
    			else:
    				print("输入错误请重新输入")
    
    
    def main():
    	"""完成整体的控制"""
    	# 1. 创建一个京东商城的对象
    	jd = JD()
    	# 2. 调用这个对象的run方法,让其运行
    	jd.run()
    
    
    if __name__ == "__main__":
    	main()
    
    

3.查询–>包含sql注入

  1. 查询–sql注入

    """增删改查-面向对象  sql注入"""
    from pymysql import connect
    
    
    # 用类完成
    class JD(object):
    	def __init__(self):
    		# 连接数据库
    	    self.conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf8')
    	    # 获取游标对象
    	    self.cursor = self.conn.cursor()
    
    	def __del__(self):
    		# 关闭连接
    		self.conn.close()
    		self.cursor.close()
    
    	@staticmethod
    	def print_menu():
    		# 静态方法没什么值也不用传
    		print("------京东------")
    		print("0. 退出")
    		print("1. 查询所有的商品")
    		print("2. 查询所有的品牌分类")
    		print("3. 查询所有的商品分类")
    		print("4. 增加商品品牌种类")
    		print("5. 查询商品")
    		return input("请输入功能所对应的序号:")
    
    	def execute_sql(self, sql):
    		# 输出查询内容
    		self.cursor.execute(sql)
    		for item in self.cursor.fetchall():
    			print(item)
    
    	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 add_brands(self):
    		"""增加品牌"""
    		item = input("请输入要添加的品牌:")
    		sql = """insert into goods_brands (name) values ("%s")""" % item
    		self.cursor.execute(sql)
    		# 提交 增删改 必须有这个
    		self.conn.commit()
    
    	def get_info_by_name(self):
    		"""查询商品信息+sql注入"""
    		# 当用户输入的是: 'or 1=1 or '1 时会出现sql注入  --> 前面的'和自己写的sql语句中的'进行匹配,使得其出现三个判断语句,期中一个成功则将会将所有的信息查出
    		find_name = input("请输入要查询的商品信息:")
    		sql = """select * from goods where name='%s'""" % find_name
    		self.execute_sql(sql)
    
    	def run(self):
    		"""运行"""
    		while True:
    			num = self.print_menu()
    			if num == "1":
    				self.show_all_items()
    			elif num == "2":
    				self.show_cates()
    			elif num == "3":
    				self.show_brands()
    			elif num == "4":
    				self.add_brands()
    			elif num == "5":
    				self.get_info_by_name()
    			elif num == "0":
    				break
    			else:
    				print("输入错误请重新输入")
    
    
    def main():
    	"""完成整体的控制"""
    	# 1. 创建一个京东商城的对象
    	jd = JD()
    	# 2. 调用这个对象的run方法,让其运行
    	jd.run()
    
    
    if __name__ == "__main__":
    	main()
    
    

4.解决sql注入

  1. 解决sql注入

    """增删改查-面向对象  解决sql注入"""
    from pymysql import connect
    
    
    # 用类完成
    class JD(object):
    	def __init__(self):
    		# 连接数据库
    	    self.conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf8')
    	    # 获取游标对象
    	    self.cursor = self.conn.cursor()
    
    	def __del__(self):
    		# 关闭连接
    		self.conn.close()
    		self.cursor.close()
    
    	@staticmethod
    	def print_menu():
    		# 静态方法没什么值也不用传
    		print("------京东------")
    		print("0. 退出")
    		print("1. 查询所有的商品")
    		print("2. 查询所有的品牌分类")
    		print("3. 查询所有的商品分类")
    		print("4. 增加商品品牌种类")
    		print("5. 查询商品")
    		return input("请输入功能所对应的序号:")
    
    	def execute_sql(self, sql):
    		# 输出查询内容
    		self.cursor.execute(sql)
    		for item in self.cursor.fetchall():
    			print(item)
    
    	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 add_brands(self):
    		"""增加品牌"""
    		item = input("请输入要添加的品牌:")
    		sql = """insert into goods_brands (name) values ("%s")""" % item
    		self.cursor.execute(sql)
    		# 提交 增删改 必须有这个
    		self.conn.commit()
    
    	def get_info_by_name(self):
    		"""查询商品信息+解决sql注入"""
    		# 当用户输入的是: 'or 1=1 or '1 时会出现sql注入  --> 前面的'和自己写的sql语句中的'进行匹配,使得其出现三个判断语句,期中一个成功则将会将所有的信息查出
    		# 解决方法将sql语句让其自己拼接
    		find_name = input("请输入要查询的商品信息:")
    		sql = 'select * from goods where name=%s'
    		self.cursor.execute(sql, [find_name])
    		print(self.cursor.fetchall())
    
    	def run(self):
    		"""运行"""
    		while True:
    			num = self.print_menu()
    			if num == "1":
    				self.show_all_items()
    			elif num == "2":
    				self.show_cates()
    			elif num == "3":
    				self.show_brands()
    			elif num == "4":
    				self.add_brands()
    			elif num == "5":
    				self.get_info_by_name()
    			elif num == "0":
    				break
    			else:
    				print("输入错误请重新输入")
    
    
    def main():
    	"""完成整体的控制"""
    	# 1. 创建一个京东商城的对象
    	jd = JD()
    	# 2. 调用这个对象的run方法,让其运行
    	jd.run()
    
    
    if __name__ == "__main__":
    	main()
    
    

四、总结

  1. 在查询语句中需要拼接字符串的时候要用列表或者元组代替让cursor.execute自己完成拼接,可以防止sql注入
  2. 完成查询后要记得关闭链接否则会占用资源
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值