Hi,
hier mal die aktuelle Version:
getImpfquoten.py:
import openpyxl
from pathlib import Path
import requests
import os,sys
import csv
from datetime import datetime
url = "https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Daten/Impfquotenmonitoring.xlsx;jsessionid=159D4550C958EDFAA9A49921FA132A35.internet122?__blob=publicationFile"
# url = "https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Daten/Impfquotenmonitoring.xlsx"
r = requests.get(url, allow_redirects=True)
open(r'/var/skripte/data/Impfquotenmonitoring.xlsx', 'wb').write(r.content)
xlsx_file = Path(os.getcwd(), r'/var/skripte/data/Impfquotenmonitoring.xlsx')
wb_obj = openpyxl.load_workbook(xlsx_file,data_only=True)
datakt=datetime.now()
dat="--"
sheet=wb_obj.active
for sht in wb_obj:
print(sht.title)
try:
dat = datetime.strptime(sht.title[-8:],'%d.%m.%y')
sheet=sht
except:
pass
print("Selected: "+sheet.title)
# print(dat)
if dat=="--":
print("Datum nicht ermittelbar!")
sys.exit(99)
#sheet = wb_obj.active
sum = 0
anzbl = 0
titel = 0
headline = None
with open(r'/var/skripte/data/Impfquotenmonitoring.csv', 'w', newline="") as f:
c = csv.writer(f)
for row in sheet.iter_rows(min_row=1,max_col=25,max_row=19, values_only=True):
try:
sum=sum+row[2]
nrow=[str(dat.date()),str(datakt),str(datetime.timestamp(dat))]
anzbl=anzbl+1
row = [x for x in row if x is not None]
nrow.extend(list(row))
if titel==0:
titel=1
headline = [x for x in headline if x is not None]
trow=['Datenstand','Dat-Import', 'Datenstand-Unix']
trow.extend(list(headline))
print(trow)
c.writerow(trow)
print(nrow)
c.writerow(nrow)
except:
if headline==None:
headline=list(row)
else:
for i in range(0,len(headline)):
if row[i] is not None:
if headline[i] is None:
headline[i]=row[i]
else:
headline[i]=headline[i]+"."+row[i]
if anzbl!=16:
print("Es sollten 16 Bundesländer sein:",anzbl)
sys.exit(99)
if len(headline)!=15:
print("Anzahl Spalten sollte 15 sein:",len(headline))
sys.exit(99)
print("Gesamtzahl der Impfungen in Deutschland: " + str(sum))
print("Datenstand: " + str(dat) + " Sheetname:" + str(sheet.title))
getImpfquoten.sh:
#!/bin/bash
NOW=`date +"%d.%m.%g %H:%M.%S"`
NOWDAT=`date +"%u"`
USER=usermysql
PASS=passwordmysql
echo "Starte Abgleich: $NOW"
rm /var/skripte/data/Impfquotenmonitoring.csv
rm /var/skripte/data/Impfquotenmonitoring.xlsx
rm /var/skripte/data/cor_impfung.csv
python3.9 -u /var/skripte/getImpfquoten.py
if [ $? -eq 99 ]
then
echo "Fehler bei Datenabholung"
cp /var/skripte/data/Impfquotenmonitoring.xlsx /var/skripte/data/Impquotenmonitoring_$NOWDAT.xlsx.backup
cp /var/skripte/data/Impfquotenmonitoring.csv /var/skripte/data/Impfquotenmonitoring_$NOWDAT.csv.backup
exit 99
fi
cp /var/skripte/data/Impfquotenmonitoring.csv /var/skripte/data/Impfquotenmonitoring_$NOWDAT.csv.backup
cp /var/skripte/data/Impfquotenmonitoring.csv /var/skripte/data/cor_impfung.csv
echo "Starte Import $NOW"
mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --ignore --local -u $USER -p$PASS iobroker /var/skripte/data/cor_impfung.csv
mysql -u $USER -p$PASS iobroker -e "DELETE i1.* from cor_impfung i1 inner join cor_impfung i2 on (i1.datenstand=i2.datenstand and i1.rs=i2.rs and i1.import_datum<i2.import_datum)"
mysql -u $USER -p$PASS iobroker -e "UPDATE cor_impfung inner join cor_bundesland on rs=lan_ew_ags SET Bundesland=lan_ew_gen"
Tabelle:
CREATE TABLE `cor_impfung` (
`datenstand` date NOT NULL DEFAULT current_timestamp(),
`import_datum` datetime NOT NULL DEFAULT current_timestamp(),
`datenstand_unix` bigint(20) NOT NULL DEFAULT 0,
`RS` int(11) NOT NULL DEFAULT -1,
`Bundesland` mediumtext COLLATE utf8_bin NOT NULL DEFAULT '\'"??"\'',
`Gesamtimpfungen` int(11) NOT NULL DEFAULT 0,
`Impfungen` int(11) DEFAULT 0,
`BioNTech` int(11) NOT NULL DEFAULT 0,
`Moderna` int(11) NOT NULL DEFAULT 0,
`AstraZeneca` int(11) NOT NULL DEFAULT 0,
`Differenz_Vortag` int(11) DEFAULT 0,
`Impfquote` double NOT NULL DEFAULT 0,
`Zweitimpfung` int(11) NOT NULL DEFAULT 0,
`BioNTech_2` int(11) NOT NULL DEFAULT 0,
`Moderna_2` int(11) NOT NULL DEFAULT 0,
`AstraZeneca_2` int(11) NOT NULL DEFAULT 0,
`Zweit_Differenz_Vortag` int(11) NOT NULL DEFAULT 0,
`Impfquote_2` double NOT NULL DEFAULT 0
) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Index:
ALTER TABLE `cor_impfung`
ADD PRIMARY KEY (`datenstand`,`Bundesland`(24),`import_datum`),
ADD KEY `datenstand-unix` (`datenstand_unix`,`Bundesland`(24));
COMMIT;
Erklärung siehe oben bei meinem letzten Post
