Python模拟购物商品系统涉及数据库

import pymysql

HOST = 'localhost'
PORT = 3306
USERNAME = 'root'
PASSWORD = '123'
DB_NAME = 'jdshop'
CHARSET = 'utf8'
conn = pymysql.Connect(
    # key=value
    host=HOST,
    port=PORT,
    user=USERNAME,
    password=PASSWORD,
    db=DB_NAME,
    charset=CHARSET
)
# 创建一个游标对象
cursor = conn.cursor()
'''
 查询shopMessage表格，以及取出全部数据
'''


def Sql_M():
    sql = 'select *from shopMessage'
    cursor.execute(sql)  # 执行查询命令
    shop_arr = cursor.fetchall()
    return shop_arr


'''
    查询shoppingcart(购物车)表格，以及取出全部数据
'''


def Sql_C():  # 购物车
    sql = 'select *from shoppingcart'
    cursor.execute(sql)  # 执行查询命令
    shop_arr = cursor.fetchall()
    return shop_arr


def addShop():
    '''先查看有几条记录,进而得到添加商品的序号'''
    shop_arr = Sql_M()
    flag=-1
    num = len(shop_arr) + 1  # 商品序号加一
    name = str(input('请输入商品名称：'))
    count = int(input('请输入增加数量：'))
    for  line in range(0,len(shop_arr)):
        if shop_arr[line][1]==name:
            flag = line#如果商品原来存在，则不执行插入操作，执行修改操作

    if flag==-1:
       price = int(input('请输入商品的价格：'))
       sql = 'INSERT INTO shopMessage(Num,Name,Count,Price) VALUES(%s,"%s",%d,%d)' % (num, name, count, price)
    else:
       count=count+shop_arr[flag][2]
       sql='UPDATE shopMessage SET Count= %d WHERE Name="%s"'% (count,name)
       print(name+"的数量已经增加...")
    cursor.execute(sql)  # 执行
    conn.commit()  # 提交数据
    print('添加成功...')

'''
    OK了
    1.sql语句，有字符串的时候：用"%s"
    2.是values不是value
    懂了吗？
'''
def showShop():
    # 先取出数据，再进行遍历
    shop_arr = Sql_M()
    print('商品序号\t名称\t\t\t数量\t价 格\t')
    for i in range(0, len(shop_arr)):
        print("%s\t\t\t%s\t\t\t%d\t\t%d元" % (shop_arr[i][0], shop_arr[i][1], shop_arr[i][2], shop_arr[i][3]))


def searchShop():
    num = str(input('请输入商品的编号: '))
    # 依据商品号进行查询
    sql = 'select *from shopMessage WHERE NUM="%s"' % num
    cursor.execute(sql)
    # 把查询到的数据全部遍历出来，---只有一条
    shop_arr = cursor.fetchall()
    print('商品序号\t名称\t\t\t数量\t价 格\t')
    for i in range(0, len(shop_arr)):
        print("%s\t\t\t%s\t\t\t%d\t\t%d元" % (shop_arr[i][0], shop_arr[i][1], shop_arr[i][2], shop_arr[i][3]))
        return shop_arr  # 方便增加购物车使用


def addTocart():
    # 获取购物车的数据后，为其商品号+1
    cart_ar = Sql_C()
    num = len(cart_ar) + 1
    # 查询商品是否存在
    shop_arr = searchShop()
    flag = -1
    name = shop_arr[0][1]
    count = int(input('请输入添加数量：'))
    price = shop_arr[0][3]
    for  line in range(0,len(cart_ar)):
        if cart_ar[line][1]==name:
            flag = line#如果商品原来存在，则不执行插入操作，执行修改操作
    if flag==-1:
        sql = 'INSERT INTO shoppingcart(Num,Name,Count,Price) VALUES (%s,"%s",%d,%d)' % (num, name, count, price)
    elif shop_arr[0][2]-count>=0:
       sql='UPDATE shoppingcart SET Count= %d WHERE Name="%s"'% (count+cart_ar[flag][2],name)
    else:
        print("数量不够啦...只有%s"%shop_arr[0][2]+'了')
        return 0
    sql_shop='UPDATE shopMessage SET Count= %d WHERE Name="%s"'% (shop_arr[0][2]-count,name)#更新商品数量
    cursor.execute(sql)
    cursor.execute(sql_shop)
    conn.commit()
    print('已加入购物车')


def showCart():  # 显示购物车
    cart_arr = Sql_C()
    print('商品序号\t名称\t\t\t数量\t价 格\t')
    for i in range(0, len(cart_arr)):
        print("%s\t\t\t%s\t\t\t%d\t\t%d元" % (cart_arr[i][0], cart_arr[i][1], cart_arr[i][2], cart_arr[i][3]))


def print_menu():
    print('1.添加商品')
    print('2.查看所有商品')
    print('3.查看指定编号商品')
    print('4.添加到购物车')
    print('5.显示购物车')
    print('6.退出')
    fuction = int(input("请输入您要选择的功能："))
    return fuction


frag = True
while (frag == True):
    fuction = print_menu()
    if (fuction == 1):
        addShop()  # 添加商品
    elif (fuction == 2):
        showShop()
    elif (fuction == 3):
        searchShop()
    elif (fuction == 4):
        addTocart()
    elif (fuction == 5):
        showCart()
    elif (fuction == 6):
        frag = False