В этой статье я расскажу о разработке веб-приложения на основе Flask, которое взаимодействует с базой данных SQL Server для анализа данных о населении. Приложение позволяет пользователям запрашивать диапазоны населения, извлекать округа по штатам и получать штаты в определенных диапазонах населения. Я также расскажу о том, как интегрировать Redis для кэширования результатов запросов для улучшения производительности.
Почему Flask, SQL Server и Redis?
Flask – это легкий и гибкий веб-фреймворк на Python, идеально подходящий для создания небольших и средних веб-приложений. Он предоставляет необходимые инструменты для создания RESTful API, отображения динамических HTML-шаблонов и взаимодействия с базами данных. С другой стороны, SQL Server – это надежная система управления реляционными базами данных (СУБД), широко используемая в корпоративных приложениях. Комбинирование Flask с SQL Server позволяет нам создать мощное приложение для анализа данных и визуализации.
Для дальнейшего улучшения производительности мы интегрируем Redis, хранилище данных в памяти, для кэширования часто запрашиваемых результатов запросов. Это снижает нагрузку на базу данных и ускоряет время ответа на повторяющиеся запросы.
Обзор приложения
Наше приложение Flask выполняет следующие задачи:
- Запрос диапазонов населения. Пользователи могут указать год и диапазон населения, чтобы получить количество штатов, попадающих в эти диапазоны.
- Получить округа по штату. Пользователи могут ввести код штата, чтобы получить список округов.
- Получить штаты по диапазону населения. Пользователи могут указать диапазон населения и год, чтобы получить список штатов в этом диапазоне.
- Примечание. Чтобы протестировать, не стесняйтесь создавать свою схему в базе данных и вставлять образцовые данные по необходимости на основе предоставленных ниже API с использованием SQL-запросов. Кроме того, HTML-страницы, используемые здесь, могут представлять собой основной дизайн таблицы, который извлекает возвращенные данные из кода приложения Flask и отображает результаты.
Давайте перейдем к деталям реализации.
Настройка приложения Flask
1. Предварительные требования
Прежде чем начать, убедитесь, что у вас установлены следующие компоненты через ваш терминал root (команды совместимы с MacOS):
- Python 3.x
- Flask (
pip install flask) - SQLAlchemy (
pip install sqlalchemy) - PyODBC (
pip install pyodbc) - Redis (
pip install redis)
2. Подключение к базе данных
Мы используем SQLAlchemy для подключения к базе данных SQL Server. Вот как может быть настроено подключение:
from sqlalchemy import create_engine
import urllib
# SQL Server connection string
params = urllib.parse.quote_plus(
"Driver={ODBC Driver 17 for SQL Server};"
"Server=tcp:username.database.windows.net,1433;"
"Database=population;"
"Uid=user@username;"
"Pwd={azure@123};"
"Encrypt=yes;"
"TrustServerCertificate=no;"
"Connection Timeout=30;"
)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
Эта строка подключения использует ODBC-драйвер для SQL Server и включает параметры для шифрования и времени ожидания.
3. Настройка Redis
Redis используется для кэширования результатов запросов. Вот как настроить подключение к Redis:
import redis
# Redis connection
redis_client = redis.StrictRedis(
host='username.redis.cache.windows.net',
port=6380,
db=0,
password='encryptedpasswordstring',
ssl=True
)
4. Реализация маршрутов приложения
Маршрут домашней страницы
Маршрут домашней страницы отображает основную страницу приложения:
.route('/')
def index():
return render_template('index.html')
Запрос диапазона населения с кэшированием Redis
Этот маршрут обрабатывает запросы на диапазоны населения. Сначала он проверяет, кэширован ли результат в Redis. Если нет, он делает запрос к базе данных и кэширует результат для будущего использования:
.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')
Получение округов по коду штата с кэшированием Redis
Этот маршрут извлекает округа для заданного кода штата. Он также использует Redis для кэширования результатов:
.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')
Получение штатов по диапазону населения с кэшированием Redis
Этот маршрут извлекает штаты в указанном диапазоне населения и кэширует результаты:
.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')
Сравнение производительности: SQL Server против Redis
| Query Type | Redis Fetch Time | SQL Execution Time |
|---|---|---|
| Запрос диапазона населения (кэшированный) | 0,002 секунды | 0,000 секунд |
| Запрос диапазона населения (новый) | 0,002 секунды | 1,342 секунды |
Основной вывод: Redis сокращает время выполнения с ~1,3 секунды до ~0,002 секунды, делая запросы 650 раз быстрее!
Как Redis улучшает производительность
Redis – это хранилище данных в памяти, которое действует как кэширующий слой между приложением и базой данных. Вот как это работает в нашем приложении:
- Ключ кэша. Для каждого запроса генерируется уникальный ключ на основе его параметров.
- Проверка кэша. Перед выполнением запроса к базе данных приложение проверяет, есть ли результат уже в кэше Redis.
- Попадание в кэш. Если результат найден в Redis, он немедленно возвращается, избегая запроса к базе данных.
- Промах кэша. Если результат не найден, выполняется запрос, и результат кэшируется в Redis для будущего использования.
- Истечение срока кэширования. Кэшированные результаты устанавливаются на истечение определенного времени (например, 1 час) для обеспечения свежести данных.
Путем кэширования результатов часто используемых запросов Redis значительно снижает нагрузку на базу данных и улучшает время ответа для повторяющихся запросов.
Вывод
В этой статье мы создали приложение Flask, взаимодействующее с базой данных SQL Server для анализа данных о населении. Мы интегрировали Redis для кэширования результатов запросов, улучшая производительность и снижая нагрузку на базу данных. Следуя bewst практикам, вы можете расширить это приложение для обработки более сложных запросов и масштабировать его для использования в производстве.
Ссылка: Исходный код этого полного приложения можно найти на GitHub.
Source:
https://dzone.com/articles/build-data-analytics-platform-flask-sql-redis