How to connect to SPARQL endpoint?

TBu · November 19, 2020, 7:26am

Hello everyone,

I want to do some graph analysis and my source data is accessible via a SPARQL endpoint exposed by a Jena Fuseki server.

I’d like to know if there is any package like the SPARQLWrapper package for Python (SPARQLWrapper · PyPI).

Basically, what I want to do is:

Query a SPARQL endpoint
Convert the result into a Data Frame
Do some other stuff that is not relevant here

Is there such a package? I have not been able to find one, yet, but probably I’ve been looking in the wrong place?

Thank you very much in advance for your help.

Kind regards,

Thomas

TimG · October 10, 2023, 10:38am

This is an old request with no response (which I take to be a bad sign). However, I’d like to add that I too want to be able to do this. UK Government data is available through a SPARQL endpoint and I’d like access to it from Julia.

Has any progress towards this been made in the last three years?

Thanks!

Asmund_Hjulstad · October 14, 2023, 7:56am

Calling Python from Julia is really easy using PyCall, and myself I like the automatic conda package installation using pyimport_conda.

Rewriting the example at the SPARQLWrapper documentation site would give something like the below.

This installs Julia-“owned” copy of a minimal conda distribution, installs the required conda package (and dependencies). I don’t know how to escape the triple-quotes, so I moved that outside.

using Pkg
Pkg.add(["PyCall", "Conda"])
using PyCall, Conda

# Force install
SPARQLWrapper  = pyimport_conda("SPARQLWrapper", "SPARQLWrapper")

query = """
PREFIX gts: <http://resource.geosciml.org/ontology/timescale/gts#>

SELECT *
WHERE {
    ?a a gts:Age .
}
ORDER BY ?a
LIMIT 3
"""

b = py"""
from SPARQLWrapper import SPARQLWrapper, JSON

sparql = SPARQLWrapper(
    "http://vocabs.ardc.edu.au/repository/api/sparql/"
    "csiro_international-chronostratigraphic-chart_geologic-time-scale-2020"
)
sparql.setReturnFormat(JSON)

# gets the first 3 geological ages
# from a Geological Timescale database,
# via a SPARQL endpoint
sparql.setQuery($(query))

try:
    ret = sparql.queryAndConvert()

    for r in ret["results"]["bindings"]:
        print(r)
except Exception as e:
    print(e)
"""

ret = py"ret"

println("Print from Julia")
values = [d["a"]["value"] for d in ret["results"]["bindings"]]
println(values)

TimG · October 14, 2023, 11:37am

That’s really helpful. Thank you.

I’ve never used Python and I’m only a beginner at Julia, but this gives me a really clear way forward to try.

TimG · October 19, 2023, 5:00pm

This is an uphill struggle for me!

I’m trying to extract postcodes and their related ward, local authority and parliamentary constituency:

#using Pkg
#Pkg.add(["PyCall", "Conda"])
using DataFrames, PyCall, Conda

# Force install
SPARQLWrapper  = pyimport_conda("SPARQLWrapper", "SPARQLWrapper")

query = """
PREFIX pcd: <http://statistics.data.gov.uk/def/postcode/unit#>
PREFIX geog: <http://statistics.data.gov.uk/def/hierarchy/best-fit#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?display ?ward ?la ?pcon
WHERE
{
  ?s rdf:type <http://statistics.data.gov.uk/def/postcode/unit> .
  ?s pcd:postcode1space ?display ;
     geog:ward ?wardcode ;
     geog:localauthoritydistrict ?lacode ;
     geog:parliamentaryconstituency ?pconcode .
  ?wardcode rdfs:label ?ward .
  ?lacode rdfs:label ?la .
  ?pconcode rdfs:label ?pcon .
  filter(?display in ("OX17 2JP","B2 5EE"))
}
LIMIT 10
"""

b = py"""
from SPARQLWrapper import SPARQLWrapper, JSON

sparql = SPARQLWrapper(
    "http://statistics.data.gov.uk/sparql"
)
sparql.setReturnFormat(JSON)


sparql.setQuery($(query))

try:
    ret = sparql.queryAndConvert()

    for r in ret["results"]["bindings"]:
        print(r)
except Exception as e:
    print(e)
"""

ret = py"ret"

println("Print from Julia")

