def normalize_feature(): feature_vectors = DBUtil.select_all_features() size = len(feature_vectors) feature_vectors = numpy.asarray(feature_vectors) min = numpy.min(feature_vectors, axis=0) max = numpy.max(feature_vectors, axis=0) normalized_vectors = numpy.divide(feature_vectors - min, max - min) normalized_vectors = normalized_vectors.tolist() mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 1 for normalized_vector in normalized_vectors: if index % 100 == 0: print str(index) + " of " + str(size) # visitDuration, numIP, bytesTransfer, urlLength, numLogs2PerIP, numErrorPerIP, numLogsSecure, unsafeChar cur.execute( "INSERT INTO Normalized_Features (id, visitDuration, numIP, bytesTransfer, urlLength, numLogs2PerIP, numErrorPerIP, numLogsSecure, unsafeChar) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)", (index, normalized_vector[0], normalized_vector[1], normalized_vector[2], normalized_vector[3], normalized_vector[4], normalized_vector[5], normalized_vector[6], normalized_vector[7])) mysql_conn.commit() index = index + 1 cur.close() mysql_conn.close()
def delete(self): self.pre_delete and self.pre_delete() pk = self.__primary_key__.name args = (getattr(self, pk), ) DBUtil.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args) return self
def simple_features(): log_list = DBUtil.select_all_logs() count_dict = DBUtil.select_log_count_by_ip() mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 0 for log in log_list: index = index + 1 if index % 1000 == 0: print index visitDuration = int(log.time_taken_ms) bytesTransfer = int(log.sc_bytes) + int(log.cs_bytes) urlLength = 0 space_index = log.cs_uri.find(" ") if space_index != -1: urlLength = len(log.cs_uri[space_index:]) if log.c_ip == "": numIP = 0 else: numIP = count_dict[log.c_ip] cur.execute( "INSERT INTO Features (id, c_ip, visitDuration, numIP, bytesTransfer, urlLength) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE visitDuration=VALUES(visitDuration), numIP=VALUES(numIP), bytesTransfer=VALUES(bytesTransfer), urlLength=VALUES(urlLength)", (log.id, log.c_ip, visitDuration, numIP, bytesTransfer, urlLength)) mysql_conn.commit() cur.close() mysql_conn.close()
def main(): SHARE_Q = queue.Queue() # r = requests.get(const.METADATA_URL_PREFIX) # x = json.loads(r.text) # dataid = x['result'] # count = 1 conn = DBUtil.createConnection() # for s in dataid: # DBUtil.insertDataSetID(conn, s) # logger.debug(str(count) + " _ " + s) # count = count +1 count = 1 dataid2 = DBUtil.getNotProcessedDataSet(conn) # for s in dataid2: # logger.debug(str(count) + " _ " + s.package_name) # logger.debug(str(count) + " _ " + s) # count = count +1 DBUtil.closeConnection(conn) fetchers = [] if (config.fetcher_num > 0): for i in range(config.fetcher_num): fetchers.append(Fetcher(SHARE_Q, i, dataid2)) fetchers[i].start()
def simple_features(): log_list = DBUtil.select_all_logs() mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 0 for log in log_list: index = index + 1 if index % 1000 == 0: print index visitDuration = int(log.time_taken_ms) bytesTransfer = int(log.sc_bytes) * int(log.cs_bytes) seriousError = 1 if int(log.sc_status) >= 400 else 0 list1 = ["javascript", "expression", "applet", "meta", "xml", "blink", "link", "style", "script", "object", "iframe", "frame", "frameset", "ilayer", "layer", "bgsound", "title", "base", "vbscript", "embed", "`", "--", ";", "or", "select", "union", "insert", "update", "replace", "truncate", "delete", "sp", "xp", "system(", "eval(", "http:", "https:", "ftp:", "ptp:", "data:", "../", "..\\", "jsessionid", "login.jsp?userid", "login.jsp?password"] tags = uri_contain_keyword(log.cs_uri, list1) cur.execute( "INSERT INTO Features (id, c_ip, visitDuration, bytesTransfer, seriousError, tags) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE visitDuration=VALUES(visitDuration), bytesTransfer=VALUES(bytesTransfer), seriousError=VALUES(seriousError), tags=VALUES(tags)", (log.id, log.c_ip, visitDuration, bytesTransfer, seriousError, tags)) mysql_conn.commit() cur.close() mysql_conn.close()
def run(self): # Download info is from DB # issue #22 if self.queue is None: conn = DBUtil.createConnection() row = self.getResourceFromDB(conn) while row is not None: self.process(conn, row) row = self.getResourceFromDB(conn) DBUtil.closeConnection(conn) return # Download info from shared queue while True: if not self.queue.empty(): # Get metadata item from queue, and execute download logic item = self.queue.get() # Get the flag to check whether the download task is OK or not self.count = self.count + 1 logger.info("Thread {" + str(threading.get_ident()) + "} has processed " + str(self.count) + " metadata") try: logger.info("Thread {" + str(threading.get_ident()) + "} start download " + item.getResourceID()) self.download_flag = item.download() except Exception as e: logging.exception( str(threading.get_ident()) + " download " + item.getResourceID() + " ERROR!!!")
def tags2(): logs = DBUtil.select_all_logs() list2 = ["javascript", "expression", "applet", "meta", "xml", "blink", "link", "style", "script", "object", "iframe", "frame", "frameset", "ilayer", "layer", "bgsound", "title", "base", "vbscript", "embed", "`", "--", ";", "or", "select", "union", "insert", \ "update", "replace", "truncate", "delete", "sp", "xp", "system(", "eval(", "ftp:", "ptp:", "data:", "../", "..\\", "jsessionid", "login.jsp?userid", "login.jsp?password"] ip_dict = {} # index = 0 print len(logs) for log in logs: # index = index + 1 # if index % 1000 == 0: # print index tags2 = uri_contain_keyword(log.cs_uri, list2) if tags2 == 1: count = ip_dict.get(log.c_ip, 0) count = count + 1 ip_dict[log.c_ip] = count print len(ip_dict) index = 0 mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() for ip, count in ip_dict.iteritems(): index = index + 1 if index % 10 == 0: print index cur.execute("UPDATE Features SET tags2=%s WHERE c_ip=%s", (count, ip)) mysql_conn.commit() cur.close() mysql_conn.close()
def process(self, conn, row): r = row.nameresult() # update processed to True update_str = "UPDATE resource_metadata set processed=TRUE WHERE id = '{}'".format( r.id) try: conn.query(update_str) except: logging.exception("Update error !!") # Download data status = self.download(r) # if download complete, remove from DB if status == 200: del_str = "Delete From resource_metadata WHERE id = {}".format( r.id) try: conn.query(del_str) except: logging.exception("Delete error !!") # write result to DB now = datetime.datetime.now() timeStr = (now - datetime.timedelta(seconds=config.update_interval_sec) ).strftime('%Y-%m-%d %H:%M:%S') DBUtil.insertDownloadResult(conn, r.package_name, r.resource_id, status, timeStr)
def simple_features(): log_list = DBUtil.select_all_logs() mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 0 for log in log_list: index = index + 1 if index % 1000 == 0: print index visitDuration = int(log.time_taken_ms) bytesTransfer = int(log.sc_bytes) * int(log.cs_bytes) seriousError = 1 if int(log.sc_status) >= 400 else 0 tags = access_tag(log.cs_uri) cur.execute( "INSERT INTO Features (id, c_ip, visitDuration, bytesTransfer, seriousError, tags) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE visitDuration=VALUES(visitDuration), bytesTransfer=VALUES(bytesTransfer), seriousError=VALUES(seriousError), tags=VALUES(tags)", (log.id, log.c_ip, visitDuration, bytesTransfer, seriousError, tags)) mysql_conn.commit() cur.close() mysql_conn.close()
def normalize_feature(): feature_vectors = DBUtil.select_all_features() feature_vectors = numpy.asarray(feature_vectors) avg = numpy.mean(feature_vectors) std = numpy.std(feature_vectors, axis=0) normalized_vectors = (feature_vectors - avg) / std normalized_vectors = normalized_vectors.tolist() mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 1 for normalized_vector in normalized_vectors: if index % 100 == 0: print index cur.execute( "INSERT INTO Normalized_Features (id, visitDuration, bytesTransfer, numLogs2PerIP, numErrorPerIP, numLogsSecure, seriousError, tags, tags2) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)", (index, normalized_vector[0], normalized_vector[1], normalized_vector[2], normalized_vector[3], normalized_vector[4], normalized_vector[5], normalized_vector[6], normalized_vector[7])) mysql_conn.commit() index = index + 1 cur.close() mysql_conn.close()
def first_run_history2(self): logger.debug(str(self.fetcher_id) + " _ "+str(len(self.dataid))) self.timeStr = self.buildQueryTimeStr() data_count = 0 conn = None for index in range(self.fetcher_id, len(self.dataid), config.fetcher_num): meta = Metadata.getMetaData(self.dataid[index], self.timeStr) conn = DBUtil.createConnection() DBUtil.UpdateDataSetToProcessed(conn, self.dataid[index]) data_count = data_count + 1 # logger.debug("Fetcher {" + str(self.fetcher_id)+"} query {" + str(data_count) + "} data set " # + self.dataid[index] + # " has {" + str(len(meta)) + "} resource") logger.debug("Fetcher [{}] query [{}] dataset [{}] @ dataid[{}] + has [{}] resource" .format(str(self.fetcher_id), str(data_count), self.dataid[index], index, str(len(meta)))) for m in meta: DBUtil.InsertResourceURL(conn, m.getDataSetID(),m.getFileID() ,m.getDownloadURL(), m.getFormat()) logger.debug( "Fetcher {" + str(self.fetcher_id) + "} " + m.getDownloadURL() + " " + m.getFormat() + " " + m.getDataSetID() + " " + m.getFileID() + " " + m.getResourceID() ) DBUtil.closeConnection(conn)
def import_GPS_points(dbPath, path, i): """向数据库GPS表GPS_points_i导入数据, i为插入第i个表""" conn = DBUtil.get_conn(dbPath) # 一次性读入整个文件 pointsFile = open(path) try: allLines = pointsFile.readlines() finally: pointsFile.close() # 处理每一行 parameters = [] lineNum = 1 for line in allLines: line = line.strip('\n') lineList = line.split(',') lineTuple = (lineList[0], lineList[1], lineList[2], lineList[3], lineList[4]) parameters.append(lineTuple) lineNum += 1 print(len(parameters)) insertSql = 'insert into GPS_points_' + \ str(i) + '("id", "lat", "lon", "time_stamp", "mode")' + \ 'values (?,?,?,?,?)' DBUtil.insert(conn, insertSql, parameters) DBUtil.closeDB(conn)
class OrderDaoImpl: def __init__(self): self.conn = DBUtil().openConnection() # save table info, return order_id def saveOrder(self, order): with self.conn: cur = self.conn.cursor() sql = " insert into OrderTbl(orderTime,userId,tableId,personNum)values(%s,%s,%s,%s) " # set arg value = [ order.getOrderTime(), order.getUserId(), order.getTableId(), order.getPersonNum() ] # execute cur.execute(sql, value); # return order_id sql2 = " select max(id) as id from OrderTbl " cur.execute(sql2) row = cur.fetchone() return row[0] #save order list def saveOrderDetail(self, od): with self.conn: cur = self.conn.cursor() sql = " insert into OrderDetailTbl(orderId,menuId,num,remark)values(%s,%s,%s,%s) " value = [ od.getOrderId(), od.getMenuId(), od.getNum(), od.getRemark() ] # execute cur.execute(sql, value); # using: update table status def updateTableStatus(self, table_id): with self.conn: cur = self.conn.cursor() sql = " update TableTbl set flag=1 where id = %s " cur.execute(sql, table_id) # empty: update table status def updateTableStatus2(self, order_id): with self.conn: cur = self.conn.cursor() sql = " update TableTbl set flag = 0 where id = " +\ " ( select tableId from OrderTbl where id = %s) " cur.execute(sql, order_id)
def __init__(self, parent=None): super(EditPictureAnswerForm, self).__init__(parent) self.m_db = DBUtil() self.setupUi(self) self.toolButton_da1.clicked.connect(self.AddPictureAnswer) self.pushButton_answer_zoomout_1.clicked.connect( self.PictureAnswerZoomout) self.pushButton_answer_zoomin_1.clicked.connect( self.PictureAnswerZoomin)
class EditPictureAnswerForm(QtWidgets.QWidget, Ui_AddPictureForm): childclicked = pyqtSignal(str) id = "" def __init__(self, parent=None): super(EditPictureAnswerForm, self).__init__(parent) self.m_db = DBUtil() self.setupUi(self) self.toolButton_da1.clicked.connect(self.AddPictureAnswer) self.pushButton_answer_zoomout_1.clicked.connect( self.PictureAnswerZoomout) self.pushButton_answer_zoomin_1.clicked.connect( self.PictureAnswerZoomin) def AddPictureAnswer(self): try: if self.id == "": reply = QMessageBox.information(self, "标题", "试题标识不能为空,请先添加试题!", QMessageBox.Yes) return else: fileName, filetype = QFileDialog.getOpenFileName( self, "QFileDialog.getOpenFileName()", "", "图片文件(*.jpg);") # 将选中的文件入库 self.m_db.WriteFileToBlob("TPDA", "XXFZK.TKB", "where ID='" + self.id + "'", fileName) self.m_db.UpdateTable( "update XXFZK.TKB set ZQDALX='图片' where ID='" + self.id + "'") # 显示图片 image = QPixmap() image.load(fileName) scene = QGraphicsScene() self.picture_answer_item = QGraphicsPixmapItem(image) scene.addItem(self.picture_answer_item) self.graphicsView_da1.setScene(scene) self.picture_answer_zoomscale = 1 except Exception as e: print("添加图片答案时发错误!" + e) def PictureAnswerZoomout(self): self.picture_answer_zoomscale = self.picture_answer_zoomscale + 0.05 if self.picture_answer_zoomscale >= 1.2: self.picture_answer_zoomscale = 1.2 self.picture_answer_item.setScale(self.picture_answer_zoomscale) def PictureAnswerZoomin(self): try: self.picture_answer_zoomscale = self.picture_answer_zoomscale - 0.05 if self.picture_answer_zoomscale <= 0: self.picture_answer_zoomscale = 0.2 self.picture_answer_item.setScale(self.picture_answer_zoomscale) except: print("缩小图片答案1时发生错误")
def getStartEndPoint(DBPath, i): """ get the segment start GPS point id, end GPS point id, the number of segment GPS points, such as: [[1,67,56],...] means point 1~point 67 have 56 GPS points. start id: 1742 end id: 2250 num: 509 start id: 4009 end id: 4728 num: 720 """ conn = DBUtil.get_conn(DBPath) # find all points fetchAllSql = 'select id,point_label ' + \ 'from GPS_points_' + str(i) allRecords = DBUtil.fetchAll(conn, fetchAllSql) if allRecords is None: print('fetch point set Fail!') return """ records: type list-> [(1, 'non-walk-point').....] (id, point_label) id: 0 point_label: 1 """ # print(allRecords) displayPoint = [] count = 1 index = 1 pre = allRecords[0] temp = [] totalTraNum = 0 while index < len(allRecords): if index == len(allRecords) - 1: temp.append(allRecords[index][0]) temp.append(count - 1) cur = allRecords[index] if pre[1] == 'none' and cur[1] != 'none': temp.append(pre[0]) if pre[1] != 'none' and cur[1] == 'none': temp.append(pre[0]) temp.append(count - 1) print('start id: ' + str(temp[0]) + ' end id: ' + str(temp[1]) + ' num: ' + str(count - 1)) displayPoint.append(temp) temp.clear() count = 0 totalTraNum += 1 pre = cur count += 1 index += 1 # output the last segment print('start id: ' + str(temp[0]) + ' end id: ' + str(temp[1]) + ' num: ' + str(count - 1)) print('total segment number: ' + str(totalTraNum)) DBUtil.closeDB(conn)
def OnAdd(self, event): dlg = self.dialog(self) if dlg.ShowModal() == wx.ID_OK: try: DBUtil.insert(self.tablename, dlg.get_data()) self.refreshData() except Exception as exp: wx.MessageBox(exp.message + ",\nAdd failed", R.String.TITLE_FAILURE) dlg.Destroy()
def initUi(self): # 获取显示器分辨率 self.desktop = QApplication.desktop() self.screenRect = self.desktop.screenGeometry() self.height = self.screenRect.height() self.width = self.screenRect.width() self.resize(self.width, self.height) self.setWindowTitle("数据编辑") self.mainLayout = QFormLayout() self.setLayout(self.mainLayout) self.inputControls = [] self.m_db = DBUtil()
def run(self): start_ts = time.time() wx.CallAfter(self.window.UpdateStatus, "Connecting...") conn = DBUtil.connect(self.__conn_str) wx.CallAfter(self.window.UpdateStatus, "Querying...") desc, rows = DBUtil.fetch_table(conn, self.__query_str) wx.CallAfter(self.window.UpdateStatus, "Done") end_ts = time.time() wx.CallAfter(self.window.StopQuery, desc, rows, end_ts - start_ts)
def OnDelete(self, event): row = self.lstData.GetRow() result = wx.MessageBox("Are you sure to delete {0}".format(row), R.String.TITLE_DELETE, wx.CENTER | wx.YES_NO | wx.ICON_QUESTION, self) if result == wx.YES: try: DBUtil.delete(self.tablename, row) self.refreshData() except Exception as exp: wx.MessageBox(exp.message + ",\nDelete failed", R.String.TITLE_FAILURE)
def OnDump(self, event): dlg = wx.FileDialog(self, message="Dump", wildcard="*.sql", style=wx.FD_SAVE | wx.FD_OVERWRITE_PROMPT) if dlg.ShowModal() == wx.ID_OK: path = dlg.GetPath() try: DBUtil.dump(path, self.tablename, self.lstData.Dump()) except Exception as exp: wx.MessageBox(exp.message + ",\nDump failed", R.String.TITLE_FAILURE) dlg.Destroy()
def OnExport(self, event): dlg = wx.FileDialog(self, message="Export", wildcard="*.csv", style=wx.FD_SAVE | wx.FD_OVERWRITE_PROMPT) if dlg.ShowModal() == wx.ID_OK: path = dlg.GetPath() try: DBUtil.save_table(self.tablename, path) except Exception as exp: wx.MessageBox(exp.message + ",\nExport failed", R.String.TITLE_FAILURE) dlg.Destroy()
def restoreSimulation(sim_id, problem_id, junction_id=None, lat=None, long=None): db = DBUtil().getDatabase() logging.info('Source simulation: ' + str(source_id) + ' has exec id') setvar = { 'duration': 0, 'running': False, 'problem_id': problem_id, 'current_exec_id': sim_record.get('current_exec_id') } if junction_id is not None: setvar[junctions] = [junction_id] if lat is not None: setvar[latitude] = lat setvar[longitude] = long db.simulation.update({"_id": sim_id}, { '$set': setvar, '$unset': { 'current_exec_id': "" } })
def getSimulationHistoryTableDB(userid): db = DBUtil().getDatabase() grouprow = db.student.find_one({"_id": userid}, { 'group_id': 1, 'master_group_id': 1 }) mode = getUserModeDB(userid) logging.info('group data: ' + str(grouprow)) return db.simulation.find({ '$and': [{ "status": 'SAVED' }, { "mode": mode }, { '$or': [{ '$and': [{ "shared_with": 'GROUP' }, { "master_group_id": grouprow['master_group_id'] }] }, { '$and': [{ "shared_with": 'NONE' }, { "user_id": userid }] }] }] })
def getUserModeDB(userid): db = DBUtil().getDatabase() usermoderow = db.user_studentrole.find_one({'user_id': userid}) if usermoderow.get('user_mode') is None: return 'GROUP' else: return usermoderow['user_mode']
def persistQueueingData(sim_exec_id): # work around for invalid xml generate in sumo version 0.17.1 f = open(QUEUE_OUT_FILE, 'r') outfile = open(QUEUE_OUT_FILE + ".cleaned", 'w') for line in f: linestring = line.rstrip('\n') if(linestring == "/>"): outfile.write(" </lanes>\n") elif(linestring != ">"): outfile.write(line) outfile.flush(); f.close() outfile.close() db = DBUtil().getDatabase() tree = ElementTree() tree.parse(QUEUE_OUT_FILE + ".cleaned") steps = tree.findall('data') for step in steps: lanes = step.findall('lanes/lane') if lanes is not None: for lane in lanes: db.simulation_queue_data.insert({ "sim_exec_id": sim_exec_id, "lane_id": lane.attrib['id'], "timestep": float(step.attrib['timestep']), "queueingtime": float(lane.attrib['queueing_time']), #store in feet instead of meters "queueinglength": convertMeterToFeet(float(lane.attrib['queueing_length'])) })
def py_main(): data = DBUtil.get_email_data() email_address = data[1] email_password = data[2] print(data) robot_send_email(email_address) # 发送邮件 print("已发送邮件") emailcode = robot_get_emailcode(email_address, email_password) # 获取邮箱验证码 print("邮箱验证码:" + emailcode) WebTools.register(email_address, emailcode) # 网站注册 print("注册成功") content = WebTools.login_to_user(email_address) # 登录并进入用户中心 print("登陆成功") my_vmess = Spider.change_vmess( Spider.get_vmess(content)) # 获取机场地址并改为我们的机场地址 print("机场已就绪,随时可以转发!!!") return my_vmess
def setMaxSimulationStepRateDB(simulation_id): db = DBUtil().getDatabase() db.simulation.update({ "_id": simulation_id, }, {"$set": { "update_rate": 0 }})
def __init__(self): self.url = "https://fe-api.zhaopin.com/c/i/sou?start=%s&pageSize=90&cityId=489&workExperience=-1&education=-1&companyType=-1&employmentType=-1&jobWelfareTag=-1&kw=python&kt=3&_v=0.37997175&x-zp-page-request-id=99cb08a8ab3446338433325c01713a96-1558357664883-153990" self.header = { "user-agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.110 Safari/537.36" } self.db = DBUtil.MysqlUtil()
def createDefaultVehicleData(simulation_id): db = DBUtil().getDatabase() db.vehicle.insert({ "simulation_id": simulation_id, "name": "Car", "accel": "20", "decel": "30", "sigma": "1", "max_speed": "100", "length": "10", "probability": "0.5" }) db.vehicle.insert({ "simulation_id": simulation_id, "name": "Bus", "accel": "15", "decel": "25", "sigma": "1", "max_speed": "70", "length": "15", "probability": "0.3" }) db.vehicle.insert({ "name": "Truck", "simulation_id": simulation_id, "accel": "10", "decel": "15", "sigma": "1", "max_speed": "50", "length": "20", "probability": "0.2" })
def assignVMForSimulationID(sim_id): db = DBUtil().getDatabase() vm_details = db.virtualmachine.find_one({"status": 'AVAIL'}) present_time = datetime.datetime.now() if vm_details is None: # no VM is available, check for stale VM logging.info('no VM is available, checking for stale VM...') stale_time = present_time - datetime.timedelta(minutes=15) vm_details = db.virtualmachine.find_one({ "sim_start_time": { "$lt": stale_time }, 'status': 'IN_USE' }) if vm_details is not None: logging.info('Found Usable VM with ID: ' + vm_details["_id"]) db.virtualmachine.update({'_id': vm_details["_id"]}, { '$set': { 'status': 'IN_USE', 'simulation_id': ObjectId(sim_id), 'sim_start_time': present_time } }) vm_details = getAssignedVMDetailsForSimID(sim_id) return vm_details
class UserDaoImpl: def __init__(self): self.conn = DBUtil().openConnection() def login(self, account, password): # 查询SQL语句 sql = " select id,account,password,name,permission,remark "+\ " from UserTbl "+\ " where account=%s and password=%s " with self.conn: cur = self.conn.cursor() values = [ account, password ] # 执行查询 cur.execute(sql, values) row = cur.fetchone() Id = row[0] name = row[3] permission = row[4] remark = row[5] # 封装用户信息 u = User() u.setId(Id) u.setAccount(account) u.setPassword(password) u.setName(name) u.setPermission(permission) u.setRemark(remark) return u return None
def copySimulation(destination_id, source_id): db = DBUtil().getDatabase() sim_record = db.simulation.find_one({"_id": source_id}, {"_id": 0}) if sim_record.get('current_exec_id') is not None: logging.info('Source simulation: ' + str(source_id) + ' has exec id') db.simulation.update({"_id": destination_id}, { '$set': { 'duration': 0, 'running': False, 'problem_id': sim_record['problem_id'], 'current_exec_id': sim_record.get('current_exec_id') } }) else: logging.info('Source simulation: ' + str(source_id) + ' does not have exec id, unsetting it in destination: ' + str(destination_id)) db.simulation.update({"_id": destination_id}, { '$set': { 'running': False, 'problem_id': sim_record['problem_id'] }, '$unset': { 'current_exec_id': "" } })
def saveSimulationDataWithUserInput(simulation_id, label, shared_with, user_id): db = DBUtil().getDatabase() existingLabel = db.simulation.find_one({ "label": label, "status": "SAVED", }) if existingLabel is not None: return {'response': {'status': "exists"}} sim_record = db.simulation.find_one({ "_id": simulation_id, }, {"_id": 0}) sim_record['shared_with'] = shared_with if shared_with == 'NONE': sim_record['user_id'] = user_id elif shared_with == 'ALL': colab_group_record = db.studentgroup.find_one( { "_id": sim_record['group_id'], }, { "_id": 0, "colab_group_id": 1 }) sim_record["colab_group_id"] = colab_group_record["colab_group_id"] sim_record['status'] = 'SAVED' sim_record['label'] = label sim_record['backuptime'] = datetime.now() sim_record['old_id'] = simulation_id sim_record['backedup_by'] = user_id id = db.simulation.insert(sim_record) return {'response': {'status': "success", 'id': id}}
def persistTripData(sim_exec_id): db = DBUtil().getDatabase() tree = ElementTree() tree.parse(TRIP_OUT_FILE) trips = tree.findall('tripinfo') route_tree = ElementTree() route_tree.parse(ROUTE_OUT_FILE) routes = route_tree.findall('vehicle') for trip in trips: vehicle_route = [] for route in routes: if route.attrib['id'] == trip.attrib['id']: edge_string = route.find('route').attrib['edges'] vehicle_route = edge_string.split() break db.simulation_trip_data.insert({ "sim_exec_id": sim_exec_id, "vehicle_id": trip.attrib['id'], "departstep": float(trip.attrib['depart']), "duration": float(trip.attrib['duration']), "routelength": convertMeterToFeet(float(trip.attrib['routeLength'])), "waittime": float(trip.attrib['waitSteps']), "speed": convertMperSecToMPH(float(trip.attrib['routeLength']) / float(trip.attrib['duration'])), "type": trip.attrib['vType'], "route": vehicle_route })
def finishSimulationDB(simulation_id): db = DBUtil().getDatabase() db.simulation.update({ "_id": simulation_id, }, {"$set": { "running": False }})
def persistInductionData(sim_exec_id): db = DBUtil().getDatabase() tree = ElementTree() tree.parse(INDUCTION_OUT_FILE) intervals = tree.findall('interval') for interval in intervals: vehiclecount = int(interval.attrib['nVehContrib']) if vehiclecount != 0: # valid data xmlloop_id = interval.attrib['id'] intervals_with_type = interval.findall('typedInterval') for interval_with_type in intervals_with_type: type_vehicle_count = int(interval_with_type.attrib['nVehContrib']) if type_vehicle_count != 0: db.simulation_induction_data.insert({ "sim_exec_id": sim_exec_id, "induction_id": interval_with_type.attrib['id'], "begintime": float(interval_with_type.attrib['begin']), "endtime": float(interval_with_type.attrib['end']), "vehicletype": interval_with_type.attrib['type'], "count": type_vehicle_count })
def getAssignedVMDetailsForSimID(sim_id): db = DBUtil().getDatabase() vm_details = db.virtualmachine.find_one({ "simulation_id": sim_id, "status": 'IN_USE' }) return vm_details
class CheckTableDaoImpl: def __init__(self): self.conn = DBUtil().openConnection() # 获得餐桌列表 def getTableList(self): # 查询SQL语句 sql =" select num,flag from TableTbl" with self.conn: cur = self.conn.cursor() cur.execute(sql) # 获得预定义语句 rows = cur.fetchall() result = [] for row in rows: num = row[0] flag = row[1] ct = CheckTable() ct.setFlag(flag) ct.setNum(num) result.append(ct) return result return None
def unsafeChar(): log_list = DBUtil.select_all_logs() log_dict = {} unsafechar_value_dict = {} index = 0 total_log_num = len(log_list) for log in log_list: index = index + 1 if index % 1000 == 0: print str(index) + " of " + str(total_log_num) unsafechar_value = scanSafeChar(log.cs_uri) dict = log_dict.get(log.c_ip, {}) dict[log.id] = unsafechar_value log_dict[log.c_ip] = dict agg_value = unsafechar_value_dict.get(log.c_ip, 0) unsafechar_value_dict[log.c_ip] = agg_value + unsafechar_value mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 0 for c_ip, dict in log_dict.iteritems(): agg_value = unsafechar_value_dict.get(c_ip) for log_id, unsafechar_value in dict.iteritems(): if unsafechar_value > 0: index = index + 1 if index % 10 == 0: print str(index) + " of " + str(total_log_num) cur.execute("UPDATE Features SET unsafechar=%s WHERE id=%s", (agg_value, log_id)) mysql_conn.commit() cur.close() mysql_conn.close()
def mysql_connect_test(): mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() cur.execute("SELECT * FROM LOG LIMIT 1") for row in cur: print row cur.close() mysql_conn.close()
def vectorize(): db = DBUtil.initDB() emails = db.get_all_brushed_emails() sentences = LabeledLineSentence(emails) model = Doc2Vec(min_count=1, window=10, size=100, sample=1e-4, negative=5, workers=8) model.build_vocab(sentences.to_array()) for epoch in range(10): # @UnusedVariable model.train(sentences.sentences_perm()) model.save(model_file)
class UnionTableDaoImpl: def __init__(self): self.conn = DBUtil().openConnection() def getTableList(self): # 查询SQL语句 sql =" select id,num from TableTbl where flag = 1 " with self.conn: cur = self.conn.cursor() cur.execute(sql) # 判断订单详细 result = [] rows = cur.fetchall() for row in rows: # 获得菜单信息 Id = row[0] num = row[1] ut = UnionTable() ut.setId(Id) ut.setNum(num) result.append(ut) return result return None def updateStatus(self, tableId1, tableId2): with self.conn: cur = self.conn.cursor() prepare = "new_proc" values = [ tableId1, tableId2 ] cur.callproc(prepare, values)
def __init__(self, **kwargs): assert "eventName" not in kwargs assert "eventTime" not in kwargs assert "id" not in kwargs #TODO: check for other reserved words too? assert "key" not in self.__dict__ self.eventName = self.__class__.__name__ self.eventTime = int(DBUtil.get_current_gmtime()) if len(kwargs) <= 0: return for key, value in kwargs.iteritems(): assert hasattr(self, key) assert not _isCallable(getattr(self, key)) setattr(self, key, value)
def numLogs2PerIP(): threshold = 1.5 log_list = DBUtil.select_all_logs() numLogs2PerIP_list = [] mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() index = 0 for log in log_list: index = index + 1 if index % 100 == 0: print index lower_bound = log.log_time - datetime.timedelta(0, threshold) upper_bound = log.log_time + datetime.timedelta(0, threshold) cur.execute('SELECT COUNT(*) FROM Cleaned_Log WHERE c_ip=%s AND (log_time BETWEEN %s AND %s)', (log.c_ip, lower_bound, upper_bound)) rows = cur.fetchall() for row in rows: numLogs2PerIP_list.append([log.id, row[0]]) for numLogs2PerIP in numLogs2PerIP_list: cur.execute( "INSERT INTO Features (id, numLogs2PerIP) VALUES (%s, %s) ON DUPLICATE KEY UPDATE numLogs2PerIP=VALUES(numLogs2PerIP)", (numLogs2PerIP[0], numLogs2PerIP[1])) mysql_conn.commit() cur.close() mysql_conn.close()
def _insert_row(self, cur, forcedTime=None): sql = "INSERT INTO %s" % (self._get_table_name()) keys = [] values = [] for key, value in self.__dict__.iteritems(): if _isCallable(value): continue if key == "eventName": continue if key == "eventTime": if forcedTime: value = forcedTime else: value = DBUtil.int_to_ctime(value) keys.append(key) values.append(value) sql = sql + " (%s) VALUES (%s)" % (", ".join(keys), ", ".join(["%s"]*len(keys))) return cur.execute(sql, values)
def insert(self, cur): #figure out what hour this event supposedly happened in: assert type(self.eventTime) == types.IntType, "eventTime must be an integer number of seconds!" exactTime = self.eventTime - (self.eventTime % _AGGREGATE_INTERVAL) exactTime = DBUtil.int_to_ctime(exactTime) #check if there is an existing row for this time and source: sql = "SELECT COUNT(*) FROM %s" % (self._get_table_name()) sql += " WHERE eventTime = %s and source = %s;" cur.execute(sql, (exactTime, self.source)) numRows = cur.fetchone()[0] if numRows > 0: assert numRows == 1, "Should never be multiple rows for the same time and source!" #update that row with the new count: sql = "UPDATE %s" % (self._get_table_name()) sql += " SET amount = amount + %s WHERE eventTime = %s and source = %s;" return cur.execute(sql, (self.amount, exactTime, self.source)) else: #insert a new row: return self._insert_row(cur, exactTime)
def insert_all_into_db(event_list): mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() error_count = 0 for event in event_list: try: cur.execute("INSERT INTO Log (log_time, s_sitename, s_computername, s_ip, cs_method, cs_uri_stem, cs_uri_query, s_port, cs_username, c_ip, cs_version, cs_user_agent, cs_cookie, cs_referer, cs_host, sc_status, sc_substatus, sc_win32_status, sc_bytes, cs_bytes, time_taken_ms) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (event.log_time, event.s_sitename, event.s_computername, event.s_ip, event.cs_method, event.cs_uri_stem, event.cs_uri_query, event.s_port, event.cs_username, event.c_ip, event.cs_version, event.cs_user_agent, event.cs_cookie, event.cs_referer, event.cs_host, event.sc_status, event.sc_substatus, event.sc_win32_status, event.sc_bytes, event.cs_bytes, event.time_taken_ms)) mysql_conn.commit() except Exception,e: info = sys.exc_info() print info[0],":",info[1] print e print traceback.format_exc() print event mysql_conn.rollback() error_count = error_count + 1
def numErrorPerIP(): mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() cur.execute( "SELECT * FROM (SELECT c_ip, COUNT(*) FROM Cleaned_Log WHERE sc_status>=400 or sc_substatus!=0 GROUP BY c_ip) AS TEMP_TABLE WHERE TEMP_TABLE.c_ip!=''") rows = cur.fetchall() total = len(rows) print total index = 0 for row in rows: index = index + 1 if index % 10 == 0: print index cur.execute("UPDATE Features SET numErrorPerIP=%s WHERE c_ip=%s", (row[1], row[0])) mysql_conn.commit() cur.close() mysql_conn.close()
class ChangeTableDaoImpl: def __init__(self): self.util = DBUtil() def changeTable(self, orderId, tableId): sql = " update TableTbl set flag = 0 where id = " +\ " (select tableId from OrderTbl as ot where ot.id = %s)" sql2 = " update OrderTbl set tableId = %s where id = %s " sql3 = " update TableTbl set flag = 1 where id = %s" try: conn = self.util.openConnection() conn.autocommit(False) cur = conn.cursor() # update table status cur.execute(sql, orderId) # update order values = [ tableId, orderId ] cur.execute(sql2, values) # update table status again cur.execute(sql3, tableId) #事务的特性1、原子性的手动提交 conn.commit() cur.close() conn.close() except mdb.Error, e: #如果出现了错误,那么可以回滚,就是上面的三条语句要么执行,要么都不执行 conn.rollback() print "Error %d: %s" % (e.args[0],e.args[1])
sentences = LabeledLineSentence(emails) model = Doc2Vec(min_count=1, window=10, size=100, sample=1e-4, negative=5, workers=8) model.build_vocab(sentences.to_array()) for epoch in range(10): # @UnusedVariable model.train(sentences.sentences_perm()) model.save(model_file) if not os.path.isfile(model_file): print("Model doesn't exist, vectorizing...") vectorize() model = Doc2Vec.load(model_file) db = DBUtil.initDB() emails = db.get_all_brushed_emails() train_arrays = [] train_labels = [] test_arrays = [] test_labels = [] for email in emails: email_id = email.id prefix_train_pos = 'email_' + str(email_id) if email_id % 5 != 0: train_arrays.append(model.docvecs[prefix_train_pos]) train_labels.append(int(email.is_scheduling)) else: test_arrays.append(model.docvecs[prefix_train_pos])
#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs(Host) sc-status sc-substatus sc-win32-status sc-bytes cs-bytes TimeTakenMS log = Log(date=csv_line[0], time=csv_line[1], s_sitename=csv_line[2], s_computername=csv_line[3], s_ip=csv_line[4], cs_method=csv_line[5], cs_uri=csv_line[6], s_port=csv_line[8], cs_username=csv_line[9], c_ip=csv_line[10], cs_version=csv_line[11], cs_user_agent=csv_line[12], cs_cookie=csv_line[13], cs_referer=csv_line[14], cs_host=csv_line[15], sc_status=csv_line[16], sc_substatus=csv_line[17], sc_win32_status=csv_line[18], sc_bytes=csv_line[19], cs_bytes=csv_line[20], time_taken_ms=csv_line[21]) log_list.append(log) print len(log_list) return log_list if __name__=="__main__": # Transform original log files to transformed_log.log # log_folder_path = "/Users/hli/Workspace/Webserver_Log_Data" # logs = read_original_log_file(log_folder_path) # transform_to_correct_format(logs, "/Users/hli/Workspace/Webserver_Log_Data/transformed_log.log") # Transform original log files to transformed_log.log # Save all logs to MySql logs = read_transformed_log_file("/Users/hli/Workspace/Webserver_Log_Data/transformed_log.log") DBUtil.insert_all_into_db(logs)
#-*-coding:utf-8-*- """ Description Created on 11/14/15 @author: Hui Li """ from random import randint import DBUtil if __name__=="__main__": log_count = DBUtil.select_log_count() # randon pick 45 logs and do injections ids = [] for i in range(0, 45): ids.append(randint(1, log_count + 1)) print ids # all random ids ids = [782646, 767402, 171206, 329690, 436102, 853455, 568442, 709924, 891034, 425445, 349647, 593099, 155354, 168514, 126931, 596548, 315300, 605358, 239982, 27978, 174194, 781837, 390737, 818783, 546082, 877560, 464808, 424398, 512913, 899854, 106033, 33012, 342636, 674911, 39162, 794296, 901763, 823964, 749323, 691157, 740987, 292924, 430652, 814821, 718571] # actually used ids # ids = [782646, 767402, 171206, 329690, 436102, 853455, 568442, 709924, 891034, 425445, 349647, 593099, 27978, 174194, 781837, 390737, 818783, 546082, 877560, 464808, 424398, 512913, 899854, 106033, 33012, 342636, 674911, 39162, 794296, 901763, 823964, 749323, 691157, 740987, 292924, 430652, 814821, 718571] print ids[0] log = DBUtil.select_one_log(ids[0]) log.s_sitename = "DRUPAL"
class UpdateDaoImpl: def __init__(self): self.conn = DBUtil().openConnection() # 获得菜单列表 def getMenuList(self): # 查询SQL语句 sql =" select id,type,price,name,pic,remark from MenuTbl " with self.conn: cur = self.conn.cursor() # 执行查询 cur.execute(sql) # 判断订单详细 rows = cur.fetchall() result = [] for row in rows: # 获得菜单信息 Id = row[0] typeId = row[1] price = row[2] name = row[3] pic = row[4] remark = row[5] m = Menu() m.setId(Id) m.setName(name) m.setPic(pic) m.setPrice(price) m.setRemark(remark) m.setTypeId(typeId) result.append(m) return result return None # 获得餐桌列表 def getTableList(self): # 查询SQL语句 sql =" select id,num,description from TableTbl " with self.conn: cur = self.conn.cursor() # 执行查询 cur.execute(sql) # 判断订单详细 rows = cur.fetchall() result = [] for row in rows: # 获得菜单信息 Id = row[0] num = row[1] desc = row[2] t = Table() t.setId(Id) t.setNum(num) t.setDesc(desc) result.append(t) return result return None
#encoding=utf-8 import DBUtil import MySQLdb traningClassifications =['businise','PE','health','travel'] trainingCount=0 map={} contentMap={} connection = DBUtil.getConnection(); sql = "select type from list"; cursor = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) cursor.execute(sql) result = cursor.fetchall() for s in result: print s['type'] cursor.close() DBUtil.closeConnection(connection) for type in traningClassifications: connection = DBUtil.getConnection(); sql="select count(sample.id) from sample,list where sample.id=list.id and list.type=%s"; cursor = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) cursor.execute(sql,type) result = cursor.fetchall() map[type]=result[0]['count(sample.id)'] trainingCount+=result[0]['count(sample.id)'] cursor.close() DBUtil.closeConnection(connection)
def numLogs2PerIP(threshold): mysql_conn = DBUtil.get_mysql_conn() cur = mysql_conn.cursor() query = ("SELECT * FROM Cleaned_Log ORDER BY log_time") cur.execute(query) rows = cur.fetchall() # inverted index # log.c_ip, ([log ids], [all the logs generated by this ip]) log_dict = {} print len(rows) index = 0 for row in rows: index = index + 1 if index%10==0: print str(index) + " of " + str(len(rows)) log = Log(date=None, time=None, id=row[0], datetime_obj=row[1], s_sitename=row[2], s_computername=row[3], s_ip=row[4], cs_method=row[5], cs_uri=row[6], s_port=row[7], cs_username=row[8], c_ip=row[9], cs_version=row[10], cs_user_agent=row[11], cs_cookie=row[12], cs_referer=row[13], cs_host=row[14], sc_status=row[15], sc_substatus=row[16], sc_win32_status=row[17], sc_bytes=row[18], cs_bytes=row[19], time_taken_ms=row[20]) list = log_dict.get(log.c_ip, [[],[]]) list[0].append(log.id) list[1].append(log) log_dict[log.c_ip] = list print len(log_dict) time_interval = datetime.timedelta(0, threshold) index = 0 for ip, list in log_dict.iteritems(): index = index + 1 if index%10==0: print str(index) + " of " + str(len(log_dict)) # log_ip: count count_dict = dict.fromkeys(list[0], 0) log_list = list[1] for i in range(0, len(log_list)): target_log = log_list[i] for j in range(i + 1, len(log_list)): scan_log = log_list[j] if scan_log.log_time - target_log.log_time <= time_interval: count_dict[target_log.id] = count_dict[target_log.id] + 1 count_dict[scan_log.id] = count_dict[scan_log.id] + 1 else: break for log_id, count in count_dict.iteritems(): cur.execute( "INSERT INTO Features (id, numLogs2PerIP) VALUES (%s, %s) ON DUPLICATE KEY UPDATE numLogs2PerIP=VALUES(numLogs2PerIP)", (log_id, count)) mysql_conn.commit() cur.close() mysql_conn.close()
def synchronize(self, conn): cur = conn.cursor() if not DBUtil.table_exists(cur, self.tableName): self._create_table(cur) self._check_table_columns(cur) conn.commit()
def insert_session_into_table(session_threshold): mysql_conn = DBUtil.get_mysql_conn() user_list = [] cur = mysql_conn.cursor() cur.execute("SELECT * FROM users") rows = cur.fetchall() for row in rows: user = User(c_ip=row[0], cs_user_agent=row[1]) user_list.append(user) print "users:" + str(len(user_list)) session_list = [] user_count = 1 for user in user_list: cur.execute("SELECT * FROM LOG WHERE c_ip=%s and cs_user_agent=%s ORDER BY log_time ASC", (user.c_ip, user.cs_user_agent)) rows = cur.fetchall() session = Session(user.c_ip, user.cs_user_agent, session_threshold) if user_count % 10 == 0: print user_count user_count = user_count + 1 for row in rows: event = Event(date=None, time=None, s_sitename=row[1], s_computername=row[2], s_ip=row[3], cs_method=row[4], cs_uri_stem=row[5], cs_uri_query=row[6], s_port=row[7], cs_username=row[8], c_ip=row[9], cs_version=row[10], cs_user_agent=row[11], cs_cookie=row[12], cs_referer=row[13], cs_host=row[14], sc_status=row[15], sc_substatus=row[16], sc_win32_status=row[17], sc_bytes=row[18], cs_bytes=row[19], time_taken_ms=row[20], datetime_obj=row[0]) if session.is_in_this_session(event): session.add_event(event) else: session_list.append(session) # start new session session = Session(user.c_ip, user.cs_user_agent, session_threshold) session.add_event(event) print "sessions: " + str(len(session_list)) session_id = 1 for session in session_list: for event in session.event_list: try: cur.execute("INSERT INTO Sessions (session_id, log_time, s_sitename, s_computername, s_ip, cs_method, cs_uri_stem, cs_uri_query, s_port, cs_username, c_ip, cs_version, cs_user_agent, cs_cookie, cs_referer, cs_host, sc_status, sc_substatus, sc_win32_status, sc_bytes, cs_bytes, time_taken_ms) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (session_id, event.log_time, event.s_sitename, event.s_computername, event.s_ip, event.cs_method, event.cs_uri_stem, event.cs_uri_query, event.s_port, event.cs_username, event.c_ip, event.cs_version, event.cs_user_agent, event.cs_cookie, event.cs_referer, event.cs_host, event.sc_status, event.sc_substatus, event.sc_win32_status, event.sc_bytes, event.cs_bytes, event.time_taken_ms)) mysql_conn.commit() except Exception,e: info = sys.exc_info() print info[0],":",info[1] print e print traceback.format_exc() mysql_conn.rollback() session_id = session_id + 1
def __init__(self): self.conn = DBUtil().openConnection()
db = DBSCAN().fit(feature_vectors) cluster_labels = db.labels_ result_folder_path = "../../results/dbscan/" if not os.path.isdir(result_folder_path): os.makedirs(result_folder_path) with open(result_folder_path + "dbscan.txt", "w") as output: for label in cluster_labels: output.write(str(label) + "\n") if __name__ == "__main__": normalized_vectors = DBUtil.select_all_normalized_features() normalized_vectors = numpy.asarray(normalized_vectors) print "normalized_vectors:" + str(len(normalized_vectors)) for k in range(2, 101): print k kmeans_clustering(normalized_vectors, k) # for W in range(20, 60, 10): # print W # single_linkage(normalized_vectors, W) # _DBSCAN(normalized_vectors)