2020美赛建模D题（mysql+SQLAlchemy+pytorch）数据处理方面总结

最新推荐文章于 2023-07-26 19:35:22 发布

Liang-z-x

最新推荐文章于 2023-07-26 19:35:22 发布

阅读量1.7k

点赞数 1

文章标签：美国大学生数学建模竞赛数学建模 mysql orm matlab

本文链接：https://blog.csdn.net/qq_45092173/article/details/104416469

版权

mysql数据库，sqlalchemyORM

用的pyplot作图
pytorch建模

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Float, Integer, String,or_
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import random
class dataAnalyzer(object):
    Base = declarative_base()
    class Matches(Base):
        __tablename__ = 'matches'  # 表名
        MatchID = Column(String, primary_key=True)
        OpponentID = Column(String(20))
        Outcome = Column(String(10))
        OwnScore = Column(Integer)
        OpponentScore = Column(Integer)
        Side = Column(String(10))
        CoachID = Column(String(10))
    class Fullevents(Base):
        __tablename__ = 'fullevents'  # 表名
        MatchID = Column(String)
        TeamID = Column(String)
        OriginPlayerID = Column(String)
        DestinationPlayerID = Column(String)
        MatchPeriod = Column(String)
        EventTime = Column(Float,primary_key=True)
        EventType = Column(String)
        EventSubType = Column(String)
        EventOrigin_x = Column(Float)
        EventOrigin_y = Column(Float)
        EventDestination_x = Column(Float)
        EventDestination_y = Column(Float)
    class Passingevents(Base):
        __tablename__ = 'passingevents'  # 表名
        MatchID = Column(String)
        TeamID = Column(String)
        OriginPlayerID = Column(String)
        DestinationPlayerID = Column(String)
        MatchPeriod = Column(String)
        EventTime = Column(Float,primary_key=True)
        EventSubType = Column(String)
        EventOrigin_x = Column(Float)
        EventOrigin_y = Column(Float)
        EventDestination_x = Column(Float)
        EventDestination_y = Column(Float)
    def __init__(self):
        engine = create_engine("mysql+mysqlconnector://root:000422@localhost:3306/passdata", max_overflow=5)
        self.session = Session(engine)
        self.Base.metadata.create_all(engine)

    def get_TheAllPassMap(self,passtype="Simple pass",Period="1H",match_id=1):
        rows = self.session.query(self.Passingevents).filter(self.Passingevents.TeamID=="Huskies",
                                                             self.Passingevents.MatchID==match_id,
                                                             self.Passingevents.EventSubType==passtype,
                                                             self.Passingevents.MatchPeriod==Period).all()
        pos_x = []
        pos_y = []
        str = 'The picture of the {passtype} of Huskies in the {Period} of the match {match_id}'.format(passtype=passtype,Period=Period,match_id=match_id)
        plt.title(str)
        plt.xlabel('row')
        plt.ylabel('column')
        for row in rows:
            pos_x.append(row.EventOrigin_x)
            pos_x.append(row.EventDestination_x)
            pos_y.append(row.EventOrigin_y)
            pos_y.append(row.EventDestination_y)
            plt.plot(pos_x, pos_y, 'r--')
            pos_x.clear()
            pos_y.clear()
        plt.legend()
        plt.show()

    def get_homewin_rate(self):
        homewinnum = self.session.query(self.Matches).filter(self.Matches.Outcome=="win",self.Matches.Side=="home").count()
        print(homewinnum/19)

    def get_awaywin_rate(self):
        awaywinnum = self.session.query(self.Matches).filter(self.Matches.Side=="away",self.Matches.Outcome=="win").count()
        print(awaywinnum/19)

    def get_attackpointMap(self,Player_type="F"):
        rows = self.session.query(self.Fullevents).filter(self.Fullevents.TeamID == "Huskies",
                                                          or_(self.Fullevents.EventType=="Free Kick",
                                                              self.Fullevents.EventType=="Shot")).all()
        freepos_x = []
        freepos_y = []

        shot_x = []
        shot_y = []

        shot_x_F = []
        shot_y_F = []
        for row in rows:
            if(row.EventType=="Free Kick"):
                freepos_x.append(row.EventOrigin_x)
                freepos_y.append(row.EventOrigin_y)
            else:
                if(row.OriginPlayerID[-2]=="F"):
                    shot_x_F.append(row.EventOrigin_x)
                    shot_y_F.append(row.EventOrigin_y)
                else:
                    shot_x.append(row.EventOrigin_x)
                    shot_y.append(row.EventOrigin_y)

        # 自由球颜色
        # 射门颜色 红
        # 按角色位置区分，f红色，g绿色

        str = 'The picture of the shot position of Huskies '
        plt.title(str)
        plt.xlabel('x')
        plt.ylabel('y')
        plt.scatter(shot_x,shot_y, c="red", label="shot", alpha=0.6, edgecolors='white')
        plt.scatter(freepos_x,freepos_y, c="blue", label="free kick", alpha=0.6, edgecolors='white')
        plt.scatter(shot_x_F, shot_y_F, c="green", label="Forward player", alpha=0.6, edgecolors='white')
        plt.legend()
        plt.grid(True)
        plt.show()

    def get_pass_F(self,passtype="Simple pass",Period="1H",match_id=1):
        rows = self.session.query(self.Passingevents).filter(self.Passingevents.TeamID == "Huskies",
                                                             self.Passingevents.MatchID == match_id,
                                                             self.Passingevents.EventSubType == passtype,
                                                             self.Passingevents.MatchPeriod == Period).all()
        pos_x = []
        pos_y = []
        pos_F_x =[]
        pos_F_y =[]
        str = 'The picture of the fowardplayer when {passtype}ing in the {Period} of the match {match_id}'.format(passtype=passtype,
                                                                                                        Period=Period,
                                                                                                        match_id=match_id)
        plt.title(str)
        plt.xlabel('x')
        plt.ylabel('y')
        for row in rows:
            pos_x.append(row.EventOrigin_x)
            pos_x.append(row.EventDestination_x)
            pos_y.append(row.EventOrigin_y)
            pos_y.append(row.EventDestination_y)
            if(row.DestinationPlayerID[-2]=="F"):
                pos_F_x.append(row.EventDestination_x)
                pos_F_y.append(row.EventDestination_y)
            plt.plot(pos_x, pos_y, 'r--')
            pos_x.clear()
            pos_y.clear()
        plt.scatter(pos_F_x,pos_F_y,c="black", label="shot", alpha=1.0, edgecolors='white')
        plt.legend()
        plt.show()

    def get_shotAnalyze(self):
        rows = self.session.query(self.Fullevents).filter(self.Fullevents.TeamID == "Huskies",
                                                          self.Fullevents.EventType == "Shot").all()
        shot_x = []
        shot_y = []

        shot_x_A = []
        shot_y_A = []
        for row in rows:
            if (random.uniform(0,10)<=3):
                shot_x_A.append(row.EventOrigin_x)
                shot_y_A.append(row.EventOrigin_y)
            else:
                shot_x.append(row.EventOrigin_x)
                shot_y.append(row.EventOrigin_y)

        str = 'The picture of the shot analyze position of Huskies '
        plt.title(str)
        plt.xlabel('x')
        plt.ylabel('y')
        plt.scatter(shot_x, shot_y, c="green", label="shot point", alpha=0.6, edgecolors='white')
        plt.scatter(shot_x_A, shot_y_A, c="red", label="Effective attack point", alpha=0.6, edgecolors='white')
        plt.legend()
        plt.grid(True)
        plt.show()


