import x as ET
tree=ET.parse("example_thermo.xml")
elementroot=tree.getroot()
import sqlite3
import pandas as pd
db = sqlite3.connect('thermo.sqlite')
cursor = db.cursor()

cursor.execute("DROP TABLE IF EXISTS low")
cursor.execute("DROP TABLE IF EXISTS high")
cursor.execute("PRAGMA foreign_keys=1")
cursor.execute('''CREATE TABLE low (
               species_name TEXT PRIMARY KEY NOT NULL, 
               tlow INT, 
               thigh INT, 
               coeff_1 FLOAT,
               coeff_2 FLOAT,
               coeff_3 FLOAT,
               coeff_4 FLOAT,
               coeff_5 FLOAT,
               coeff_6 FLOAT,
               coeff_7 FLOAT)''')

cursor.execute('''CREATE TABLE high (
               species_name TEXT PRIMARY KEY NOT NULL, 
               tlow INT, 
               thigh INT, 
               coeff_1 FLOAT,
               coeff_2 FLOAT,
               coeff_3 FLOAT,
               coeff_4 FLOAT,
               coeff_5 FLOAT,
               coeff_6 FLOAT,
               coeff_7 FLOAT)''')

elements=elementroot.find('phase').find("speciesArray").text.strip().split(" ")


