MySQL和Python交互的示例
一.准备数据
创建数据表
--创建"京东"数据库 createdatabasejing_dongcharset=utf8; --使用"京东"数据库 usejing_dong; --创建一个商品goods数据表 createtablegoods( idintunsignedprimarykeyauto_incrementnotnull, namevarchar(150)notnull, cate_namevarchar(40)notnull, brand_namevarchar(40)notnull, pricedecimal(10,3)notnulldefault0, is_showbitnotnulldefault1, is_saleoffbitnotnulldefault0 );
插入数据
--向goods表中插入数据 insertintogoodsvalues(0,'r510vc15.6英寸笔记本','笔记本','华硕','3399',default,default); insertintogoodsvalues(0,'y400n14.0英寸笔记本电脑','笔记本','联想','4999',default,default); insertintogoodsvalues(0,'g150th15.6英寸游戏本','游戏本','雷神','8499',default,default); insertintogoodsvalues(0,'x550cc15.6英寸笔记本','笔记本','华硕','2799',default,default); insertintogoodsvalues(0,'x240超极本','超级本','联想','4880',default,default); insertintogoodsvalues(0,'u330p13.3英寸超极本','超级本','联想','4299',default,default); insertintogoodsvalues(0,'svp13226scb触控超极本','超级本','索尼','7999',default,default); insertintogoodsvalues(0,'ipadmini7.9英寸平板电脑','平板电脑','苹果','1998',default,default); insertintogoodsvalues(0,'ipadair9.7英寸平板电脑','平板电脑','苹果','3388',default,default); insertintogoodsvalues(0,'ipadmini配备retina显示屏','平板电脑','苹果','2788',default,default); insertintogoodsvalues(0,'ideacentrec34020英寸一体电脑','台式机','联想','3499',default,default); insertintogoodsvalues(0,'vostro3800-r1206台式电脑','台式机','戴尔','2899',default,default); insertintogoodsvalues(0,'imacme086ch/a21.5英寸一体电脑','台式机','苹果','9188',default,default); insertintogoodsvalues(0,'at7-7414lp台式电脑linux）','台式机','宏碁','3699',default,default); insertintogoodsvalues(0,'z220sfff4f06pa工作站','服务器/工作站','惠普','4288',default,default); insertintogoodsvalues(0,'poweredgeii服务器','服务器/工作站','戴尔','5388',default,default); insertintogoodsvalues(0,'macpro专业级台式电脑','服务器/工作站','苹果','28888',default,default); insertintogoodsvalues(0,'hmz-t3w头戴显示设备','笔记本配件','索尼','6999',default,default); insertintogoodsvalues(0,'商务双肩背包','笔记本配件','索尼','99',default,default); insertintogoodsvalues(0,'x3250m4机架式服务器','服务器/工作站','ibm','6888',default,default); insertintogoodsvalues(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
二.SQL演练
1.SQL语句的强化
查询类型cate_name为'超极本'的商品名称、价格
selectname,pricefromgoodswherecate_name='超级本';
显示商品的种类
selectcate_namefromgoodsgroupbycate_name;
求所有电脑产品的平均价格,并且保留两位小数
selectround(avg(price),2)asavg_pricefromgoods;
显示每种商品的平均价格
selectcate_name,avg(price)fromgoodsgroupbycate_name;
查询每种类型的商品中最贵、最便宜、平均价、数量
selectcate_name,max(price),min(price),avg(price),count(*)fromgoodsgroupbycate_name;
查询所有价格大于平均价格的商品，并且按价格降序排序
selectid,name,pricefromgoods whereprice>(selectround(avg(price),2)asavg_pricefromgoods) orderbypricedesc;
查询每种类型中最贵的电脑信息
select*fromgoods innerjoin ( select cate_name, max(price)asmax_price, min(price)asmin_price, avg(price)asavg_price, count(*)fromgoodsgroupbycate_name )asgoods_new_info ongoods.cate_name=goods_new_info.cate_nameandgoods.price=goods_new_info.max_price;
2.创建"商品分类""表
--创建商品分类表 createtableifnotexistsgoods_cates( idintunsignedprimarykeyauto_increment, namevarchar(40)notnull );
查询goods表中商品的种类
selectcate_namefromgoodsgroupbycate_name;
将分组结果写入到goods_cates数据表
insertintogoods_cates(name)selectcate_namefromgoodsgroupbycate_name;
3.同步表数据
通过goods_cates数据表来更新goods表
updategoodsasginnerjoingoods_catesascong.cate_name=c.namesetg.cate_name=c.id;
4.创建"商品品牌表"表
通过create...select来创建数据表并且同时写入记录,一步到位
--selectbrand_namefromgoodsgroupbybrand_name; --在创建数据表的时候一起插入数据 --注意:需要对brand_name用as起别名，否则name字段就没有值 createtablegoods_brands( idintunsignedprimarykeyauto_increment, namevarchar(40)notnull)selectbrand_nameasnamefromgoodsgroupbybrand_name;
5.同步数据
通过goods_brands数据表来更新goods数据表
updategoodsasginnerjoingoods_brandsasbong.brand_name=b.namesetg.brand_name=b.id;
6.修改表结构
查看goods的数据表结构,会发现cate_name和brand_name对应的类型为 varchar 但是存储的都是数字
descgoods;
通过altertable语句修改表结构
altertablegoods changecate_namecate_idintunsignednotnull, changebrand_namebrand_idintunsignednotnull;
7.外键
分别在goods_cates和goods_brands表中插入记录
insertintogoods_cates(name)values('路由器'),('交换机'),('网卡');
insertintogoods_brands(name)values('海尔'),('清华同方'),('神舟');
在goods数据表中写入任意记录
insertintogoods(name,cate_id,brand_id,price)
values('LaserJetProP1606dn黑白激光打印机',12,4,'1849');
查询所有商品的详细信息(通过内连接)
selectg.id,g.name,c.name,b.name,g.pricefromgoodsasg innerjoingoods_catesascong.cate_id=c.id innerjoingoods_brandsasbong.brand_id=b.id;
查询所有商品的详细信息(通过左连接)
selectg.id,g.name,c.name,b.name,g.pricefromgoodsasg leftjoingoods_catesascong.cate_id=c.id leftjoingoods_brandsasbong.brand_id=b.id;
- 如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢?可以使用之前讲过的外键来解决
- 外键约束:对数据的有效性进行验证
- 关键字:foreignkey,只有innodb数据库引擎支持外键约束
- 对于已经存在的数据表如何更新外键约束
--给brand_id添加外键约束成功 altertablegoodsaddforeignkey(brand_id)referencesgoods_brands(id); --给cate_id添加外键失败 --会出现1452错误 --错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除 altertablegoodsaddforeignkey(cate_id)referencesgoods_cates(id);
- 如何在创建数据表的时候就设置外键约束呢?
- 注意:goods中的cate_id的类型一定要和goods_cates表中的id类型一致
createtablegoods( idintprimarykeyauto_incrementnotnull, namevarchar(40)default'', pricedecimal(5,2), cate_idintunsigned, brand_idintunsigned, is_showbitdefault1, is_saleoffbitdefault0, foreignkey(cate_id)referencesgoods_cates(id), foreignkey(brand_id)referencesgoods_brands(id) );
如何取消外键约束
--需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称 showcreatetablegoods; --获取名称之后就可以根据名称来删除外键约束 altertablegoodsdropforeignkey外键名称;
在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
三.数据库的设计
创建"商品分类"表(之前已经创建,无需再次创建)
createtablegoods_cates( idintunsignedprimarykeyauto_incrementnotnull, namevarchar(40)notnull );
创建"商品品牌"表(之前已经创建,无需再次创建)
createtablegoods_brands( idintunsignedprimarykeyauto_incrementnotnull, namevarchar(40)notnull );
创建"商品"表(之前已经创建,无需再次创建)
createtablegoods( idintunsignedprimarykeyauto_incrementnotnull, namevarchar(40)default'', pricedecimal(5,2), cate_idintunsigned, brand_idintunsigned, is_showbitdefault1, is_saleoffbitdefault0, foreignkey(cate_id)referencesgoods_cates(id), foreignkey(brand_id)referencesgoods_brands(id) );
创建"顾客"表
createtablecustomer( idintunsignedauto_incrementprimarykeynotnull, namevarchar(30)notnull, addrvarchar(100), telvarchar(11)notnull );
创建"订单"表
createtableorders( idintunsignedauto_incrementprimarykeynotnull, order_date_timedatetimenotnull, customer_idintunsigned, foreignkey(customer_id)referencescustomer(id) );
创建"订单详情"表
createtableorder_detail( idintunsignedauto_incrementprimarykeynotnull, order_idintunsignednotnull, goods_idintunsignednotnull, quantitytinyintunsignednotnull, foreignkey(order_id)referencesorders(id), foreignkey(goods_id)referencesgoods(id) );
说明
- 以上创建表的顺序是有要求的,即如果goods表中的外键约束用的是goods_cates或者是goods_brands,那么就应该先创建这2个表,否则创建goods会失败
- 创建外键时,一定要注意类型要相同,否则失败
四.Python中操作MySQL步骤
引入模块
在py文件中引入pymysql模块
frompymysqlimport*
Connection对象
- 用于建立与数据库的连接
- 创建对象：调用connect()方法
conn=connect(参数列表)
- 参数host：连接的mysql主机，如果本机是'localhost'
- 参数port：连接的mysql主机的端口，默认是3306
- 参数database：数据库的名称
- 参数user：连接的用户名
- 参数password：连接的密码
- 参数charset：通信采用的编码方式，推荐使用utf8
对象的方法
- close()关闭连接
- commit()提交
- cursor()返回Cursor对象，用于执行sql语句并获得结果
Cursor对象
- 用于执行sql语句，使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象：调用Connection对象的cursor()方法
cs1=conn.cursor()
对象的方法
- close()关闭
- execute(operation[,parameters])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
- fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
- fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
对象的属性
- rowcount只读属性，表示最近一次execute()执行后受影响的行数
- connection获得当前连接对象
五.增删改查
frompymysqlimport*
defmain():
#创建Connection连接
conn=connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
#获得Cursor对象
cs1=conn.cursor()
#执行insert语句，并返回受影响的行数：添加一条数据
#增加
count=cs1.execute('insertintogoods_cates(name)values("硬盘")')
#打印受影响的行数
print(count)
count=cs1.execute('insertintogoods_cates(name)values("光盘")')
print(count)
##更新
#count=cs1.execute('updategoods_catessetname="机械硬盘"wherename="硬盘"')
##删除
#count=cs1.execute('deletefromgoods_cateswhereid=6')
#提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
conn.commit()
#关闭Cursor对象
cs1.close()
#关闭Connection对象
conn.close()
if__name__=='__main__':
main()
查询一行数据
frompymysqlimport*
defmain():
#创建Connection连接
conn=connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
#获得Cursor对象
cs1=conn.cursor()
#执行select语句，并返回受影响的行数：查询一条数据
count=cs1.execute('selectid,namefromgoodswhereid>=4')
#打印受影响的行数
print("查询到%d条数据:"%count)
foriinrange(count):
#获取查询的结果
result=cs1.fetchone()
#打印查询的结果
print(result)
#获取查询的结果
#关闭Cursor对象
cs1.close()
conn.close()
if__name__=='__main__':
main()
查询多行数据
frompymysqlimport*
defmain():
#创建Connection连接
conn=connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
#获得Cursor对象
cs1=conn.cursor()
#执行select语句，并返回受影响的行数：查询一条数据
count=cs1.execute('selectid,namefromgoodswhereid>=4')
#打印受影响的行数
print("查询到%d条数据:"%count)
#foriinrange(count):
##获取查询的结果
#result=cs1.fetchone()
##打印查询的结果
#print(result)
##获取查询的结果
result=cs1.fetchall()
print(result)
#关闭Cursor对象
cs1.close()
conn.close()
if__name__=='__main__':
main()
六.参数化
- sql语句的参数化，可以有效防止sql注入
- 注意：此处不同于python的字符串格式化，全部使用%s占位
frompymysqlimport*
defmain():
find_name=input("请输入物品名称：")
#创建Connection连接
conn=connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
#获得Cursor对象
cs1=conn.cursor()
##非安全的方式
##输入"or1=1or"(双引号也要输入)
#sql='select*fromgoodswherename="%s"'%find_name
#print("""sql===>%s<===="""%sql)
##执行select语句，并返回受影响的行数：查询所有数据
#count=cs1.execute(sql)
#安全的方式
#构造参数列表
params=[find_name]
#执行select语句，并返回受影响的行数：查询所有数据
count=cs1.execute('select*fromgoodswherename=%s',params)
#注意：
#如果要是有多个参数，需要进行参数化
#那么params=[数值1,数值2....]，此时sql语句中有多个%s即可
#打印受影响的行数
print(count)
#获取查询的结果
#result=cs1.fetchone()
result=cs1.fetchall()
#打印查询的结果
print(result)
#关闭Cursor对象
cs1.close()
#关闭Connection对象
conn.close()
if__name__=='__main__':
main()
以上就是MySQL和Python交互的示例的详细内容，更多关于MySQL和python交互的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。