1 # 导入pymysql
2 import pymysql
3
4 class usermanager(object):
5 #构造器
6 def __init__(self, id , username , sex ):
7 self.id = id,
8 self.username = username,
9 self.sex = sex
10
11 #数据库连接参数
12 def sqlconn(self):
13 return pymysql.connect( host='127.0.0.1',#我的IP地址
14 port=3306, # 不是字符串不需要加引号。
15 user='root',
16 password='123456',
17 db='user',
18 charset='utf8',)
19
20 def print1(self):
21 print(str(self.id[0]))
22
23 # 查询所有
24 def selectall(self):
25 conn=self.sqlconn()
26 cursor=conn.cursor()
27 sql = 'select id,username,sex from userinfo'
28 cursor.execute(sql)
29 res=cursor.fetchall()
30 cursor.close()
31 conn.close()
32 return res
33
34 # 依据ID删除用户
35 def deletebynum(self):
36 conn=self.sqlconn()
37 cursor=conn.cursor()
38 sql = "delete from userinfo where id="+str(self.id[0])
39 print(sql)
40 cursor.execute(sql)
41 conn.commit()
42 cursor.close()
43 conn.close()
44
45 # 新增用户
46 def insertuser(self):
47 conn=self.sqlconn()
48 cursor=conn.cursor()
49 sql = "insert into userinfo(id,username,sex) values(%s,%s,%s)"
50 cursor.execute(sql,[self.id[0],self.username[0],self.sex[0]])
51 conn.commit()
52 cursor.close()
53 conn.close()
54
55 #更新用户
56 def update(self):
57 conn=self.sqlconn()
58 cursor=conn.cursor()
59 sql = "update userinfo set sex=%s,username=%s where id=%s"
60 cursor.execute(sql,[self.sex[0],self.username[0],self.id[0]])
61 conn.commit()
62 cursor.close()
63 conn.close()
64
65 #main函数
66 if __name__ == '__main__':
67 sql1=usermanager(1,'vlaj','男')
68 res=sql1.selectall()
69 for user in res:
70 print(user)
71
72 #sql1.print1()
73 #sql1.deletebynum()
74 #sql2=usermanager(19,"alik","男")
75 #sql2.insertuser()
76
77 sql2=usermanager(19,"alik1","女")
78 sql2.update()