Implementation of Python actual combat simple student course selection system

Time:2022-1-9

1、 Experimental purpose

Implementation of student course selection system

2、 Experimental environment

python3.6
Pymysql (Python connection MySQL)
Xlrd (operation Excel)

3、 Program structure

Implementation of Python actual combat simple student course selection system

1. Run first first_ run. py:
Function:Create database, table and other information

2.Run second_ run. py:
Function:Realize students’ course selection

3.Account and password xlsx:
Store student information (class roster can be saved)

For example:

Implementation of Python actual combat simple student course selection system

4、 Database structure

Links between tables

Implementation of Python actual combat simple student course selection system

5、 Functions of each table

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
student_ Login: store student account information (directly import the class roster, see the code for details)
                                    Field:
                                                s_ No: student number,
                                                s_ Name: student name,
                                                s_ Login: student account number,
                                                s_ PD: Student password
                Course: store course information
                            Field:
                                        c_ ID: course number
                                        c_ Name: course name
                student_ Class: student course selection table, which stores student course selection information
                                Field:
                                        s_ No: Student ID (set foreign key to connect with student_login table s_no)
                                        c_ ID: course number (set foreign key to connect with course table c_id)
                admin_ Login: administrator information table, which stores the administrator account
                                Field:
                                            a_ No: administrator number
                                            a_ Name: administrator name
                                            a_ Login: administrator account
                                            a_ PD: administrator password

6、 Code part

first_ run. Py code is as follows:

?
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
import pymysql
import xlrd
def create_all():
    try:
        password = input('please enter the MySQL password (root user):')
        db = pymysql.connect(host='localhost', user='root', password=password)
        cursor = db.cursor()
    except pymysql.err.operationalerror:
        print('password input error!')
    else:
        try:
            sql = 'create database student charset utf8;'
            cursor.execute(sql)
        except pymysql.err.programmingerror:
            print("can't create database 'student' database exists!")
        else:
            sql0 = 'use student;'
            #Create Curriculum
            sql1 = "create table course (c_id int(10) primary key auto_increment, c_name varchar ( 30 ) not null)default charset utf8;"
            #Create student account table
            sql2 = "create table student_login(s_no char(10), s_name varchar(30), s_login char(20), s_pd char(20) not null, primary key(s_no)) default charset utf8;"
            #Create student course selection table
            sql3 = "create table student_class (s_no char(10),c_id int,constraint foreign key (s_no) references student_login (s_no),constraint foreign key (c_id) references course (c_id),unique(s_no,c_id)) default charset utf8;"  #Unique (s_no, c_id))
            #Create administrator account table
            sql4 = "create table admin_login(a_no char(10), a_name varchar(30), a_login char(10)  unique, a_pd char(10) not null, primary key(a_no)) default charset utf8;"
            cursor.execute(sql0)
            cursor.execute(sql1)
            cursor.execute(sql2)
            cursor.execute(sql3)
            cursor.execute(sql4)
            db.commit()
            print('successful!')
