Preface

python Is an interpretative and object-oriented programming language , It can be applied to many life and work scenes , Meet people's different needs .MySQL Is an open source free relational database , Deeply trusted by users and enterprises . But building a database （ Or table ） And using the operation database is a little complicated , Can we all implement these operations in scripting , And it is very convenient to apply to our program functions and code , The answer is yes ！
Next , We will use python And one of it has ORM( Object relation mapping ) To build database table model and manipulate database easily in script , And develop a very practical application ！
But since this is the initial version （ The first version ）, Therefore, the functional realization and structure do not pursue perfection , But it's also good for beginners or the initial version , in addition , I hope readers will continue to pay attention to the author , Updated and better functional versions have been presented to the audience ！

One , Demand analysis

For many beginners or college majors, when learning a basic programming language, they will be required to write a student score （ Or information ） Management system , in addition , Whether for homework or interest , Writing a practical application will greatly increase your programming ability and sense of achievement , So this blog can be used as your （python Language ） Job reference or guidance for developing such an application ！

Two , Preparation

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 files support （ The virtual environment was adopted at the beginning of this time , Readers are free to choose ）

3、 ... and , Write code

1.app.py

from models import STU# introduce STU class , Easy to use things session operation 
from __init__ import session,engine#__init__ Configure and connect modules for databases in the same directory 

def stu_information():  #  Functions for inputting and saving student related information , This 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 Chinese score ：")
    english = input(" Please enter the student's English score ：")
    user = STU(name=name, number=number, math=math, chinese=chinese, english=english)# Sinister name It's the database ( Model class ) Corresponding column name name, On the right name It is our input Collected variables 
    session.add(user)
    session.commit()
    print('>> Add success ！\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()# First check and find out the object of the corresponding student number , Before adding information again, overwrite the previous information , To achieve the purpose of revision 
    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 Chinese score ：")
    users.english = input(" Please enter the student's English score ：")
    session.add(users)
    session.commit()
    return

def stu_delete():  #  Function for deleting relevant information function 
    numbers = eval(input(" Please enter the serial number to delete the student information ："))
    result=session.query(STU).filter_by(number=numbers).first()
    if result != None:# If the returned object is not empty , Continue deletion 
        session.delete(result)
        session.commit()
        print('>> Delete successful ')
    else:
        print('>> Check that 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 of the student information you want to query ：")
    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 ', ' Chinese language and literature ', ' 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('>> Check that 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 transcripts >' .center(52,'—'))
    print('|{0:^22}|{1:^6}|{2:^6}|{3:^5}|{4:^5}|'.format(' full name ',' Student number ',' mathematics ',' Chinese language and literature ',' English '))# In order to form a table on the output effect , The output format has been adjusted accordingly 
    print('-' * 56)# The horizontal line of the table is separated by ‘-’ constitute , If you want to use other symbols, you can also （+=）
    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 that sort different types of grades 
    lists=[]# A list used to store a single list of converted information 
    rank_count=0# The initial value set for traversing the 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 of multiple tuples , Contains various information and total score 
    for i in rank_result:
        p=list(i)# Convert a single tuple into a list （ Array ）, It is convenient for the later index to get the desired data 
        lists.append(p)
    print('< Total score ranking table >'.center(70,'='))# The horizontal line of the table is separated by ‘=’ constitute , If you want to use other symbols, you can also （+-）, But experiments have been done, and this effect is better for this watch 
    print('|{0:^22}|{1:^6}|{2:^6}|{3:^5}|{4:^5}|{5:^8}|{6:^5}|'.format(' full name ',' Student number ',' mathematics ',' Chinese language and literature ',' English ',' Total score ',' ranking '))
    print('=' * 73)
    for k in lists:
        rank_count+=1# Each time I traverse the list in descending order of the total score , Pass duration recording operation , 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:  # while Loop function enables the main interface to be used by the user all the time , Until the user doesn't 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. Input 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. Search for 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. The ranking of students' grades ;','|'))
    print('{0:<3}{1:<72}\t{2:>}'.format('|','0. Exit procedure ;','|'))
    print('-'*80,'\n')
    select = input(" Please enter your function selection >>")
    if select == '1':
        stu_information()  #  Function call or parameter passing use 
    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 your exam ！！！")
        print(" developer :SteveDraw, mua ^_^")
        print('-'*70)
        break
    else:
        print(">> Your input is wrong ！ Please re-enter... As prompted ！\n")  #  The format of the reminder is incorrect 
        continue

2.init.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import db#models It is a database model module file in the same directory 
engine=create_engine('mysql+pymysql://root:< Yours mysql The login password >@localhost/sqltest')# Database connection engine , According to the database configuration you choose 
DbSession = sessionmaker(bind=engine)
session = DbSession()#session Used to create a session between a program and a database , All objects need to be loaded and saved through session object 
db.metadata.create_all(engine)# Create data table , To exist is to ignore 

3.models.py

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

db=declarative_base()# This is sqlalchemy A method of internal encapsulation , Construct a base class through it , This base class and its subclasses , Can be Python Class and database table association mapping 

class STU(db):# In the selected database , Use this class model to build data tables , And define the class attribute corresponding to the field attribute in the table 
    __tablename__='stu'# Name the table stu

    id=Column(Integer,primary_key=True)# The built-in serial number of the data table , In the order of input , As the primary key of the relational table 
    name=Column(String(64))#
    number=Column(Integer,unique=True)# Because the student number is unique , And we take the student number as the reference for search , So use unique Set to True, 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 positional relationship ！
Operation process ：
0. Set up mysql database （ That is, the corresponding database ）
1. First create a written models.py;
2. Then run the prepared initial configuration file __init__.py file ;
3. Then run the main program app.py file

Four , Function description

1. Interface display

2. Introduction to system flow chart

3. Function is introduced

Advantages and disadvantages of the system

5、 ... and , Summary of problems and learning

SQLAlchemy Using document
Python SQLAlchemy Introductory tutorial
Python operation MySql—— Use SQLAlchemy ORM Operating the database
Python operation MySQL And SQLAlchemy
Windows Under the platform MySQL Installation and basic command line use
be based on python Language achievement management system （ No database version ）

6、 ... and , Project documents

File zip download
GitHub Project address

-------------------------------------------------- Last , If there is any deficiency in the text , it is respectful to have you criticize and correct sth ！------------------------------------------------------------