Python DBUtil类代码示例

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 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 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()

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)  

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

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