def insert_student_login(db):
    def open_excel():
        try:
            book = xlrd.open_workbook("Account password. Xlsx"#File name, put the file and py file in the same directory
        except:
            print("open excel file failed!")
        else:
            try:
                sheet = book.sheet_by_name("sheet1"#Sheet1 in EXECL
            except:
                print('no sheet1')
            else:
                print('yes')
                return sheet
 
    def insert_data():
        sheet = open_excel()
        cursor = db.cursor()
        for i in range(1, sheet.nrows):  #The first row is the title name, which corresponds to the field name in the table, so it should start from the second row. The computer starts counting with 0, so the value is 1
            s_no = str(sheet.cell(i, 0).value)[0:10#Take row I, column 0
            s_name = sheet.cell(i, 1).value  #Take row I, column 1, and so on
            s_login = str(sheet.cell(i, 2).value)[0:10]
            s_pd = str(sheet.cell(i, 3).value)[0:10]
            # print(name)
            # print(data)
            # value = (name,data)
            # print(value)
            sql = "insert into student_login values('%s','%s','%s','%s')" % (s_no, s_name, s_login, s_pd)
            cursor.execute(sql)  #Execute SQL statement
            db.commit()
    insert_data()
    # cursor. Close() # close the connection
    # db. Close() # close data
    print("Insert succeeded!")
 
 
def insert_admin_login(db):
    try:
        cursor = db.cursor()
        sql = 'insert into admin_login values("1","admin","1","1")'
        cursor.execute(sql)
        db.commit()
    except:
        print('insert admin_login failed!!!')
    else:
        print('successful!')
 
def insert_into_course(db):
    try:
        cursor = db.cursor()
        sql = 'insert into course values (1, "high number"), (2, "College English");'      #By default, two courses are inserted for selection
        cursor.execute(sql)
        db.commit()
    except:
        print('insert course failed!')
    else:
        print('successful!')
 
def main():
    create_all()
    try:
        passwd = input('please enter MySQL password:')
        db = pymysql.connect(host="localhost", user="root", passwd=passwd, db="student", charset='utf8')
    except:
        print("could not connect to mysql server!")
    else:
        insert_student_login(db)
        insert_admin_login(db)
        insert_into_course(db)
 
if __name__ == '__main__':
    main()

second_ run. Py code is as follows:

?
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
import pymysql
 
#Create cursor function
def get_db():
    try:
        passwd = input('please enter MySQL password:')
        db = pymysql.connect('127.0.0.1', 'root', passwd, 'student')
    except pymysql.err.operationalerror:
        print('password input error! go die!')
    else:
        return db
def get_cursor(db):
    cursor = db.cursor()
    return cursor
#Select identity
def login(db, cursor):
    menu_login()
    i = 0
    while true:
        i += 1  #Set the cycle and exit the system more than three times
        login_select = input('please enter your options:')
        if login_select == '1'#The number here is of string type. Remember to use quotation marks!
            student_login(db, cursor)  #Jump to student login page
        elif login_select == '2':
            admin_login(db, cursor)  #Jump to the administrator login page
        else:
            print('please enter the correct option! > > > > > > Please wipe your eyes and reselect -- * -- (^ ^) - * --: ')
            if i >= 3:
                print('goodbye, thank you!')
                break
 
#Student login authentication
def student_login(db,cursor):
    print('           -----------------****----------^_^|- Welcome to the student system-|^_^----------****----------------------------       ')
    l = 0
    while true:
        login = input('please enter your account number:')
        sql = "select * from student_login where student_login.s_login='%s'" % login
        cursor.execute(sql)
        login_id = cursor.fetchall()
        if len(login_id) == 0:
            l += 1
            print('account does not exist, please re-enter:')
            if l >= 3:
                print()
                print("The account does not exist, please contact the administrator to apply for an account!")
                exit()
        else:
            p = 0  #The first error: it is placed in the while statement, which makes it impossible to exit more than three times (P will be initialized to 0 in each cycle)
            while true:
                password = input('please enter your password:')
                sql2 = "select * from student_login where student_login.s_login='%s'and student_login.s_pd ='%s'" % (
                    login, password)
                cursor.execute(sql2)
                login_pd = cursor.fetchall()
                if len(login_pd) == 0:
                    p += 1
                    print('wrong password!')
                    if p >= 3:
                        print('the password was entered incorrectly three times. Goodbye has you! ')
                        exit()
                elif len(login_pd) != 0:
                    sql3 = "select s_name,s_no from student_login where s_login = '%s'; " % login
                    # sql4 = "select s_no from student_login where s_login = '%s';" % login
                    cursor.execute(sql3)
                    # cursor.execute(sql4)
                    data = cursor.fetchall()[0]
                    s_name = data[0]           #Name
                    s_no = data[1]             #Student number
                    print()
                    print("----------------******************************************************************, s_name,
                          "| -- enter the student course selection system ^ ^ ---------------****** ------------")
                    #Student system module
                    i = 0
                    while true:
                        student_select_menu()
                        student_select = input('please enter your options:')
                        if student_select == '1':
                            show_course(cursor)
                        elif student_select == '2':
                            select_course(db, cursor, s_name, s_no)
                        elif student_select == '3':
                            show_class(cursor, s_no)
                            # exit()
                        elif student_select == '4':
                            update_class(db, cursor, s_name, s_no)
                        elif student_select == '5':
                            print('\ nyou have logged out^_^ \n')
                            select = input('please enter 1 or 2 to enter the student and administrator system respectively, or enter 0 to exit the system:')
                            if select == '1':
                                student_login(db, cursor)
                            elif select == '2':
                                admin_login(db, cursor)
                            elif select == '0':
                                exit()
                            else:
                                print('please enter the correct option!')
                        elif i >= 3:
                            print('goodbye, thank you!')
                            print()
                            break  #Re login student operation, password lock
                        else:
                            i += 1
                            print('please enter the correct option! > > > > > > Please wipe your eyes and reselect -- * -- (^ ^) - * --: ')
#Administrator login authentication
def admin_login(db, cursor):
    print('      -------------------****----------^_^|- Welcome to the administrator system-|^_^----------****--------------------------       ')
    l = 0
    while true:
        login = input('please enter your account number:')
        sql = "select * from admin_login where admin_login.a_login='%s'" % login
        cursor.execute(sql)
        login_id = cursor.fetchall()
        if len(login_id) == 0:
            l += 1
            print('account does not exist, please re-enter:')
            if l >= 3:
                print()
                print("The account does not exist, please contact the webmaster to apply for an account!")
                exit()
        else:
            p = 0  #The first error: it is placed in the while statement, which makes it impossible to exit more than three times (P will be initialized to 0 in each cycle)
            while true:
                password = input('please enter your password:')
                sql2 = "select * from admin_login where admin_login.a_login='%s'and admin_login.a_pd ='%s'" % (
                    login, password)
                cursor.execute(sql2)
                login_pd = cursor.fetchall()
                if len(login_pd) == 0:
                    p += 1
                    print('wrong password!')
                    if p >= 3:
                        print('the password was entered incorrectly three times. Goodbye has you! ')
                        exit()
                elif len(login_pd) != 0:
                    sql3 = "select a_name from admin_login where a_login = '%s'; " % login
                    cursor.execute(sql3)
                    s_name = cursor.fetchall()[0][0]
                    print()
                    print("------------------- ******************************************************************, s_name, "| -- enter the administrator system ^ ^ ---------------****** ------------")
                    #Administrator system module
                    i = 0
                    while true:
                        admin_select_menu()
                        admin_select = input('please enter your options:')
                        if admin_select == '1':
                            show_course(cursor)
                            # exit()
                        elif admin_select == '0':
                            delete_course(db, cursor)
                        elif admin_select == '2':
                            add_course(db, cursor)
                            # exit()
                        elif admin_select == '3':
                            show_studentlogin(cursor)
                            # exit()
                        elif admin_select == '4':
                            add_studentlogin(db, cursor)
                            # exit()
                        elif admin_select == '5':
                            show_adminlogin(cursor)
                            # exit()
                        elif admin_select == '6':
                            add_admin_login(db, cursor)
                            # exit()
                        elif admin_select == '7':
                            show_student_class(cursor)
                        elif admin_select == '8':
                            print('you have logged out! \n')
                            select = input('please enter 1 or 2 to enter the student and administrator system respectively, or enter 0 to exit the system:')
                            if select == '1':
                                student_login(db,cursor)
                            elif select == '2':
                                admin_login(db, cursor)
                            elif select == '0':
                                exit()
                            else:
                                print('please enter the correct option!')
                        elif i >= 3:
                            print('goodbye, thank you!')
                            print()
                            break  #Log in to the administrator system again
                        else:
                            i += 1
                            print('please enter the correct option! > > > > > > Please wipe your eyes and reselect -- * -- (^ ^) - * --: ')
#Login menu bar
def menu_login():
    menu_login1 = '''
        -----------------------------****----------(^_^)----------****------------------------------------
        |Welcome to the student course selection system|
        |1. Student login|
        |2. Administrator login|
        |(please select your operation in the menu, select other invalid, and automatically exit the system more than three times!)                   |
        '''
    print(menu_login1)
#Student course selection menu bar
def student_select_menu():
    menu_login = '''
            |1. View the current optional courses|
            |2. Select courses|
            |3. View the selected courses|
            |4. Change course|
            |5. Exit the system|
            |(please select your operation in the menu, select other invalid, and automatically exit the system more than three times!)                   |
            '''
    print(menu_login)
#Administrator action menu
def admin_select_menu():
    menu_login = '''
            |0. Delete course|
            |1. View all courses|
            |2. Add courses|
            |3. View all student account information|
            |4. Add student account|
            |5. View all administrator information|
            |6. Add administrator account|
            |7. View the course selection information of all students|
            |8. Exit the system|
            |(please select your operation in the menu, select other invalid, and automatically exit the system more than three times!)                   |
            '''
    print(menu_login)
#Student system module
#View all courses completed
def show_course(cursor):
    sql = "select * from course;"
    cursor.execute(sql)
    data = cursor.fetchall()
    #Print (type (data)) # tuple type
    item = len(data)
    if item == 0:
        print('no course information yet!')
    else:
        print()       #Line feed
        print('courses are as follows:')
        for i in range(item):
            course = data[i]
            select = {
                "Number": course[0],
                "Course": course[1]
            }
            print('                 ', select)
#Course selection completed
def select_course(db, cursor, s_name, s_no):
    print(s_name)
    try:
        number = int(input('please enter your course number:'))
        sql = "select c_name from course where c_id = %s" % number  #Find course name
        cursor.execute(sql)
        course = cursor.fetchall()[0][0]
    except indexerror:
        print('There is no course you want to choose, please choose again! ')
    except valueerror:
        print('please input the course number correctly!')
    else:
        print('the course you selected is: ', course)
        confirm = input('continue (Y / N):')
        if confirm == 'y' or confirm == 'y':
            try:
                sql_insert = "insert into student_class values('%s','%s');" % (s_no, number)     #Insert course
                cursor.execute(sql_insert)
                db.commit()
            except:
                print("Course already exists or course selection failed!")
            else:
                print('successful!')
        else:
            print('failed!!')
#View selected courses
def show_class(cursor, s_no):
    try:
        sql = 'select c_name from student_class sc inner join course c on sc.c_id = c.c_id inner join student_login sl on sc.s_no = sl.s_no where sc.s_no = "%s";' % s_no
        cursor.execute(sql)
        data = cursor.fetchall()
    except indexerror:
        print('no course selection information yet!')
    else:
        print('\ n' the course you chose is: ')
        for i in range(len(data)):
            print('                             ', data[i][0], '^_^')
 
#Modify course selection
def update_class(db, cursor, s_name, s_no):
    while true:
        try:
            course = input('please enter the course number you want to modify:')
            sql0 = "select * from student_class where s_no = '%s' and c_id = '%s'" % (s_no, course)
            cursor.execute(sql0)
            data0 = cursor.fetchall()
            if len(data0) != 0:
                re_course = input('please enter the course number you want to select:')
                sql = "select c_name from course where c_id = %s" % re_course  #Find if course number exists
                cursor.execute(sql)
                data = cursor.fetchall()      #Course corresponding to course number
            else:
                print('you didn't choose this course!')              #Students did not choose the course in the course selection table
                continue            #Terminate the following statements and enter the next cycle
 
        except indexerror:
            print('There is no course you want to choose, please choose again! ')
        else:
            if len(data) != 0:
                print('the course you re selected is: ', data[0][0])     #Data [0] [0] slice out the course name
                confirm = input('continue (Y / y):')
                if confirm == 'y' or confirm == 'y':
                    try:
                        sql = "update `student`.`student_class` set `c_id` = '%s' where `s_no` = '%s' and `c_id` = '%s' limit 1" % (
                            re_course, s_no, course)  #Update course
                        cursor.execute(sql)
                        db.commit()
                    except:
                        print("Failed")
                    else:
                        print('successful!')
                        break                    #Modification successfully exited the loop
                else:
                    print('failed!!')
            else:
                print('There is no such course! ')
 
 
#Administrator module
#Add course
def delete_course(db, cursor):
    try:
        course = input('please enter the course number you want to delete:')
        sql = 'delete from course where c_id = %s ' % course
        cursor.execute(sql)
        db.commit()
    except:
        print('delete failed! ')
    else:
        print('deleted successfully ^ ^ ')
 
def add_course(db, cursor):
    course = input('please enter the course you want to insert:')
    try:
        sql = "insert into course(c_name) values ('%s')" % course
        cursor.execute(sql)
        db.commit()  #Execute insert statement
    except pymysql.err.integrityerror:
        print('course already exists, cannot repeat!')
    else:
        print('added successfully')
 
#View student account (completed)
def show_studentlogin(cursor):
    sql = 'select * from student_login;'
    cursor.execute(sql)
    data = cursor.fetchall()
    print('student account number is as follows: \ n ')
    for i in range(len(data)):
        item = data[i]
        dict = {
            'sno': item[0],
            's_name': item[1],
            's_login': item[2],
            's_pd': item[3]
        }
        print('                ', dict)
#Add student account
def add_studentlogin(db, cursor):
    try:
        s_no = input('please enter student ID:')
        s_name = input('please enter the student's name:')
        s_login = input('please enter the student's account number:')
        s_pd = input('please enter the student's password:')
        cursor.execute('insert into student_login values("%s","%s","%s","%s");'% (s_no, s_name, s_login, s_pd))
        db.commit()
    except pymysql.err.integrityerror:
        print("Failed to add, student number / account already exists!")
    else:
        print('added successfully ^ ^')
 
#Finished viewing administrator account
def show_adminlogin(cursor):
    sql = 'select * from admin_login;'
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in range(len(data)):
        item = data[i]
        dict = {
            'sno': item[0],
            's_name': item[1],
            's_login': item[2],
            's_pd': item[3]
        }
        print('                                 ', dict)
def add_admin_login(db, cursor):     #Note that when you pass in parameters, you must first pass dB and then cursor
    try:
        s_no = input('please enter the administrator's number:')
        s_name = input('please enter the name of the administrator:')
        s_login = input('please enter the administrator's account number:')
        s_pd = input('please enter the administrator's password:')
        sql = 'insert into admin_login values("%s","%s","%s","%s");' % (s_no, s_name, s_login, s_pd)
        cursor.execute(sql)
        db.commit()
    except pymysql.err.integrityerror:
        print("Failed to add, number / account already exists!")
    else:
        print('added successfully ^ ^')
 
#View student course selection information (end)
def show_student_class(cursor):
    sql = 'select * from student_class sc inner join course c on sc.c_id = c.c_id inner join student_login sl on sc.s_no = sl.s_no order by s_name;'
    cursor.execute(sql)
    data = cursor.fetchall()
    print('\n')
    if len(data) > 1:
        for i in range(len(data)):
            item = data[i]
            #Print (item) # print query results
            dict = {                #Value
                'sc_no': item[0],
                'sc_name': item[5],
                'sc_course': item[3]
            }
            print('                        ', dict)
    else:
        print('no course selection information')
def main():
    try:
        db = get_db()
        cursor = get_cursor(db)
    except attributeerror:
        print('attributeerror!')
    else:
        login(db, cursor)
        
if __name__ == '__main__':
    main()

7、 Effect display

Run first_ run:

Implementation of Python actual combat simple student course selection system

Here, because I have created a database, the try statement directly captures errors
Delete the student database and run it again (you can also add judgment on whether there is a database in the SQL statement)

Implementation of Python actual combat simple student course selection system

At this time, we can see that our database and tables have been created
You can check the database to confirm:

Implementation of Python actual combat simple student course selection system

Then run second_ run:

Implementation of Python actual combat simple student course selection system

1. Student login

Implementation of Python actual combat simple student course selection system

Please check the specific functions by yourself.

Of course, the code has many shortcomings:

It is not encapsulated into a class. All the codes are nested functions, and the level is not particularly clear

If there is no visual interface, Tkinter module can be added to add a good visual interface.

This is the end of this article about the simple student course selection system implemented by Python in practice. For more information about Python student course selection system, please search the previous articles of developepper or continue to browse the relevant articles below. I hope you will support developepper in the future!

Recommended Today

You have to know about JVM garbage collection

catalogue 1、 Four citation methods1.1 strong reference1.2 soft reference1.3 weak reference1.4 phantom reference 2、 How to judge whether the object is garbage2.1 reference counting method2.2 root accessibility analysis 3、 Garbage collection algorithm3.1 mark sweep3.2 mark compact3.3 mark copy 4、 Garbage collector4.1 classification and characteristics4.1.1 serial4.1.2 throughput priority4.1.3 priority of response time4.2 serial garbage collector details […]