数据库课程设计（期末实训答辩作品）

前言

​ 这次实训是我第一次参加实训课然后独立完成的作品，由于是第一次，我就本着会做就行的心态，然后潦草迅速的完成了这次实训，所以这个作品还是有缺陷的，比如大部分的数据都是手动填入，如果使用列表选择填入的方式会更加灵活。也不是很难，就是把列表的选项连接到数据库的一个表上，然后需要把组件的样式调整（这个过程比较繁琐）。总体代码不是很复杂，我把所有的源代码放在了文章里面，有兴趣的话就自己拿吧。

宠物医院诊疗管理系统

数据库基本表

主人：姓名，电话（主键），会员卡金额（默认为0）

科室：科室id（主键），科室名称

宠物：宠物id（主键），宠物名字、种类、性别（男，女）、品种，电话（外键）

医生：医生id（主键），医生姓名，职称，科室id，电话，入职状态（就职，离职）

诊疗服务：诊疗服务id（主键），药物信息，服务信息，医疗单价，诊疗时间

诊疗记录：（医生id，宠物id，诊疗服务id）外键

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
-- 创建数据库
CREATE DATABASE pet_clinic;
USE pet_clinic;

-- 创建主人表
CREATE TABLE owner (
    name VARCHAR(20),
    phone VARCHAR(11) PRIMARY KEY,
    membership_card_balance DECIMAL(10, 2) DEFAULT 0
);

-- 创建科室表
CREATE TABLE department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(20)
);

-- 创建宠物表
CREATE TABLE pet (
    pet_id INT AUTO_INCREMENT PRIMARY KEY,
    pet_name VARCHAR(20),
    species VARCHAR(20),
    gender VARCHAR(10) CHECK (gender IN ('雄', '雌')),
    breed VARCHAR(20),
    owner_phone VARCHAR(11),
    CONSTRAINT fk_owner FOREIGN KEY (owner_phone) REFERENCES owner(phone)
);

-- 创建医生表
CREATE TABLE doctor (
    doctor_id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_name VARCHAR(20),
    title VARCHAR(20),
    department_id INT,
    phone VARCHAR(11),
    employment_status VARCHAR(10) DEFAULT '就职' CHECK (employment_status IN ('就职', '离职')),
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- 创建诊疗服务表
CREATE TABLE medical_service (
    medical_service_id  INT AUTO_INCREMENT PRIMARY KEY,
    drug_information TEXT,
    service_information TEXT,
    medical_service_time DATETIME,
    unit_price DECIMAL(10, 2) DEFAULT 0
);

-- 创建诊疗记录表
CREATE TABLE medical_record (
    doctor_id INT,
    pet_id INT,
    medical_service_id INT AUTO_INCREMENT,
    CONSTRAINT fk_doctor FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id),
    CONSTRAINT fk_pet FOREIGN KEY (pet_id) REFERENCES pet(pet_id),
    CONSTRAINT fk_medical_service FOREIGN KEY (medical_service_id) REFERENCES medical_service(medical_service_id)
);

前端功能设计

1.密码登录

2.录入诊疗信息：姓名，电话，宠物名称，种类，性别，品种，医生id，药物信息，服务信息，单价

3.入职系统：医生姓名，医生职称，科室id，电话号码

4.会员充值系统

5.医生信息修改系统

6.查询宠物信息、某人的宠物、诊疗信息，某人宠物的诊疗信息，医生信息，某医生的信息，所有在岗医生的信息

功能需要的sql语句

诊疗开单需要:

1.在owner录入主人信息（姓名，电话）

2.询owner中的电话

3.在pet录入宠物信息（宠物名称，品种，性别，）

4.pet中查询所有宠物名称，品种，性别

5.在medical_service录入医生id，药品信息，服务信息，服务时间，诊疗单价

入职系统需要：

1.在doctor录入医生姓名，电话，职称，入职科室

2.查询doctor所有的医生的姓名，电话，职称，入职科室

会员充值系统需要：

1.（存储过程）根据电话号码，查询owenr中的姓名，电话，会员金额

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 切换分隔符
DELIMITER //

-- 创建存储过程
CREATE PROCEDURE UpdateMembershipBalance(
    IN p_phone VARCHAR(11),
    IN p_new_balance DECIMAL(10, 2)
)
BEGIN
    UPDATE owner
    SET membership_card_balance = p_new_balance
    WHERE phone = p_phone;

END //

-- 切换回默认分隔符
DELIMITER ;

2.根据给出的电话号码，修改会员金额

医生信息修改信息系统：

1.根据医生id查询指定医生姓名，职称，科室id，电话，入职状态（就职，离职）

2.根据医生id修改指定医生的医生姓名，职称，科室id，电话，入职状态（就职，离职）

查询系统需要：

1.（视图）查询所有宠物的主人姓名，电话，宠物名称，种类，性别，品种，会员余额

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW pet_owner_info AS
SELECT 
    o.name AS owner_name,
    o.phone AS owner_phone,
    p.pet_name,
    p.species,
    p.gender,
    p.breed,
    o.membership_card_balance
FROM 
    owner o
JOIN 
    pet p ON o.phone = p.owner_phone;  -- 通过电话关联宠物和主人

2.查询指定电话的宠物所有的主人姓名，电话，宠物名称，种类，性别，品种

3.（视图）查询所有宠物诊疗信息包括：主人名字，电话，宠物名称，种类，医生，药物信息，服务信息，单价，诊疗时间。根据诊疗id排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE VIEW pet_treatment_info AS
SELECT 
    o.name AS owner_name,
    o.phone AS owner_phone,
    p.pet_name,
    p.species,
    d.doctor_name,
    ms.drug_information,
    ms.service_information,
    ms.unit_price,
    ms.medical_service_time
