学习pymysql的一个简单示例
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
功能:pymysql简单实例
环境:python3.7
日期:2019/9/9 21:15
作者:指尖魔法师
版本:1.0
"""
from pymysql import connect
class JD(object):
def __init__(self):
self.conn = connect(host='localhost', port=3306, user='root',
password='123456', database='jingdong', charset='utf8')
self.cr = self.conn.cursor()
def __del__(self):
self.cr.close()
self.conn.close()
def execute_sql(self, sql):
"""执行查询语句"""
self.cr.execute(sql)
result = self.cr.fetchall()
for temp in result:
print(temp)
def show_all_items(self):
"""显示所有商品"""
sql = 'select * from items;'
self.execute_sql(sql)
def show_cates(self):
"""显示所有分类"""
sql = 'select * from cates;'
self.execute_sql(sql)
def show_brands(self):
"""显示所有品牌"""
sql = 'select * from brands;'
self.execute_sql(sql)
def add_item(self):
"""新增商品"""
itemname = input("请输入商品名称:")
cateid = int(input("请输入类别编号:"))
brandid = int(input("请输入品牌编号:"))
parms = [itemname, cateid, brandid]
sql = 'insert into items values(0,%s,%s,%s);'
self.cr.execute(sql, parms)
self.conn.commit()
def del_item(self):
"""删除商品"""
itemid = int(input("请输入需要删除的商品编号:"))
sql = 'delete from items where id = %s;'
parms = [itemid]
self.cr.execute(sql, parms)
self.conn.commit()
def login(self):
"""登录判断"""
ret = False
while True:
user = input("请输入账号或'q'退出:")
if user == 'q':
return False
psw = input("请输入密码:")
parms = [user]+[psw]
sql = 'select * from admin where username=%s and password=%s;'
self.cr.execute(sql, parms)
ret = self.cr.fetchone()
if ret:
return True
else:
print("账号或者密码输入错误,请重新输入!")
def print_sel(self):
"""打印选择列表"""
print("---------京东商城产品管理系统--------")
print("1.显示所有商品")
print("2.显示所有分类")
print("3.显示所有品牌")
print("4.新增商品")
print("5.删除商品")
print("6.退出")
return input("请选择:")
def run(self):
if self.login():
while True:
num = self.print_sel()
if num == '1':
self.show_all_items()
elif num == '2':
self.show_cates()
elif num == '3':
self.show_brands()
elif num == '4':
self.add_item()
elif num == '5':
self.del_item()
elif num == '6':
exit()
else:
print("输入错误,请重新选择!")
def main():
jd = JD()
jd.run()
if __name__ == '__main__':
main()
表结构创建语句
create database jingdong charset=utf8;
use jingdong;
create table admin(
id int unsigned auto_increment not null primary key,
username varchar(30) not null,
password varchar(30) not null
);
insert into admin values(0,'admin','admin'),(0,'888','888');
create table items(
id int unsigned auto_increment not null primary key,
itemname varchar(30) not null,
cate int unsigned not null,
brand int unsigned not null
);
insert into items values(0,'神州1号',1,3),(0,'神州2号',2,2),(0,'神州3号',1,4),(0,'神州4号',2,3),(0,'神州5号',3,1);
create table cates(
id int unsigned auto_increment not null primary key,
catename varchar(30) not null
);
insert into cates values(0,'电脑'),(0,'手机'),(0,'相机'),(0,'电风扇');
create table brands(
id int unsigned auto_increment not null primary key,
brandname varchar(30) not null
);
insert into brands values(0,'苹果'),(0,'魅族'),(0,'诺基亚'),(0,'明基');