Repository navigation
Expand file tree
/
Copy pathmysqlRoot.py
More file actions
executable file
·218 lines (186 loc) · 6.61 KB
/
Copy pathmysqlRoot.py
File metadata and controls
executable file
·218 lines (186 loc) · 6.61 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
from __future__ import unicode_literals
import pymysql
def creatConnect():
conn = pymysql.connect(host="localhost", user="root",
password="123456", db="RootDB", port=3306, charset="utf8")
cur = conn.cursor()
return (conn, cur)
def close(conn, cur):
cur.close()
conn.close()
def main():
try:
conn, cur = creatConnect()
create_users(conn)
create_friends(conn)
create_history(conn)
conn.commit()
except:
conn = pymysql.connect(host="localhost", user="root",
password="123456", port=3306, charset="utf8")
cur = conn.cursor()
cur.execute('create database RootDB default character set utf8;')
conn.commit()
create_users(conn)
create_friends(conn)
create_history(conn)
conn.commit()
close(conn, cur)
def create_users(conn):
s = 'create table users(\
id int auto_increment primary key,\
rootname int,\
nickname varchar(20),\
pwd varchar(32),\
gender enum("男","女","保密"),\
tel varchar(11),\
address varchar(100),\
email varchar(30),\
birthday varchar(15),\
avatar text,\
style varchar(100),\
onlinestatus enum("在线","离线") default "离线",\
time timestamp default now(),\
last_online timestamp default now())character set utf8;'
cur = conn.cursor()
try:
cur.execute(s)
conn.commit()
print('users ok')
except:
print("create_users FILL")
conn.rollback()
def insert_users(rootname, nickname, pwd, gender, tel, address, email, birthday, avatar, style, onlinestatus):
conn, cur = creatConnect()
s = '''insert into users
(rootname, nickname, pwd, gender, tel, address, email, birthday, avatar, style, onlinestatus)
values("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")''' % (rootname, nickname, pwd, gender, tel, address, email, birthday, avatar, style, onlinestatus)
try:
cur.execute(s)
conn.commit()
print("insert_users is ok")
except:
print("insert_users FILL")
conn.rollback()
close(conn, cur)
def updatestatus_users(rootname, status):
conn, cur = creatConnect()
s = "update users set onlinestatus='%s', last_online=now() where rootname='%s'" % (status, rootname)
try:
cur.execute(s)
conn.commit()
print("update_users is ok")
except:
print("updatestatus_users FILL")
conn.rollback()
close(conn, cur)
def login(rootname, pwd):
conn, cur = creatConnect()
s = "select rootname,nickname,gender,tel,address,email,birthday,avatar,style,onlinestatus,last_online from users where rootname='%s' and pwd='%s'" % (rootname, pwd)
cur.execute(s)
data = cur.fetchone()
close(conn, cur)
return data
def select_users(rootname):
conn, cur = creatConnect()
s = "select rootname,nickname,gender,tel,address,email,birthday,avatar,style,onlinestatus from users where rootname='%s'" % rootname
cur.execute(s)
data = cur.fetchone()
close(conn, cur)
return data
def create_friends(conn):
s = '''create table friends(
id int auto_increment primary key,
rootname int not null,
f_rootname int not null)character set utf8;'''
cur = conn.cursor()
try:
cur.execute(s)
conn.commit()
print('create_friends is ok')
except:
print("create_friends FILL")
conn.rollback()
def insert_friends(rootname, f_rootname):
conn, cur = creatConnect()
s = 'insert into friends(rootname,f_rootname) values("%s","%s"),("%s","%s")' % (
rootname, f_rootname, f_rootname, rootname)
try:
cur.execute(s)
conn.commit()
print("insert_friends is ok")
except Exception as e:
print(e)
conn.rollback()
close(conn, cur)
def delete_friends(rootname, f_rootname):
conn, cur = creatConnect()
s = 'delete from friends where (rootname="%s" and f_rootname="%s") or (rootname="%s" and f_rootname="%s")' %(
rootname, f_rootname, f_rootname, rootname)
try:
cur.execute(s)
conn.commit()
print("delete_friends is ok")
except Exception as e:
print(e)
conn.rollback()
close(conn, cur)
def select_friends(rootname):
conn, cur = creatConnect()
s = "select users.nickname, friends.f_rootname, users.avatar from friends,users where friends.rootname='%s' and users.rootname=friends.f_rootname;" % rootname
cur.execute(s)
data = cur.fetchall()
close(conn, cur)
return data
def create_history(conn):
s = '''create table history(
id int auto_increment primary key,
rootname int not null,
f_rootname int not null,
record text,
time timestamp default now())character set utf8;'''
cur = conn.cursor()
try:
cur.execute(s)
conn.commit()
print('create_history ok')
except:
print("create_history FILL")
conn.rollback()
def insert_history(rootname, f_rootname, record):
conn, cur = creatConnect()
print("history:",rootname, f_rootname)
s = "insert into history(rootname, f_rootname, record) values('%s', '%s', '%s')" % (
rootname, f_rootname, record)
try:
cur.execute(s)
conn.commit()
print("insert_hist is ok")
except:
print("insert_history FILL")
conn.rollback()
close(conn, cur)
def select_history(rootname, f_rootname):
conn, cur = creatConnect()
s = "select record from history where ((rootname = '%s')and(f_rootname = '%s')) or (( f_rootname = '%s')and(rootname = '%s'))" % (
rootname, f_rootname, f_rootname, rootname)
cur.execute(s)
data = cur.fetchall()
close(conn, cur)
return data
def selectfive_history(rootname, f_rootname):
conn, cur = creatConnect()
s = "select record from history where (rootname = '%s' and f_rootname = '%s') or (rootname = '%s' and f_rootname = '%s') order by id desc limit 5;" % (
rootname, f_rootname, f_rootname, rootname)
cur.execute(s)
data = cur.fetchall()
close(conn, cur)
return data
def selectlast_online_history(last_online, rootname):
conn, cur = creatConnect()
print("mySQL>",last_online)
s = "select record from history where time > ('%s') and f_rootname = '%s' order by rootname;" % (last_online, rootname)
cur.execute(s)
data = cur.fetchall()
close(conn, cur)
return data