lod_query.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "---\n",
    "title: \"Linked open data query from SPARQL\"\n",
    "jupyter: python3\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SPARQL query\n",
    "\n",
    "The below Python code experiments with using SPARQLWrapper to retrieve data from NFDI4Culture's Wikibase based on a SPARQL query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item</th>\n",
       "      <th>itemLabel</th>\n",
       "      <th>creationDateStart</th>\n",
       "      <th>creationDateEnd</th>\n",
       "      <th>inscription</th>\n",
       "      <th>materialLabel</th>\n",
       "      <th>methodLabel</th>\n",
       "      <th>description</th>\n",
       "      <th>media</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege VII: The city of Waitzen occupied by the...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Statt Waitzen wie die von vom Türcken beleger...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege I: The Fortress of Tottis retaken by the...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Tottis, wie die von den Christen bei ...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege II: The Fortress of Gran occupied by the...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Gran wie die von Christen belegert ge...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege XII: The Fortress of Gran occupied by th...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Gran wie die vom Türcken belegert gew...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege XI: The capital city Offen occupied by t...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Hauptstatt Offen, wie die von Christn beleger...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege III: The Fortress of Raab occupied by th...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Raab, wie die vom Türcken belegert ge...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege IV: The Fortress of Comorna occupied by ...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Comorna wie die vom Türckn belegert g...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege V: The Fortress of Gran retaken by the C...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Gran wie die von den Christen wider e...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege VI: The Fortress of Vizzegrad occupied b...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>“Vestung Vizzegrad wie die von Christen belege...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege VIII: The Fortress of Raab, which was re...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Vestung Raab, die Christen beÿ der Nacht wide...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege IX: The capital city Offen occupied by t...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Hauptstatt Offen. wie die von Christen belege...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>Siege X: The capital city Offen occupied by th...</td>\n",
       "      <td>1602-01-01T00:00:00Z</td>\n",
       "      <td>1604-01-01T00:00:00Z</td>\n",
       "      <td>„Hauptstatt Offen, wie die von Christen belege...</td>\n",
       "      <td>Canvas</td>\n",
       "      <td>Oil painting</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "      <td>https://wikibase.wbworkshop.tibwiki.io/entity/...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 item  \\\n",
       "0   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "1   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "2   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "3   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "4   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "5   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "6   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "7   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "8   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "9   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "10  https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "11  https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "\n",
       "                                            itemLabel     creationDateStart  \\\n",
       "0   Siege VII: The city of Waitzen occupied by the...  1602-01-01T00:00:00Z   \n",
       "1   Siege I: The Fortress of Tottis retaken by the...  1602-01-01T00:00:00Z   \n",
       "2   Siege II: The Fortress of Gran occupied by the...  1602-01-01T00:00:00Z   \n",
       "3   Siege XII: The Fortress of Gran occupied by th...  1602-01-01T00:00:00Z   \n",
       "4   Siege XI: The capital city Offen occupied by t...  1602-01-01T00:00:00Z   \n",
       "5   Siege III: The Fortress of Raab occupied by th...  1602-01-01T00:00:00Z   \n",
       "6   Siege IV: The Fortress of Comorna occupied by ...  1602-01-01T00:00:00Z   \n",
       "7   Siege V: The Fortress of Gran retaken by the C...  1602-01-01T00:00:00Z   \n",
       "8   Siege VI: The Fortress of Vizzegrad occupied b...  1602-01-01T00:00:00Z   \n",
       "9   Siege VIII: The Fortress of Raab, which was re...  1602-01-01T00:00:00Z   \n",
       "10  Siege IX: The capital city Offen occupied by t...  1602-01-01T00:00:00Z   \n",
       "11  Siege X: The capital city Offen occupied by th...  1602-01-01T00:00:00Z   \n",
       "\n",
       "         creationDateEnd                                        inscription  \\\n",
       "0   1604-01-01T00:00:00Z  „Statt Waitzen wie die von vom Türcken beleger...   \n",
       "1   1604-01-01T00:00:00Z  „Vestung Tottis, wie die von den Christen bei ...   \n",
       "2   1604-01-01T00:00:00Z  „Vestung Gran wie die von Christen belegert ge...   \n",
       "3   1604-01-01T00:00:00Z  „Vestung Gran wie die vom Türcken belegert gew...   \n",
       "4   1604-01-01T00:00:00Z  „Hauptstatt Offen, wie die von Christn beleger...   \n",
       "5   1604-01-01T00:00:00Z  „Vestung Raab, wie die vom Türcken belegert ge...   \n",
       "6   1604-01-01T00:00:00Z  „Vestung Comorna wie die vom Türckn belegert g...   \n",
       "7   1604-01-01T00:00:00Z  „Vestung Gran wie die von den Christen wider e...   \n",
       "8   1604-01-01T00:00:00Z  “Vestung Vizzegrad wie die von Christen belege...   \n",
       "9   1604-01-01T00:00:00Z  „Vestung Raab, die Christen beÿ der Nacht wide...   \n",
       "10  1604-01-01T00:00:00Z  „Hauptstatt Offen. wie die von Christen belege...   \n",
       "11  1604-01-01T00:00:00Z  „Hauptstatt Offen, wie die von Christen belege...   \n",
       "\n",
       "   materialLabel   methodLabel  \\\n",
       "0         Canvas  Oil painting   \n",
       "1         Canvas  Oil painting   \n",
       "2         Canvas  Oil painting   \n",
       "3         Canvas  Oil painting   \n",
       "4         Canvas  Oil painting   \n",
       "5         Canvas  Oil painting   \n",
       "6         Canvas  Oil painting   \n",
       "7         Canvas  Oil painting   \n",
       "8         Canvas  Oil painting   \n",
       "9         Canvas  Oil painting   \n",
       "10        Canvas  Oil painting   \n",
       "11        Canvas  Oil painting   \n",
       "\n",
       "                                          description  \\\n",
       "0   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "1   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "2   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "3   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "4   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "5   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "6   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "7   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "8   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "9   https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "10  https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "11  https://wikibase.wbworkshop.tibwiki.io/entity/...   \n",
       "\n",
       "                                                media  \n",
       "0   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "1   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "2   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "3   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "4   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "5   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "6   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "7   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "8   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "9   https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "10  https://wikibase.wbworkshop.tibwiki.io/entity/...  \n",
       "11  https://wikibase.wbworkshop.tibwiki.io/entity/...  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON\n",
    "from PIL import Image\n",
    "from ipypublish import nb_setup\n",
    "import numpy as np\n",
    "import pandas\n",
    "import requests\n",
    "import sparql_dataframe\n",
    "\n",
    "def get_delay(date):\n",
    "    try:\n",
    "        date = datetime.datetime.strptime(date, '%a, %d %b %Y %H:%M:%S GMT')\n",
    "        timeout = int((date - datetime.datetime.now()).total_seconds())\n",
    "    except ValueError:\n",
    "        timeout = int(date)\n",
    "    return timeout\n",
    "\n",
    "def get_image(url, headers):\n",
    "    r = requests.get(url, headers=headers, stream=True)\n",
    "    if r.status_code == 200:\n",
    "        im = Image.open(r.raw)\n",
    "        return im\n",
    "    if r.status_code == 500:\n",
    "        return None\n",
    "    if r.status_code == 403:\n",
    "        return None\n",
    "    if r.status_code == 429:\n",
    "        timeout = get_delay(r.headers['retry-after'])\n",
    "        print('Timeout {} m {} s'.format(timeout // 60, timeout % 60))\n",
    "        time.sleep(timeout)\n",
    "        get_image(url, headers)\n",
    "\n",
    "# Example using NFDI4Culture Wikibase\n",
    "\n",
    "# Specify the NFDI4Culture Wikibase SPARQL endpoint\n",
    "endpoint_url = 'https://query.wbworkshop.tibwiki.io/proxy/wdqs/bigdata/namespace/wdq/sparql'\n",
    "\n",
    "# Query\n",
    "query = \"\"\"\n",
    "SELECT ?item ?itemLabel ?creationDateStart ?creationDateEnd ?inscription ?materialLabel ?methodLabel ?description ?media\n",
    "WHERE { \n",
    "    ?item tibt:P25 tib:Q60.\n",
    "    ?item tibt:P79 ?creationDateStart.\n",
    "    ?item tibt:P80 ?creationDateEnd.\n",
    "    ?item tibt:P18 ?inscription.\n",
    "    ?item tibt:P38 ?material.\n",
    "    ?item tibt:P39 ?method.\n",
    "    ?item tibt:P26 ?description.\n",
    "    ?item tibt:P16 ?media\n",
    "    SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n",
    "} \n",
    "#All paintings from \"Belagerungsszenen des Langen Türkenkriegs\"\n",
    "\"\"\"\n",
    "\n",
    "dataframe = sparql_dataframe.get(endpoint_url, query, post=True)\n",
    "\n",
    "dataframe\n",
    "\n",
    "# Example using Wikidata\n",
    "\n",
    "# Specify the Wikidata SPARQL endpoint\n",
    "#endpoint_url = 'https://query.wikidata.org/bigdata/namespace/wdq/sparql'\n",
    "\n",
    "#user_agent = 'cp4c_bot/0.0 (https://github.com/SimonXIX/cp4c; ad7588@coventry.ac.uk)'\n",
    "#sparql = SPARQLWrapper(endpoint_url, agent=user_agent)\n",
    "\n",
    "# query = \"\"\"\n",
    "# #Cats, with pictures\n",
    "# #defaultView:ImageGrid\n",
    "# SELECT ?item ?itemLabel ?pic\n",
    "# WHERE\n",
    "# {\n",
    "# ?item wdt:P31 wd:Q146 .\n",
    "# ?item wdt:P18 ?pic\n",
    "# SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\" }\n",
    "# }\n",
    "# LIMIT 2\n",
    "# \"\"\"\n",
    "\n",
    "# Retrieve results and convert to JSON format\n",
    "#sparql.setQuery(query)\n",
    "#sparql.setReturnFormat(JSON)\n",
    "#result = sparql.query().convert()\n",
    "\n",
    "#for item in result['results']['bindings']:\n",
    "    # image_url=item['pic']['value']\n",
    "    # headers = {'User-Agent': 'cp4c_bot/0.0 (https://github.com/SimonXIX/cp4c; ad7588@coventry.ac.uk)'}\n",
    "    # im = get_image(image_url, headers)\n",
    "\n",
    "    # print('Cat name: ', item['itemLabel']['value'])\n",
    "    # display(im)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}