#!/usr/bin/python
# -*- coding: utf-8 -*-
#title           :SQL_resultat.py
#description     :
#author          :Damien CHOISNE
#date            :03/15/2012
#version         :1.0
#usage           :Construire une base de données SQL
#notes           :
#python_version  :2.7.2
#==============================================================================
import subprocess
import sys
import os
import MySQLdb as mdb
from Bio import SeqIO

Nom_Orga = ""

con = mdb.connect('localhost','root','metaviro','test')

cur = con.cursor()
cur.execute("SELECT Orga_ID, Nom_Orga FROM Orga")
fetch_Orga_ID = cur.fetchall()

dico_Orga = {}
for i in fetch_Orga_ID :
    dico_Orga.setdefault(i[0], []).append(i[1]) # dico du style {ID:Nom_Orga}

for key, value in dico_Orga.iteritems():
    Nom_Orga = dico_Orga[key]
    print Nom_Orga
    cur = con.cursor()
    cur.execute("SELECT CDS.CDS_ID FROM CDS, Orga WHERE CDS.Orga_ID=Orga.Orga_ID  AND Orga.Orga_ID = "+str(key)+"")
    liste_cdsID_Orga = cur.fetchall()
#   print liste_cdsID_Orga

    for CDS_ID in liste_cdsID_Orga :
        cur = con.cursor()
        cur.execute("SELECT Liste_dinucl.dinucl, dinucl.Occurrence FROM Liste_dinucl INNER JOIN dinucl ON Liste_dinucl.dinucl_ID = dinucl.dinucl_ID WHERE dinucl.CDS_Id = '"+str(CDS_ID)+"' ")

        fetch_OligoOccurence = cur.fetchall() #on recupere les occurences des oligo

        longueur_sequence = ''
        cur = con.cursor()
        cur.execute("SELECT CDS.len_seq FROM CDS WHERE CDS.CDS_ID = '"+str(CDS_ID)+"'")
        longeur_seq = cur.fetchall()
        print longeur_seq
        longueur_sequence = longeur_seq[0][0] # on recupere la longueur du cds

        dico_cds_OligoOccurence = {}
        for i in fetch_OligoOccurence :
            dico_cds_OligoOccurence.setdefault(i[0], []).append(i[1]) #on le transforme en dico

        for key, value in dico_cds_OligoOccurence.iteritems():
            dico_cds_OligoOccurence[key][0]=round(float(dico_cds_OligoOccurence[key][0])/longueur_sequence,5) #on le passe en frequence avec 5 chiffre apres la virgule

        print "dico_apres", dico_cds_OligoOccurence