text: #!/usr/bin/env python
  # coding: utf-8
  # author: Max Fischer
  
  import psycopg2 as pg
  import psycopg2.extras
  import psycopg2.extensions
  import re
  import json
  import datetime
  import requests
  
  from db_config import SQLALCHEMY_DATABASE_URI
  
  print('Crawler for DIVI Data')
  
  
  def get_connection():
      conn = pg.connect(SQLALCHEMY_DATABASE_URI)
      conn.set_session(autocommit=False, isolation_level=psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)
      cur = conn.cursor()
      return conn, cur
  
  
  URL = "https://diviexchange.z6.web.core.windows.net"
  
  
  def rep(e):
      d = {}
      for key, entry in e.items():
          d[key.replace('(','').replace(')', '').replace(' ', '_').replace('-', '').lower()] = entry
      return d
  
  print('Fetch data')
  response = requests.get(URL)
  html = response.text
  
  regex = r"vegaEmbed\(\"#left\", (.+?), \{\"renderer\": \"canvas\", \"actions\": false\}\);"
  print('First try at parsing')
  match = re.findall(regex, html, re.DOTALL)
  
  try:
      if len(match) != 1:
          raise Error()
      data = json.loads(match[0])
      if not 'datasets' in data:
          raise Error()
  except:
      print("Divi Data format has undergone a major change again, crawler needs regex update.")
      exit(2)
      
  print('Parsing for Covid-19 current')
  # sort keys by length of containing arrays, the longest one is the hospitals list
  covid19_current = [v for k, v in sorted(data['datasets'].items(), key=lambda item: len(item[1]), reverse=True)][0]
  entries_covid19 = [rep(entry) for entry in covid19_current]
  print('entries hospitals', len(entries_covid19))
  
  #
  # disabled due to divi shutdown
  #
  # hospitaldata = [v for k, v in sorted(data.items(), key=lambda item: len(item[1]), reverse=True)][0]
  # entries = [rep(entry) for entry in hospitaldata]
  # print('entries hospitals', len(entries))
  
  
  
  
  
  
  
  aqueryOld = 'INSERT INTO divi_hospital_capacities_and_cases(datenbestand, gemeindeschluessel, ort, bundeslandschluessel, plz, webaddresse, icu_low_care_frei, icu_low_care_belegt, icu_low_care_einschaetzung, icu_low_care_in_24h, icu_high_care_frei, icu_high_care_belegt, icu_high_care_einschaetzung, icu_high_care_in_24h, icu_ecmo_care_frei, icu_ecmo_care_belegt, icu_ecmo_care_einschaetzung, icu_ecmo_care_in_24h, ecmo_faelle_jahr, covid19_aktuell, covid19_kumulativ, covid19_beatmet, covid19_verstorben, geom) VALUES %s'
  
  aqueryCovid19 = 'INSERT INTO divi_hospital_covid19_aktuell(datenbestand, klinikname, covid19_aktuell, geom) VALUES %s'
  
  try:  
      conn, cur = get_connection()
  
      #
      # old divi insert
      #
      #cur.execute("SELECT Max(datenbestand) from divi_hospital_capacities_and_cases")
      #last_update = cur.fetchone()[0]
      #
      #now = datetime.datetime.now().replace(tzinfo=datetime.timezone(datetime.timedelta(hours=+1)))
      #print("db data version:", last_update)
      #print("fetched data version:", now)
      #print('Insert new data into DB...')
      #
      #psycopg2.extras.execute_values (
      #cur, aqueryOld, entries, template='(\''+ str(now) + '\', %(gemeindeschluessel)s, %(ort)s, %(bundesland)s, %(plz)s, %(webadresse)s, %(icu_low_care_frei)s, %(icu_low_care_belegt)s, %(icu_low_care_einschätzung)s, %(icu_low_care_in_24_h_anzahl)s, %(icu_high_care_frei)s, %(icu_high_care_belegt)s, %(icu_high_care_einschätzung)s, %(icu_high_care_in_24_h_anzahl)s, %(icu_ecmo_frei)s, %(icu_ecmo_belegt)s, %(icu_ecmo_care_einschätzung)s, %(icu_ecmo_care_in_24_h_anzahl)s, %(anzahl_ecmofälle_pro_jahr)s, %(covid19_aktuell)s, %(covid19_kumulativ)s, %(covid19_beatmet)s, %(covid19_verstorben)s, ST_SetSRID( ST_Point(%(lon)s, %(lat)s), 4326))', page_size=500
      #)
      
      #
      # covid 19 divi data
      #
      
      cur.execute("SELECT Max(datenbestand) from divi_hospital_covid19_aktuell")
      last_update = cur.fetchone()[0]
  
      now = datetime.datetime.now().replace(tzinfo=datetime.timezone(datetime.timedelta(hours=+1)))
      print("db data version:", last_update)
      print("fetched data version:", now)
      if last_update is not None and abs((now - last_update).total_seconds()) <= (60*60 - 120):
          print("No new data available (+/- 1h), skip update")
      else:    
          print('Insert new data into DB...')
  
          psycopg2.extras.execute_values (
          cur, aqueryCovid19, entries_covid19, template='(\''+ str(now) + '\', %(klinikname)s, %(covid19_aktuell)s, ST_SetSRID( ST_Point(%(lon)s, %(lat)s), 4326))', page_size=500
          )
          
          conn.commit()
  
          print('Success')
      if(conn):
          cur.close()
          conn.close()
  
      exit(0)    
  
  except (Exception, pg.DatabaseError) as error:
      conn, cur = get_connection()
  
      print(error)
      print("Error in transction - Reverting all other operations of a transction")
      print("Most likely a simultanious update was applied faster.")
  
      conn.rollback()
  
      if(conn):
          cur.close()
          conn.close()
  
      exit(1)   
  
  
  
  
  
  