FROM 
    medical_record mr
JOIN 
    pet p ON mr.pet_id = p.pet_id
JOIN 
    owner o ON p.owner_phone = o.phone
JOIN 
    doctor d ON mr.doctor_id = d.doctor_id
JOIN 
    medical_service ms ON mr.medical_service_id = ms.medical_service_id
ORDER BY 
    mr.medical_service_id ;  -- 根据医疗记录ID降序排序

4.（视图）查询医生的所有信息，包括：医生id，医生名字，职称，科室号，科室名称，电话号码，入职状态

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW doctor_info AS
SELECT 
    d.doctor_id,
    d.doctor_name,
    d.title,
    d.department_id,
    dep.department_name,
    d.phone,
    d.employment_status
FROM 
    doctor d
JOIN 
    department dep ON d.department_id = dep.department_id;  -- 通过科室 ID 关联医生和科室

插入基础数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 插入科室数据（只保留前三个科室数据插入示例）
INSERT INTO department (department_id, department_name) VALUES
(1, '内科'),
(2, '外科'),
(3, '皮肤科');

-- 插入医生数据,可查也可不插
INSERT INTO doctor (doctor_name, title, department_id, phone, employment_status) VALUES
('张医生', '主治医师', 1, '13200000001', '就职'),
('李医生', '副主任医师', 2, '13200000002', '就职'),
('王医生', '主任医师', 3, '13200000003', '就职'),
('赵医生', '主治医师', 1, '13200000004', '就职'),
('孙医生', '副主任医师', 2, '13200000005', '就职'),
('刘医生', '主任医师', 3, '13200000006', '就职'),
('陈医生', '主治医师', 1, '13200000007', '就职'),
('杨医生', '副主任医师', 2, '13200000008', '就职'),
('黄医生', '主任医师', 3, '13200000009', '就职'),
('周医生', '主治医师', 1, '13200000010', '就职');

代码实现

LoginPage.py

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
from tkinter import *
from tkinter import messagebox
import mysql
from MianPgae import MianPage


# 主登录界面的设计
class LoginPage:
    def __init__(self, master):
        self.root = master
        self.root.geometry('300x150')
        self.root.title('管理人员登录页')
        self.page = Frame(self.root)
        self.page.pack()
        self.username = StringVar()
        self.password = StringVar()
        Label(self.page, text='账号: ').grid(row=1, column=1)
        Entry(self.page, textvariable=self.username).grid(row=1, column=2)
        Label(self.page, text='密码: ', ).grid(row=2, column=1, pady=18)
        Entry(self.page, show='*', textvariable=self.password).grid(row=2, column=2)
        Button(self.page, text='注册', command=self.register).grid(row=3, column=1, pady=4)
        Button(self.page, text='登录', command=self.login).grid(row=3, column=2)
        Button(self.page, text='退出', command=self.page.quit).grid(row=3, column=3)

    def register(self):
        self.page.destroy()
        add_login(self.root)

    def login(self):
        uname = self.username.get()
        pwd = self.password.get()
        flag, message = mysql.check_login(uname, pwd)
        if flag:
            self.page.pack_forget()
            if uname == 'root':
                mysql.rootstatus = True
            MianPage(self.root)
        else:
            messagebox.showwarning(title='警告', message=message)

# 注册界面的设计
class add_login:
    def __init__(self, master):
        self.page = master
        self.username = StringVar()
        self.password = StringVar()
        self.password_examine = StringVar()
        self.password_root = StringVar()
        self.page.title('注册账号')
        self.page.geometry('300x200')
        self.root = Frame(self.page)
        self.root.pack()
        Label(self.root, text='账   号: ').grid(row=1, column=1)
        Entry(self.root, textvariable=self.username).grid(row=1, column=2)
        Label(self.root, text='密   码: ').grid(row=2, column=1, pady=18)
        Entry(self.root, show='*', textvariable=self.password).grid(row=2, column=2)
        Label(self.root, text='确 认 密 码: ').grid(row=3, column=1)
        Entry(self.root, show='*', textvariable=self.password_examine).grid(row=3, column=2)
        Label(self.root, text='管理员密码: ').grid(row=4, column=1, pady=18)
        Entry(self.root, show='*', textvariable=self.password_root).grid(row=4, column=2)
        Button(self.root, text='返  回', command=self.login_page).grid(row=5, column=1)
        Button(self.root, text='注  册', command=self.examine).grid(row=5, column=2)

    # 返回主登录界面
    def login_page(self):
        self.root.pack_forget()
        LoginPage(self.page)

    def examine(self):
        self.uname = self.username.get()
        self.pwd = self.password.get()
        self.pwd_exm = self.password_examine.get()
        self.pwd_root = self.password_root.get()
        if len(self.uname) < 5:
            messagebox.showwarning(title='警告', message='账号不符合要求，请输入最少5位字符')
        elif mysql.check_usname(self.uname) == True:
            messagebox.showwarning(title='警告', message='账号已存在，请更改你的账号')
        elif len(self.pwd) < 8:
            messagebox.showwarning(title='警告', message='密码不符合要求，请输入最少8位字符')
        elif self.pwd != self.pwd_exm:
            messagebox.showwarning(title='警告', message='两次密码不相同，请重新输入')
        elif self.pwd_root != 'root':
            messagebox.showwarning(title='警告', message='管理员密码错误，无法注册账号')
        else:
            self.login()

    # 注册登录
    def login(self):
        mysql.add_admin_name_pwd(self.uname, self.pwd)
        self.root.pack_forget()
        MianPage(self.page)

# 主函数
if __name__ == '__main__':
    page = Tk()
    LoginPage(page)
    page.mainloop()

