代碼記錄

---

數據錄入(python+mysql)

• 前言

相比于200612代碼增加了一個性別、運動員編號、運動員姓名字段。

• 代碼

# -*- coding: utf-8 -*-import re import datetime import os from pandas import DataFrame import pandas as pd import pymysqlclass EiDealData:def __init__(self):self.path = r"F:\MyStudio\PythonStudio\goatbishop.project01\MyNewWork"self.RunData = {}self.countFileNun = 0self.count1 = 0self.count2 = 0self.filter1 = 0self.filter2 = 0self.db = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',password = '19970928',database = 'datacup',charset = 'utf8')self.cur = self.db.cursor()def GetFile(self):fileName = os.listdir(self.path)self.GetFormatData(fileName)def GetFormatData(self, fileName):for item in fileName:if os.path.isdir(item):path = self.path + "\\" + itemfileName2 = os.listdir(path)for file in fileName2:if file.endswith(".csv"):self.countFileNun +=1#print(file)#判斷后綴fNameDeal = re.split("\W+", file)#print(fNameDeal)sonDictName = "R_"+ "".join(fNameDeal[0:(len(fNameDeal)- 1)])self.RunData[sonDictName] = {}self.RunData[sonDictName]["REACTION"] = []self.RunData[sonDictName]["RESULT"] = []self.RunData[sonDictName]["DATE"] = []self.RunData[sonDictName]["FN"] = [] #0為沒有犯規，1為犯規self.RunData[sonDictName]["NUMBER"] = [] #運動員編號self.RunData[sonDictName]["NAME"] = [] #運動員姓名ReadFilePath = path + "\\" + filewith open(ReadFilePath, 'r') as f:data = f.readline()#print(data)while (data):start_char = data[0]#flag1 = re.findall(r"\+|\(|\)", data)#print(flag1)flag2 = len(re.split("\W", data))#print(flag2)if ((ord(start_char) >= 49) & (ord(start_char) <= 57) & (len(data) >= 16) & (flag2 >= 9)):#print(data)self.count1 +=1#ord("a")為計算"a"的ASCII碼值pattern0 = r"\b\d{1,2}\.\d{2}\b"pattern1 = r"\b\d\.\d{3}\b"pattern2 = r"\d{1,2} [A-Z][a-zA-Z]{2} \d{2}\b"pattern3 = r" \d{3,4} "pattern4 = r"\d (?P<name>[A-Z]\S* (?P<temp>\S+ )+?)[A-Z]{3} \d"result = re.findall(pattern0, data)reaction = re.findall(pattern1, data)number = re.findall(pattern3, data)#print(data)if result:result = float(result[0])else:temp = re.findall(r"DNF|DQ|DNS", data)if temp:if temp[0] == "DNF":result = -1elif temp[0] == "DQ":result = -2elif temp[0] == "DNS":result = -3else:#print(temp)#print(data) result = -4if reaction:reaction = float(reaction[0])print(data)try:name = re.search(pattern4, data).group("name")except Exception as e:print("我進入錯誤區了")tempPattern = r"\d (?P<name>(?P<temp>\S+ )+?)[A-Z]{3} \d"name = re.search(tempPattern, data).group("name")finally:print(name)temp = re.findall(r"\d+", name)if temp:print("二次過濾")pattern5 = r"(?P<name>(?P<temp>[\D]+ )+?)[A-Z]{3} \d"name = re.search(pattern5, data).group("name")print(name)else:data = f.readline()self.filter1 +=1continueif name:name = nameelse:name = "None"if number:number = int(number[0])else:number = -1try:date = re.findall(pattern2, data)[0]except Exception as e:print("匹配異常,可能是原始數據不規整")deal_date = "2020-01-01"else:deal_date = datetime.datetime.strptime(date,'%d %b %y').strftime('%Y-%m-%d')#print(date)fn = re.findall(r"\bF\b|\bF1\b", data)#print(fn)if fn:fn = 1else:fn = 0self.RunData[sonDictName]["RESULT"].append(result)self.RunData[sonDictName]["REACTION"].append(reaction)self.RunData[sonDictName]["DATE"].append(deal_date)self.RunData[sonDictName]["FN"].append(fn)self.RunData[sonDictName]["NUMBER"].append(number)self.RunData[sonDictName]["NAME"].append(name)data = f.readline()print(item, self.countFileNun)self.countFileNun = 0self.writeMysql(item)self.RunData = {}def writeCsv(self):passdef writeMysql(self, RunYear):dataDealed = self.RunDatafor key in dataDealed:print(key)#print(key)CompetitionType = re.findall(r"n(Final)|(Round1)|(SemiFinal)|(Preliminary)", key)[0]#print(CompetitionType)CompetitionType = "".join(CompetitionType)ProjectClass = re.findall(r"\d{3}|hurdles", key)ProjectClass = "".join(ProjectClass)Gender = re.findall(r"(Women)|(Men)", key)print(Gender)if Gender: Gender = "".join(Gender[0])else:Gender = "None"#print(Gender)GenderL = [Gender]*len(dataDealed[key]["DATE"])#print(len(dataDealed[key]["DATE"]))self.count2 += len(dataDealed[key]["DATE"])ProjectClassL = [ProjectClass]*len(dataDealed[key]["DATE"])#CompetitionType = "".join(cp_list)#print(CompetitionType)CompetitionTypeL = [CompetitionType]*len(dataDealed[key]["DATE"])RunYearL = [RunYear]*len(dataDealed[key]["DATE"])tempData = zip(dataDealed[key]["DATE"], dataDealed[key]["RESULT"],dataDealed[key]["REACTION"], dataDealed[key]["FN"],dataDealed[key]["NUMBER"], dataDealed[key]["NAME"],CompetitionTypeL,ProjectClassL,RunYearL, GenderL)for item in tempData:#print(item)try:sql = 'insert into RunData2(PersonDATE,RESULT,REACTION,FN,AthleteNUMBER,AthleteNAME,CompetitionType,ProjectClass,RunYear,Gender) \values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'self.cur.execute(sql, item)self.db.commit()print("提交成功...") except Exception as e:self.db.rollback()print("錯誤信息:", e)def main(self):self.GetFile()#self.writeMysql()self.cur.close()self.db.close() if __name__ == "__main__":eiDeal = EiDealData()eiDeal.main()print("進入規則內個數：",eiDeal.count1)print("實際導入sql個數",eiDeal.count2)print("過濾掉數據個數：",eiDeal.filter1)

總結

以上是生活随笔為你收集整理的今日代码(200725)--数据录入(python+mysql)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。