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