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()
