In dit artikel zal ik de ontwikkeling van een op Flask gebaseerde webapplicatie bespreken die communiceert met een SQL Server-database om bevolkingsgegevens te analyseren. De applicatie stelt gebruikers in staat om bevolkingsbereiken op te vragen, provincies op te halen per staat en staten binnen specifieke bevolkingsbereiken op te halen. Ik zal ook bespreken hoe u Redis kunt integreren voor het cachen van queryresultaten om de prestaties te verbeteren.
Waarom Flask, SQL Server en Redis?
Flask is een lichtgewicht en flexibel Python webframework dat perfect is voor het bouwen van kleine tot middelgrote webapplicaties. Het biedt de benodigde tools om RESTful API’s te maken, dynamische HTML-templates te renderen en te communiceren met databases. Aan de andere kant is SQL Server een robuust relationeel databasebeheersysteem (RDBMS) dat veel wordt gebruikt in bedrijfstoepassingen. Door Flask te combineren met SQL Server kunnen we een krachtige applicatie bouwen voor gegevensanalyse en visualisatie.
Om de prestaties verder te verbeteren, zullen we Redis integreren, een in-memory datastore, om vaak opgevraagde queryresultaten te cachen. Dit vermindert de belasting op de database en versnelt de responstijden voor herhaalde queries.
Applicatie-overzicht
Onze Flask-applicatie voert de volgende taken uit:
- Query populatieranges. Gebruikers kunnen een jaar en populatierange opgeven om het aantal staten binnen die ranges te krijgen.
- Fetch counties per staat. Gebruikers kunnen een staatscode invoeren om een lijst van counties op te halen.
- Haal staten op op basis van populatierange. Gebruikers kunnen een populatierange en jaar opgeven om een lijst van staten binnen die range te krijgen.
- Opmerking. Voel je vrij om je eigen schema in de database te maken en voorbeeldgegevens in te voegen op basis van de volgende API’s die zijn gedeeld met behulp van SQL-query’s. Ook kunnen de HTML-pagina’s die hier worden gebruikt een basis tabelontwerp zijn dat de teruggegeven gegevens uit de Flask-appcode vastlegt en de resultaten weergeeft.
Laten we de implementatiedetails induiken.
De Flask-toepassing instellen
1. Vereisten
Voordat je begint, zorg ervoor dat je het volgende hebt geïnstalleerd via je terminal (commando’s compatibel met MacOS):
- Python 3.x
- Flask (
pip install flask) - SQLAlchemy (
pip install sqlalchemy) - PyODBC (
pip install pyodbc) - Redis (
pip install redis)
2. Databaseverbinding
We gebruiken SQLAlchemy om verbinding te maken met de SQL Server-database. Hier is hoe de verbinding kan worden geconfigureerd:
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)
Deze verbindingsreeks maakt gebruik van de ODBC-stuurprogramma voor SQL Server en bevat parameters voor versleuteling en time-out.
3. Redis Configuratie
Redis wordt gebruikt om queryresultaten te cachen. Zo stel je de Redis-verbinding in:
import redis
# Redis connection
redis_client = redis.StrictRedis(
host='username.redis.cache.windows.net',
port=6380,
db=0,
password='encryptedpasswordstring',
ssl=True
)
4. Implementatie van de toepassingsroutes
Homepagina Route
De homepagina route rendert de hoofdpagina van de toepassing:
.route('/')
def index():
return render_template('index.html')
Populatiebereikquery met Redis-caching
Deze route behandelt query’s voor populatiebereiken. Het controleert eerst of het resultaat gecachet is in Redis. Zo niet, dan wordt de database bevraagd en het resultaat gecachet voor toekomstig gebruik:
.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')
Haal provincies op per staat met Redis-caching
Deze route haalt provincies op voor een gegeven staatcode. Het maakt ook gebruik van Redis om de resultaten te cachen:
.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')
Haal staten op per populatiebereik met Redis-caching
Deze route haalt staten binnen een gespecificeerd populatiebereik op en cacht de resultaten:
.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')
Prestatievergelijking: SQL Server vs. Redis
| Query Type | Redis Fetch Time | SQL Execution Time |
|---|---|---|
| Populatiebereikquery (Gecachet) | 0,002 seconden | 0,000 seconden |
| Populatiebereikquery (Vers) | 0,002 seconden | 1,342 seconden |
Belangrijkste leerpunt: Redis vermindert de uitvoeringstijd van ~1,3 seconden naar ~0,002 seconden, waardoor query’s 650x sneller worden!
Hoe Redis de prestaties verbetert
Redis is een in-memory dataopslag die fungeert als een cache-laag tussen de applicatie en de database. Zo werkt het in onze applicatie:
- Cache-sleutel. Voor elke query wordt een unieke sleutel gegenereerd op basis van de parameters.
- Cache-controle. Voordat een databasequery wordt uitgevoerd, controleert de applicatie of het resultaat al gecachet is in Redis.
- Cache-hit. Als het resultaat wordt gevonden in Redis, wordt het onmiddellijk geretourneerd, waardoor een databasequery wordt vermeden.
- Cache-miss. Als het resultaat niet wordt gevonden, wordt de query uitgevoerd en wordt het resultaat gecachet in Redis voor toekomstig gebruik.
- Cache-verval. Gecachte resultaten worden ingesteld om te verlopen na een bepaalde tijd (bijv. 1 uur) om de gegevensversheid te garanderen.
Door veelgebruikte queryresultaten te cachen, vermindert Redis aanzienlijk de belasting op de database en verbetert het de responstijden voor herhaalde queries.
Conclusie
In dit artikel hebben we een Flask-applicatie gebouwd die communiceert met een SQL Server-database om bevolkingsgegevens te analyseren. We hebben Redis geïntegreerd om queryresultaten te cachen, waardoor de prestaties verbeteren en de belasting van de database afneemt. Door best practices te volgen, kunt u deze applicatie uitbreiden om complexere queries aan te kunnen en schaalbaar maken voor productiegebruik.
Link: De broncode van deze volledige applicatie is te vinden op GitHub.
Source:
https://dzone.com/articles/build-data-analytics-platform-flask-sql-redis