values = DataFrame()
values[!, :display] = [d["display"]["value"] for d in ret["results"]["bindings"]]
values[!, :ward] = [d["ward"]["value"] for d in ret["results"]["bindings"]]
values[!, :la] = [d["la"]["value"] for d in ret["results"]["bindings"]]
values[!, :pcon] = [d["pcon"]["value"] for d in ret["results"]["bindings"]]
println(values)

This works (horay!) but I have three questions.

My output at Print from Julia looks like this:

rint from Julia
10×4 DataFrame
 Row │ display   ward       la         pcon      
     │ String    String     String     String
─────┼───────────────────────────────────────────
   1 │ OX17 2JP  E05013260  E06000062  E14000942
   2 │ OX17 2JP  E05013260  E06000062  E14000942
   3 │ OX17 2JP  E05013260  E06000062  E14000942
   4 │ OX17 2JP  E05013260  E06000062  E14000942
   5 │ OX17 2JP  E05013260  E06000062  E14000942
   6 │ OX17 2JP  E05013260  E06000062  E14000942
   7 │ OX17 2JP  E05013260  E06000062  E14000942
   8 │ OX17 2JP  E05013260  E06000062  E14000942
   9 │ B2 5EE    E05011151  E08000025  E14000564
  10 │ B2 5EE    E05011151  E08000025  E14000564

Q1. Why does my query return multiple lines per postcode?
Q2. How can I pass a (possibly very long) list of postcodes into the query?
Q3. How can I extract the output rather more elegantly into the DataFrame?

Thank you for your help!

TimG · October 20, 2023, 6:14pm

Making progress. I have answered two of my own questions:

strtry = """ "OX17 2JP", "B2 5EE", "G12 9ER", "AB1 0AG", "SY25 6PY" """

query = """
PREFIX pcd: <http://statistics.data.gov.uk/def/postcode/unit#>
PREFIX geog: <http://statistics.data.gov.uk/def/hierarchy/best-fit#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
#PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX stge: <http://statistics.data.gov.uk/def/statistical-geography#>

SELECT ?display ?ward ?la ?pcon ?reg ?dintr ?dterm
WHERE
{
    ?s rdf:type <http://statistics.data.gov.uk/def/postcode/unit> .
    ?s pcd:postcode1space ?display ;
        geog:ward ?wardcode ;
        geog:europeanelectoralregion ?regcode ;
        geog:localauthoritydistrict ?lacode ;
        geog:parliamentaryconstituency ?pconcode .
    OPTIONAL {?s <http://statistics.data.gov.uk/def/postcode/unit/dateofintroduction> ?dintr ;
                <http://statistics.data.gov.uk/def/postcode/unit/dateoftermination> ?dterm .}
    ?wardcode stge:officialname ?ward .
    ?lacode stge:officialname ?la .
    ?pconcode stge:officialname ?pcon .
    ?regcode stge:officialname ?reg .
    filter(?display in ($strtry))
}
LIMIT 1000
"""
b = py"""
from SPARQLWrapper import SPARQLWrapper, JSON
sparql = SPARQLWrapper("http://statistics.data.gov.uk/sparql")
sparql.setReturnFormat(JSON)
sparql.setQuery($(query))
try:
    ret = sparql.queryAndConvert()
except Exception as e:
    print(e)
"""

ret = py"ret"

values = DataFrame(display=String[], ward=String[], la=String[], pcon=String[], reg=String[], dintr=Union{Date,Missing}[], dterm=Union{Date,Missing}[])
for (i,d) in enumerate(ret["results"]["bindings"])
    push!(values, ["","","","","",missing,missing])
    values[i, :display] = d["display"]["value"]
    values[i, :ward] = d["ward"]["value"]
    values[i, :la] = d["la"]["value"]
    values[i, :pcon] = d["pcon"]["value"]
    values[i, :reg] = d["reg"]["value"]
    if get!(d, "dintr", "missing") !== "missing"
        values[i, :dintr] = Date(last(d["dintr"]["value"],7), "yyyy-mm")
    end
    if get!(d, "dterm", "missing") !== "missing"
        values[i, :dterm] = Date(last(d["dterm"]["value"],7), "yyyy-mm")
    end
end

println(values)

Which produces exactly what I want:

