<jupyter_start><jupyter_text>Homework 10: `SQL` Due Date:  Thursday, November 16th at 11:59 PMYou 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.<jupyter_code>import sqlite3

from bs4 import BeautifulSoup
import numpy as np
import pandas as pd

conn = sqlite3.connect('HW10DB.sqlite')
cursor = conn.cursor()
cursor.execute('DROP TABLE IF EXISTS LOW')
cursor.execute('DROP TABLE IF EXISTS HIGH')
cursor.execute('DROP TABLE IF EXISTS ALL_TEMPS')

cursor.execute("""CREATE TABLE LOW (
               SPECIES_NAME TEXT, 
               TLOW REAL, 
               THIGH REAL, 
               COEFF_1 REAL, 
               COEFF_2 REAL, 
               COEFF_3 REAL, 
               COEFF_4 REAL, 
               COEFF_5 REAL, 
               COEFF_6 REAL, 
               COEFF_7 REAL)""")

cursor.execute("""CREATE TABLE HIGH (
               SPECIES_NAME TEXT, 
               TLOW REAL, 
               THIGH REAL, 
               COEFF_1 REAL, 
               COEFF_2 REAL, 
               COEFF_3 REAL, 
               COEFF_4 REAL, 
               COEFF_5 REAL, 
               COEFF_6 REAL, 
               COEFF_7 REAL)""")

soup = BeautifulSoup(open('nasa_coeff.xml', 'r').read(), 'xml')


def viz_tables (cols, query):
    """Prints SQL query and displays DataFrame created from queried records."""
    q = cursor.execute(query).fetchall()
    series_list = []
    for i, col_name in enumerate(cols):
        series_list.append((col_name, [col[i] for col in q]))
    df = pd.DataFrame.from_items(series_list)
    print(df)


def get_low_high_temp_nodes (nasa_nodes):
    max0 = float(nasa_nodes[0].attrs['Tmax'])
    max1 = float(nasa_nodes[1].attrs['Tmax'])
    if max1 > max0:
        return nasa_nodes[0], nasa_nodes[1]
    return nasa_nodes[1], nasa_nodes[0]


def parse_coeffs_from_tag (floatArray):
    result = floatArray.split(',')
    result = [i.strip() for i in result]
    result = [float(i) for i in result]
    return result


def add_row_to_table (nasa, species, table, cursor):
    """Adds row in table for species represented in <NASA> element."""
    query = "INSERT INTO {} (SPECIES_NAME, TLOW, THIGH, COEFF_1, COEFF_2, " \
            "COEFF_3, COEFF_4, COEFF_5, COEFF_6, COEFF_7) VALUES " \
            "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)".format(table)
    coeffs = parse_coeffs_from_tag(nasa.find('floatArray').string)
    vals = (species, float(nasa.attrs['Tmin']), float(nasa.attrs['Tmax']),
            coeffs[0], coeffs[1], coeffs[2], coeffs[3], coeffs[4], coeffs[5],
            coeffs[6])
    cursor.execute(query, vals)


for species in soup.find('speciesData').find_all('species'):
    name = species.attrs['name']
    nasa_nodes = species.find('thermo').find_all('NASA')
    low_node, high_node = get_low_high_temp_nodes(nasa_nodes)
    add_row_to_table(low_node, name, 'LOW', cursor)
    add_row_to_table(high_node, name, 'HIGH', cursor)

conn.commit()<jupyter_output><empty_output><jupyter_text>Question 2: `WHERE` Statements 1. 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).```pythondef get_coeffs(species_name, temp_range):    ''' Fill in here'''    return coeffs```2. 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.```pythondef get_species(temp, temp_range):    ''' Fill in here'''    return coeffs```<jupyter_code>def get_coeffs(species_name, temp_range):
    if temp_range.lower() == 'high': table = 'HIGH'
    elif temp_range.lower() == 'low': table = 'LOW'
    else: raise ValueError('Invalid temp_range.')

    query = 'SELECT COEFF_1, COEFF_2, COEFF_3, COEFF_4, COEFF_5, COEFF_6, ' \
            'COEFF_7 FROM {0} ' \
            'WHERE SPECIES_NAME = ?'.format(table)

    coeffs = cursor.execute(query, (species_name,)).fetchone()
    return coeffs

def get_species(temp, temp_range):
    """Returns set containing unique species given temp conditions."""
    if temp_range.lower() == 'low':
        query_low = 'SELECT SPECIES_NAME FROM LOW WHERE THIGH < ?'
        query_high = 'SELECT SPECIES_NAME FROM HIGH WHERE THIGH < ?'
    elif temp_range.lower() == 'high':
        query_low = 'SELECT SPECIES_NAME FROM LOW WHERE TLOW > ?'
        query_high = 'SELECT SPECIES_NAME FROM HIGH WHERE TLOW > ?'
    else:
        raise ValueError('Invalid temp_range.')

    species_low = cursor.execute(query_low, (temp,)).fetchall()
    species_high = cursor.execute(query_high, (temp,)).fetchall()
    
    # Unpack resulting lists of tuples.
    species_low = [i[0] for i in species_low]
    species_high = [i[0] for i in species_high]
    species_high.extend(species_low)
    return set(species_high)<jupyter_output><empty_output><jupyter_text>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`. 1. 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).```pythondef 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.<jupyter_code>join_query = 'CREATE TABLE ALL_TEMPS AS ' \
             'SELECT LOW.SPECIES_NAME, LOW.TLOW AS TEMP_LOW, HIGH.THIGH AS ' \
             'TEMP_HIGH ' \
             'FROM LOW ' \
             'JOIN HIGH ON LOW.SPECIES_NAME=HIGH.SPECIES_NAME'

cursor.execute(join_query)
conn.commit()
def get_range (species_name):
    query = 'SELECT (TEMP_HIGH - TEMP_LOW) FROM ALL_TEMPS ' \
            'WHERE SPECIES_NAME = ?'
    result = cursor.execute(query, (species_name,)).fetchone()
    return result[0]


# Ensure function works as expected.
o2_range = get_range('O2')
np.testing.assert_almost_equal(o2_range, 3300)
print('Range retrieved for O2 what was expected.')

conn.close()<jupyter_output>Range retrieved for O2 what was expected.
{'fraction_non_alphanumeric': 0.10803047333583114, 'fraction_numerical': 0.013363307106282004, 'mean_word_length': 3.4812534974818132, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 3}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '105325', 'license': 'MIT', 'n_tokens_mistral': 2606, 'n_tokens_neox': 2549, 'n_words': 836, 'path': 'homeworks/HW10/HW10-final.ipynb', 'repo_name': 'nate-stein/cs207_nate_stein', 'size': '8007'}