pymysql使用案例 Python 销售业务处理 商品信息管理系统

如何使用pymysql和pandas等库，用python实现MySQL数据库操作？

import pandas as pd

import pymysql




def sql_select(sql):

    try:

        with mysql_conn.cursor() as cursor:

            cursor.execute(sql)

            return cursor.fetchall()

    except Exception as e:

        print(e)




def sql_update(sql):

    cursor = mysql_conn.cursor()

    cursor.execute(sql)

    mysql_conn.commit()




def show_stuff():

    column_name = sql_select(

        "select column_name from information_schema.columns where table_name = 'store'")

    stuff = pd.DataFrame(sql_select("select * from store"))

    stuff.columns = column_name

    return stuff




def sell_dealing(function):

    stuff = show_stuff()

    print(stuff)

    stuff_id = input("请输入要修改库存的商品id：")

    stuff_amount = input("请输入新的商品库存：")

    sql_update("update store set amount=\"" + stuff_amount + "\" where id=\"" + stuff_id + "\"")

    stuff = show_stuff()

    print(stuff)

    if function == '1':

        usertype1()

    elif function == '2':

        usertype2()




def query(function):

    print("选择要操作的功能：")

    print("1. 按持有人名称查询")

    print("2. 按持有人名称和身份证查询")

    func = input()

    if func == '1':

        username = input("请输入持有人名称：")

        print(sql_select("select id,username,money from card where username=\"" + username + "\""))

        if function == '1':

            usertype1()

        elif function == '2':

            usertype2()

    elif func == '2':

        username = input("请输入持有人名称：")

        id_card = input("请输入持卡人身份证号：")

        print(sql_select(

            "select id,username,money from card where username=\"" + username + "\" and id_card=\"" + id_card + "\""))

        if function == '1':

            usertype1()

        elif function == '2':

            usertype2()

    else:

        query(function)




def usertype1():

    print("选择要操作的功能：")

    print("1. 销售业务处理")

    print("2. 查询")

    function = input()

    if function == '1':

        sell_dealing('1')

    elif function == '2':

        query('1')

    else:

        usertype1()




def show_user():

    column_name = sql_select(

        "select column_name from information_schema.columns where table_name = 'user'")

    users = pd.DataFrame(sql_select("select * from user"))

    users.columns = column_name[-4:]

    return users




def add_user():

    username = input("请输入用户名：")

    password = input("请输入密码：")

    id_ = input("请输入用户ID：")

    level = input("请输入级别，1-普通用户；2-高级用户：")

    if level in ['1', '2']:

        sql_update(

            "insert into user values (\"" + username + "\",\"" + id_ + "\",\"" + password + "\",\"" + level + "\")")

        users = show_user()

        print(users)

        if level == '1':

            with open("user.dat", "a") as a:

                a.write(username + " " + password + " " + id_ + " " + level + "\n")

    else:

        add_user()

    usertype2()




def delete_user():

    id_ = input("请输入要删除的用户ID：")

    sql_update("delete from user where id=\"" + id_ + "\"")

    users = show_user()

    print(users)

    usertype2()




def usertype2():

    print("选择要操作的功能：")

    print("1. 销售业务处理")

    print("2. 查询")

    print("3. 添加用户")

    print("4. 删除用户")

    function = input()

    if function == '1':

        sell_dealing('2')

    elif function == '2':

        query('2')

    elif function == '3':

        add_user()

    elif function == '4':

        delete_user()

    else:

        usertype2()




def login():

    id_ = input("请输入用户ID：")

    password = input("请输入密码：")

    res = sql_select("select username from user where id=\"" + id_ + "\" and password=" + password)

    if len(res) != 0:

        user_type = sql_select("select level from user where id=\"" + id_ + "\"")

        print(user_type[0][0])

        if user_type[0][0] == 1:

            usertype1()

        elif user_type[0][0] == 2:

            usertype2()

    else:

        login()




if __name__ == '__main__':

    mysql_conn = pymysql.connect(host='localhost', port=3306, user='root', password='password',

                                 db='db name')



    login()