5×7 DataFrame
 Row │ display   ward                     la                     pcon                    reg            dintr       dterm      
     │ String    String                   String                 String                  String         Date?       Date?
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ B2 5EE    Ladywood                 Birmingham             Birmingham, Ladywood    West Midlands  missing     missing
   2 │ OX17 2JP  Middleton Cheney         West Northamptonshire  South Northamptonshire  East Midlands  missing     missing
   3 │ AB1 0AG   Lower Deeside            Aberdeen City          Aberdeen South          Scotland       1990-12-01  1992-07-01
   4 │ G12 9ER   Partick East/Kelvindale  Glasgow City           Glasgow North           Scotland       missing     missing
   5 │ SY25 6PY  Llangeitho               Ceredigion             Ceredigion              Wales          missing     missing

I am wondering if I could use HTTP.jl to construct this query in native Julia. I’ve tried a number of ways but always unsuccessfully. For example,

query = """QUERY = PREFIX pcd: <http://statistics.data.gov.uk/def/postcode/unit#> PREFIX geog: <http://statistics.data.gov.uk/def/hierarchy/best-fit#> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX stge: <http://statistics.data.gov.uk/def/statistical-geography#> SELECT ?display ?ward ?la ?pcon ?reg ?dintr ?dterm WHERE { ?s rdf:type <http://statistics.data.gov.uk/def/postcode/unit> . ?s pcd:postcode1space ?display ; geog:ward ?wardcode ; geog:europeanelectoralregion ?regcode ; geog:localauthoritydistrict ?lacode ; geog:parliamentaryconstituency ?pconcode . OPTIONAL {?s <http://statistics.data.gov.uk/def/postcode/unit/dateofintroduction> ?dintr ; <http://statistics.data.gov.uk/def/postcode/unit/dateoftermination> ?dterm .} ?wardcode stge:officialname ?ward . ?lacode stge:officialname ?la . ?pconcode stge:officialname ?pcon . ?regcode stge:officialname ?reg . filter(?display in ("OX17 2JP")) } LIMIT 1000"""

url = raw"http://statistics.data.gov.uk/sparql.json"
response = HTTP.post(url, query = "$query")

(essentially the same as the python formulation) gives:

ERROR: LoadError: HTTP.Exceptions.StatusError(400, "POST", "/sparql.json?QUERY = PREFIX pcd: <http://statistics.data.gov.uk/def/postcode/unit#> PREFIX geog: <http://statistics.data.gov.uk/def/hierarchy/best-fit#> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX stge: <http://statistics.data.gov.uk/def/statistical-geography#> SELECT ?display ?ward ?la ?pcon ?reg ?dintr ?dterm WHERE { ?s rdf:type <http://statistics.data.gov.uk/def/postcode/unit> . ?s pcd:postcode1space ?display ; geog:ward ?wardcode ; geog:europeanelectoralregion ?regcode ; geog:localauthoritydistrict ?lacode ; geog:parliamentaryconstituency ?pconcode . OPTIONAL {?s <http://statistics.data.gov.uk/def/postcode/unit/dateofintroduction> ?dintr ; <http://statistics.data.gov.uk/def/postcode/unit/dateoftermination> ?dterm .} ?wardcode stge:officialname ?ward . ?lacode stge:officialname ?la . ?pconcode stge:officialname ?pcon . ?regcode stge:officialname ?reg . filter(?display in (\"OX17 2JP\")) } LIMIT 1000", HTTP.Messages.Response:
"""
HTTP/1.0 400 Bad Request
Content-Type: text/html; charset=UTF-8
Referrer-Policy: no-referrer
Content-Length: 273
Date: Fri, 20 Oct 2023 18:09:57 GMT


<html><head>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
<title>400 Bad Request</title>
</head>
<body text=#000000 bgcolor=#ffffff>
<h1>Error: Bad Request</h1>
<h2>Your client has issued a malformed or illegal request.</h2>
<h2></h2>
</body></html>
""")

What do I need to do different?

Topic		Replies	Views
Lack of stable PostgreSQL-communication a major showstopper Data	33	5031	January 8, 2021
[ANN] PythonCall and JuliaCall Package Announcements interoperability , python , pythoncall	84	11324	May 8, 2023
Please recommend a Julia ecosystem for Statistics New to Julia	28	4439	June 8, 2019
Recent experience with Julia as the main data science driver General Usage	18	3731	August 8, 2021
How to Query Trino via REST python client? General Usage question , pycall	1	987	July 28, 2023

How to connect to SPARQL endpoint?

Related topics