From d391c4ca17a32c672b6aa65bb586d73eee4b48fc Mon Sep 17 00:00:00 2001 From: Zachary Sloan Date: Thu, 21 Feb 2013 16:26:22 -0600 Subject: Adding genotypes and new_genotypes (json files) directories to git so they can be pulled to the new EC2 server --- web/genotypes/process.py | 170 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 170 insertions(+) create mode 100755 web/genotypes/process.py (limited to 'web/genotypes/process.py') diff --git a/web/genotypes/process.py b/web/genotypes/process.py new file mode 100755 index 00000000..96cbc9b4 --- /dev/null +++ b/web/genotypes/process.py @@ -0,0 +1,170 @@ +#!/usr/bin/python +import sys +import os +import string +import reaper +import MySQLdb +""" +def swap(A, B): + temp = A + A = B + B = A + +fp = open('ColXCvi.txt') +fout = open('ColXCvi2.txt', "wb") +line = fp.readline() +i = 0 +while line: + line2 = map(string.strip, string.split(line.strip())) + print len(line2) + if i == 0: + line2 = line2[:7] + map(lambda X:'ColXCvi' + X, line2[7:]) + X = line2[1] + line2[1] = line2[2] + line2[2] = X + line2 = line2[:5] + line2[7:] + fout.write(string.join(line2[1:], "\t") + "\n") + line = fp.readline() + i += 1 + +fout.close() +fp.close() +""" +""" +try: + #import getpass + #passwd = getpass.getpass('Please enter mysql password here : ') + con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron") + cursor = con.cursor() + print "You have successfully connected to mysql.\n" +except: + print "You entered incorrect password.\n" + sys.exit(0) + +for geno in ("ColXBur.geno", "ColXCvi.geno"): + a = reaper.Dataset() + a.read(geno) + #print a.prgy, len(a.prgy) + for item in a.prgy: + cursor.execute("insert into Strain(Name, SpeciesId) values(%s, 3)", item) + +cursor.close() + +try: + #import getpass + #passwd = getpass.getpass('Please enter mysql password here : ') + con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron") + cursor = con.cursor() + print "You have successfully connected to mysql.\n" +except: + print "You entered incorrect password.\n" + sys.exit(0) +cursor.execute("select max(Id)+1 from Geno") +maxId = cursor.fetchone()[0] + +for geno in ("ColXBur.geno", "ColXCvi.geno"): + a = reaper.Dataset() + a.read(geno) + #print a.prgy, len(a.prgy) + for chr in a: + for locus in chr: + print geno, locus.name, chr.name, locus.Mb + try: + cursor.execute("insert into Geno(Id, Name, Chr, MB_UCSC, chr_num, Source, Source2) values(%s, %s, %s, %s, %s, %s, %s)", (maxId, locus.name, chr.name, locus.Mb, chr.name, "Institute for Agronomical Research", "Institute for Agronomical Research")) + maxId += 1 + except: + pass +cursor.close() +mysql> select max(Id) from Data; ++----------+ +| max(Id) | ++----------+ +| 22163234 | ++----------+ +1 row in set (0.00 sec) + +mysql> select max(DataId) from GenoXRef; ++-------------+ +| max(DataId) | ++-------------+ +| 16098047 | ++-------------+ +1 row in set (0.04 sec) + +mysql> select * from Strain order by Id desc limit 5; ++------+------------+-----------+--------+ +| Id | Name | SpeciesId | Symbol | ++------+------------+-----------+--------+ +| 1828 | ColXCvi499 | 3 | NULL | +| 1827 | ColXCvi497 | 3 | NULL | +| 1826 | ColXCvi496 | 3 | NULL | +| 1825 | ColXCvi495 | 3 | NULL | +| 1824 | ColXCvi494 | 3 | NULL | ++------+------------+-----------+--------+ + +""" + +""" +try: + #import getpass + #passwd = getpass.getpass('Please enter mysql password here : ') + con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron") + cursor = con.cursor() + print "You have successfully connected to mysql.\n" +except: + print "You entered incorrect password.\n" + sys.exit(0) + +freezeId = 14 +for geno in ("ColXBur.geno", "ColXCvi.geno"): + a = reaper.Dataset() + a.read(geno) + strainIds = [] + for strain in a.prgy: + cursor.execute("select Id from Strain where Name = '%s' and SpeciesId=3" % strain) + strainIds.append(cursor.fetchone()[0]) + for chr in a: + for locus in chr: + cursor.execute("select max(Id)+1 from Data") + dataId = cursor.fetchone()[0] + cursor.execute("select Id from Geno where Name = '%s'" % locus.name) + GenoId = cursor.fetchone()[0] + #print geno, locus.name, chr.name, locus.Mb, dataId, GenoId + for i, item in enumerate(locus.genotype): + cursor.execute("insert into Data values(%s, %s, %s)" ,(dataId, strainIds[i], item)) + cursor.execute("insert into GenoXRef values(%s, %s, %s)" , (freezeId, GenoId, dataId)) + freezeId -= 1 +cursor.close() +""" + +try: + #import getpass + #passwd = getpass.getpass('Please enter mysql password here : ') + con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron") + cursor = con.cursor() + print "You have successfully connected to mysql.\n" +except: + print "You entered incorrect password.\n" + sys.exit(0) + +freezeId = 14 +for geno in ("ColXBur.geno", "ColXCvi.geno"): + values = [-1, 1, 0, 0] + if geno == "ColXBur.geno": + strains = ["Col-0", "Bur-0", "ColXBurF1", "BurXColF1"] + else: + strains = ["Col-0", "Cvi", "ColXCviF1", "CviXColF1"] + strainIds = [] + for strain in strains: + cursor.execute("select Id from Strain where Name = '%s' and SpeciesId=3" % strain) + strainIds.append(cursor.fetchone()[0]) + print strainIds + cursor.execute("select DataId from GenoXRef where GenoFreezeId = %d" % freezeId) + results = cursor.fetchall() + for dataId in results: + for i, strainId in enumerate(strainIds): + #print "insert into Data values(%s, %s, %s)" % (dataId[0], strainId, values[i]) + cursor.execute("insert into Data values(%s, %s, %s)" ,(dataId[0], strainId, values[i])) + freezeId -= 1 +cursor.close() + -- cgit v1.2.3