def initDB(dbPath, clear=True): # Initialize the database if clear and os.path.exists(dbPath): print "Removing existing database", dbPath os.remove(dbPath) if not os.path.exists(os.path.dirname(dbPath)): os.makedirs(os.path.dirname(dbPath)) con = DB.connect(dbPath) for tableName in sorted(settings.CGI_TABLES.keys()): columns = getColumns(tableName) table = settings.CGI_TABLES[tableName] con.execute(DB.defineSQLTable(tableName, columns, table.get("primary_key", None))) if "indices" in table: DB.addIndices(con, tableName, table["indices"]) return con
def makeCountTables(filename): con = DB.connect(filename) con.execute(""" CREATE TABLE disease AS SELECT hugo_gene_symbol, matched_disease_term, nci_disease_concept_code, organism, COUNT(*) AS term_count FROM sentence WHERE matched_disease_term IS NOT NULL GROUP BY hugo_gene_symbol, matched_disease_term, nci_disease_concept_code, organism ORDER BY hugo_gene_symbol; """) DB.addIndices(con, "disease", ["hugo_gene_symbol"]) con.execute(""" CREATE TABLE drug AS SELECT hugo_gene_symbol, drug_term, nci_drug_concept_code, organism, COUNT(*) AS term_count FROM sentence WHERE drug_term IS NOT NULL GROUP BY hugo_gene_symbol, drug_term, nci_drug_concept_code, organism ORDER BY hugo_gene_symbol; """) DB.addIndices(con, "drug", ["hugo_gene_symbol"]) con.commit() con.close()