Application development based on python and MySQL database: student information score management system version 1.0

Keywords: Python MySQL orm

preface

python is an interpretative and object-oriented programming language, which can be applied to many life and work scenes to meet people's different needs. MySQL is an open source and free relational database, which is deeply trusted by users and enterprises. However, the establishment of database (or table) and the use of operation database are a little complicated. Can we all implement these operations in scripting and apply them to our program functions and code conveniently? The answer is yes!
Next, we will use python and its ORM (object relational mapping) library to establish the database table model and manipulate the database easily in the script, and develop a practical application!
However, since this is the initial version (the first version), the functional implementation and structure do not pursue perfection, but it is also good for beginners or the initial version. In addition, I hope readers will continue to pay attention to the author and update and present a better functional version to the audience!

1, Demand analysis

For many beginners or university majors, when learning a basic programming language, they will be required to write a student achievement (or information) management system. In addition, whether for homework or interest, writing a practical application will greatly increase your programming ability and interest, so this blog can be used as your (python language) Job reference or guidance for developing such an application!

2, Preliminary preparation

Knowledge requirements or configuration reserveremarks
python basic syntaxBecause it is the initial version, it is only written with simple and clear python syntax to ensure that it is easy to understand, which can also verify your learning achievements
SQLAlchemy LibraryThis is a library of python, which can meet the requirements of building a bridge between object-oriented Python and relational database, so as to achieve concise operation of data in the database. In addition, we can also use the corresponding SQL operation statements to operate data. Therefore, both the native language and the local SQLAlchemy dialect (their own functions and methods) can be used. We should learn the basic usage methods
pymysql LibrarySince SQLAlchemy uses SQLite database by default, and we use MySQL, we'd better master some usage methods to download this library
Basic usage of mysqlAlthough it is convenient to establish data tables and operate database data with simple SQLAlchemy function method (instead of complex and native database operation), it is convenient to operate the database with native language in some function implementation, because the functions and methods used by SQLAlchemy to realize some functions will be very complex, Therefore, you still need to master the basic MySQL operation statements
mysql databaseInstall the database, set the login password and account name, and create a new database in MySQL. The name can be customized
GITHubIt's best to access and use GitHub, because I will upload the files of this system to GitHub for everyone to learn, but I will also upload the compressed package of the project in CSDN

The problems and solutions in learning and development will be put in the problem and learning summary module of this blog. If you need to learn later in the article!
Library package file support (virtual environment is adopted at the beginning of this time, and readers can choose freely)

3, Write code

1.app.py

from models import STU#The STU class is introduced to facilitate the use of session operations
from __init__ import session,engine#__ init__ Configure and connect modules for databases in the same directory

def stu_information():  # The function of inputting and saving student related information is an important data part of the system
    name = input("Please enter the student's name:")
    number = input("Please enter the student number of the student:")
    math = input("Please enter the student's math score:")
    chinese = input("Please enter the student's language score:")
    english = input("Please enter the student's English score:")
    user = STU(name=name, number=number, math=math, chinese=chinese, english=english)#The name on the left is the column name corresponding to the database (model class), and the name on the right is the variable collected by our input
    session.add(user)
    session.commit()
    print('>>Successfully added!\n')

def stu_change():  # Modify the function of single student information function
    numbers = input("Please enter the student number of the student:")
    users = session.query(STU).filter_by(number=numbers).first()#Find out the object corresponding to the student number first, and add the information before overwriting it again to achieve the purpose of modification
    users.name = input("Please enter the student's name:")
    users.math = input("Please enter the student's math score:")
    users.chinese = input("Please enter the student's language score:")
    users.english = input("Please enter the student's English score:")
    session.add(users)
    session.commit()
    return

def stu_delete():  # Function for deleting related information function
    numbers = eval(input("Please enter the serial number to delete student information:"))
    result=session.query(STU).filter_by(number=numbers).first()
    if result != None:#If the returned object is not empty, continue to delete
        session.delete(result)
        session.commit()
        print('>>Delete succeeded')
    else:
        print('>>There is no such person or your input is wrong!')

