def wsq_to_txt(table_name, date): """ Vuelca en tmp/dumped.txt el resultado de la query a la BD squidlogs """ if(table_name == 'visited'): query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \ "f_lang_id, f_ns_id, count(*) " + \ "from Filtered where f_action_id is null " + \ "and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \ "group by date(f_date_time), f_lang_id, f_ns_id;" elif(table_name == 'saved'): query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \ "f_lang_id, f_ns_id, count(*) " + \ "from Filtered where f_action_id = 2 " + \ "and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \ "group by date(f_date_time), f_lang_id, f_ns_id;" elif(table_name == 'actions'): query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \ "f_action_id, f_lang_id, f_ns_id, count(*) " + \ "from Filtered where f_action_id in (0, 1, 3, 4) " + \ "and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \ "group by date(f_date_time), f_action_id, f_lang_id, f_ns_id;" log_msg4("Creando dump para " + table_name) exec_mysql(getConfig().db_name_squidlogs, query=query, dumped=True) log_msg_ok4()
def create_tables(): """ Crea nuevas tablas VISITEDYYYY, SAVEDYYYY, ACTIONSYYYY """ log_msg4("No hay tablas para el año " + txt_year + ". Creando") create_table('visited') create_table('saved') create_table('actions') global new_tables_created new_tables_created = True log_msg_ok4()
def txt_to_table(table_name): """ Populo la tabla con los datos del _result.txt """ table_name_year = get_table_name_year(table_name) # si el fichero no tiene nada no hay nada que pasar a la BD if is_empty(TXT_FILE): log_msg4("WARNING: No se populó la tabla. dump.txt vacío!!") return # http://stackoverflow.com/questions/3971541/what-file-and-directory-permissions-are-required-for-mysql-load-data-infile # http://www.markhneedham.com/blog/2011/01/18/mysql-the-used-command-is-not-allowed-with-this-mysql-version/ query = "LOAD DATA LOCAL INFILE '" + TXT_FILE + "' INTO TABLE " + \ table_name_year + " IGNORE 1 LINES;" log_msg4("Volcando sobre " + table_name_year) exec_mysql(DB_NAME, query=query, options=['local-infile']) log_msg_ok4()