from datetime import datetime
start_time  = datetime.now()
print("Last runa: ", start_time)

# pip install sparqlwrapper
# https://rdflib.github.io/sparqlwrapper/

import sys,json
import pandas as pd
from SPARQLWrapper import SPARQLWrapper, JSON

endpoint_url = "https://query.wikidata.org/sparql"

queryOpenDataMiss = """SELECT (REPLACE(STR(?org), ".*Q", "Q") AS ?wikidata) ?org ?orgLabel ?www   WHERE {
  ?org wdt:P31 wd:Q127448.
  ?org wdt:P856 ?www.
  ?org wdt:P361 ?lan.
  ?lan wdt:P31 wd:Q193556.
  ?org wdt:P276?/wdt:P625 ?coord.
  minus {?org wdt:P8402 ?portalValue}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv". }
} 
GROUP BY ?org ?orgLabel ?www
ORDER BY DESC (?orgLabel) """


def get_sparql_dataframe(endpoint_url, query):
    """
    Helper function to convert SPARQL results into a Pandas data frame.
    """
    user_agent = "salgo60/%s.%s" % (sys.version_info[0], sys.version_info[1])
 
    sparql = SPARQLWrapper(endpoint_url, agent=user_agent)
    sparql.setQuery(query)
    sparql.setReturnFormat(JSON)
    result = sparql.query()

    processed_results = json.load(result.response)
    cols = processed_results['head']['vars']

    out = []
    for row in processed_results['results']['bindings']:
        item = []
        for c in cols:
            item.append(row.get(c, {}).get('value'))
        out.append(item)

    return pd.DataFrame(out, columns=cols)

WDOpenDataCheck = get_sparql_dataframe(endpoint_url, queryOpenDataMiss)
WDOpenDataCheck.shape

import urllib3, json
from tqdm import tqdm
timeout = urllib3.util.Timeout(connect=2.0, read=7.0)
http = urllib3.PoolManager()

listOpenDataCheck = []
for WD, row in tqdm(WDOpenDataCheck.iterrows(), total=WDOpenDataCheck.shape[0]):
    url = row["www"] +"/psidata"
    
    new_item = dict()
    new_item['wikidata'] = row["wikidata"] 
    try:
        r = http.request('GET', url)        
        new_item['status'] = r.status

    except:
        print ("Error ", r.status, url, row["wikidata"] )
        new_item['status'] = r.status
#    if r.status == 200:
#        print (row["wikidata"] , r.status, url)
    new_item['url'] = url 
#    new_item['country'] = row["country"] 
    
    listOpenDataCheck.append(new_item)
print (len(listOpenDataCheck))

OpenDataNewtot = pd.DataFrame(listOpenDataCheck,
                  columns=['wikidata','status','url'])
OpenDataNewtot.shape

OpenDataNewtot[OpenDataNewtot.status == 200]