async def api_v1_stats(request: Request):
median_query: ProxyIP = ProxyIP.raw("""SELECT latency
FROM proxy_ips
WHERE is_valid = 1
ORDER BY latency
LIMIT 1
OFFSET (
SELECT COUNT(*) FROM proxy_ips WHERE is_valid = 1
) / 2""").get()
median = median_query.latency
mean_query: ProxyIP = ProxyIP.raw("""SELECT AVG(latency) as latency
FROM proxy_ips
WHERE is_valid = 1 AND latency < 9999""").get()
mean = mean_query.latency
valid_count = _get_valid_proxies_query().count()
total_count = ProxyIP.select().count()
return json({
'median': median,
'valid_count': valid_count,
'total_count': total_count,
'mean': mean,
})
async def api_v1_providers(request: Request):
provider_query: ProxyIP = ProxyIP.raw("""
SELECT
provider,
is_valid,
COUNT(*) AS count,
MAX(updated_at) AS updated_at
FROM proxy_ips
GROUP BY provider, is_valid
""")
provider_list = {}
# add known providers to list
for p in all_providers:
name = p.__name__
provider_list[name] = {
'name': name.replace('Provider', ''),
'ips_available': 0,
'ips_valid': 0,
'updated_at': None
}
# add statistics from database
for p in provider_query:
if not p.provider in provider_list:
# don't crash when a non-existing provider is in the database
name = p.provider or '(none)'
provider_list[p.provider] = {
'name': name,
'ips_available': 0,
'ips_valid': 0,
'updated_at': None
}
last_updated = provider_list[p.provider]['updated_at']
if last_updated is None or last_updated < p.updated_at:
provider_list[p.provider]['updated_at'] = p.updated_at
if p.is_valid:
provider_list[p.provider]['ips_valid'] += p.count
provider_list[p.provider]['ips_available'] += p.count
else:
provider_list[p.provider]['ips_available'] += p.count
return json({'providers': provider_list.values()})
