impfquotenmonitor.py

import openpyxl
from pathlib import Path
import requests
import os,sys,random
from SPARQLWrapper import SPARQLWrapper, JSON

url = "https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Daten/Impfquotenmonitoring.xlsx;jsessionid=159D4550C958EDFAA9A49921FA132A35.internet122?__blob=publicationFile"
r = requests.get(url, allow_redirects=True)
open('Impfquotenmonitoring.xlsx', 'wb').write(r.content)
xlsx_file = Path(os.getcwd(), 'Impfquotenmonitoring.xlsx')
wb_obj = openpyxl.load_workbook(xlsx_file)
sheet = wb_obj.active
sum = 0
for i in range(2,18):
    sum += sheet.cell(row = i, column = 2).value

erlaeuterung = wb_obj['Erläuterung']
m_row = erlaeuterung.max_row
for i in range(1, m_row + 1):
    cell_obj = erlaeuterung.cell(row = i, column = 1)
    if "Datenstand:" in str(cell_obj.value):
        datenstand = str(cell_obj.value)

endpoint_url = "https://query.wikidata.org/sparql"
query = "SELECT DISTINCT ?city ?cityLabel ?population WHERE {"
query +=  "?city wdt:P31/wdt:P279* wd:Q486972;"
query +=  "      wdt:P17 wd:Q183;"
query +=  "      wdt:P1082 ?population;"
query +=  "FILTER (abs(?population -" + str(sum) + ") < 100)"
query +=  'SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }'
query +="}"
user_agent = "Impfquotenmonitorvergleich"
sparql = SPARQLWrapper(endpoint_url, agent=user_agent)
sparql.setQuery(query)
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
result = random.choice(results["results"]["bindings"])
city = result["cityLabel"]["value"]

print("Gesamtzahl der Impfungen in Deutschland: " + str(sum))
print("Das entspricht der Einwohnerzahl von " + str(city))
print(datenstand)