MianPage.py

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
from tkinter import *
from views import (Insert1Frame, Insert2Frame, Change1Frame, Change2Frame, Search1Frame,
                   Search2Frame, Search3Frame)
import mysql
import keyboard

# 主框架
class MianPage:
    def __init__(self, master):
        self.root = master
        self.root.title('宠物医院诊断管理系统')
        self.root.geometry('570x460')
        self.create_page()

    def create_page(self):
        self.insert_frame1 = Insert1Frame(self.root)
        self.insert_frame2 = Insert2Frame(self.root)
        self.change_frame1 = Change1Frame(self.root)
        self.change_frame2 = Change2Frame(self.root)
        self.search_frame1 = Search1Frame(self.root)
        self.search_frame2 = Search2Frame(self.root)
        self.search_frame3 = Search3Frame(self.root)

        menubar = Menu(self.root, tearoff=False)
        menubar.add_command(label='诊疗开单', command=self.show_insert1)
        if mysql.rootstatus == True:
            menubar.add_command(label='入职系统', command=self.show_insert2)

        datachange = Menu(menubar)
        datachange.add_command(label="会员充值入口系统", command=self.show_change1, accelerator="Ctrl + H")
        if mysql.rootstatus == True:
            datachange.add_command(label="医生信息修改系统", command=self.show_change2, accelerator="Ctrl + X")

        datasearch = Menu(menubar)
        datasearch.add_command(label="诊疗信息", command=self.show_search1, accelerator="Ctrl + Z")
        datasearch.add_command(label="宠物信息", command=self.show_search2, accelerator="Ctrl + C")
        datasearch.add_command(label="医生信息", command=self.show_search3, accelerator="Ctrl + Y")

        keyboard.add_hotkey('ctrl+h', self.show_change1)
        if mysql.rootstatus == True:
            keyboard.add_hotkey('ctrl+x', self.show_change2)

        keyboard.add_hotkey('ctrl+z', self.show_search1)
        keyboard.add_hotkey('ctrl+c', self.show_search2)
        keyboard.add_hotkey('ctrl+y', self.show_search3)

        menubar.add_cascade(label="修改管理", menu=datachange)
        menubar.add_cascade(label="查询系统", menu=datasearch)

        self.root.config(menu=menubar)

        def xShowMenu(event):
            menubar.post(event.x_root, event.y_root)

        self.root.bind("<Button-3>", xShowMenu)
        self.show_insert1()

    # 调用开单界面
    def show_insert1(self):
        self.root.geometry('570x460')
        self.insert_frame1.pack()
        self.insert_frame2.forget()
        self.change_frame1.forget()
        self.change_frame2.forget()
        self.search_frame1.forget()
        self.search_frame2.forget()
        self.search_frame3.forget()

    #进入入职界面
    def show_insert2(self):
        self.root.geometry('570x250')
        self.insert_frame1.forget()
        self.insert_frame2.pack()
        self.change_frame1.forget()
        self.change_frame2.forget()
        self.search_frame1.forget()
        self.search_frame2.forget()
        self.search_frame3.forget()

    # 调用会员充值界面
    def show_change1(self):
        self.root.geometry('570x290')
        self.insert_frame1.forget()
        self.insert_frame2.forget()
        self.change_frame1.pack()
        self.change_frame2.forget()
        self.search_frame1.forget()
        self.search_frame2.forget()
        self.search_frame3.forget()

    # 调用修改医生信息界面
    def show_change2(self):
        self.root.geometry('570x290')
        self.insert_frame1.forget()
        self.insert_frame2.forget()
        self.change_frame1.forget()
        self.change_frame2.pack()
        self.search_frame1.forget()
        self.search_frame2.forget()
        self.search_frame3.forget()

    #调用查询诊疗信息界面
    def show_search1(self):
        self.root.geometry('1460x290')
        self.insert_frame1.forget()
        self.insert_frame2.forget()
        self.change_frame1.forget()
        self.change_frame2.forget()
        self.search_frame1.pack()
        self.search_frame2.forget()
        self.search_frame3.forget()
    # 调用查询宠物信息界面
    def show_search2(self):
        self.root.geometry('570x290')
        self.insert_frame1.forget()
        self.insert_frame2.forget()
        self.change_frame1.forget()
        self.change_frame2.forget()
        self.search_frame1.forget()
        self.search_frame2.pack()
        self.search_frame3.forget()

    # 调用查询医生信息界面
    def show_search3(self):
        self.root.geometry('570x290')
        self.insert_frame1.forget()
        self.insert_frame2.forget()
        self.change_frame1.forget()
        self.change_frame2.forget()
        self.search_frame1.forget()
        self.search_frame3.pack()
        self.search_frame2.forget()

views.py

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
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
from tkinter import *
from tkinter import ttk
import mysql
from datetime import datetime

