设备管理，连接两个数据库，用的easygui

# -*- coding:GBK -*-
import string

import pymysql as pys
import pymssql as pms
import easygui as eg
import datetime

ms_server = ''
ms_user = ''
ms_pass = ''
ms_db = ''

my_server = ''
my_user = ''
my_pass = ''
my_db = ''


def convert_to_tuple(strings):  # 这个函数用来把坑爹的choicebox生成的str转成tuple
    strings = strings.split(" ")
    s_str = []
    for i in strings:
        s_str.append((i.strip(string.punctuation)))
    t_str = tuple(s_str)
    return t_str


class MssqlClass(object):
    def __init__(self, conn):
        self.conn = conn

    def get_user_info(self, name):
        cursor = self.conn.cursor()
        try:
            cursor.execute('select EMAIL '
                           'from View_EASY_SYS_USER_HelpDesk '
                           'WHERE EMPLOYEE_ID=%s', name)
            info = cursor.fetchone()
            return info
        except Exception as e:
            print u'获取用户信息失败' + str(e)
        finally:
            cursor.close()


class MysqlClass(object):
    def __init__(self, conn):
        self.conn = conn

    def get_stock_equip(self):
        cursor = self.conn.cursor()
        try:
            cursor.execute('select eq_id,eq_type,eq_instance from status where status="in"')
            rs = cursor.fetchall()
            return rs
        except Exception as e:
            print u'获取在库设备列表失败' + str(e)
        finally:
            cursor.close()

    def get_users_equip(self, name):
        cursor = self.conn.cursor()
        try:
            cursor.execute('select eq_id,eq_type,eq_instance from status where ac_user=\'%s\' AND status=\'out\'' % name)
            rs = cursor.fetchall()
            if cursor.rowcount < 1:
                return None
            else:
                return rs
        except Exception as e:
            print u'获取用户借用列表失败' + str(e)
        finally:
            cursor.close()

    def insert_data(self, name, equip, status):
        cursor = self.conn.cursor()
        try:
            cur_date = str(datetime.date.today())
            other_infomation = (name, status, cur_date)
            info = equip + other_infomation
            info = str(info)
            cursor.execute('insert into record (eq_id,eq_type,eq_instance,ac_user,status,ac_date) values ' + info)
        except Exception as e:
            print u'插入信息失败' + str(e)
        finally:
            cursor.close()

    def update_data(self, name, status, eq_id):
        cursor = self.conn.cursor()
        try:
            cur_date = datetime.date.today()
            cursor.execute('update status set ac_user=\'%s\',status=\'%s\',ac_date=\'%s\' where eq_id=\'%s\'' % (name, status, cur_date, eq_id))
        except Exception as e:
            print u'更新信息失败' +str(e)
        finally:
            cursor.close()


def action():
    user_name = eg.enterbox(msg=u'请输入域账户名:', title=u'请输入')
    if user_name == '':
        eg.msgbox(u'错误，请输入域账户名称!')
        return False
    elif user_name == None:
        return 2
    # 获取用户信息
    msconn = pms.connect(ms_server, ms_user, ms_pass, ms_db)
    ms = MssqlClass(msconn)
    user_info = ms.get_user_info(user_name)
    msconn.close()
    if user_info == None:
        eg.msgbox(u'域账号不正确或没有创建')
        return False
    # 确认用户信息

    # 请用户选择操作

    selection = eg.buttonbox(msg=u'请选择您的操作', title=u'请选择操作',
                                 choices=(u'借用', u'归还'))

    if selection == u'借用':
        myconn = pys.connect(my_server, my_user, my_pass, my_db)
        try:
            my = MysqlClass(myconn)
            stock_equip = my.get_stock_equip()
            borrow_equip = eg.choicebox(msg=u'请选择要借用的设备', title=u'借用列表', choices=stock_equip)
            if borrow_equip == None:
                eg.msgbox(u'您取消了操作')
                # myconn.close()
                return False
            else:
                borrow_equip = convert_to_tuple(borrow_equip)
                eq_id = borrow_equip[0]
                my.insert_data(user_name, borrow_equip, status='out')
                my.update_data(user_name, status='out', eq_id=eq_id)
                myconn.commit()
                eg.msgbox(msg=u'%s 借用 %s 设备，操作成功！' % (user_name, borrow_equip[2]))
        except Exception as e:
            eg.msgbox(str(e))
            myconn.rollback()
        finally:
            myconn.close()
        return True
    elif selection == u'归还':
        myconn = pys.connect(my_server, my_user, my_pass, my_db)
        my = MysqlClass(myconn)
        try:
            users_equit = my.get_users_equip(user_name)
            if users_equit == None:
                eg.msgbox(u'您没有借用设备！')
                # myconn.close()
                return False

            else:
                giveback_equit = eg.choicebox(msg=u'请选择要归还的设备', title=u'用户借用的设备', choices=users_equit)
                if giveback_equit == None:
                    eg.msgbox(u'用户取消')
                    # myconn.close()
                    return False
                else:
                    giveback_equit = convert_to_tuple(giveback_equit)
                    eq_id = giveback_equit[0]
                    my.insert_data(user_name, giveback_equit, status='in')
                    my.update_data(user_name, status='in', eq_id=eq_id)
                    myconn.commit()
                    eg.msgbox(msg=u'%s 归还 %s 设备，操作成功！' % (user_name, giveback_equit[2]))
        except Exception as e:
            eg.msgbox(str(e))
            myconn.rollback()
        finally:
            myconn.close()
        return True
    else:
        return False
while 1:
    res = action()
    if res == 2:
        break