Navigation

    Logo
    • Register
    • Login
    • Search
    • Recent
    • Tags
    • Unread
    • Categories
    • Unreplied
    • Popular
    • GitHub
    • Docu
    • Hilfe
    1. Home
    2. Deutsch
    3. Skripten / Logik
    4. JavaScript
    5. Corona-Daten nach MySQL importieren

    NEWS

    • Neuer Blog: Fotos und Eindrücke aus Solingen

    • ioBroker@Smart Living Forum Solingen, 14.06. - Agenda added

    • ioBroker goes Matter ... Matter Adapter in Stable

    Corona-Daten nach MySQL importieren

    This topic has been deleted. Only users with topic management privileges can see it.
    • sissiwup
      sissiwup @sissiwup last edited by

      Hallo,

      der Vorteil ist, man hat mit dem Import der drei Dateien auch die gesamte Historie enthalten. D.h. man kann komplette Zeitreihen machen.

      So sieht das z.B. in grafana aus:

      Bildschirmfoto 2020-04-02 um 14.42.06.png

      Dazu gehört dann:
      Bildschirmfoto 2020-04-02 um 14.43.23.png

      Das schöne ist, man kann hier Landkreise, Bundesländer etc. immer mit nahezu den gleichen Select machen.
      K_SKreis sind die Kreise (wie sie der corona-Adpater schreibt)
      R_Landkreis sind die Kreise in "normaler" Schreibweise
      R_Bundesland sind die Länder
      mir R_ gibt es Einzelwerte
      mit L_ gibt es Landkreiswerte
      mit B_ gib es Bundeslandwerte
      mit K_ gibt es weitere Informationen zum Kreis

      Man kann also z.B. leicht feststellen, wie sieht mein Kreis im Verhältnis zu meinem Bundesland oder zu Gesamtdeutschland aus.

      Die Tabelle eignet sich auch um z.B. mit Tableau weiterzuarbeiten.

      a200 1 Reply Last reply Reply Quote 0
      • a200
        a200 @sissiwup last edited by

        @sissiwup coole Sache. bei mir bekomme ich ich aber Probleme bei der Abfrage

        SELECT sum(R_Fall) as fall, sum(R_Tote) as Tote, R_Landkreis FROM `cor_view` where R_Fall>0 group by ID_L order by fall DESC
        
        #1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cor_view.R_Landkreis' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
        
        sissiwup 1 Reply Last reply Reply Quote 0
        • sissiwup
          sissiwup @a200 last edited by

          @a200 sagte in Corona-Daten nach MySQL importieren:

          @sissiwup coole Sache. bei mir bekomme ich ich aber Probleme bei der Abfrage

          SELECT sum(R_Fall) as fall, sum(R_Tote) as Tote, R_Landkreis FROM `cor_view` where R_Fall>0 group by ID_L order by fall DESC
          
          #1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cor_view.R_Landkreis' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
          

          Hallo,

          dann probier bitte mal group by R_Landkreis

          haselchen 1 Reply Last reply Reply Quote 0
          • haselchen
            haselchen Most Active @sissiwup last edited by

            @sissiwup

            Welche Gemeinde in der Region Hannover? 😉

            sissiwup 1 Reply Last reply Reply Quote 0
            • sissiwup
              sissiwup @haselchen last edited by

              @haselchen sagte in Corona-Daten nach MySQL importieren:

              @sissiwup

              Welche Gemeinde in der Region Hannover? 😉

              Dicht dabei, Schaumburg.

              haselchen 1 Reply Last reply Reply Quote 0
              • haselchen
                haselchen Most Active @sissiwup last edited by

                @sissiwup

                Dachte Du wärst ein bisschen näher 🙂
                Trotzdem ein Gruß aus der Herzogstadt 92km entfernt 🙂

                1 Reply Last reply Reply Quote 0
                • a200
                  a200 last edited by a200

                  @sissiwup kannst du deine grafana dashboards zur Verfügung stellen? Es wäre einfacher die dann zu editieren als sie erstmal abtippen zu müssen. Großer Dank für deine Arbeit.

                  und wo bekomme ich die Kreise vollständig? In der kreise.sql Datei sind lediglich 25 Kreise vorhanden.

                  sissiwup 1 Reply Last reply Reply Quote 0
                  • sissiwup
                    sissiwup @a200 last edited by

                    @a200
                    Anbei die vollständige SQL:
                    kreise.sql

                    Und das Dashboard:
                    (die Variablen müssen vermutlich angepaßt werden)
                    ioBroker Corona-1585949802227.json

                    sissiwup 1 Reply Last reply Reply Quote 0
                    • sissiwup
                      sissiwup @sissiwup last edited by

                      Berechnung von geheilten (wie RKI vorschlägt) 14 Tage nach Meldung

                      SELECT
                        unix_timestamp(R_MeldeDatum) as time_sec,
                        (select sum(R_FALL) from cor_view i 
                         where unix_timestamp(i.R_MeldeDatum)<=unix_timestamp(v.R_MeldeDatum)-1209600 and K_SKreis = "Schaumburg") as val,
                        "Genesen" as metric
                      FROM cor_view v
                      WHERE $__unixEpochFrom()<unix_timestamp(R_MeldeDatum) and $__unixEpochTo()>unix_timestamp(R_MeldeDatum)
                      and K_Skreis = "Schaumburg"
                      group by R_MeldeDatum  
                      ORDER BY R_MeldeDatum ASC 
                      

                      1209600 = 14 * 24 * 60 * 60

                      sissiwup 1 Reply Last reply Reply Quote 0
                      • sissiwup
                        sissiwup @sissiwup last edited by sissiwup

                        Hallo,

                        wenn man anstelle eines View eine Tabelle verwenden möchte (ist etwas schneller), dann hilft folgendes:

                        (Update 4.4. Delete Zahlen<0)

                        createZiel.txt

                        DROP TABLE IF EXISTS cor_view;
                        
                        CREATE TABLE cor_view AS
                        SELECT
                        r.IDBundesLand as ID_B, r.IDLandkreis as ID_L,SUBSTRING(r.MeldeDatum,1,10) as R_MeldeDatum, r.ObjectID as ID_R,
                        r.Bundesland as R_Bundesland, r.Landkreis as R_Landkreis, r.Altersgruppe as R_Alter, r.Geschlecht as R_Geschl, r.AnzahlFall as R_Fall, r.AnzahlTodesfall as R_Tote, r.Datenstand as
                         R_Datenstand, r.NeuerFall as R_Neuerfall, r.NeuerTodesFall as NeuerTodesFall,
                        b.LAN_ew_EWZ as B_Einwohner,b.FallZahl as B_Fallzahl,b.Death as B_Tote,
                        l.EWZ as L_Einwohner,l.KFL as L_Flaeche, l.death_rate as L_TodesRate, l.cases as L_Faelle, l.deaths as L_Tote, l.cases_per_100k as L_Faelle_pro_100000,l.cases_per_population as L_
                        Faelle_pro_Bevoelkerung,
                        k.skreis as K_SKreis, k.bevoelkerung as K_Bevoelkerung, k.maenner as K_Maenner,k.frauen as K_Frauen, k.dichte as K_Dichte
                        FROM
                        cor_rki r,cor_bundesland b,cor_landkreise l, kreise k
                        where r.IdLandkreis=l.RS and r.IdBundesland=b.id and r.IdLandkreis=k.id
                        and r.AnzahlFall>0
                        order by ID_B,ID_L,R_MeldeDatum;
                        
                        update cor_view set R_Tote=0 where R_Tote<0;
                        
                        ALTER TABLE `cor_view`
                          ADD UNIQUE KEY `PRIME` (`ID_B`,`ID_L`,`R_MeldeDatum`,`ID_R`) USING BTREE,
                          ADD KEY `I1` (`R_MeldeDatum`,`R_Bundesland`,`R_Fall`,`R_Tote`),
                          ADD KEY `I2` (`R_MeldeDatum`,`R_Landkreis`,`R_Fall`,`R_Tote`),
                          ADD KEY `I3` (`R_MeldeDatum`,`K_SKreis`,`R_Fall`,`R_Tote`);
                        COMMIT;
                        

                        Das Batch Skript sieht dann so aus:

                        #!/bin/bash
                        NOW=`date +"%d.%m.%g %H:%M.%S"`
                        NOWDAT=`date +"%d_%m_%g"`
                        USER=DBUSER
                        PASS=DBPASSWORD
                        
                        rm /var/skripte/data/*.csv
                        wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                        wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                        wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                        
                        cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                        cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                        cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                        
                        #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                        
                        mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                        mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                        mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                        
                        mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                        
                        1 Reply Last reply Reply Quote 1
                        • a200
                          a200 last edited by a200

                          @sissiwup cool, das sieht schon besser aus. Jetzt meckert grafana über eine Tabelle datapoints, die sie nicht finden kann. Kannst du auch hier helfen?
                          Erledigt. Das lag daran, dass ich in der DB nur deine Daten habe und die aus iobroker nicht.

                          sissiwup 1 Reply Last reply Reply Quote 0
                          • sissiwup
                            sissiwup @a200 last edited by sissiwup

                            Die csv-Datei für rki ist nicht mehr komplett ladbar (vermutlich ein Fehler auf der Seite)

                            PS: Datenstand in der cor_rki Tabelle muss 22 Zeichen lang sein...

                            Hier eine kleine Umgehungslösung:
                            rkijson.py

                            import csv, json
                            import datetime as dt
                            import requests
                            
                            outfile = r'/var/skripte/data/cor_rki.csv'
                            
                            count = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&returnCountOnly=true&f=pjson"
                            anz = requests.get(count)
                            anz_json = json.loads(anz.text)
                            anzahl = anz_json['count']
                            print("Zeilen:" + str(anzahl))
                            
                            ofile = open(outfile, 'w+')
                            output = csv.writer(ofile)
                            
                            search1 = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&resultOffset="
                            search2 = "&resultRecordCount=1000&f=pjson"
                            i = 0
                            while i < anzahl:
                                print("Readfile:" + str((int)(i / 1000)) + " von " + str((int)(int(anzahl) / 1000)))
                                s_str = search1 + str(i) + search2
                                x = requests.get(s_str)
                                data = json.loads(x.text)
                            
                                daten = data['features']
                                for row in daten:
                                    zeit = row['attributes']['Meldedatum'] / 1000
                                    row['attributes']['Meldedatum'] = dt.datetime.utcfromtimestamp(zeit).strftime(
                                        "%Y-%m-%dT%H:%M:%S.000Z")
                                if i == 0:
                                    output.writerow(daten[0]['attributes'].keys())
                                for row in daten:
                                    output.writerow(row['attributes'].values())
                                i = i + 1000
                            
                            

                            Das Downloadskript sieht dann wie folgt aus:

                            #!/bin/bash
                            NOW=`date +"%d.%m.%g %H:%M.%S"`
                            NOWDAT=`date +"%d_%m_%g"`
                            USER=DBUSER
                            PASS=DBPASSWORT
                            
                            rm /var/skripte/data/cor*.csv
                            #wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                            python3 -u /var/skripte/rkijson.py
                            wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                            wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                            
                            cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                            cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                            cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                            
                            #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                            
                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                            
                            mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                            
                            sissiwup 1 Reply Last reply Reply Quote 0
                            • sissiwup
                              sissiwup @sissiwup last edited by sissiwup

                              Hier noch die JHU Daten:

                              Skript:

                              #!/bin/bash
                              NOW=`date +"%d.%m.%g %H:%M.%S"`
                              NOWDAT=`date +"%d_%m_%g"`
                              USER=DBUSER
                              PASS=DBPASSWORT
                              
                              rm /var/skripte/data/jhu*.csv
                              wget -O /var/skripte/data/jhu_fall.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_
                              global.csv
                              wget -O /var/skripte/data/jhu_tote.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_glo
                              bal.csv
                              wget -O /var/skripte/data/jhu_genesen.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recover
                              ed_global.csv
                              
                              cp /var/skripte/data/jhu_fall.csv /var/skripte/data/fall_$NOWDAT.csv.backup
                              cp /var/skripte/data/jhu_tote.csv /var/skripte/data/tote_$NOWDAT.csv.backup
                              cp /var/skripte/data/jhu_genesen.csv /var/skripte/data/genesen_$NOWDAT.csv.backup
                              
                              python3 -u /var/skripte/convertJHU.py
                              
                              mysql -u $USER -p$PASS iobroker < /var/skripte/data/createJHU.txt
                              
                              mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_jhu.csv
                              
                              mysql -u $USER -p$PASS iobroker < /var/skripte/data/updateJHU.txt
                              

                              und das convertJHU.py

                              import csv
                              import datetime as dt
                              
                              outfile = r'/var/skripte/data/cor_jhu.csv'
                              infile1 = r'/var/skripte/data/jhu_fall.csv'
                              infile2 = r'/var/skripte/data/jhu_genesen.csv'
                              infile3 = r'/var/skripte/data/jhu_tote.csv'
                              
                              now = dt.date.today()
                              
                              
                              def conv_date(ind):
                                  val = ind.split("/")
                                  txt = "20" + val[2] + "-" + ("0" + val[0])[-2:] + "-" + ("0" + val[1])[-2:]
                                  return txt
                              
                              
                              def read_files(csv_reader, fall_art):
                                  for row in csv_reader:
                                      keys = list(row.keys())
                                      for subkey in keys[4:]:
                                          if row["Country/Region"] == "Canada":
                                              row["Province/State"] = ""
                                          nice_key = conv_date(subkey)
                                          key = nice_key + ":" + row["Country/Region"] + ":" + row["Province/State"]
                                          # print("Key=" + key)
                                          if key not in countrys.keys():
                                              lists = {"Meldedatum": nice_key, "Land": row["Country/Region"],
                                                       "Bundesland": row["Province/State"], "Import": now, fall_art: row[subkey]}
                                              # print("List=" + str(lists))
                                              countrys[key] = lists
                                              # print("C=" + str(countrys))
                                          else:
                                              lists = countrys[key]
                                              if fall_art not in lists.keys():
                                                  lists[fall_art] = row[subkey]
                                              else:
                                                  lists[fall_art] = row[subkey] + lists[fall_art]
                                              countrys[key] = lists
                              
                              
                              countrys = {}
                              
                              in_f = open(infile1)
                              csv_reader = csv.DictReader(in_f)
                              read_files(csv_reader, "fall")
                              
                              in_f = open(infile2)
                              csv_reader = csv.DictReader(in_f)
                              read_files(csv_reader, "genesen")
                              
                              in_f = open(infile3)
                              csv_reader = csv.DictReader(in_f)
                              read_files(csv_reader, "tote")
                              
                              print(len(countrys))
                              # print(countrys)
                              
                              # for row in list(countrys.values())[:10]:
                              #    print(row)
                              
                              out_f = open(outfile, "w+")
                              fields = ["Meldedatum", "Land", "Bundesland", "fall", "genesen", "tote", "fall_tag" ,"genesen_tag" ,"tote_tag" ,"Import"]
                              csv_writer = csv.DictWriter(out_f, fieldnames=fields)
                              csv_writer.writerow(dict((fn, fn) for fn in fields))
                              for row in list(countrys.values())[:]:
                                  csv_writer.writerow(row)
                              

                              Canada wird gesondert
                              behandelt, da genesene nur für Gesamtcanada vorhanden sind.

                              Und die Tabelle createJHU.txt:

                              CREATE TABLE IF NOT EXISTS cor_jhu(
                                Meldedatum    varchar(10) NOT NULL,
                                Land          varchar(50) NOT NULL,
                                Bundesland    varchar(50) DEFAULT NULL,
                                fall          int(11) NOT NULL,
                                genesen       int(11) NOT NULL,
                                tote          int(11) NOT NULL,  
                                fall_tag      int(11) NOT NULL,
                                genesen_tag   int(11) NOT NULL,
                                tote_tag      int(11) NOT NULL,
                                Import        varchar(10) NOT NULL
                              ) DEFAULT CHARACTER SET = UTF8;
                              
                              ALTER TABLE `cor_jhu`
                                ADD UNIQUE KEY `U1` (`Meldedatum`,`Land`,`Bundesland`),
                                ADD UNIQUE KEY `U2` (`Land`,`Bundesland`,`Meldedatum`),
                                ADD KEY `I1` (`Land`,`Meldedatum`,`fall`),
                                ADD KEY `I2` (`Land`,`Meldedatum`,`genesen`),
                                ADD KEY `I3` (`Land`,`Meldedatum`,`tote`);
                              COMMIT;
                              
                              truncate cor_jhu;
                              

                              Und die SQL-Korrektur updateJHU.txt:

                              update cor_jhu set land="Korea",bundesland="South" where land like "%Korea%" and Bundesland like "%South%";
                              
                              update cor_jhu j1,cor_jhu j2 set j1.fall_tag=j1.fall-j2.fall,j1.tote_tag=j1.tote-j2.tote,j1.genesen_tag=j1.genesen-j2.genesen WHERE
                              j1.land=j2.land and j1.bundesland=j2.bundesland and date_sub(j1.meldedatum,interval 1 day)=j2.meldedatum and j1.meldedatum>date("2020-01-22");
                              
                              COMMIT
                              
                              sissiwup 1 Reply Last reply Reply Quote 0
                              • sissiwup
                                sissiwup @sissiwup last edited by

                                Hier mal ein Vergleich JHU und RKI:

                                Bildschirmfoto 2020-04-05 um 18.18.46.png

                                sissiwup 1 Reply Last reply Reply Quote 0
                                • sissiwup
                                  sissiwup @sissiwup last edited by

                                  Hallo,

                                  und hier mal ein paar Auswertungen auf den JHU_daten:

                                  Bildschirmfoto 2020-04-06 um 01.08.42.png

                                  Bildschirmfoto 2020-04-06 um 01.09.50.png

                                  iobroker Corona Welt-1586128248813.json

                                  1 Reply Last reply Reply Quote 0
                                  • frankjoke
                                    frankjoke @sissiwup last edited by

                                    @sissiwup und Kollegen!

                                    Bin jetzt 2 in Quarantäne gewesen und seit dem Wochenende zu Hause.

                                    Hatte nur ein altes Chrome-book mit mir welches keine linux-Befehle erlaubte und einen neuen 4GB Raspi den ich mir vorher noch gekauft hatte. Ich habe wegen meiner fehlenden Testmöglichkeiten kein iobroker-adapter geschrieben aber eine kleine web-app mit der die Krankheitsverlaufkurve von Staaten angezeigt und verglöichen werden kann.

                                    Ihr könnt die App direkt auf git anschauen: https://frankjoke.github.io/coronafj/
                                    Das repo dazu ist https://github.com/frankjoke/coronafj

                                    Es gibt einen chart tab und einen list tab, sonst sollte es selbsterklärend sein.
                                    Da ich das für meine Kollegen aus allen Ländern gemacht habe ist's halt in Englisch, aber man kann sehr schön die Unterschiedliche Entwicklung der Länder sehen!

                                    Übrigens, bin ab jetzt in Pension (oder Rente wie ihr sagen würdet) und hoffe mich bald wieder meinen iobroker-Adaptern widmen zu können, wir dürfen sowieso nicht wirklich rausgehen hier in A!

                                    p.s.: verwende eine api verwendet die von da https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6 gespeist wird.

                                    sigi234 2 Replies Last reply Reply Quote 1
                                    • sigi234
                                      sigi234 Forum Testing Most Active @frankjoke last edited by

                                      @frankjoke sagte in Corona-Daten nach MySQL importieren:

                                      Übrigens, bin ab jetzt in Pension

                                      Gratuliere Dir. 😀

                                      1 Reply Last reply Reply Quote 0
                                      • sigi234
                                        sigi234 Forum Testing Most Active @frankjoke last edited by

                                        @frankjoke

                                        Cool, App läuft einwandfrei! 👍

                                        sissiwup 1 Reply Last reply Reply Quote 0
                                        • sissiwup
                                          sissiwup @sigi234 last edited by

                                          Hallo,

                                          habe bei JHU die Tabelle verbreitert, damit auch die Tageswerte da sind und habe Süd-Korea repariert (da hier ein Fehler in den Daten ist)

                                          sissiwup 1 Reply Last reply Reply Quote 0
                                          • sissiwup
                                            sissiwup @sissiwup last edited by sissiwup

                                            RKI hat neue Werte hinzugefügt. RefDatum (da wo die Erkrankung aufgetreten ist) und Zahlen für genesen:

                                            getCorona.sh:

                                            NOW=`date +"%d.%m.%g %H:%M.%S"`
                                            NOWDAT=`date +"%d_%m_%g"`
                                            USER=DBUSER
                                            PASS=DBPASSWORD
                                            
                                            rm /var/skripte/data/cor*.csv
                                            #wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                                            python3 -u /var/skripte/rkijson.py
                                            wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                                            wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                                            
                                            cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                                            cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                                            cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                                            
                                            #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                                            
                                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                                            
                                            mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                                            

                                            rkijson.py:

                                            import csv, json
                                            import datetime as dt
                                            import requests
                                            
                                            outfile = r'/var/skripte/data/cor_rki.csv'
                                            
                                            count = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&returnCountOnly=true&f=pjson"
                                            anz = requests.get(count)
                                            anz_json = json.loads(anz.text)
                                            anzahl = anz_json['count']
                                            print("Zeilen:" + str(anzahl))
                                            
                                            ofile = open(outfile, 'w+')
                                            output = csv.writer(ofile)
                                            
                                            search1 = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&resultOffset="
                                            search2 = "&resultRecordCount=1000&f=pjson"
                                            i = 0
                                            while i < anzahl:
                                                print("Readfile:" + str((int)(i / 1000)) + " von " + str((int)(int(anzahl) / 1000)))
                                                s_str = search1 + str(i) + search2
                                                x = requests.get(s_str)
                                                data = json.loads(x.text)
                                            
                                                daten = data['features']
                                                for row in daten:
                                                    zeit = row['attributes']['Meldedatum'] / 1000
                                                    row['attributes']['Meldedatum'] = dt.datetime.utcfromtimestamp(zeit).strftime(
                                                        "%Y-%m-%dT%H:%M:%S.000Z")
                                                    meld = row['attributes']['Refdatum'] / 1000
                                                    row['attributes']['Refdatum'] = dt.datetime.utcfromtimestamp(meld).strftime(
                                                        "%Y-%m-%dT%H:%M:%S.000Z")
                                                    dat = row['attributes']['Datenstand']
                                                    row['attributes']['Datenstand'] = dat.replace(",",":")
                                                if i == 0:
                                                    output.writerow(daten[0]['attributes'].keys())
                                                for row in data['features']:
                                                    output.writerow(row['attributes'].values())
                                                i = i + 1000
                                            

                                            createTable.txt:

                                            CREATE TABLE IF NOT EXISTS cor_rki(
                                               IdBundesland    INTEGER  NOT NULL
                                              ,Bundesland      VARCHAR(44) NOT NULL
                                              ,Landkreis       VARCHAR(44) NOT NULL
                                              ,Altersgruppe    VARCHAR(9) NOT NULL
                                              ,Geschlecht      VARCHAR(9) NOT NULL
                                              ,AnzahlFall      INTEGER  NOT NULL
                                              ,AnzahlTodesfall INTEGER  NOT NULL
                                              ,ObjectId        INTEGER  NOT NULL PRIMARY KEY
                                              ,Meldedatum      VARCHAR(24) NOT NULL
                                              ,IdLandkreis     VARCHAR(5) NOT NULL
                                              ,Datenstand      VARCHAR(22) NOT NULL
                                              ,NeuerFall       INTEGER  NOT NULL
                                              ,NeuerTodesfall  INTEGER  NOT NULL
                                              ,Refdatum        VARCHAR(24) NOT NULL
                                              ,NeuGenesen      INTEGER  NOT NULL
                                              ,AnzahlGenesen   INTEGER  NOT NULL
                                            ) DEFAULT CHARACTER SET = UTF8;
                                            
                                            ALTER TABLE `cor_rki`
                                              ADD KEY `Meldedatum` (`Meldedatum`),
                                              ADD KEY `IdLandkreis` (`IdLandkreis`),
                                              ADD KEY `Datenstand` (`Datenstand`),
                                              ADD KEY `Refdatum` (`Refdatum`);
                                            COMMIT;
                                            
                                            CREATE TABLE IF NOT EXISTS cor_landkreise(
                                               OBJECTID             INTEGER  NOT NULL PRIMARY KEY
                                              ,ADE                  INTEGER
                                              ,GF                   INTEGER
                                              ,BSG                  BIT
                                              ,RS                   VARCHAR(5) NOT NULL
                                              ,AGS                  VARCHAR(5)
                                              ,SDV_RS               VARCHAR(11)
                                              ,GEN                  VARCHAR(44) NOT NULL
                                              ,BEZ                  VARCHAR(44) NOT NULL
                                              ,IBZ                  INTEGER
                                              ,BEM                  VARCHAR(13)
                                              ,NBD                  VARCHAR(4)
                                              ,SN_L                 INTEGER
                                              ,SN_R                 INTEGER
                                              ,SN_K                 INTEGER
                                              ,SN_V1                INTEGER
                                              ,SN_V2                INTEGER
                                              ,SN_G                 INTEGER
                                              ,FK_S3                VARCHAR(1)
                                              ,NUTS                 VARCHAR(5)
                                              ,RS_0                 INTEGER
                                              ,AGS_0                INTEGER
                                              ,WSK                  VARCHAR(23)
                                              ,EWZ                  INTEGER  NOT NULL
                                              ,KFL                  NUMERIC(7,2)
                                              ,DEBKG_ID             VARCHAR(16)
                                              ,Shape_Area           NUMERIC(17,7) NOT NULL
                                              ,Shape_Length         NUMERIC(17,10) NOT NULL
                                              ,death_rate           NUMERIC(17,15) NOT NULL
                                              ,cases                INTEGER  NOT NULL
                                              ,deaths               INTEGER  NOT NULL
                                              ,cases_per_100k       NUMERIC(17,14) NOT NULL
                                              ,cases_per_population NUMERIC(19,17) NOT NULL
                                              ,BL                   VARCHAR(22) NOT NULL
                                              ,BL_ID                INTEGER  NOT NULL
                                              ,county               VARCHAR(36) NOT NULL
                                              ,last_update          VARCHAR(16) NOT NULL
                                            ) DEFAULT CHARACTER SET = UTF8;
                                            
                                            ALTER TABLE `cor_landkreise`
                                              ADD KEY `RS` (`RS`);
                                            COMMIT;
                                            
                                            CREATE TABLE IF NOT EXISTS cor_bundesland(
                                               ID               INTEGER  NOT NULL PRIMARY KEY
                                              ,LAN_ew_AGS       INTEGER  NOT NULL
                                              ,LAN_ew_GEN       VARCHAR(44) NOT NULL
                                              ,LAN_ew_BEZ       VARCHAR(44) NOT NULL
                                              ,LAN_ew_EWZ       INTEGER  NOT NULL
                                              ,OBJECTID         INTEGER  NOT NULL
                                              ,Fallzahl         INTEGER  NOT NULL
                                              ,Aktualisierung   VARCHAR(24) NOT NULL
                                              ,AGS_TXT          INTEGER  NOT NULL
                                              ,GlobalID         VARCHAR(36) NOT NULL
                                              ,faelle_100000_EW NUMERIC(16,13) NOT NULL
                                              ,Shape_Area       NUMERIC(17,5) NOT NULL
                                              ,Shape_Length     NUMERIC(16,9) NOT NULL
                                              ,Death            INTEGER  NOT NULL
                                            ) DEFAULT CHARACTER SET = UTF8;
                                            
                                            truncate cor_rki;
                                            truncate cor_bundesland;
                                            truncate cor_landkreise;
                                            

                                            createZiel.txt:
                                            Achtung falls ihr die Tabelle cor_datum nicht verwendet, dann die Bezüge hier löschen (Tabelle siehe nächste Post)

                                            DROP TABLE IF EXISTS cor_view;
                                            
                                            CREATE TABLE cor_view AS
                                            SELECT
                                            r.IDBundesLand as ID_B, r.IDLandkreis as ID_L,SUBSTRING(r.MeldeDatum,1,10) as R_MeldeDatum, r.ObjectID as ID_R,
                                            r.Bundesland as R_Bundesland, r.Landkreis as R_Landkreis, r.Altersgruppe as R_Alter, r.Geschlecht as R_Geschl, r.AnzahlFall as R_Fall, r.AnzahlTodesfall as R_Tote, r.Datenstand as
                                             R_Datenstand, r.NeuerFall as R_Neuerfall, r.NeuerTodesFall as R_NeuerTodesFall,
                                            SUBSTRING(r.Refdatum,1,10) as R_Refdatum, r.NeuGenesen as R_NeuGenesen, r.AnzahlGenesen as R_AnzahlGenesen,
                                            b.LAN_ew_EWZ as B_Einwohner,b.FallZahl as B_Fallzahl,b.Death as B_Tote,
                                            l.EWZ as L_Einwohner,l.KFL as L_Flaeche, l.death_rate as L_TodesRate, l.cases as L_Faelle, l.deaths as L_Tote, l.cases_per_100k as L_Faelle_pro_100000,l.cases_per_population as L_
                                            Faelle_pro_Bevoelkerung,
                                            k.skreis as K_SKreis, k.bevoelkerung as K_Bevoelkerung, k.maenner as K_Maenner,k.frauen as K_Frauen, k.dichte as K_Dichte
                                            FROM
                                            cor_rki r,cor_bundesland b,cor_landkreise l, kreise k
                                            where r.IdLandkreis=l.RS and r.IdBundesland=b.id and r.IdLandkreis=k.id
                                            and r.AnzahlFall>0
                                            order by ID_B,ID_L,R_MeldeDatum;
                                            
                                            update cor_view set R_Tote=0 where R_Tote<0;
                                            update cor_view set R_AnzahlGenesen=0 where R_AnzahlGenesen<0;
                                            
                                            update cor_datum set rki=false;
                                            update cor_datum set rki=true where d_datum<=(select max(R_meldedatum) from cor_view) and d_datum>=(select min(R_meldedatum) from cor_view);
                                            
                                            ALTER TABLE `cor_view`
                                              ADD UNIQUE KEY `PRIME` (`ID_B`,`ID_L`,`R_MeldeDatum`,`ID_R`) USING BTREE,
                                              ADD KEY `I1` (`R_MeldeDatum`,`R_Bundesland`,`R_Fall`,`R_Tote`),
                                              ADD KEY `I2` (`R_MeldeDatum`,`R_Landkreis`,`R_Fall`,`R_Tote`),
                                              ADD KEY `I3` (`R_MeldeDatum`,`K_SKreis`,`R_Fall`,`R_Tote`);
                                            COMMIT;
                                            
                                            sissiwup 1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post

                                            Support us

                                            ioBroker
                                            Community Adapters
                                            Donate

                                            851
                                            Online

                                            31.8k
                                            Users

                                            80.0k
                                            Topics

                                            1.3m
                                            Posts

                                            communication
                                            5
                                            22
                                            2209
                                            Loading More Posts
                                            • Oldest to Newest
                                            • Newest to Oldest
                                            • Most Votes
                                            Reply
                                            • Reply as topic
                                            Log in to reply
                                            Community
                                            Impressum | Datenschutz-Bestimmungen | Nutzungsbedingungen
                                            The ioBroker Community 2014-2023
                                            logo