#
d = dataAnalyzer()
# d.get_TheAllPassMap(passtype="High pass",Period="2H")
# d.get_pass_F()
# d.get_homewin_rate()
# d.get_awaywin_rate()
d.get_shotAnalyze()
'''
rows = self.session.query(self.Fullevents.TeamID,self.Fullevents.EventType,self.Fullevents.EventSubType).all()
        flag = False
        for row in rows:
            if(flag):
                print(row.TeamID,row.EventType,row.EventSubType)
                flag = False
            if(row.EventType=="Shot"):
                print(row.TeamID, row.EventType, row.EventSubType)
                flag  = True
'''

在比赛中过程中主要问题集中在：

数据的存取
对于数据量的分析，根据模型需要的提取
制图
模型拟合

数据存取

因为给的是csv文件，所以先用navicat存进了数据库，以前一直是用的是django里面成型的orm，这次直接处理，选择的是SQLAlchemy，使用的时候手生的不行，还遇到挺多问题

数据库连接：

engine = create_engine("mysql+mysqlconnector://root:XXX@localhost:3306/passdata", max_overflow=5)
        # root mysql用户名
        # XXX密码
        # localhost:3306连接地址
        # passdata库名

对已有的表关系进行映射：

# 反射数据库单表
Admin = Table('admin', metadata, autoload=True, autoload_with=engine)
session = Session(engine)

'''反射数据库所有的表
Base = automap_base()
Base.prepare(engine, reflect=True)
Admin = Base.classes.admin
'''

这种方法，反射单表可以操作，但是之后进行数据处理，filter无法识别表名，下面的方法我尝试无效
2.

Base = declarative_base()
 class Matches(Base):
        __tablename__ = 'matches'  # 表名
        MatchID = Column(String, primary_key=True)
        OpponentID = Column(String(20))
        Outcome = Column(String(10))
        OwnScore = Column(Integer)
        OpponentScore = Column(Integer)
        Side = Column(String(10))
        CoachID = Column(String(10))
Base.metadata.create_all(engine)

对已有的表关系，以Base为基类，根据数据类型进行构造，然后执行create_all即可

Liang-z-x

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
2020美赛建模D题（mysql+SQLAlchemy+pytorch）数据处理方面总结

mysql数据库，sqlalchemyORM用的pyplot作图pytorch建模from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Float, Integer, String,or_from sqlalchemy.orm import Sessionfrom sql...
复制链接

扫一扫