def _parseStatement_Create(self, statement): match = self.re_CreateStatement.search(statement) if match: table = SQLHelper.SqlTable( match.group('tablename') ) for ln in match.group('body').split(','): match = self.re_GetColumn.search(ln) if match: name = match.group('name') type = match.group('type') if re.search("unsigned",ln): unsigned = True else: unsigned = False if re.search("NOT NULL",ln): nullVal = False else: nullVal = False if re.search("AUTO_INCREMENT",ln): autoInc = True else: autoInc = False match = re.search("DEFAULT\s+(?P<val>\S+)",ln) if match: default = match.group('val') else: default=None table.addColumn( SQLHelper.SqlColumn(columnName=name, columnType=type, isPrimary=False, defaultVal=default, nullValid=nullVal, autoIncrement=autoInc, isUnsigned=unsigned) ) if re.search("PRIMARY\s+KEY",ln): primaryKey = re.search("PRIMARY\s+KEY\s+[(][`](?P<key>\w+)[`][)]",ln).group('key') for column in table.columns: if column.name == primaryKey: column.primary = True self.addTable(table)
def createStr_JavaStruct(self): s = "" s += self.createStr_Header() + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Variables()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Constants()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Init()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getValues()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_fromCursor()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getProjection()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_isString()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_needsConverted()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_jsonToCV()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getDisplayData()) + "\n" s += "\n" s += self.createStr_Footer() return s
def checkOriginFilesConsistency(caseName): syslog.syslog("PCAP APP: checkOriginFilesConsistency started: "+str(datetime.datetime.now())) originFiles = SQLHelper.loadFiles(caseName, 'origin') #files in directory files = [f for f in os.listdir(CASES_DIR + caseName + ORIGIN_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + ORIGIN_DIR,f))] #add orrigin/ prefix to each file files = map(lambda fileName: 'origin/'+ fileName, files) # check if are some new or deleted files newFiles = [file for file in files if file not in originFiles] deletedFiles = [file for file in originFiles if file not in files] for file in deletedFiles: saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file) for file in newFiles: a = saveFile.addFile(caseName, CASES_DIR + caseName + PCAP_DIR + file) # check if files was overwritten originFiles = SQLHelper.loadFiles(caseName, 'origin', additionalColumn = ',SIZE') for file in originFiles: fileSize = os.path.getsize(CASES_DIR + caseName + PCAP_DIR + file[0]) if file[1] != fileSize: saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file[0]) saveFile.addFile(caseName, CASES_DIR + caseName + PCAP_DIR + file[0]) syslog.syslog("PCAP APP: checkOriginFilesConsistency ended: "+str(datetime.datetime.now()))
def main(): SQLHelper.initdatabase(config.DBNAME) SQLHelper.inittable() db = pymysql.connect(host='localhost', user='******', password=config.SQLPWD, db=config.DBNAME, port=3306, charset='utf8', local_infile=True) cursor = db.cursor() templist = [(CNNAMES, 0), (CNANAMES, 1), (EN2CNNAMES, 2)] # 1. 单条插入最慢 # 2. 多条插入后再提交事务稍快 # 3. load data最快 # 注意:有的系统上面要以\r\n分割行,有的系统要以\n分割行 for name in templist: sql = "load data local infile '{}' into table chinese " \ "lines terminated by '\\r\\n' ignore 3 lines (content) set nametype={};" \ .format(name[0], name[1]) loaddata(sql, cursor) templist = [(CNIDIOM, 'idiom'), (JPNAMES, 'japanese'), (ENNAMES, 'english')] for name in templist: sql = "load data local infile '{}' into table {} " \ "lines terminated by '\\r\\n' ignore 3 lines (content) ;" \ .format(name[0], name[1]) loaddata(sql, cursor) cursor.close() db.close()
def sendFriends(self): #发送好友列表 result=sql.GetData("select * from Friend where UserNumber='"+self.student[1]+"'") length=len(result) if length !=0: for item in result: stu=sql.GetData("select * from User where UserNumber='"+item[2]+"'")[0] self.sendLine("friendlist|||"+str(stu[1])+"|||"+str(stu[2])+"|||"+str(stu[4])+"|||"+str(stu[5]))
def inserFriend(self,number): #添加一条好友记录 field=["UserNumber","FriendNumber"] values=[str(self.student[1]),str(number)] values1=[str(number),str(self.student[1])] if sql.GetCountMore("Friend", field,values)==0: sql.Insert("Friend", field, values) if sql.GetCountMore("Friend", field,values1)==0: sql.Insert("Friend",field,values1)
def GetCourseOfAready(Account,Password,Term): atry=actiontojwc2.Tojwc() # zhanghao="020252009009"; # Term="20111" get=atry.trytoconnect(Account,Password,Term) if get==0: try: print time.ctime()+"\t"+"抓取已选课程---学号:"+Account atry.getContents() parser=atry.GetAlreadyCourse("http://jwc.ouc.edu.cn:8080/ouc/SS020.do") option=parser('option')#抓取option数量 #判断信息中是否含有 if len(option)>0: for i in range(len(option)): termChoice=""#课程学期 termChoice=str(parser('option')[i]['value']) if termChoice>"2009"+"0": html=atry.GetAreadyCourseContent(termChoice, "http://jwc.ouc.edu.cn:8080/ouc/SS020.do") course=html.findAll('table',width="100%") #print str(course[3]),str(course[4]) ask=["UserNumber","Term"] answer=[Account,termChoice] count=SQLHelper.GetCountMore("Course", ask, answer); #count=0代表第一次插入已选课程 if count==0: field=["UserNumber","CourseContent","Curriculum","Term"] #MySQLdb.escape_string()参数必须是string类型的 # str(course[3])为已选课程的详细信息,str(course[4])为已选课程的表哥形式 value=[Account,MySQLdb.escape_string(str(course[3])),MySQLdb.escape_string(str(course[4])),termChoice] insert=SQLHelper.Insert("Course", field, value) if insert==1: print time.ctime()+"\t成绩---"+Account+"---Insert OK" else: print "插入个人课表出现程序上的错误,请检查!!" else: fields=["CourseContent","Curriculum"] values=[MySQLdb.escape_string(str(course[3])),MySQLdb.escape_string(str(course[4]))] ask=["UserNumber","Term"] answer=[Account,termChoice] update=SQLHelper.UpdateMore("Course", fields, values, ask, answer) if update==1: print time.ctime()+"\t课表---"+Account+"---Update OK" else: print "插入个人课表出现程序上的错误,请检查!!" atry.logout() print time.ctime()+"\t"+"学生课表更新完成。" except Exception: atry.logout() print traceback.print_exc() return 3 else : if get==1: print time.ctime()+"\t课表---"+Account+"登陆失败,请检查用户名、密码是否正确。" if get==2: print time.ctime()+"\t课表---"+Account+"该用户已经登录,请登出或稍后再试!"
def createStr_CreateStr(self): s = "public static final String[] SQL_CREATE_ENTRIES = {\n" for table in self.tables: s += SQLHelper.indent( SQLHelper.toJavaString(table.createStr_CreateStr())) s += ",\n\n" tmp = table.createStr_InsertStr() if tmp: s += SQLHelper.indent( SQLHelper.toJavaString(tmp)) s += ",\n\n" return s[0:-3] +"\n};"
def addFriend(self,number,hostnum,flag): result=sql.GetData("select * from User where UserNumber='"+number+"'") if len(result)==0: self.sendLine("friends|||add|||0") else: #self.sendLine("friends|||add|||3") sql.Insert("Message", ["sourcenum","desnum","flag"], [hostnum,number,flag]) if flag=="2": #同意好友申请,数据库中增加记录 self.inserFriend(number) self.sendFriends()
def createStr_CreateStr(self): s = "public static final String[] SQL_CREATE_ENTRIES = {\n" for table in self.tables: s += SQLHelper.indent( SQLHelper.toJavaString(table.createStr_CreateStr())) s += ",\n\n" tmp = table.createStr_InsertStr() if tmp: s += SQLHelper.indent(SQLHelper.toJavaString(tmp)) s += ",\n\n" return s[0:-3] + "\n};"
def createStr_JavaSqLite(self): s = "" s += self.createStr_Header() +"\n" s += "\n" s += SQLHelper.indent(self.createStr_Classes()) +"\n" s += "\n" s += SQLHelper.indent(self.createStr_CreateStr()) +"\n" s += "\n" s += SQLHelper.indent(self.createStr_DropStr()) +"\n" s += "\n" s += self.createStr_Footer() return s
def sendMsg(self): result=sql.GetData("select * from Message where desnum='"+self.student[1]+"'") if not len(result)==0: for item in result: if item[3]=="2": self.sendFriends() stu=sql.GetData("select * from User where UserNumber='"+item[1]+"'") for one in stu: self.sendLine("friends|||add|||"+str(item[3])+"|||" +str(one[1])+"|||"+str(one[2])+"|||"+str(one[4])+"|||"+str(one[5])) sql.Delete("Message", "desnum", self.student[1]) else: print self.student[1]+"\tNo new message."
def createStr_JavaSqLite(self): s = "" s += self.createStr_Header() + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Classes()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_CreateStr()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_DropStr()) + "\n" s += "\n" s += self.createStr_Footer() return s
def AnalyzeChoiceCourse(Account,Password,Term): atry=actiontojwc2.Tojwc() get=atry.trytoconnect(Account,Password,Term) if get==0: atry.getContents() parser=atry.GetStudentChooseCourse("http://jwc.ouc.edu.cn:8080/ouc/SS003.do") p=re.compile("\<span\>\d\d\d\d\d\d\d\<br \/>\<\/span\>") number=parser('span') for i in range(len(number)): coursenumber=p.findall(str(number[i])) if len(coursenumber)>0: courseNumber=str(coursenumber[0])[6:len(coursenumber)-14]; # data0=SQLHelper.GetData("select * from MajorCourse where ChooseNumber='"+courseNumber+"' and Term='"+Term+"'") # print courseNumber # # majorID=None; # pNumber=None; # if len(data0)>0: ## for k in range(len(data0[0])): ## print str(data0[0][k]) # majorID=string.atoi(str(data0[0][0])) # pNumber=string.atoi(str(data0[0][10])) # else: # data1=SQLHelper.GetData("select * from AllCourse where CourseNumber='"+courseNumber+"' and Term='"+Term+"'") # if len(data1)>0: ## for l in range(len(data1[0])): ## ## print str(data1[0][l]) # majorID=string.atoi(str(data1[0][0])) # pNumber=string.atoi(str(data1[0][8])) # else: # print "课程号为:"+courseNumber+"不存在!!" ## print majorID,pNumber ask=["MajorID","Account","Term"] answer=[courseNumber,Account,Term] count=SQLHelper.GetCountMore("CourseAnalysis", ask, answer); #count=0表中没有该课程的任何信息 if count==0: field=["MajorID","Account","Term"] # #MySQLdb.escape_string()参数必须是string类型的 # # str(course[3])为已选课程的详细信息,str(course[4])为已选课程的表哥形式 value=[courseNumber,Account,Term] # for j in range(len(value)): # print str(value[j]),type(value[j]) insert=SQLHelper.Insert("CourseAnalysis",field,value) if insert==1: print "OK" else: print "插入选课分析表出现程序上的错误,请检查!!" atry.logout() else : print get
def getReadableFileInfo(fileName, caseName): fileID = SQLHelper.getFileID(fileName, caseName) info = SQLHelper.getFileInfo(fileID) s = sizeof_fmt(info[1]).replace(" ", "") if info[0] is None: #return ["None",sizeof_fmt(info[1]), info[2], info[3], info[4]] return ["None",s, info[2], info[3], info[4], info[5], fileID] conn = sqlite3.connect(DATABASE) conn.execute('pragma foreign_keys=ON') q = conn.execute("SELECT CONTENT FROM FILTERS WHERE ID = ?", (info[0],)) filterContent = q.fetchone()[0] conn.commit() conn.close() return [filterContent, s, info[2], info[3], info[4], info[5], fileID]
def getSizeOfNewFilesInCase(caseName): originFiles = SQLHelper.loadFiles(caseName, 'origin') #files in directory files = [f for f in os.listdir(CASES_DIR + caseName + ORIGIN_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + ORIGIN_DIR, f))] files = map(lambda fileName: 'origin/'+ fileName, files) newFiles = [file for file in files if file not in originFiles] # check if files was overwritten overwritten = 0 originFiles = SQLHelper.loadFiles(caseName, 'origin', additionalColumn = ',SIZE') for file in originFiles: if os.path.isfile(CASES_DIR + caseName + PCAP_DIR + file[0]): fileSize = os.path.getsize(CASES_DIR + caseName + PCAP_DIR + file[0]) if file[1] != fileSize: overwritten += fileSize return sum(os.path.getsize(CASES_DIR+ caseName + PCAP_DIR + f) for f in newFiles) + overwritten if newFiles or overwritten != 0 else ""
def GetBiXiuCourse(Account,Password,Term): atry=actiontojwc2.Tojwc() get=atry.trytoconnect(Account,Password,Term) if get==0: try: print time.ctime()+"\t"+"抓取通识必修课---学期:"+Term atry.getContents() academic=atry.GetAcademic("http://jwc.ouc.edu.cn:8080/ouc/SS013.do") # term="20111" number=len(academic[0]) #print str(academic[0][0]),str(academic[0][2]) m=0 for i in range(2): if i==0: m=0 else: m=2 for j in range(len(academic)): CourseOne=atry.GetAllCourse(str(academic[j][0]), str(academic[j][2]), str(m), "http://jwc.ouc.edu.cn:8080/ouc/SS013.do") #print CourseOne coursemore=actiontojwc2.GrabCourse(CourseOne) for k in range(len(coursemore)): ask=["CourseNumber","SchoolHouse","Term"] answer=[str(coursemore[k][0]),str(m),Term] count=SQLHelper.GetCountMore("AllCourse", ask, answer) #count=SQLHelper.GetCountOne("AllCourse", "CourseNumber", str(coursemore[k][0])) #print count if count>0: continue; else: field=["CourseName","CourseNumber","CourseTime","XueShi","CourseScore","Money","TeacherName","PeopleNumber","Remark","SchoolHouse","Term","Academic","CourseType"] value=[str(academic[j][3]),str(coursemore[k][0]),str(coursemore[k][1]),str(coursemore[k][2]),str(coursemore[k][3]),str(coursemore[k][4]),str(coursemore[k][5]),str(coursemore[k][6]),str(coursemore[k][7]),str(m),Term,str(academic[j][1]),"0"] insert=SQLHelper.Insert("AllCourse",field ,value) if insert==1: continue else: print "通识必修课抓取错误。" print time.ctime()+"\t"+"通识必修课抓取完成。" atry.logout() except Exception: atry.logout() print traceback.print_exc() return 3 else : if get==1: print "登陆失败,请检查用户名、密码是否正确。" if get==2: print "该用户已经登录,请登出或稍后再试!"
def findGap(crewID, date, cursor): # searches the SQL for a given crew and date # Get all requests older than current date # [11] is MOB ETA # [13] is DEMOB ETD rows = SQ.getDatesOld(crewID, date, cursor) print(len(rows)) reqIDGap = -1 # Checks if crew is assigned after most recent assignment if (not checkFirstRow(crewID, rows[0], date, cursor)): for i in range(len(rows) - 1): # determines how long it would take to go to homebase on current assignmnet timeHome = calcTimeNeeded(crewID, rows[i][0], cursor) # determines how long the gap between assignmnets actually is timeBetween = gapCalc(rows[i][11], rows[i + 1][13]) print(timeHome) print(timeBetween) if (timeHome != None and timeBetween != None): # diff is the difference between the time it would take to get home and the time they have # if it is positive then the crew is at their homebase diff = (timeBetween - timeHome) delta = diff.total_seconds() print("Delta: ", delta) # Positive means that the crew CAN return home for R and R if delta >= 0: print("POSITIVE: ", diff) reqIDGap = i if (reqIDGap != -1): break if (reqIDGap != -1): getPrevReqDays(rows, i, crewID, date, cursor) else: return 14
def updateFile(filePath, caseName, filterID): syslog.syslog("PCAP APP: updateFile: "+filePath+" started: "+str(datetime.datetime.now())) fileName = getDBNameFromPath(filePath) dateTimes = getDateTimeFromFile(filePath) fileSize = os.path.getsize(filePath) fileID = SQLHelper.getFileID(fileName, caseName) if fileID: SQLHelper.updateFileInfo(fileID, filterID, fileSize, dateTimes) else: conn = sqlite3.connect(DATABASE) q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?",(caseName,)) caseID = q.fetchone()[0] q = conn.execute("INSERT INTO FILES VALUES (null,?,'filtered',?,?,?,?,?,?,null)",(fileName, caseID, filterID, fileSize, dateTimes[0], dateTimes[1],'description',)) conn.commit() conn.close() syslog.syslog("PCAP APP: updateFile: "+filePath+" ended: "+str(datetime.datetime.now()))
def getFilter(caseName, fileName = None, type = "case"): conn = sqlite3.connect(DATABASE) if fileName is None: q = conn.execute("SELECT FILTERID FROM CASES WHERE CASES.NAME = ?",(caseName,)) IDs = q.fetchone() if IDs is None: return "None" filterID = IDs[0] q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?",(filterID,)) filterContent = q.fetchone() # if filter exist set it to its string value else set it to None filterContent = filterContent[0] if filterContent else "None" else: fileID = SQLHelper.getFileID(fileName, caseName) q = conn.execute("SELECT FILTERID FROM FILES WHERE FILES.ID = ?", (fileID,)) IDs = q.fetchone() if IDs is None: return "None" filterID = IDs[0] q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?",(filterID,)) filterContent = q.fetchone() # if filter exist set it to its string value else set it to None filterContent = filterContent[0] if filterContent else filterID conn.close() return filterContent if filterContent else "None"
def applyTmpFilter(filePath, filterContent, caseName): currentFilter = helper.getFilter(caseName, helper.getDBNameFromPath(filePath), type = 'file') filteredFileName = applyFilterOnFile(filePath, filterContent, caseName, True) if not os.path.isfile(CASES_DIR + caseName + TMP_DIR + filteredFileName): return None summFilter = currentFilter + ' && ' + filterContent if currentFilter != 'None' else filterContent conn = sqlite3.connect(DATABASE) conn.execute('pragma foreign_keys=ON') # crete new fitler in db conn.execute("INSERT INTO FILTERS VALUES(null, ?, \'\', \'\')",(summFilter,)) q = conn.execute('SELECT max(ID) FROM FILTERS') filterID = q.fetchone()[0] q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?",(caseName,)) IDs = q.fetchone() caseID = IDs[0] if SQLHelper.getFileID(helper.getDBNameFromPath(CASES_DIR + caseName + TMP_DIR + filteredFileName), caseName) is not None: conn.commit() conn.close() helper.updateFile(CASES_DIR + caseName + TMP_DIR + filteredFileName, caseName, filterID) else: sourceFile = helper.getDBNameFromPath(filePath) fileSize = os.path.getsize(CASES_DIR + caseName + TMP_DIR + filteredFileName) dateTimes = helper.getDateTimeFromFile(CASES_DIR + caseName + TMP_DIR + filteredFileName) conn.execute("INSERT INTO FILES VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?, ?)", ("tmp/"+filteredFileName, "tmp", caseID, filterID, fileSize, dateTimes[0], dateTimes[1], sourceFile,'description',)) #conn.execute("INSERT INTO FILES VALUES (null,\'"+"tmp/"+filteredFileName+"\',\'tmp\',"+str(caseID)+","+str(filterID)+","+str(fileSize)+",\'"+dateTimes[0]+"\',\'"+ dateTimes[1]+"\',\'"+sourceFile+"\')") conn.commit() conn.close() return filteredFileName
def applyTimeFilterOnFile(filePath, caseName, start = '', end = '', override = False): syslog.syslog("PCAP APP: applyTimeFilterOnFile: "+filePath+" started: "+str(datetime.datetime.now())) if start == '' and end == '': return None tmpF = tempfile.NamedTemporaryFile(delete=True) outputFileName = os.path.basename(tmpF.name + '.pcap') outputFilePath = CASES_DIR + caseName + TMP_DIR + outputFileName subprocess.call(['editcap','-A', start, '-B',end, filePath,outputFilePath]) if not os.path.isfile(outputFilePath): return None if override: os.rename(outputFilePath, filePath) outputFilePath = filePath outputFileName = helper.getDBNameFromPath(filePath) conn = sqlite3.connect(DATABASE) conn.execute('pragma foreign_keys=ON') # crete new fitler in db q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?", (caseName,)) IDs = q.fetchone() caseID = IDs[0] if SQLHelper.getFileID(helper.getDBNameFromPath(outputFilePath), caseName) is not None: conn.commit() conn.close() helper.updateFile(outputFilePath, caseName, 'null') else: sourceFile = helper.getDBNameFromPath(filePath) fileSize = os.path.getsize(outputFilePath) dateTimes = helper.getDateTimeFromFile(outputFilePath) conn.execute("INSERT INTO FILES VALUES (null, ?, ?, ?, null, ?, ?, ?, ?, ?)", ("tmp/"+outputFileName, "tmp", caseID, fileSize, dateTimes[0], dateTimes[1], sourceFile,'description',)) # conn.execute("INSERT INTO FILES VALUES (null,\'"+"tmp/"+outputFileName+"\',\'tmp\',"+str(caseID)+",null,"+str(fileSize)+",\'"+dateTimes[0]+"\',\'"+ dateTimes[1]+"\',\'"+sourceFile+"\')") conn.commit() conn.close() syslog.syslog("PCAP APP: applyTimeFilterOnFile: "+filePath+" ended: "+str(datetime.datetime.now())) return outputFilePath
def sendNewsPage(self,newsclass): #教务处新闻页数 jwc=sql.GetCountOne("News", "NewsClassID", newsclass) page=jwc/10 if jwc%10 !=0: page=page+1 self.sendLine("newspage|||"+str(page)+"|||"+newsclass)
def sendTerm(self): #发送用户的学期列表,用户通过学期来获取课程信息 result=sql.GetData("select Term from Course where UserNumber="+str(self.student[1])) length=len(result) for one in result: self.sendLine("term|||"+str(one[0])) # self.sendLine("termend") self.sendCourse(result[length-2][0])
def showTables(): ret = sql.GetCommand("show tables") if len(ret) == 0: print "No table found!" else: for table in ret: print table return ret
def ODS(): session['refresh'] = False latestAssetDate = SQLHelper.getLatestAssetDate() AssetsUpdatedThisMonth = SQLHelper.getAssetsUpdatedThisMonth( latestAssetDate) NewFundsThisMonth = SQLHelper.getNewFundsThisMonth() NewFundsThisMonth = sum(NewFundsThisMonth) SQLHelper.generateAssetGraph(latestAssetDate) SQLHelper.generateNewFundsGraph() #SQLHelper.generateAssetMonthly(latestAssetDate) f = open("previous.txt", "r") previous = f.readline() f.close() fundsSinceRefresh = NewFundsThisMonth - int(previous) f = open("previous.txt", "w") f.write(str(NewFundsThisMonth)) f.close() return render_template('ODS.html', latestAssetDate=latestAssetDate, AssetsUpdatedThisMonth=AssetsUpdatedThisMonth, NewFundsThisMonth=NewFundsThisMonth, fundsSinceRefresh=fundsSinceRefresh)
def Insert(title,content,time,name,id):#插入到数据库中 count=GetCount(title,name,time); if count <1: number="insert into News(Title,Content,Time,NewsClassName,NewsClassID) values('%s','%s','%s','%s','%d')"% (title,content,time,name,id) insert=SQLHelper.InsertAboutNumber(number) if insert==1: print "OK" else: print "插入新闻出现错误!!"
def send10News(self,page,newsclass): #发送10条新闻,客户端一页显示10条 ten=sql.GetData("SELECT * FROM News where NewsClassID="+newsclass+" order by id desc LIMIT "+str(int(page)*10)+ ",10") if len(ten)!=0: for item in ten: print item[0] self.sendLine("news|||"+str(item[0])+"|||"+str(item[1])+"|||"+str(item[4])) if page=="0": self.curnewsid[newsclass]=str(ten[0][0])
def searchFriend(self,name): result=sql.GetData("select * from User where UserName='******'") #搜索结果 #不存在 if len(result)==0: self.sendLine("friends|||search|||0") else: for item in result: self.sendLine(("friends|||search|||1|||"+item[1]+"|||"+item[2]+"|||"+item[4]+"|||"+item[5]).encode('UTF-8'))
def showInformation(tablename): cmd = "select * from %s" % tablename ret = sql.GetCommand(cmd) if len(ret) == 0: print "No information found in %s" % (tablename) else: for item in ret: print item return ret
def GetCount(title, classname): #验证数据库中是否包含该条信息 # cur=db.cursor() #count=cur.execute("select * from Information where Title='"+title+"' and ClassName='"+classname+"'") ask = ["Title", "NewsClassName"] answer = [title, classname] count = SQLHelper.GetCountMore("News", ask, answer) #cur.close() #db.close() return count
def sendCourse(self,term): #发送课程 result=sql.GetData("select * from Course where UserNumber="+str(self.student[1])+" and Term="+term)[0] # self.sendLine("coursetable") # self.send(str(result[2])) # self.sendLine("yeah") # self.sendLine("coursetable|||"+str(term)+"|||0|||") # self.transport.write(str(result[2])) # print result[2] self.send("coursetable|||"+str(term)+"|||1|||",str(result[3]))
def sendNewsbyID(self,id,newsclass): #id=新闻id,newsclass=新闻类型 #按照id发送新闻 result=sql.GetData("select * from News where NewsClassID="+newsclass+" and id >'"+id+"' order by id desc ") if len(result) !=0: self.curnewsid[newsclass]=str(result[0][0]) for item in result: self.sendLine("newsupdated|||"+str(item[0])+"|||"+str(item[1])+"|||"+str(item[4])) else: print newsclass+"\tNo new news."
def checkFilteredFileConsistency(caseName): syslog.syslog("PCAP APP: checkFilteredFileConsistency started: "+str(datetime.datetime.now())) filteredFiles = SQLHelper.loadFiles(caseName, 'filtered') #files in directory files = [f for f in os.listdir(CASES_DIR + caseName + PCAP_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + PCAP_DIR, f))] deletedFiles = [file for file in filteredFiles if file not in files] for file in deletedFiles: saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file) syslog.syslog("PCAP APP: checkFilteredFileConsistency ended: "+str(datetime.datetime.now()))
def start(): # This should be removed if ever committed to an online place or shared address = "*****@*****.**" password = "******" mainPath = r"C:\Users\Carson\OneDrive\College CSU\Year 4\Summer 2018" download = mainPath + r"\IHT Project\Data\Server\Daily Test\Full" con = connectEmail(address, password) emailNumbers = get_unread(con) print("Unread: ", emailNumbers) count = 1 conn = SQ.connectDB("root", "Code", "localhost", "iht_test") cursor = conn.cursor(buffered=True) # Checks if we have unread emails if (emailNumbers != None): # Iterates through all the unread emails for i in emailNumbers: print("Running: ", str(count) + " / ", len(emailNumbers)) #### Email Download and Process #### result, data = con.fetch(i, '(RFC822)') msg = email.message_from_bytes(data[0][1]) temp = data[0][1].splitlines() date = str(temp[2][12:]) date = date[3:-1] get_attachments(download, msg, date=date) # Gets the filename of the downloaded attatchemnts fileName = "blank" for part in msg.walk(): if (fileName == "blank" or fileName == None): fileName = part.get_filename() fileName = fileName[:-4] + "csv" fileName = fileName.replace('\n', '') fileName = fileName.replace('\r ', ' ') # Determines which file we are dealing with and processes accordingly if ("Report View of CREW STATUS TABLE - ONCE DAILY" in fileName): #print("Update Crews") crewsFile = open(download + "\\" + fileName) SQ.update_crews(crewsFile, cursor) conn.commit() elif ("Report View of CREW REQUEST AUTOMATED QUERY - Released in prior 48 hours" in fileName): #print("Update All") requestFile = open(download + "\\" + fileName) SQ.update_all(requestFile, cursor) conn.commit() elif ("Report View of CREW REQUEST AUTOMATED QUERY - ONCE DAILY" in fileName): #print("Update All") requestFile = open(download + "\\" + fileName) SQ.update_all(requestFile, cursor) conn.commit() count += 1
def applyFilterOnCase(caseName, newFilter, mode = "edit", start = '', end = ''): syslog.syslog("PCAP APP: applyFilterOnCase: "+caseName+" started: "+str(datetime.datetime.now())) IDs = SQLHelper.getCaseAndFilterIDs(caseName) if IDs is None: return caseID = IDs[0] filterID = IDs[1] conn = sqlite3.connect(DATABASE) conn.execute('pragma foreign_keys=ON') if mode == "edit": if filterID: q = conn.execute("UPDATE FILTERS SET CONTENT = ?, START_DATETIME = ?, END_DATETIME = ? WHERE FILTERS.ID = ?", (newFilter, start, end, filterID,)) #q = conn.execute("UPDATE FILTERS SET CONTENT = \'"+newFilter+"\', START_DATETIME = \'"+start+"\', end_DATETIME = \'"+end+"\' WHERE FILTERS.ID = "+str(filterID)) else: q = conn.execute("INSERT INTO FILTERS VALUES(null, ?, ?, ?)",(newFilter, start, end,)) q = conn.execute('SELECT max(ID) FROM FILTERS') filterID = q.fetchone()[0] q = conn.execute("UPDATE CASES SET FILTERID = ? WHERE CASES.ID = ?", (filterID, caseID)) q = conn.execute("SELECT FILENAME FROM FILES WHERE FILES.TYPE = ? AND FILES.CASEID = ?", ('origin', caseID,)) else: #q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?", (filterID,)) #currentFilter = q.fetchone() #currentFilter = currentFilter[0] #if newFilter: # newFilter = currentFilter + " && " + newFilter if currentFilter else newFilter #else: #newFilter = currentFilter q = conn.execute("UPDATE FILTERS SET CONTENT = ?, START_DATETIME = ?, end_DATETIME = ? WHERE FILTERS.ID = ?", (newFilter, start, end, filterID,)) q = conn.execute("SELECT FILENAME FROM FILES WHERE FILES.TYPE = ? AND FILES.CASEID = ?", ('filtered', caseID)) files = [] for row in q: files.append(CASES_DIR + caseName + PCAP_DIR + row[0]) conn.commit() conn.close() for file in files: filteredFileName = applyFilterOnFile(file, newFilter, caseName, override = True) if not os.path.isfile(CASES_DIR + caseName + PCAP_DIR + filteredFileName): f = open(CASES_DIR + caseName + PCAP_DIR + filteredFileName, 'w') f.write("") f.close() if mode == "append": os.rename(CASES_DIR + caseName + PCAP_DIR + filteredFileName, file) filteredFileName = os.path.basename(file) applyTimeFilterOnFile(CASES_DIR + caseName + PCAP_DIR + filteredFileName, caseName, start, end, override = True) helper.updateFile(CASES_DIR + caseName + PCAP_DIR + filteredFileName, caseName, filterID) helper.clearTmp(caseName) syslog.syslog("PCAP APP: applyFilterOnCase: "+caseName+" ended: "+str(datetime.datetime.now()))
def printPCAPs(case): #print all pcap files in this case PCAPs directory path = CASES_DIR + case.caseName + PCAP_DIR listDir = os.listdir(path) originFiles = SQLHelper.loadFiles(case.caseName, 'origin') filteredFiles = SQLHelper.loadFiles(case.caseName, 'filtered') tmpFiles = SQLHelper.loadFiles(case.caseName, 'tmp') print '<h2>Available PCAP files</h2>' formStr = '<form action="main.py" class="form-horizontal" method="post">' formStr += '<a data-toggle="collapse" href="#originFiles"><h3>Original files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+ORIGIN_DIR)+')</h3></a>' if originFiles else "" formStr += '<div id="originFiles" class="collapse">' formStr += '<table id="" class="display" cellspacing="0" width="100%">' formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>' originFiles.sort() for file in originFiles: info = helper.getReadableFileInfo(file,case.caseName) formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], info[4], info[5]) formStr += '</tbody></table></div>' formStr += '<a data-toggle="collapse" href="#filteredFiles"><h3>Filtered files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+PCAP_DIR)+')</h3></a>' if filteredFiles else "" formStr += '<div id="filteredFiles" class="collapse">' formStr += '<table id="" class="display" cellspacing="0" width="100%">' formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>' filteredFiles.sort() for file in filteredFiles: info = helper.getReadableFileInfo(file,case.caseName) sourceID = str(SQLHelper.getFileID(info[4],case.caseName)) + '.' if str(SQLHelper.getFileID(info[4],case.caseName)) != 'None' else '' formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5]) formStr += '</tbody></table></div>' tmpFiles.sort if tmpFiles: sourceID = str(SQLHelper.getFileID(info[4],case.caseName)) + '.' if str(SQLHelper.getFileID(info[4],case.caseName)) != 'None' else '' formStr += '<a data-toggle="collapse" href="#tmpFiles"><h3>Temporary files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+TMP_DIR)+')</h3></a>' formStr += '<div id="tmpFiles" class="collapse">' formStr += '<table id="" class="display" cellspacing="0" width="100%">' formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>' for file in tmpFiles: info = helper.getReadableFileInfo(file,case.caseName) formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5]) formStr += '</tbody></table></div>' formStr += '<div class="form-group"><div class="col-md-6">' formStr += '<input type="submit" class="btn btn-default pull-right" value="Select File" name="selectFile"/>' formStr += '<input type="submit" class="btn btn-default pull-left" name="clearTmp" value="Delete All Temporary Files"></div>' formStr += '</div>' formStr += '<input type="hidden" name="actions" value="deleteFile">' formStr += '<input type="hidden" name="pagesToRender" value="showFile"><input type="hidden" name="caseName" value="'+case.caseName+'">' formStr += '</form>' print formStr print '<hr/>'
def createStr_DropStr(self): s = "public static final String[] SQL_DELETE_ENTRIES = {\n" for table in self.tables: tmp = "\""+ table.createStr_DropStr() +"\"" s += SQLHelper.indent(tmp) +",\n" return s[0:-2] +"\n};"
def updateFileDescription(filePath, caseName, description): fileName = getDBNameFromPath(filePath) fileID = SQLHelper.getFileID(fileName, caseName) SQLHelper.updateFileDescription(fileID, description)
def printInputFilterForm(filePath, caseName): info = helper.getReadableFileInfo(helper.getDBNameFromPath(filePath), caseName) approximateTime = helper.getRenderGraphTime(os.path.getsize(filePath)) title = 'Approximate time to render graph is: ' + helper.getReadableTimeInfo(approximateTime) originFiles = SQLHelper.loadFiles(caseName, 'origin') filteredFiles = SQLHelper.loadFiles(caseName, 'filtered') tmpFiles = SQLHelper.loadFiles(caseName, 'tmp') filesApproxStr = generateFilesAproximateTimeString(originFiles, caseName) filesApproxStr += generateFilesAproximateTimeString(filteredFiles, caseName) filesApproxStr += generateFilesAproximateTimeString(tmpFiles, caseName) options = '<optgroup label="Original files">' for file in originFiles: options += ' <option value="'+file+'">'+file+'</option>' options += '</optgroup>' options += '<option data-divider="true"></option>' options += '<optgroup label="Filtered files">' for file in filteredFiles: options += ' <option value="'+file+'">'+file+'</option>' options += '</optgroup>' options += '<option data-divider="true"></option>' options += '<optgroup label="Temporary files">' for file in tmpFiles: options += ' <option value="'+file+'">'+file+'</option>' allFiles = originFiles + filteredFiles + tmpFiles table = '<div id="files" class="collapse">' table += '<table id="" class="display" cellspacing="0" width="100%">' table += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>' for file in allFiles: info = helper.getReadableFileInfo(file, caseName) sourceID = str(SQLHelper.getFileID(info[4],caseName)) + '.' if str(SQLHelper.getFileID(info[4],caseName)) != 'None' else '' table += '<tr><th>%s</th><th><div class="checkbox"><label><input type="checkbox" value="%s" name="additionalFiles" id="additionalFiles"><b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]), file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5]) table += '</tbody></table></div>' info = helper.getReadableFileInfo(helper.getDBNameFromPath(filePath), caseName) options += '</optgroup>' print '<h2>File Details</h2>' formStr = '<form action="main.py" class="form-horizontal" method="post">' formStr += '<div class="form-group"><label class="col-md-2">Current filter on File:</label>' formStr += '<p class="col-md-10 form-control-static">'+info[0]+'</p></div>' formStr += '<div class="form-group"><label class="col-md-2">Size:</label>' formStr += '<p class="col-md-10 form-control-static">'+info[1]+'</p></div>' formStr += '<div class="form-group"><label class="col-md-2">First Packet:</label>' formStr += '<p class="col-md-10 form-control-static">'+info[2]+'</p></div>' formStr += '<div class="form-group"><label class="col-md-2">Last Packet:</label>' formStr += '<p class="col-md-10 form-control-static">'+info[3]+'</p></div>' formStr += '<div class="form-group"><label class="col-md-2">Source File:</label>' formStr += '<p class="col-md-10 form-control-static">'+info[4]+'</p></div>' formStr += '<div class="form-group"><label class="col-md-2">Description:</label>' formStr += '<div class="col-md-4"><input type="text" class="form-control" name="description"value="%s" /></div>' % info[5] formStr += '<input type="hidden" name="pagesToRender" value="showFile">' formStr += '<input type="hidden" name="caseName" value="'+caseName+'"/>' formStr += '<input type="hidden" name="filePath" value="'+filePath+'"/>' formStr += '<input type="hidden" name="actions" value="editDescription"/>' formStr += '<input type="submit" class="btn btn-default" value="Edit" name="Edit"/></div></form>' formStr += '<h2>Graph settings</h2>' formStr += '<form action="main.py" class="form-horizontal" method="post">' formStr += '<div class="form-group"><label class="col-md-2">Time window:</label>' formStr += '<div class="col-md-2"><label>From:</label><input type="text" title="Enter date and time in format: YYYY-MM-DD HH:MM:SS" class="form-control" value="%s"name="start"/></div>' % (info[2]) formStr += '<div class="col-md-2"><label>To:</label><input type="text" title="Enter date and time in format: YYYY-MM-DD HH:MM:SS" class="form-control" value="%s"name="end"/></div><div class="col-md-4">*This condition applies on ALL selected files.</div></div>' % (info[3]) formStr += '<div class="form-group"><label class="col-md-2">Files to compare:</label>' formStr += '<div class="col-md-10"><a data-toggle="collapse" href="#files">Show/Hide</a></div></div>' formStr += '<div class="form-group"><div class="col-md-12"> '+table+'</div></div>' formStr += '<div class="form-group"><label class="col-md-2">Edit filter:</label>' formStr += '<div class="col-md-4"><textarea class="form-control" name="filterContent"></textarea></div><div class="col-md-4">*This condition applies ONLY on primary selected file.</div></div>' formStr += '<div class="form-group"><label class="col-md-2">Graph time sample rate(s):</label>' formStr += '<div class="col-md-4"><input type="text" title="Enter amount of seconds is one time tick in graph. To low value can cause text label colisions. If empty, default value will be used." class="form-control" name="xtics"/></div></div>' formStr += '<div class="form-group"><div class="col-md-6">' formStr += '<input type="submit" class="btn btn-default pull-right" title="'+title+'"value="Render Graph" name="renderGraph" id="renderGraph" onclick="var t = getSumTime(\''+filesApproxStr+'\','+str(approximateTime)+');startProgresBar(t);">' formStr += '<input type="submit" class="btn btn-default pull-right" title="'+title+'"name="renderDetailedGraph" value="Render Detailed Graph" name="renderDetailedGraph" id="renderDetailedGraph" onclick="var t = getSumTime(\''+filesApproxStr+'\','+str(approximateTime)+');startProgresBar(t);"></div></div>' formStr += '<input type="hidden" name="actions" value="applyFilter">' formStr += '<input type="hidden" name="pagesToRender" value="showFile:showGraph">' formStr += '<input type="hidden" name="caseName" value="'+caseName+'">' formStr += '<input type="hidden" name="filePath" value="'+filePath+'">' formStr += '</form>' print formStr print '<hr/>' print htmlGen.generateProgresBar()
from SQLHelper import * # import module in different directory import os, sys sys.path.append(os.path.abspath('..')) import csv_helper as csvh import helper_format as hf if __name__ == "__main__": # Create engine object and Session class (as properties of SQLHelper) sqlHelper = SQLHelper() sqlHelper.create_engine() sqlHelper.create_Session_class()