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