createDbCursor.execute("ALTER TABLE snp DISABLE trigger ALL;")
createDbCursor.execute("ALTER TABLE locus DISABLE trigger ALL;")
createDbCursor.close()
# Dictionaries and arrays for SQL and MongoDB queries
snpInserts = {} # Dictionary for rsid/insert for SNP data
lociInserts = [] # Array for loci insert queries
rsidList = {} # Dictionary of RSIDs that will also hold the
# primary key for each SNP in SQL
# Load each chromosome into database
for curChr in chromosomes:
result = Result()
result.method = "pgsql"
result.tag = tag
print "Chromosome " + str(curChr)
result.chromosome = str(curChr)
# Set file paths for current chromosome
curSnpFilePath = snpFilePath.format(curChr)
curLociFilePath = lociFilePath.format(curChr)
if len(path) > 0:
curSnpFilePath = path.rstrip('\\').rstrip('/') + '\\' + curSnpFilePath
curLociFilePath = path.rstrip('\\').rstrip('/') + '\\' + curLociFilePath
# Clear dictionaries for loading multiple chromosomes
snpInserts.clear()
lociInserts = []
rsidList.clear()
# Open results file, print headers
resultsFileName = 'qresults-pgsql-nosql'
if resultsFileName != "":
resultsFileName += '-' + tag
resultsFileName += '.txt'
resultsFile = open(resultsFileName, 'w')
result = Result()
resultsFile.write(result.toHeader() + '\n')
# Create pgsql connection
postgresConnection = psycopg2.connect("dbname=" + databaseName + " user="******"ACSL6","ZDHHC8","TPH1","SYN2","DISC1","DISC2","COMT","FXYD6","ERBB4","DAOA","MEGF10","SLC18A1","DYM","SREBF2","NXRN1","CSF2RA","IL3RA","DRD2"]
for z in range(1,11):
for g in genes:
result = Result()
result.method = "pgsql-jsonb-QrySet" + str(z)
result.tag = tag + "-" + g + "/" + str(z)
print "Running queries: " + g + "/" + str(z)
sys.stdout.flush()
qryStart = time.time()
cursor.execute('SELECT count(*) FROM snp WHERE jsondata->\'loci\' @> \'[{"gene":"' + g + '"}]\'')
qryEnd = time.time()
result.qryByGene = qryEnd-qryStart
resultsFile.write(result.toString() + '\n')
print "Run complete!"