# 开单页面设计
class Insert1Frame(Frame):
    def __init__(self, root):
        super().__init__(root)
        self.phone = StringVar()
        self.owenr_name = StringVar()
        self.pet_name= StringVar()
        self.species = StringVar()
        self.gender= StringVar()
        self.breed = StringVar()
        self.doctor_id = StringVar()
        self.drug_information = StringVar()
        self.service_information = StringVar()
        self.unit_price = StringVar()
        self.status_insert = StringVar()
        self.status_insert1 = StringVar()
        self.insert_page()

    def insert_page(self):
        Label(self, text='电   话 : ').grid(row=1, column=1, pady=5)
        self.entry_phone = Entry(self, textvariable=self.phone)
        self.entry_phone.grid(row=1, column=2, pady=5)

        Label(self, text='姓   名 : ').grid(row=2, column=1, pady=5)
        self.entry_owenr_name = Entry(self, textvariable=self.owenr_name)
        self.entry_owenr_name.grid(row=2, column=2, pady=5)

        Label(self, text='宠物名称 : ').grid(row=3, column=1, pady=5)
        self.entry_pet_name = Entry(self, textvariable=self.pet_name)
        self.entry_pet_name.grid(row=3, column=2, pady=5)

        Label(self, text='种   类 : ').grid(row=4, column=1, pady=5)
        self.entry_species = Entry(self, textvariable=self.species)
        self.entry_species.grid(row=4, column=2, pady=5)

        Label(self, text='性   别 : ').grid(row=5, column=1, pady=5)
        self.entry_gender = Entry(self, textvariable=self.gender)
        self.entry_gender.grid(row=5, column=2, pady=5)

        Label(self, text='品   种 : ').grid(row=6, column=1, pady=5)
        self.entry_breed = Entry(self, textvariable=self.breed)
        self.entry_breed.grid(row=6, column=2, pady=5)

        Label(self, text='医生 id : ').grid(row=7, column=1, pady=5)
        self.entry_doctor_id = Entry(self, textvariable=self.doctor_id)
        self.entry_doctor_id.grid(row=7, column=2, pady=5)

        Label(self, text='药品信息 : ').grid(row=8, column=1, pady=5)
        self.entry_drug_information = Entry(self, textvariable=self.drug_information)
        self.entry_drug_information.grid(row=8, column=2, pady=5)

        Label(self, text='服务信息 : ').grid(row=9, column=1, pady=5)
        self.entry_service_information = Entry(self, textvariable=self.service_information)
        self.entry_service_information.grid(row=9, column=2, pady=5)

        Label(self, text='单价金额 : ').grid(row=10, column=1, pady=5)
        self.entry_unit_price = Entry(self, textvariable=self.unit_price)
        self.entry_unit_price.grid(row=10, column=2, pady=5)

        Button(self, text='清空', command=self.insert_deleteValue).grid(row=11, column=1, pady=10)
        Button(self, text='录入', command=self.insert_data).grid(row=11, column=3, pady=10)

        Label(self, textvariable=self.status_insert1).grid(row=12, column=2, padx=10)
        Label(self, textvariable=self.status_insert).grid(row=13, column=2, padx=10)

    def insert_data(self):
        if not self.phone.get():
            self.status_insert.set('电话不能为空')
            return
        else: self.insert_phone = self.phone.get()

        if not self.owenr_name.get():
            self.status_insert.set('姓名不能为空')
            return
        else: self.insert_owner_name = self.owenr_name.get()

        if not self.pet_name.get():
            self.status_insert.set('宠物名称不能为空')
            return
        else: self.insert_pet_name = self.pet_name.get()

        if not self.species.get():
            self.status_insert.set('种类不能为空')
            return
        else: self.insert_species = self.species.get()

        if not self.gender.get():
            self.status_insert.set('性别不能为空')
            return
        else:
            self.insert_gender = self.gender.get()

        if self.insert_gender not in ['雄', '雌']:
            self.status_insert.set('性别只能填雄或雌')
            return

        if not self.breed.get():
            self.insert_breed = 'NULL'
        else: self.insert_breed = self.breed.get()

        if not self.doctor_id.get():
            self.status_insert.set('医生id不能为空')
            return
        else:
            self.insert_doctor_id = int(self.doctor_id.get())

        if not self.drug_information.get():
            self.drug_information = 'NULL'
        else:
            self.insert_drug_information = self.drug_information.get()

        if not self.service_information.get():
            self.service_information = 'NULL'
        else:
            self.insert_service_information = self.service_information.get()

        if not self.unit_price.get():
            self.status_insert.set('单价金额不能为空')
            return
        else:
            self.insert_unit_price = float(self.unit_price.get())

        flag, s = mysql.check_doctor_id(self.insert_doctor_id)
        self.status_insert.set(s)
        if flag == False:
            return

        owner = (self.insert_owner_name, self.insert_phone)
        mysql.insert_owner(owner)

        tip, flag, mm = mysql.check_mumbership(self.insert_phone, self.insert_unit_price)
        self.status_insert1.set(tip)
        if flag == False:
            self.status_insert.set("交易停止")
            return

        pet = (self.insert_pet_name, self.insert_species, self.insert_gender, self.insert_breed, self.insert_phone)
        mysql.insert_pet(pet)

        current_time = datetime.now()
        formatted_time = current_time.strftime('%Y-%m-%d %H:%M:%S')
        service = (self.insert_drug_information, self.insert_service_information, formatted_time, mm)
        mysql.insert_service(service)

        record = (self.insert_pet_name, self.insert_species, self.insert_gender, self.insert_breed, self.insert_phone, self.insert_doctor_id)
        mysql.insert_record(record)
        self.status_insert.set("录入诊疗记录")
        self.insert_deleteValue()

    def insert_deleteValue(self):
        self.entry_phone.delete(0, END)
        self.entry_owenr_name.delete(0, END)
        self.entry_pet_name.delete(0, END)
        self.entry_species.delete(0, END)
        self.entry_gender.delete(0, END)
        self.entry_breed.delete(0, END)
        self.entry_doctor_id.delete(0, END)
        self.entry_drug_information.delete(0, END)
        self.entry_service_information.delete(0, END)
        self.entry_unit_price.delete(0, END)

