#!/usr/bin/python
import MySQLdb,dbcnf,sys,pgdb
db=pgdb.connect(database='eth',user=dbcnf.dbuser,password=dbcnf.dbpw,host='localhost')
dbc=db.cursor()
db_mysql=MySQLdb.connect(user=dbcnf.dbuser,passwd=dbcnf.dbpw,db='homo_sapiens_variation_57_37b')
dbc_mysql=db_mysql.cursor()
dbc.execute("""CREATE TABLE deep.ensvar (id SERIAL PRIMARY KEY, chromo TEXT,
position INTEGER, reference CHAR, mutated CHAR, frequency FLOAT)""")
dbc_mysql.execute('SELECT variation_id, allele, frequency FROM allele')
for n,(variation_id, allele, frequency) in enumerate(dbc_mysql.fetchall()):
 dbc_mysql.execute('SELECT ancestral_allele FROM variation WHERE variation_id=%s',(variation_id,))
 if not dbc_mysql.rowcount: continue
 ancestral_allele,=dbc_mysql.fetchone()
 if not allele or not ancestral_allele: continue
 if len(allele)>1 or len(ancestral_allele)>1: continue
 dbc_mysql.execute("""SELECT seq_region_id, seq_region_start, seq_region_end, seq_region_strand 
 FROM variation_feature WHERE variation_id=%s""",(variation_id,))
 if not dbc_mysql.rowcount: continue
 seq_region_id, seq_region_start, seq_region_end, seq_region_strand=dbc_mysql.fetchone()
 if seq_region_start!=seq_region_end: continue
 dbc_mysql.execute('SELECT name FROM seq_region WHERE seq_region_id=%s',(seq_region_id,))
 if not dbc_mysql.rowcount: continue
 seq_region_name,=dbc_mysql.fetchone()
 tp=seq_region_name,seq_region_start,allele
 dbc.execute('SELECT COUNT(*) FROM deep.ensvar WHERE chromo=%s AND position=%s AND mutated=%s',tp)
 if dbc.fetchone()[0]: continue
 dbc.execute("""INSERT INTO deep.ensvar (chromo, position, reference, mutated, frequency) VALUES 
 (%s, %s, %s, %s, %s)""",(seq_region_name, seq_region_start, ancestral_allele, allele,frequency) )
 if not n % 1000 and n: db.commit()
db.commit()