1 #! /usr/bin/env python
2 # -*- coding: utf-8 -*-
3 import MySQLdb
4
5 class Database:
6 def __init__(self,user,passwd,db,host,port):
7 self.user=user
8 self.passwd=passwd
9 self.db=db
10 self.host=host
11 self.port=port
12 try:
13 self.conn=MySQLdb.connect(user=self.user,passwd=self.passwd,db=self.db,host=self.host,port=self.port)
14 self.cur=self.conn.cursor()
15 except MySQLdb.Error as e:
16 print(e)
17
18 def is_User_exists(self,username):
19 sql="select salary from employee where name='%s';" %username
20 try:
21 self.cur.execute(sql)
22 data=self.cur.fetchall()
23 if len(data)==0:
24 return False
25 else:
26 return True
27 except MySQLdb.Error as e:
28 print (e)
29
30 #查询数据
31 def opt_Select(self,username):
32 sql="select salary from employee where name='%s';" %username
33 try:
34 self.cur.execute(sql)
35 data=self.cur.fetchall()
36 for i in data:
37 print ('工资： %d') %(i[0])
38 print('查询数据')
39 except MySQLdb.Error as e:
40 print(e)
41
42 #插入数据
43 def opt_Insert(self,username,value):
44 sql='insert into employee(name,salary) values(\'%s\',%s);' %(username,value)
45 try:
46 self.cur.execute(sql)
47 self.conn.commit()
48 print ('插入数据')
49 except MySQLdb.Error as e:
50 print(e)
51
52 #修改员工工资
53 def opt_Update(self,username,value):
54 sql='update employee set salary=%s where name=\'%s\';' %(value,username)
55 try:
56 self.cur.execute(sql)
57 self.conn.commit()
58 print ('修改数据')
59 except MySQLdb.Error as e:
60 print(e)
61
62 #删除员工数据
63 def opt_Delete(self,username):
64 sql='delete from employee where name=\'%s\';' % username
65 try:
66 self.cur.execute(sql)
67 self.conn.commit()
68 print('删除数据')
69 except MySQLdb.Error as e:
70 print(e)
71
72 def close_Conn(self):
73 try:
74 self.cur.close()
75 self.conn.close()
76 except MySQLdb.Error as e:
77 print(e)
78
79
80 tips='''
81
82 1: 查询
83 2: 新增
84 3: 修改
85 4: 删除
86 5: 退出
87
88 请选择:
89 '''
90 options=[1,2,3,4,5]
91
92 if __name__=='__main__':
93 conn_mysql=Database('root','1','test','localhost',3306)
94 while True:
95 # 选择操作
96 try:
97 choose=input(tips)
98 except(EOFError,KeyboardInterrupt):
99 choose=5
100 if choose not in options:
101 continue
102 elif choose==1:
103 username = raw_input('输入要查询员工姓名: ')
104 if conn_mysql.is_User_exists(username) is False:
105 print ('员工不存在')
106 else:
107 conn_mysql.opt_Select(username)
108 elif choose==2:
109 username,value = raw_input('输入新增员工姓名,工资: ').split(',')
110 conn_mysql.opt_Insert(username,value)
111 elif choose==3:
112 username,value= raw_input('输入要更新员工的姓名,工资: ').split(',')
113 conn_mysql.opt_Update(username,value)
114 elif choose==4:
115 username= raw_input('输入要删除员工的姓名: ')
116 conn_mysql.opt_Delete(username)
117 elif choose==5:
118 print('退出程序......')
119 sys.exit()
