mysql与python交互
-- 创建数据库
create database jingdong charset=utf8;
use jingdong;
-- 创建一个商品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
);
-- 向goods表中插入数据
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,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',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-7414p 台式电脑 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 头戴显示设备','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
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 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 cate_name,max(price) as max_price from goods group by cate_name
select g_new.cate_name,g.name,g.price
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_new.cate_name=g.cate_name and g_new.max_price=g.price
拆表
-- 创建商品分类表
create table if not exists good_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
-- 查询goods表中的商品种类
select cate_name from goods group by cate_name;
-- 将分组结果写入到goods_cates数据表
insert into good_cates(name) select cate_name from goods group by cate_name;
-- 通过goods_cates数据表来更新goods表
update goods as g inner join good_cates as c on g.cate_name=c.name set g.cate_name=c.id;
-- 分别网good_cates和good_brands表中插入记录
insert into good_cates(name) values ('路由器'),('交换机'),('网卡');
-- 在goods数据表中写入任意记录
insert into goods (name,cate_id,brand_name,price) values('LaserJet Pro P1606dn 黑白激光打印机',12,4,'1849');
-- 更改goods表字段cate_name字段为cate_id 类型改为int
alter table goods change cate_name cate_id int unsigned not null;
-- 添加外键
alter table goods add foreign key (cate_id) references good_cates (id);
-- 创建表的同时插入数据
create table 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_brands表更新goods表数据
update goods as g inner join goods_brands as b on g.brand_name=b.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);
实际开发中尽量不要使用外键
-- 删除外键
alter table goods drop foreign key cate_id;
python中操作mysql
- 安装pymysql
sudo pip install pymsql -
python操作mysql步骤
pymysql操作步骤.png
pycharm 操作mysql实例
from pymysql import connect
class JD(object):
def __init__(self):
# 创建connection连接
self.conn = connect(host='localhost', port=3306, user='root', password='123', database='jingdong', charset='utf8')
# 获得cursor 游标 对象
self.cursor = self.conn.cursor()
def __del__(self):
# 关闭游标对象
self.cursor.close()
# 关闭连接
self.conn.close()
def execute_sql(self, 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 good_cates;"
self.execute_sql(sql)
def show_brands(self):
sql = "select name from goods_brands;"
self.execute_sql(sql)
def add_brands(self):
item_name = input("请输入新商品分类名称：")
sql = """insert into goods_brands (name) values ("%s")""" % item_name
self.cursor.execute(sql)
self.conn.commit()
@staticmethod
def print_menu():
print("------京东-------")
print("0:退出")
print("1:所有的商品")
print("2:所有的商品分类")
print("3:所有的商品品牌分类")
print("4:添加一个商品分类")
return input("请输入功能对应的需要：")
def run(self):
while True:
num = JD.print_menu()
if num == "0": # 退出
break
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()
else:
print("输入错误，重新输入...")
def main():
# 1. 创建一个京东商城对象
jd = JD()
# 2. 调用这个对象的run方法，让其运行
jd.run()
if __name__ == '__main__':
main()
防止SQL注入
from pymysql import connect
class JD(object):
def __init__(self):
# 创建connection连接
self.conn = connect(host='localhost', port=3306, user='root', password='123', database='jingdong', charset='utf8')
# 获得cursor 游标 对象
self.cursor = self.conn.cursor()
def __del__(self):
# 关闭游标对象
self.cursor.close()
# 关闭连接
self.conn.close()
def execute_sql(self, 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 good_cates;"
self.execute_sql(sql)
def show_brands(self):
sql = "select name from goods_brands;"
self.execute_sql(sql)
def add_brands(self):
item_name = input("请输入新商品分类名称：")
sql = """insert into goods_brands (name) values ("%s")""" % item_name
self.cursor.execute(sql)
self.conn.commit()
def get_info_by_name(self):
find_name = input("请输入要查询的商品的名字:")
# sql = """select * from goods where name='%s'""" % find_name
# print("----->%s<------%s" % sql)
# self.execute_sql(sql)
# 防止sql注入
sql = "select * from goods where name=%s"
self.cursor.execute(sql, [find_name])
print(self.cursor.fetchall())
@staticmethod
def print_menu():
print("------京东-------")
print("0:退出")
print("1:所有的商品")
print("2:所有的商品分类")
print("3:所有的商品品牌分类")
print("4:添加一个商品分类")
print("5:根据名字查询商品")
return input("请输入功能对应的需要：")
def run(self):
while True:
num = JD.print_menu()
if num == "0": # 退出
break
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.add_brands()
else:
print("输入错误，重新输入...")
def main():
# 1. 创建一个京东商城对象
jd = JD()
# 2. 调用这个对象的run方法，让其运行
jd.run()
if __name__ == '__main__':
main()
网友评论