Dans cet article, je vais vous présenter le développement d’une application web basée sur Flask qui interagit avec une base de données SQL Server pour analyser des données de population. L’application permet aux utilisateurs de consulter des plages de population, de récupérer des comtés par état et d’extraire des états dans des plages de population spécifiques. Je discuterai également de l’intégration de Redis pour mettre en cache les résultats des requêtes afin d’améliorer les performances.
Pourquoi Flask, SQL Server et Redis ?
Flask est un framework web Python léger et flexible qui est parfait pour créer des applications web de petite à moyenne taille. Il fournit les outils nécessaires pour créer des API RESTful, rendre des modèles HTML dynamiques et interagir avec des bases de données. D’autre part, SQL Server est un système de gestion de base de données relationnelle (SGBDR) robuste qui est largement utilisé dans les applications d’entreprise. Combiner Flask avec SQL Server nous permet de construire une application puissante pour l’analyse et la visualisation des données.
Pour améliorer encore les performances, nous allons intégrer Redis, un magasin de données en mémoire, pour mettre en cache les résultats de requêtes fréquemment consultés. Cela réduit la charge sur la base de données et accélère les temps de réponse pour les requêtes répétées.
Aperçu de l’application
Notre application Flask effectue les tâches suivantes :
- Plages de population de requête. Les utilisateurs peuvent spécifier une année et une plage de population pour obtenir le nombre d’États se situant dans ces plages.
- Récupérer les comtés par État. Les utilisateurs peuvent saisir un code d’État pour obtenir une liste de comtés.
- Récupérer les États par plage de population. Les utilisateurs peuvent spécifier une plage de population et une année pour obtenir une liste d’États dans cette plage.
- Remarque. Pour tester, n’hésitez pas à créer votre propre schéma dans la base de données et insérez des données d’exemple selon les besoins en fonction des API partagées suivantes à l’aide de requêtes SQL. De plus, les pages HTML utilisées ici peuvent être une conception de tableau de base qui récupère les données renvoyées du code de l’application Flask et affiche les résultats.
Plongeons dans les détails de mise en œuvre.
Configuration de l’application Flask
1. Prérequis
Avant de commencer, assurez-vous d’avoir installé les éléments suivants via votre terminal root (commandes compatibles avec MacOS):
- Python 3.x
- Flask (
pip install flask) - SQLAlchemy (
pip install sqlalchemy) - PyODBC (
pip install pyodbc) - Redis (
pip install redis)
2. Connexion à la base de données
Nous utilisons SQLAlchemy pour se connecter à la base de données SQL Server. Voici comment la connexion peut être configurée:
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)
Cette chaîne de connexion utilise le pilote ODBC pour SQL Server et inclut des paramètres pour le chiffrement et le délai d’attente.
3. Configuration Redis
Redis est utilisé pour mettre en cache les résultats de requête. Voici comment configurer la connexion Redis :
import redis
# Redis connection
redis_client = redis.StrictRedis(
host='username.redis.cache.windows.net',
port=6380,
db=0,
password='encryptedpasswordstring',
ssl=True
)
4. Implémentation des itinéraires de l’application
Itinéraire de la page d’accueil
L’itinéraire de la page d’accueil affiche la page principale de l’application :
.route('/')
def index():
return render_template('index.html')
Requête de plage de population avec mise en cache Redis
Cet itinéraire gère les requêtes pour les plages de population. Il vérifie d’abord si le résultat est mis en cache dans Redis. Sinon, il interroge la base de données et met en cache le résultat pour une utilisation future :
.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')
Récupérer les comtés par État avec mise en cache Redis
Cet itinéraire récupère les comtés pour un code d’État donné. Il utilise également Redis pour mettre en cache les résultats :
.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')
Récupérer les États par plage de population avec mise en cache Redis
Cet itinéraire récupère les États dans une plage de population spécifiée et met en cache les résultats :
.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')
Comparaison des performances : SQL Server vs Redis
| Query Type | Redis Fetch Time | SQL Execution Time |
|---|---|---|
| Requête de plage de population (mise en cache) | 0,002 secondes | 0,000 secondes |
| Requête de plage de population (fraîche) | 0,002 secondes | 1,342 secondes |
Principale conclusion : Redis réduit le temps d’exécution de ~1,3 secondes à ~0,002 secondes, rendant les requêtes 650 fois plus rapides !
Comment Redis améliore les performances
Redis est un magasin de données en mémoire qui agit comme une couche de mise en cache entre l’application et la base de données. Voici comment il fonctionne dans notre application :
- Clé de cache. Une clé unique est générée pour chaque requête en fonction de ses paramètres.
- Vérification de cache. Avant d’exécuter une requête à la base de données, l’application vérifie si le résultat est déjà mis en cache dans Redis.
- Accès au cache. Si le résultat est trouvé dans Redis, il est renvoyé immédiatement, évitant ainsi une requête à la base de données.
- Échec du cache. Si le résultat n’est pas trouvé, la requête est exécutée et le résultat est mis en cache dans Redis pour une utilisation ultérieure.
- Expiration du cache. Les résultats mis en cache sont programmés pour expirer après un temps spécifié (par exemple, 1 heure) afin d’assurer la fraîcheur des données.
En mettant en cache les résultats de requêtes fréquemment accédés, Redis réduit considérablement la charge sur la base de données et améliore les temps de réponse pour les requêtes répétées.
Conclusion
Dans cet article, nous avons construit une application Flask qui interagit avec une base de données SQL Server pour analyser des données démographiques. Nous avons intégré Redis pour mettre en cache les résultats des requêtes, améliorant les performances et réduisant la charge de la base de données. En suivant les bonnes pratiques, vous pouvez étendre cette application pour gérer des requêtes plus complexes et la mettre à l’échelle pour une utilisation en production.
Lien : Le code source de cette application complète est disponible sur GitHub.
Source:
https://dzone.com/articles/build-data-analytics-platform-flask-sql-redis