def stu_check():  # Function to find relevant information
    numbers = input("Please enter the serial number to query student information:")
    result=session.query(STU).filter_by(number=numbers).first()
    if result != None:
        print('-' * 56)
        print('|{0:^22}|{1:^6}|{2:^6}|{3:^5}|{4:^5}|'.format('full name', 'Student number', 'mathematics', 'language', 'English'))
        print('-' * 56)
        print('|{0:^20}\t|{1:^6}\t|{2:^6}\t|{3:^6}\t|{4:^6}|'.format(result.name, result.number, result.math,result.chinese, result.english))
        print('-' * 56)
    else:
        print('>>There is no such person or your input is wrong!')

def stu_all(*b):  # Display all student information data in the system
    all_result=session.query(STU).all()
    print('<All student transcript>' .center(52,'—'))
    print('|{0:^22}|{1:^6}|{2:^6}|{3:^5}|{4:^5}|'.format('full name','Student number','mathematics','language','English'))#In order to form a table in the output effect, the output format is adjusted accordingly
    print('-' * 56)#The horizontal line separating part of the table is composed of '-'. If you want to use other symbols (+ =)
    for i in all_result:
        print('|{0:^20}\t|{1:^6}\t|{2:^6}\t|{3:^6}\t|{4:^6}|'.format(i.name, i.number, i.math, i.chinese,i.english))
        print('-' * 56)
    print(">>The information of everyone in the system has been displayed!\n")

def stu_rank():  # Functions for sorting different types of grades
    lists=[]#A list used to store a single list of converted information
    rank_count=0#The initial value used to set the traversal output order of the ranking table
    rank_result=engine.execute('select *,(math+chinese+english) Total score from stu order by Total score desc')#Returns the result composed of multiple tuples, including each information and total score
    for i in rank_result:
        p=list(i)#Convert a single tuple into a list (array) to facilitate the subsequent index to get the desired data
        lists.append(p)
    print('<Total score ranking table>'.center(70,'='))#The horizontal line separating part of the table is composed of '='. If you want to use other symbols (+ -), but experiments have shown that this effect is better for this table
    print('|{0:^22}|{1:^6}|{2:^6}|{3:^5}|{4:^5}|{5:^8}|{6:^5}|'.format('full name','Student number','mathematics','language','English','Total score','ranking'))
    print('=' * 73)
    for k in lists:
        rank_count+=1#Each time you traverse the list in descending order according to the total score, you can record the operation and give the corresponding ranking value
        print('|{0:^20}\t|{1:^6}\t|{2:^6}\t|{3:^6}\t|{4:^6}|{5:^9}|{6:^6}|'.format(k[1],k[2],k[3],k[4],k[5],k[6],rank_count))
        print('=' * 73)
    print(">>The total score ranking information of everyone in the system has been displayed!\n")

while True:  # The while loop enables the main function interface to be used by the user until the user does not need it
    print('Student achievement management system, please select the system function'.center(71,'-'))#The function options are also in a box
    print('{0:<3}{1:<72}\t{2:>}'.format('|','1.Enter student information;','|'))
    print('{0:<3}{1:<69}\t{2:>}'.format('|','2.Modify student related information;','|'))
    print('{0:<3}{1:<69}\t{2:>}'.format('|','3.Delete student related information;','|'))
    print('{0:<3}{1:<69}\t{2:>}'.format('|','4.Query student information;','|'))
    print('{0:<3}{1:<69}\t{2:>}'.format('|','5.Display information for all students;','|'))
    print('{0:<3}{1:<72}\t{2:>}'.format('|','6.Student achievement ranking;','|'))
    print('{0:<3}{1:<72}\t{2:>}'.format('|','0.Exit program;','|'))
    print('-'*80,'\n')
    select = input("Please enter your function selection>>")
    if select == '1':
        stu_information()  # Function call or parameter passing
    elif select == '2':
        stu_change()    #ditto
    elif select == '3':
        stu_delete()
    elif select == '4':
        stu_check()
    elif select == '5':
        stu_all()
    elif select == '6':
        stu_rank()
    elif select == '0':
        print('\n','Version of this system:version1.0'.center(65,'-'))
        print("Thank you for using!!! I wish you success in the exam!!!")
        print("developer:SteveDraw,kiss you^_^")
        print('-'*70)
        break
    else:
        print(">>Your input is wrong! Please re-enter as prompted!\n")  # Error input format reminder
        continue

