def filter_tables_by_topic(topic_id, tables, order): """ Filter tables on topic. :param topic_id: Topic id. :param tables: List of tables. :return: Tables that belong to topic. """ if tables is None or len(tables) == 0: return tables tables_str = "'" + "','".join(tables) + "'" query = "SELECT a.nome from tabelle a join argomenti_tabelle b \n" query += "on (b.id = a.id) " query += "WHERE b.argomento=%d " % topic_id query += "and a.nome IN (%s) \n" % tables_str if not order is None: query += "ORDER BY %s" % order new_tables = [] rows = execute_query_on_main_db(query) if rows is not None: for row in rows: table_name = row[0] new_tables.append(table_name) return new_tables
def execute_topics_query(): """ Execute a query that returns the topics. :return: The rows result set. """ query = "SELECT * FROM argomenti" rows = execute_query_on_main_db(query) return rows
def get_topic_id(table): """ Get the topic number id of the selected table. :param table: Table name. :return: The topic id. """ query = "SELECT b.argomento from tabelle a join argomenti_tabelle b " query += "on (b.id = a.id) WHERE a.nome='%s'" % table rows = execute_query_on_main_db(query) if rows is not None: for row in rows: return row[0] return 999
def get_topic_description(table): """ Get the topic description of the selected table. :param table: Table name. :return: The topic description. """ query = "SELECT c.descrizione " query += "FROM tabelle a, argomenti_tabelle b, argomenti c " query += "WHERE b.id = a.id and a.nome='%s' " \ "and c.argomento=b.argomento" % table rows = execute_query_on_main_db(query) if rows is not None: for row in rows: return "%s" % row[0] return ""
def build_topics_dict(tables): """ Build a dictionary with key table name and as value topic. :param tables: :return: """ ret = dict() tables_s = "'" + "','".join(tables) + "'" query = "SELECT a.nome, b.argomento from tabelle \n" query += "a join argomenti_tabelle b \n" query += "on (b.id = a.id) WHERE a.nome IN (%s)" % tables_s rows = execute_query_on_main_db(query) if not rows is None: for row in rows: table_name = row[0] topic = row[1] ret[table_name] = topic return ret
def build_desc_to_code_mapping(fk): """ Build an hash table with key description and code as value. :param fk: Foreign key. :return: Dictionary <description, code>. """ ret = dict() table = fk[0] code_column = fk[1] desc_column = find_table_description_column(table) query = "SELECT %s, %s " % (code_column, desc_column) query += "FROM %s" % table rows = execute_query_on_main_db(query) if not rows is None: for row in rows: code = row[0] desc = row[1] ret[desc] = code return ret
def build_topics_counter_dict(tables): """ Build a dictionary with key topic_id and value the number of table belong to the topic. :return: Dictionary with key the topic id and as value the number of items in topic. """ table_names = "'" + "','".join(tables) + "'" topics_counter_dict = dict() query = "SELECT c.argomento, COUNT(*)\n" query += "FROM tabelle a, argomenti_tabelle b, argomenti c\n" query += "WHERE b.id = a.id and c.argomento=b.argomento\n" query += "and a.nome IN(%s)" % table_names query += "GROUP BY c.argomento" rows = execute_query_on_main_db(query) if rows is not None: for row in rows: topics_counter_dict[row[0]] = row[1] return topics_counter_dict