# 入职页面设计
class Insert2Frame(Frame):
    def __init__(self, root):
        super().__init__(root)
        self.name = StringVar()
        self.title= StringVar()
        self.department_id = StringVar()
        self.phone = StringVar()
        self.status_insert = StringVar()
        self.insert_page()

    def insert_page(self):
        Label(self, text='姓   名 : ').grid(row=1, column=1, pady=5)
        self.entry_name = Entry(self, textvariable=self.name)
        self.entry_name.grid(row=1, column=2, pady=5)

        Label(self, text='电   话 : ').grid(row=2, column=1, pady=5)
        self.entry_phohe = Entry(self, textvariable=self.phone)
        self.entry_phohe.grid(row=2, column=2, pady=5)

        Label(self, text='职   称 : ').grid(row=3, column=1, pady=5)
        self.entry_title = Entry(self, textvariable=self.title)
        self.entry_title.grid(row=3, column=2, pady=5)

        Label(self, text='入职科室id: ').grid(row=4, column=1, pady=5)
        self.entry_department_id = Entry(self, textvariable=self.department_id)
        self.entry_department_id.grid(row=4, column=2, pady=5)

        Button(self, text='清空', command=self.insert_deleteValue).grid(row=5, column=1, pady=10)
        Button(self, text='录入', command=self.insert_data).grid(row=5, column=3, pady=10)

        Label(self, textvariable=self.status_insert).grid(row=8, column=2, padx=10)

    def insert_data(self):
        if not self.name.get():
            self.status_insert.set("姓名不能为空")
            return
        else: self.insert_name = self.name.get()

        if not self.phone.get():
            self.status_insert.set("电话不能为空")
            return
        else: self.insert_phone = self.phone.get()

        if not self.title.get():
            self.status_insert.set("职称不能为空")
            return
        else: self.insert_title = self.title.get()

        if not self.department_id.get():
            self.status_insert.set("科室id不能为空")
            return
        else: self.insert_department_id = int(self.department_id.get())

        flag1, s1 = mysql.check_department_id(self.insert_department_id)
        self.status_insert.set(s1)
        if flag1 == False:
            return
        flag, s = mysql.check_doctor(self.insert_name, self.insert_phone, self.insert_title, self.insert_department_id)
        self.status_insert.set(s)
        if flag == False:
            return
        mysql.insert_doctor(self.insert_name, self.insert_phone, self.insert_title, self.insert_department_id)
        self.status_insert.set('录入成功')
        self.insert_deleteValue()

    def insert_deleteValue(self):
        self.entry_phohe.delete(0, END)
        self.entry_name.delete(0, END)
        self.entry_title.delete(0, END)
        self.entry_department_id.delete(0, END)

# 会员充值界面设计
class Change1Frame(Frame):
    def __init__(self, root):
        super().__init__(root, width=570, height=290)
        self.change_phone = StringVar()
        self.status = StringVar()
        self.status_name = StringVar()
        self.change_money = StringVar()
        self.name = StringVar()
        self.phone = StringVar()
        self.money_change_before = StringVar()
        self.insert_page()

    def insert_page(self):
        Label(self, text='请输入需要查询客户的').place(x=40, y=60)
        Label(self, text='电话号码').place(x=64, y=80)
        Entry(self, textvariable=self.change_phone).place(x=30, y=100)
        Button(self, text='按电话查询', command=self.money_change).place(x=30, y=130)
        Button(self, text='清除会员余额', command=self.delete).place(x=110, y=130)
        Label(self, textvariable=self.status).place(x=45, y=160)

        Label(self, text='姓    名 : ').place(x=240, y=40)
        Label(self, textvariable=self.name).place(x=300, y=40)

        Label(self, text='电    话 : ').place(x=240, y=70)
        Label(self, textvariable=self.phone).place(x=300, y=70)

        Label(self, text='会员余额 : ').place(x=240, y=100)
        Label(self, textvariable=self.money_change_before).place(x=300, y=100)
        self.entry_change_money = Entry(self, textvariable=self.change_money)
        self.entry_change_money.place(x=380, y=100)

        Button(self, text='充    值', command=self.add_money).place(x=420, y=130)
        Label(self, textvariable=self.status_name).place(x=405, y=160)

    def money_change(self):
        if self.change_phone.get():
            self.search_phone = self.change_phone.get()
            flag, owner = mysql.search_phone(self.search_phone)
            if flag:
                self.change_Information(owner)
                self.status.set('数据查询成功')
            else:
                self.status.set(owner)
        else:
            self.status.set('请输入需要查询的电话')

    def change_Information(self, owner):
        self.name.set(owner[0][0])
        self.phone.set(owner[0][1])
        self.money_change_before.set(owner[0][2])

    def add_money(self):
        if not self.change_money.get():
            self.status_name.set('请输入充值的余额')
            return
        else:
            self.insert_money = float(self.change_money.get())

        mysql.change_mumbership(self.phone.get(), self.insert_money)
        self.status_name.set('充值成功')

    def delete(self):
        if self.phone.get():
            mysql.delete_mumbership(self.phone.get())
            self.status.set('清除成功')
        else:
            self.status.set('请先查寻有效用户')