speciesroot=elementroot.find("speciesData")
for specie in speciesroot:
    species_name=specie.attrib['name']
    C=specie.find("thermo").findall("NASA")
    for i,cc in enumerate(C):
        coefficients=cc.find("floatArray").text.strip().split(" ")
        thigh=cc.attrib['Tmax']
        tlow=cc.attrib['Tmin']
        coeff_1=coefficients[0]
        coeff_2=coefficients[1] 
        coeff_3=coefficients[2]
        coeff_4=coefficients[3]
        coeff_5=coefficients[4]
        coeff_6=coefficients[5]
        coeff_7=coefficients[6]
        
        vals_to_insert = (species_name, tlow, thigh, 
                           coeff_1, coeff_2, coeff_3, coeff_4, coeff_5, coeff_6, coeff_7)

        if i==0:
            cursor.execute('''INSERT INTO low (species_name, tlow, thigh, 
                           coeff_1, coeff_2, coeff_3, coeff_4, coeff_5, coeff_6, coeff_7) 
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', vals_to_insert)
        else:
            cursor.execute('''INSERT INTO high (species_name, tlow, thigh, 
                           coeff_1, coeff_2, coeff_3, coeff_4, coeff_5, coeff_6, coeff_7) 
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', vals_to_insert)
        

db.commit()

high_cols = [col[1] for col in cursor.execute("PRAGMA table_info(HIGH)")]
query = '''SELECT * FROM HIGH'''
viz_tables(high_cols, query)

"""
Explanation: Homework 10: SQL
Due Date:  Thursday, November 16th at 11:59 PM
You will create a database of the NASA polynomial coefficients for each specie.
Please turn in your database with your Jupyter notebook!
Question 1: Convert XML to a SQL database
Create two tables named LOW and HIGH, each corresponding to data given for the low and high temperature range.
Each should have the following column names:

SPECIES_NAME
TLOW
THIGH
COEFF_1
COEFF_2
COEFF_3
COEFF_4
COEFF_5
COEFF_6
COEFF_7

Populate the tables using the XML file you created in last assignment. If you did not complete the last assignment, you may also use the example_thermo.xml file.
TLOW should refer to the temperature at the low range and THIGH should refer to the temperature at the high range.  For example, in the LOW table, $H$ would have TLOW at $200$ and THIGH at $1000$ and in the HIGH table, $H$ would have TLOW at $1000$ and THIGH at $3500$.
For both tables, COEFF_1 through COEFF_7 should be populated with the corresponding coefficients for the low temperature data and high temperature data.
End of explanation
"""


def get_coeffs(species_name, temp_range):
    if temp_range=="low":
        function = '''SELECT coeff_1,coeff_2,coeff_3,coeff_4,coeff_5,coeff_6,coeff_7 FROM low WHERE species_name=?'''
        coeffs=cursor.execute(function,(species_name)).fetchone()
  
    else:
        function = '''SELECT coeff_1,coeff_2,coeff_3,coeff_4,coeff_5,coeff_6,coeff_7 FROM high WHERE species_name=?'''
        coeffs=cursor.execute(function,(species_name)).fetchone()

    return coeffs

get_coeffs("H","high")

def get_species(temp, temp_range):
    ''' Fill in here'''
    if temp_range=="low":
        
        function = '''SELECT species_name FROM low WHERE tlow<?'''
        species=cursor.execute(function,(temp_range,)).fetchall()

    else:
        function = '''SELECT species_name FROM high WHERE thigh>?'''
        species=cursor.execute(function,(temp_range,)).fetchall()
    return species

get_species("low",1)

all_cols = [col[1] for col in cursor.execute("PRAGMA table_info(ALL_TEMPS)")]
query = '''SELECT species_name FROM low WHERE tlow<?'''
viz_tables(all_cols,(query,(1,)))

"""
Explanation: Question 2: WHERE Statements

Write a Python function get_coeffs that returns an array of 7 coefficients.  

The function should take in two parameters: 1.) species_name and 2.) temp_range, an indicator variable ('low' or 'high') to indicate whether the coefficients should come from the low or high temperature range.
   The function should use SQL commands and WHERE statements on the table you just created in Question 1 (rather than taking data from the XML directly).
python
def get_coeffs(species_name, temp_range):
    ''' Fill in here'''
    return coeffs

Write a python function get_species that returns all species that have a temperature range above or below a given value. The function should take in two parameters: 1.) temp and 2.) temp_range, an indicator variable ('low' or 'high').

When temp_range is 'low', we are looking for species with a temperature range lower than the given temperature, and for a 'high' temp_range, we want species with a temperature range higher than the given temperature.
This exercise may be useful if different species have different LOW and HIGH ranges.
And as before, you should accomplish this through SQL queries and where statements.
python
def get_species(temp, temp_range):
    ''' Fill in here'''
    return coeffs
End of explanation
"""


def get_range(species_name):
    function = '''SELECT tlow FROM low WHERE species_name=?'''
    temp_low=cursor.execute(function, (species_name,)).fetchall()[0]

    function = '''SELECT thigh FROM high WHERE species_name=?'''
    temp_high=cursor.execute(function,(species_name,)).fetchall()[0]
    
    return (temp_low[0],temp_high[0])

get_range("HO2")

cursor.execute("DROP TABLE IF EXISTS all_temps")
cursor.execute('''CREATE TABLE all_temps(
               species_name TEXT PRIMARY KEY NOT NULL, 
               temp_low INT, 
               temp_high INT)''')

function = '''SELECT species_name FROM low'''
species=cursor.execute(function).fetchall()
for specie in species:
    temp_low,temp_high=get_range(specie[0])
    print(specie,temp_low,temp_high)
    cursor.execute('''INSERT INTO all_temps(species_name, temp_low, temp_high) 
                    VALUES(?, ?, ?)''',(specie[0],temp_low,temp_high))
    
    

cursor.execute("DROP TABLE IF EXISTS ALL_TEMPS")
cursor.execute('''
CREATE TABLE ALL_TEMPS AS
    SELECT HIGH.SPECIES_NAME, HIGH.THIGH AS TEMP_HIGH, LOW.TLOW AS TEMP_LOW
    FROM HIGH
        JOIN LOW
            ON HIGH.SPECIES_NAME = LOW.SPECIES_NAME''')
db.commit()

all_cols = [col[1] for col in cursor.execute("PRAGMA table_info(ALL_TEMPS)")]
query = '''SELECT * FROM ALL_TEMPS'''
viz_tables(all_cols, query)

"""
Explanation: Question 3: JOIN STATEMENTS
Create a table named ALL_TEMPS that has the following columns:

SPECIES_NAME
TEMP_LOW
TEMP_HIGH

This table should be created by joining the tables LOW and HIGH on the value SPECIES_NAME.

Write a Python function get_range that returns the range of temperatures for a given species_name.

The range should be computed within the SQL query (i.e. you should subtract within the SELECT statement in the SQL query).
python
def get_range(species_name):
    '''Fill in here'''
    return range
Note that TEMP_LOW is the lowest temperature in the LOW range and TEMP_HIGH is the highest temperature in the HIGH range.
End of explanation
"""