2.init.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import db#models is the database model module file in the same directory
engine=create_engine('mysql+pymysql://Root: < your mysql login password > @ localhost / sqltest ') # database connection engine, which can be configured according to the selected database
DbSession = sessionmaker(bind=engine)
session = DbSession()#Session is used to create sessions between programs and databases. All objects need to be loaded and saved through session objects
db.metadata.create_all(engine)#Create a data table. If it exists, it will be ignored

3.models.py

from sqlalchemy import Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base

db=declarative_base()#This is a method encapsulated in sqlalchemy, which constructs a base class. This base class and its subclasses can map Python classes and database tables

class STU(db):#In the selected database, use this class model to establish a data table, and define the class attributes corresponding to the field attributes in the table
    __tablename__='stu'#Name the table stu

    id=Column(Integer,primary_key=True)#The built-in serial number of the data table, which is arranged according to the input order, is used as the primary key of the relationship table
    name=Column(String(64))#
    number=Column(Integer,unique=True)#Since the student number is unique and we use the student number as the reference for search, unique is set to True, and this value cannot be repeated
    math=Column(Integer)
    chinese=Column(Integer)
    english=Column(Integer)

The above three files are all files in the same directory. Pay attention to the location relationship!

4, Function description

1. Interface display


2. Introduction to system flow chart

3. Function introduction

functional module describe
main interfaceEnter the number corresponding to the prompt to realize the corresponding function
Enter student informationAfter selecting this function, enter relevant information according to the input prompt in turn to complete the operation
Modify student related informationUse the student number as the query criteria to modify other information except the student number
Delete student related informationWith the student number as the query condition, delete all information (including the student number) of the corresponding student of the student number
Query student informationTake the student number as the query condition to return the information of the corresponding student
Display information for all studentsAfter selecting this function, the result summary of all students will appear. The table style has been adjusted to make it the same as the normal table style and improve the readability
Student achievement rankingAfter selecting this function, the ranking table of all students ranked according to the total score will appear
Exit programAfter selecting this function, the operation of the system will be ended, and the corresponding interface content will appear

System advantages and disadvantages

System advantagesSystem disadvantages
1. Be able to compare local and facilitate the establishment of data tables by using database models;1. Because it is the initial version and interacts in the run window, some run error checks are not perfect;
2. The use of python language and SQLAlchemy library can better operate database data and database interaction;2. If the main program function runs too long, there may be no response due to computer reasons, but the problem is not big;
3. The most basic functions of student information management system have been realized. In addition, the code structure and call are relatively simple;3. The data information is relatively simple and the analysis function is slightly poor, which will be expanded by updating the version in the future;
4. When outputting the interface, it imitates the table style and structure, and the readability is greatly enhanced4. There is no database migration and expansion, and future versions will be expanded;

5, Summary of problems and learning

SQLAlchemy usage document
Getting started with Python SQLAlchemy
Python operating MySql -- using SQLAlchemy ORM to operate the database
SQLAlchemy of Python operating MySQL
MySQL installation and basic command line use on Windows platform
Grade management system based on python language (no database version)

6, Project documents

File zip Download

Posted by Plakhotnik on Sun, 31 Oct 2021 10:55:40 -0700