# 医生信息修改界面
class Change2Frame(Frame):
    def __init__(self, root):
        super().__init__(root, width=570, height=290)
        self.change_doctor_id = StringVar()
        self.status = StringVar()
        self.status_name = StringVar()
        self.phone = StringVar()
        self.doctor_name = StringVar()
        self.title = StringVar()
        self.department_id = StringVar()
        self.employment_status = StringVar()
        self.doctor_name_change_before = StringVar()
        self.title_change_before = StringVar()
        self.department_id_change_before = StringVar()
        self.phone_change_before = StringVar()
        self.employment_status_change_before = StringVar()
        self.insert_page()

    def insert_page(self):
        Label(self, text='请输入需要查询医生的id').place(x=40, y=60)
        Entry(self, textvariable=self.change_doctor_id).place(x=30, y=100)
        Button(self, text='按医生id查询', command=self.select_dotcor).place(x=70, y=130)
        Label(self, textvariable=self.status).place(x=45, y=160)

        Label(self, text='姓   名 : ').place(x=240, y=20)
        Label(self, textvariable=self.doctor_name_change_before).place(x=300, y=20)
        self.entry_doctor_name = Entry(self, textvariable=self.doctor_name)
        self.entry_doctor_name.place(x=380, y=20)

        Label(self, text='职   称 : ').place(x=240, y=50)
        Label(self, textvariable=self.title_change_before).place(x=300, y=50)
        self.entry_title = Entry(self, textvariable=self.title)
        self.entry_title.place(x=380, y=50)

        Label(self, text='科室id : ').place(x=240, y=80)
        Label(self, textvariable=self.department_id_change_before).place(x=300, y=80)
        self.entry_department_id = Entry(self, textvariable=self.department_id)
        self.entry_department_id.place(x=380, y=80)

        Label(self, text='电   话 : ').place(x=240, y=110)
        Label(self, textvariable=self.phone_change_before).place(x=300, y=110)
        self.entry_phone = Entry(self, textvariable=self.phone)
        self.entry_phone.place(x=380, y=110)

        Label(self, text='入职状态 : ').place(x=240, y=140)
        Label(self, textvariable=self.employment_status_change_before).place(x=300, y=140)
        self.entry_employment_status = Entry(self, textvariable=self.employment_status)
        self.entry_employment_status.place(x=380, y=140)

        Button(self, text='保    存', command=self.update_information).place(x=320, y=190)
        Label(self, textvariable=self.status_name).place(x=305, y=220)

    def select_dotcor(self):
        if self.change_doctor_id.get():
            self.search_doctor = int(self.change_doctor_id.get())
            flag, doctor = mysql.search_doctor(self.search_doctor)
            if flag:
                self.change_Information(doctor)
                self.status.set('数据查询成功')
            else:
                self.status.set(doctor)
        else:
            self.status.set('请输入需要查询的医生id')

    def change_Information(self, doctor):
        self.doctor_name.set(doctor[0][0])
        self.title.set(doctor[0][1])
        self.department_id.set(doctor[0][2])
        self.phone.set(doctor[0][3])
        self.employment_status.set(doctor[0][4])
        self.doctor_name_change_before.set(doctor[0][0])
        self.title_change_before.set(doctor[0][1])
        self.department_id_change_before.set(doctor[0][2])
        self.phone_change_before.set(doctor[0][3])
        self.employment_status_change_before.set(doctor[0][4])

    def update_information(self):
        if not self.doctor_name.get():
            self.status_name.set('姓名不能为空')
            return
        else:
            self.insert_doctor_name = self.doctor_name.get()

        if not self.title.get():
            self.status_name.set('职称不能为空')
            return
        else:
            self.insert_title = self.title.get()

        if not self.department_id.get():
            self.status_name.set('科室id不能为空')
            return
        else:
            self.insert_department_id = int(self.department_id.get())

        if not self.phone.get():
            self.status_name.set('电话不能为空')
            return
        else:
            self.insert_phone = self.phone.get()

        if not self.employment_status.get():
            self.status_name.set('入职状态不能为空')
            return
        else:
            self.insert_employment_status = self.employment_status.get()

        if self.insert_employment_status not in ['就职', '离职']:
            self.status_name.set('入职状态只能填就职或离职')
            return

        flag1, s1 = mysql.check_department_id(self.insert_department_id)
        self.status_name.set(s1)
        if flag1 == False:
            return
        mysql.update_doctor(self.insert_doctor_name, self.insert_title, self.insert_department_id, self.insert_phone, self.insert_employment_status, int(self.change_doctor_id.get()))
        self.status_name.set('修改成功')
        self.delete_value()

    def delete_value(self):
        self.entry_doctor_name.delete(0, END)
        self.entry_title.delete(0, END)
        self.entry_department_id.delete(0, END)
        self.entry_phone.delete(0, END)
        self.entry_employment_status.delete(0, END)

# 查询诊疗信息界面设计
class Search1Frame(Frame):
    def __init__(self, root):
        super().__init__(root)
        self.table_search_view = Frame()
        self.show_table_search()

    def show_table_search(self):
        columns = ("name", "phone", "pname", "species", "doctor", "drug", "service", "price", "time")
        columns_values = ("主人名称", "电话号码", "宠物名称", "种类", "医生", "药物信息", "服务信息", "本次付费", "诊疗时间")
        self.tree_view = ttk.Treeview(self, show='headings', columns=columns)

        for col in columns:
            self.tree_view.column(col, width=160, anchor='center')

        for col, colvalue in zip(columns, columns_values):
            self.tree_view.heading(col, text=colvalue)

        self.tree_view.pack(fill=BOTH, expand=True)
        self.show_search_data()

        self.owner_phone = StringVar()
        Entry(self, textvariable=self.owner_phone).pack(side=LEFT)
        Button(self, text='根据电话查询', command=self.search_phone).pack(side=LEFT)

    def search_phone(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        if not self.owner_phone.get():
            self.show_search_data()
            return
        else:
            self.phone_value = self.owner_phone.get()
            data = mysql.search_owner_phone(self.phone_value)

        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['name'], i['phone'], i['pname'], i['species'],
                i['doctor'], i['drug'], i['service'], i['price'], i['time']
            ))

    def show_search_data(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        data = mysql.pet_information()
        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['name'], i['phone'], i['pname'], i['species'],
                i['doctor'], i['drug'], i['service'], i['price'], i['time']
            ))
