current position:Home>Application development based on Python and MySQL database: student information score management system version 1.0

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

2022-05-15 05:57:12SteveDraw

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

Knowledge requirements or configuration reserves remarks
python Basic grammar Since it is the initial version , So use only simple and clear python Grammar to write , Keep it easy to understand , This can also verify your learning achievements
SQLAlchemy library This is python A library , It can meet the requirements of building object-oriented python A bridge to relational databases , So as to realize the simple operation of the data in the database , in addition , We can also use the corresponding SQL Operation statements to manipulate data , So native languages and native languages SQLAlchemy dialect ( Own functions and methods ) Both , To learn the basic usage
pymysql library because SQLAlchemy Is the default use of SQLite database , And what we use is MySQL, So download this library , It's best to master some usage methods
mysql Basic use Although it is very convenient to establish data tables and use simple SQLAlchemy Function method to manipulate database data ( Instead of complicated and native database operation ), However, it is very convenient to operate the database with native language in some function implementation , because SQLAlchemy The functions and methods used to implement some functions will be very complicated , So we still need to master the basic MySQL Operation statement
mysql database Install the database , And set the login password and account name , And in MySQL Create a new database in (database), The name can be customized
GITHub It's best to be able to access and use GIThub, Because I will upload the files of this system GitHub, So that we can learn , But it will also be in CSDN Upload the compressed package of the project

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 )
 Insert picture description here

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

 Insert picture description here
 Insert picture description here

2. Introduction to system flow chart

 Insert picture description here

3. Function is introduced

Function module describe
main interface Enter the number corresponding to the prompt , The corresponding functions can be realized
Input student information After selecting this function , According to the input prompt , Enter relevant information , To complete the operation
Modify student related information Take the student number as the query condition , Modify other information except student number
Delete student related information Take the student number as the query condition , Delete all the information of the corresponding students of the student number ( Including the student number )
Search for student information Take the student number as the query condition , You can return the information of the corresponding students
Display information for all students After selecting this function , The results of all students can be summarized , The table style has been adjusted , Make it the same as the normal table style , Improve readability
The ranking of students' grades After selecting this function , The ranking table of all students ranked according to the total score
Exit procedure After selecting this function , End the operation of the system , And the corresponding interface content appears

Advantages and disadvantages of the system

System advantages System shortcomings
1. Be able to compare places and use the database model to establish data tables ;1. Since it is the initial version , And interact in the running window , therefore , Some running error checks are not perfect ;
2. utilize python Language and SQLAlchemy Library can better operate database data and database interaction ;2. If the main program function runs too long , Probably due to computer reasons , Operation again will not respond , But it's not a big problem ;
3. The most basic functions of student information management system have been realized , in addition , The code structure and call are also relatively simple ;3. The data information is still relatively simple , The analysis function is slightly poor , Later update version to expand ;
4. When outputting the interface, imitate the table style and structure , Readability is greatly enhanced 4. No database migration , Future versions will expand ;

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 !------------------------------------------------------------

copyright notice
author[SteveDraw],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/131/202205110610218071.html

Random recommended