Python 练习 管理

...

#coding=utf-8
import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = 'root',
    db = 'device',
    charset = 'utf8'
    )
cur = conn.cursor()

caozuo = int(input('请输入你要的操作 1：增加，2：修改，3：删除，4：查询————>'))

if caozuo == 1:
    _id = input('请输入要添加的设备id:')
    _name = input('请输入要添加的设备名称：')
    _user = input('请输入要添加的设备用户：')
    _room = input('请输入要添加的设备办公室：')

    sql_a = cur.execute("""insert into device
        values('%s','%s','%s','%s')"""%(_id,_name,_user,_room))
    print('添加的内容是：%s,%s,%s,%s'%(_id,_name,_user,_room))

elif caozuo == 2:
    _id = input('请输入要修改的设备id:')
    _name = input('请输入要修改的设备名称：')
    _user = input('请输入要修改的设备用户：')
    _room = input('请输入要修改的设备办公室：')

    _id_new = input('请输入要修改后的设备id:')
    _name_new = input('请输入要修改后的设备名称：')
    _user_new = input('请输入要修改后的设备用户：')
    _room_new = input('请输入要修改后的设备办公室：')

    sql_u_id = cur.execute("""update device set device_id = '%s'
        where device_id = '%s'
        """%(_id_new,_id))
    sql_u_name = cur.execute("""update device set device_name = '%s'
        where device_name = '%s'
        """%(_name_new,_name))
    sql_u_user = cur.execute("""update device set device_user = '%s'
        where device_user = '%s'
        """%(_user_new,_user))
    sql_u_room = cur.execute("""update device set device_room = '%s'
        where device_room = '%s'
        """%(_room_new,_room))

elif caozuo == 3:

    tiaojian = int(input('请输入要删除的条件 1：ID / 2：名称 / 3：用户 / 4：办公室'))
    if tiaojian in (1,2,3,4):
        sql_d = cur.execute("""delete from 
        where device_id = '%s' or device_name = '%s'
        or device_user = '%s' or device_room = '%s'
        """%(_id,_name,_user,_room))

elif caozuo == 4:
    caozuo_c = int(input("""请输入查询的操作 1：查询所有列 /2：查询ID / 3：查询名称 / 4：查询用户 /5: 查询办公室\n————>        
        """))
    if caozuo_c == 1:
        sql_s_all = cur.execute("""
        select * from device
        """)
        info = cur.fetchmany(sql_s_all)
        for i in info:
            print(i)
    elif caozuo_c == 2:
        _id = int(input('请输入用什么来查询设备id:1：(id 查 id) 2：（名称查id）3：（用户查id）4：(办公室查id)\n————>'))
        _id_lie = input('请输入要查询的条件:')
        if _id == 1:
            sql_s_id = cur.execute("""
            select device_id from device where device_id = '%s'"""%_id_lie)
        elif _id == 2:
            sql_s_id = cur.execute("""
            select device_id from device where device_name = '%s'"""%_id_lie)
        elif _id == 3:
            sql_s_id = cur.execute("""
            select device_id from device where device_user = '%s'"""%_id_lie)
        elif _id == 4:
            sql_s_id = cur.execute("""
            select device_id from device where device_room = '%s'"""%_id_lie)    
        # sql_s_id = cur.execute("""
        # select device_id from device where device_id = '%s'
        # or device_name = '%s' or device_user = '%s' or device_room = '%s'
        # """%(_id,_name,_user,_room))
        info = cur.fetchmany(sql_s_id)
        for i in info:
            print(i)
    elif caozuo_c == 3:
        _name = int(input('请输入用什么来查询设备名称:1：(id 查 名称) 2：（名称查名称）3：（用户查名称）4：(办公室查名称)\n————>'))
        _name_lie = input('请输入要查询的条件:')
        if _name == 1:
            sql_s_name = cur.execute("""
            select device_name from device where device_id = '%s'"""%_name_lie)
        elif _iname == 2:
            sql_s_name = cur.execute("""
            select device_name from device where device_name = '%s'"""%_name_lie)
        elif _name == 3:
            sql_s_name = cur.execute("""
            select device_name from device where device_user = '%s'"""%_name_lie)
        elif _name == 4:
            sql_s_name = cur.execute("""
            select device_name from device where device_room = '%s'"""%_name_lie)
        # sql_s_name = cur.execute("""
        # select device_name from device where device_id = '%s'
        # or device_name = '%s' or device_user = '%s' or device_room = '%s'
        # """%(_id,_name,_user,_room))
        info = cur.fetchmany(sql_s_name)
        for i in info:
            print(i)    
    elif caozuo_c == 4:
        _user = int(input('请输入用什么来查询设备用户:1：(id 查 用户) 2：（名称查用户）3：（用户查用户）4：(办公室查用户)\n————>'))
        _user_lie = input('请输入要查询的条件:')
        if _user == 1:
            sql_s_user = cur.execute("""
            select device_user from device where device_id = '%s'"""%_user_lie)
        elif _user == 2:
            sql_s_user = cur.execute("""
            select device_user from device where device_name = '%s'"""%_user_lie)
        elif _user == 3:
            sql_s_user = cur.execute("""
            select device_user from device where device_user = '%s'"""%_user_lie)
        elif _user == 4:
            sql_s_user = cur.execute("""
            select device_user from device where device_room = '%s'"""%_user_lie)
        # sql_s_user = cur.execute("""
        # select device_user from device where device_id = '%s'
        # or device_name = '%s' or device_user = '%s' or device_room = '%s'
        # """%(_id,_name,_user,_room))
        info = cur.fetchmany(sql_s_user)
        for i in info:
            print(i)
    elif caozuo_c == 5:    
        _room = int(input('请输入用什么来查询设备用户:1：(id 查 办公室) 2：（名称查办公室）3：（用户查办公室）4：(办公室查办公室)\n————>'))
        _room_lie = input('请输入要查询的条件:')
        if _room == 1:
            sql_s_room = cur.execute("""
            select device_room from device where device_id = '%s'"""%_room_lie)
        elif _room == 2:
            sql_s_room = cur.execute("""
            select device_room from device where device_name = '%s'"""%_room_lie)
        elif _room == 3:
            sql_s_room = cur.execute("""
            select device_room from device where device_user = '%s'"""%_room_lie)
        elif _room == 4:
            sql_s_room = cur.execute("""
            select device_room from device where device_room = '%s'"""%_room_lie)
        # sql_s_room = cur.execute("""
        # select device_room from device where device_id = '%s'
        # or device_name = '%s' or device_user = '%s' or device_room = '%s'
        # """%(_id,_name,_user,_room))
        info = cur.fetchmany(sql_s_room)
        for i in info:
            print(i)
    
    
cur.close()
conn.commit()
conn.close()