# 查询宠物信息界面设计
class Search2Frame(Frame):
    def __init__(self, root):
        super().__init__(root)
        self.table_search_view = Frame()
        self.show_table_search()

    def show_table_search(self):
        columns = ("name", "phone", "pname", "species", "gender", "breed", "money")
        columns_values = ("主人名称", "电话号码", "宠物名称", "种类", "性别", "品种", "会员余额")
        self.tree_view = ttk.Treeview(self, show='headings', columns=columns)

        for col in columns:
            self.tree_view.column(col, width=80, anchor='center')

        for col, colvalue in zip(columns, columns_values):
            self.tree_view.heading(col, text=colvalue)

        self.tree_view.pack(fill=BOTH, expand=True)
        self.show_search_data()

        self.owner_phone = StringVar()
        Entry(self, textvariable=self.owner_phone).pack(side=LEFT)
        Button(self, text='根据电话查询', command=self.search_phone).pack(side=LEFT)

    def search_phone(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        if not self.owner_phone.get():
            self.show_search_data()
            return
        else:
            self.phone_value = self.owner_phone.get()
            data = mysql.search_owner_phone1(self.phone_value)

        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['name'], i['phone'], i['pname'], i['species'],
                i['gender'], i['breed'], i['money']
            ))

    def show_search_data(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        data = mysql.pet_information1()
        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['name'], i['phone'], i['pname'], i['species'],
                i['gender'], i['breed'], i['money']
            ))

# 查询医生信息界面设计
class Search3Frame(Frame):
    def __init__(self, root):
        super().__init__(root)
        self.table_search_view = Frame()
        self.show_table_search()

    def show_table_search(self):
        columns = ("id1", "name", "title", "id2", "k_name", "phone", "status")
        columns_values = ("医生id", "医生名称", "职称", "科室id", "科室名称", "电话号码", "入职状态")
        self.tree_view = ttk.Treeview(self, show='headings', columns=columns)

        for col in columns:
            self.tree_view.column(col, width=80, anchor='center')

        for col, colvalue in zip(columns, columns_values):
            self.tree_view.heading(col, text=colvalue)

        self.tree_view.pack(fill=BOTH, expand=True)
        self.show_search_data()

        self.owner_phone = StringVar()
        Entry(self, textvariable=self.owner_phone).pack(side=LEFT)
        Button(self, text='根据电话查询', command=self.search_phone).pack(side=LEFT)
        Button(self, text='在岗', command=self.show_search_data1).pack(side=RIGHT)

    def search_phone(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        if not self.owner_phone.get():
            self.show_search_data()
            return
        else:
            self.phone_value = self.owner_phone.get()
            data = mysql.search_doctor_phone(self.phone_value)

        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['id1'], i['name'], i['title'], i['id2'],
                i['k_name'], i['phone'], i['status']
            ))

    def show_search_data(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        data = mysql.doctor_information()
        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['id1'], i['name'], i['title'], i['id2'],
                i['k_name'], i['phone'], i['status']
            ))

    def show_search_data1(self):
        for _ in map(self.tree_view.delete, self.tree_view.get_children('')):
            pass
        data = mysql.search_doctor_status()
        index = -1
        for i in data:
            self.tree_view.insert('', index + 1, values=(
                i['id1'], i['name'], i['title'], i['id2'],
                i['k_name'], i['phone'], i['status']
            ))

mysql.py

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
219
220
221
222
223
224
225
226
import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    autocommit=True
)
# 检查是否为超级管理员
rootstatus = False

cursor = conn.cursor()

cursor.execute("create database if not exists pet_clinic;")
conn.select_db("pet_clinic")

# 创建管理员账号表
cursor.execute("""CREATE TABLE IF NOT EXISTS admin_name_pwd(
    name varchar(10),
    pwd varchar(10)
);""")

# 检查账号是否正确
def check_login(uname, pwd):
    cursor.execute("select * from admin_name_pwd")
    results = cursor.fetchall()
    for na, pd in results:
        if na == uname and pd == pwd:
            return True, '登录成功'
    return False, '登录失败,账号或密码错误'

# 注册一个账号后写入管理员账号表
def add_admin_name_pwd(uname, pwd):
    cursor.execute("insert into admin_name_pwd values('{0}', '{1}');".format(uname, pwd))

# 检查账号是否重复
def check_usname(uname):
    cursor.execute("select count(*) from admin_name_pwd anp where name = '{0}';".format(uname))
    res = cursor.fetchall()
    if res[0][0]:
        return True
    return False

# 查询诊疗记录视图
def pet_information():
    cursor.execute("select * from pet_treatment_info")
    data = cursor.fetchall()
    key = ("name", "phone", "pname", "species", "doctor", "drug", "service","price","time")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 根据电弧查询诊疗记录
def search_owner_phone(phone):
    cursor.execute("select * from pet_treatment_info where owner_phone = '{0}'".format(phone))
    data = cursor.fetchall()
    key = ("name", "phone", "pname", "species", "doctor", "drug", "service", "price", "time")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 查询宠物信息视图
def pet_information1():
    cursor.execute("select * from pet_owner_info")
    data = cursor.fetchall()
    key = ("name", "phone", "pname", "species", "gender", "breed", "money")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 根据电话查询宠物信息视图
def search_owner_phone1(phone):
    cursor.execute("select * from pet_owner_info where owner_phone = '{0}'".format(phone))
    data = cursor.fetchall()
    key = ("name", "phone", "pname", "species", "gender", "breed", "money")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList


# 查询医生信息视图
def doctor_information():
    cursor.execute("select * from doctor_info")
    data = cursor.fetchall()
    key = ("id1", "name", "title", "id2", "k_name", "phone", "status")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 根据电话查询医生信息视图
def search_doctor_phone(phone):
    cursor.execute("select * from doctor_info where phone = '{0}'".format(phone))
    data = cursor.fetchall()
    key = ("id1", "name", "title", "id2", "k_name", "phone", "status")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 在医生信息视图中查看就职的成员
