בניית פלטפורמת נתונים עם Flask, SQL ו-Redis

import redis

​

# Redis connection

redis_client = redis.StrictRedis(

    host='username.redis.cache.windows.net',

    port=6380,

    db=0,

    password='encryptedpasswordstring',

    ssl=True

)

@app.route('/')

def index():

    return render_template('index.html')

@app.route('/population-range', methods=['GET', 'POST'])

def population_range():

    if request.method == 'POST':   # input params defined for this api 

        year = request.form['yr1']

        range1_start = request.form['r1']

        range1_end = request.form['r2']

        range2_start = request.form['r3']

        range2_end = request.form['r4']

        range3_start = request.form['r5']

        range3_end = request.form['r6']

​

        # Map year to column name

        year_map = {

            '2010': 'ten',

            '2011': 'eleven',

            '2012': 'twelve',

            '2013': 'thirteen',

            '2014': 'fourteen',

            '2015': 'fifteen',

            '2016': 'sixteen',

            '2017': 'seventeen',

            '2018': 'eighteen'

        }

        year_column = year_map.get(year, 'ten')  # Default to 'ten' if year not found

​

        # Build cache key

        cache_key = f"population_range_{year_column}_{range1_start}_{range1_end}_{range2_start}_{range2_end}_{range3_start}_{range3_end}"

​

        # Check if result is cached

        cached_result = redis_client.get(cache_key)

        if cached_result:

            result = eval(cached_result)  # Deserialize cached result

            time_taken = 0  # No database query, so time taken is negligible

            cache_status = "Cache Hit"

        else:

            # Build SQL query

            query = f"""

            SELECT 

                SUM(CASE WHEN {year_column} BETWEEN '{range1_start}' AND '{range1_end}' THEN 1 ELSE 0 END) AS range1_count,

                SUM(CASE WHEN {year_column} BETWEEN '{range2_start}' AND '{range2_end}' THEN 1 ELSE 0 END) AS range2_count,

                SUM(CASE WHEN {year_column} BETWEEN '{range3_start}' AND '{range3_end}' THEN 1 ELSE 0 END) AS range3_count

            FROM popul

            """

            print(query)  # For debugging

​

            # Execute query and measure time

            start_time = time()

            result = engine.execute(query).fetchall()

            end_time = time()

            time_taken = end_time - start_time

            cache_status = "Cache Miss"

​

            # Cache the result

            redis_client.set(cache_key, str(result), ex=3600)  # Cache for 1 hour

​

        return render_template('display.html', data1=result, t1=time_taken, cache_status=cache_status)

​

    return render_template('index.html')

@app.route('/counties-by-state', methods=['GET', 'POST'])

def counties_by_state():

    if request.method == 'POST':

        state_code = request.form['state_code']

​

        # Build cache key

        cache_key = f"counties_by_state_{state_code}"

​

        # Check if result is cached

        cached_result = redis_client.get(cache_key)

        if cached_result:

            result = eval(cached_result)  # Deserialize cached result

            time_taken = 0  # No database query, so time taken is negligible

            cache_status = "Cache Hit"

        else:

            # Build SQL query

            query = f"""

            SELECT county 

            FROM dbo.county 

            WHERE state = (SELECT state FROM codes WHERE code = '{state_code}')

            """

            print(query)  # For debugging

​

            # Execute query and measure time

            start_time = time()

            result = engine.execute(query).fetchall()

            end_time = time()

            time_taken = end_time - start_time

            cache_status = "Cache Miss"

​

            # Cache the result

            redis_client.set(cache_key, str(result), ex=3600)  # Cache for 1 hour

​

        return render_template('counties.html', data=result, time_taken=time_taken, cache_status=cache_status)

    return render_template('index.html')

@app.route('/states-by-population', methods=['GET', 'POST'])

def states_by_population():

    if request.method == 'POST':

        year = request.form['year']

        population_start = request.form['population_start']

        population_end = request.form['population_end']

​

        # Map year to column name

        year_map = {

            '2010': 'ten',

            '2011': 'eleven',

            '2012': 'twelve',

            '2013': 'thirteen',

            '2014': 'fourteen',

            '2015': 'fifteen',

            '2016': 'sixteen',

            '2017': 'seventeen',

            '2018': 'eighteen'

        }

        year_column = year_map.get(year, 'ten')  # Default to 'ten' if year not found

​

        # Build cache key

        cache_key = f"states_by_population_{year_column}_{population_start}_{population_end}"

​

        # Check if result is cached

        cached_result = redis_client.get(cache_key)

        if cached_result:

            result = eval(cached_result)  # Deserialize cached result

            time_taken = 0  # No database query, so time taken is negligible

            cache_status = "Cache Hit"

        else:

            # Build SQL query

            query = f"""

            SELECT state 

            FROM popul 

            WHERE {year_column} BETWEEN '{population_start}' AND '{population_end}'

            """

            print(query)  # For debugging

​

            # Execute query and measure time

            start_time = time()

            result = engine.execute(query).fetchall()

            end_time = time()

            time_taken = end_time - start_time

            cache_status = "Cache Miss"

​

            # Cache the result

            redis_client.set(cache_key, str(result), ex=3600)  # Cache for 1 hour

​

        return render_template('states.html', data=result, time_taken=time_taken, cache_status=cache_status)

    return render_template('index.html')