def search_doctor_status():
    cursor.execute("select * from doctor_info where employment_status = '就职'")
    data = cursor.fetchall()
    key = ("id1", "name", "title", "id2", "k_name", "phone", "status")
    jsonList = []
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 检查医生信息是否重复
def check_doctor(name, phone, title, department_id):
    cursor.execute("select count(*) from doctor where doctor_name = '{0}' and phone = '{1}' and title = '{2}' and  department_id = '{3}';".format(name, phone, title, department_id))
    res = cursor.fetchall()
    if res[0][0]:
        return False, "该医生已存在请重新输入"
    return True, ''

# 检查科室id是否正确
def check_department_id(id):
    cursor.execute("select count(*) from doctor where department_id = '{0}';".format(id))
    res = cursor.fetchall()
    if res[0][0]:
        return True, ""
    return False, '录入失败，科室id输入错误'

# 检查医生id是否正确
def check_doctor_id(id):
    cursor.execute("select count(*) from doctor where doctor_id = '{0}';".format(id))
    res = cursor.fetchall()
    if res[0][0]:
        return True, ""
    return False, '录入失败，医生id输入错误'

# 检查用户的会员情况
def check_mumbership(phone, unit_price):
    cursor.execute("select membership_card_balance from owner where phone = '{0}';".format(phone))
    res = cursor.fetchall()
    if float(res[0][0]) == 0.00:
        tip = "客户无会员，请使用移动移动设备支付￥{0}".format(unit_price)
        flag = True
        mm = unit_price
    elif unit_price > 0.88 * float(res[0][0]):
        tip = "客户的会员余额还有：{0},不足以支付该单价".format(res[0][0])
        flag = False
        mm = unit_price
    else:
        tip = "本次会员价￥{0}，会员余额还剩￥{}".format(0.88 * unit_price, float(res[0][0]) - 0.88 * unit_price)
        flag = True
        mm = 0.88 * unit_price
        change_mumbership(phone, 0 - mm)
    return tip, flag, mm

# 插入医生信息
def insert_doctor(name, phone, title, department_id):
    cursor.execute("INSERT INTO doctor (doctor_name, phone, title, department_id) VALUES ('{0}', '{1}', '{2}', '{3}');".format(name, phone, title, department_id))

# 插入不重复的用户信息
def insert_owner(owner):
    cursor.execute("select count(*) from owner where phone = '{0}';".format(owner[1]))
    res = cursor.fetchall()
    if res[0][0]:
        return
    cursor.execute("insert into owner (name, phone) values('{0}', '{1}');".format(owner[0], owner[1]))

# 插入不重复的宠物信息
def insert_pet(pet):
    cursor.execute("select count(*) from pet where pet_name = '{0}' and  species = '{1}'  and  gender = '{2}' and  breed = '{3}' and owner_phone = '{4}';".format(pet[0], pet[1], pet[2], pet[3], pet[4]))
    res = cursor.fetchall()
    if res[0][0]:
        return
    cursor.execute("insert into pet (pet_name, species, gender, breed, owner_phone) values('{0}', '{1}', '{2}','{3}','{4}');".format(pet[0], pet[1], pet[2], pet[3], pet[4]))

# 插入服务信息
def insert_service(service):
    cursor.execute("insert into medical_service (drug_information, service_information, medical_service_time, unit_price) values('{0}', '{1}', '{2}','{3}');".format(service[0], service[1], service[2], service[3]))

# 插入医疗记录
def insert_record(record):
    cursor.execute("select pet_id from pet where pet_name = '{0}' and  species = '{1}'  and  gender = '{2}' and  breed = '{3}' and owner_phone = '{4}';".format(record[0], record[1], record[2], record[3], record[4]))
    res = cursor.fetchall()
    cursor.execute("insert into medical_record (doctor_id, pet_id) values('{0}', '{1}');".format(record[5], res[0][0]))

# 利用存储过程实现会员充值
def change_mumbership(phone, money):
    cursor.execute("select membership_card_balance from owner where phone = '{0}';".format(phone))
    res = cursor.fetchall()
    cursor.execute("CALL UpdateMembershipBalance('{0}', '{1}');".format(phone, float(res[0][0]) + money))

# 清除会员权力
def delete_mumbership(phene):
    cursor.execute("UPDATE owner SET membership_card_balance = 0  WHERE phone = '{0}';".format(phene))

# 检查是否有这个电话的用户
def search_phone(phone):
    cursor.execute("select count(*) from owner where phone = '{0}';".format(phone))
    res = cursor.fetchall()
    if res[0][0]:
        cursor.execute("select * from owner where phone = '{0}';".format(phone))
        owner = cursor.fetchall()
        return True, owner
    else:
        return False, '电话为' + str(phone) + '的用户不存在'

# 检查这个id的医生是否存在
def search_doctor(doctor_id):
    cursor.execute("select count(*) from doctor where doctor_id = '{0}';".format(doctor_id))
    res = cursor.fetchall()
    if res[0][0]:
        cursor.execute("SELECT doctor_name, title, department_id, phone, employment_status FROM doctor WHERE doctor_id = '{0}';".format(doctor_id))
        doctor = cursor.fetchall()
        return True, doctor
    else:
        return False, 'id为' + str(doctor_id) + '的医生不存在'
# 更新医生的信息
def update_doctor(name, title, department_id, phone, employment_status, doctor_id):
    cursor.execute("UPDATE doctor SET doctor_name = '{0}', title = '{1}', department_id = '{2}', phone = '{3}', employment_status = '{4}' WHERE doctor_id = {5};".format(name, title, department_id, phone, employment_status, doctor_id))

# 生成超级用户的账号
if check_usname("root") == False:
    add_admin_name_pwd('root', 'root')