26

I recently suggested on the Stack Apps site that a formal API be added to SEDE, and it was suggested I reference the feature request here, for visibility.

My use case is a query that the API does not support, so I thought it would be interesting to get it programmatically from SEDE instead. I wonder if this feature might be of interest to others here? This answer outlines the current ways that SE data is available via various APIs and UIs.

I wonder if my use case (retrieving revisions filtered by user) might have been supported by OData, but that's been turned off since April of this year.

This question has sort-of been asked before, back in 2010, though that was not an explicit feature request, and is now rather out of date. It would be good to find out what the current views are on this topic, and indeed whether there is much demand.

My use-case does not urgently require this, since I can use public SE screen scraping or SEDE CSV downloading. From reading around the SE Meta network, both of these approaches seem to be tolerated, as long as it is done with careful regard to frequency and bandwidth.

5
  • Yes please. There are so many more kinds of data (etc.) that we can get with SEDE that the API does not provide (well or at all). ... A SEDE API would greatly expand the type of useful apps and scripts that could be made. Sep 2, 2015 at 19:18
  • 1
  • 1
    There is already an API of sorts. It's relatively straight forward to create, run, and retrieve query results remotely (maybe I'll document it, or you can just check it out in Fiddler or something), the problem is there's no good way to authenticate, and you'll get hit with the captcha, and the only way around it right now is to occasionally make a query through the web interface in an incognito tab or something and fill out the captcha which grants permission to your IP for a while.
    – Jason C
    May 31, 2017 at 1:43
  • @JasonC where can we find this API you mention?
    – Anoroah
    Feb 2, 2019 at 12:28
  • @Anoroah Reverse engineer it with Fiddler on the SEDE query pages; it's the ajax backend that powers that page.
    – Jason C
    Feb 6, 2019 at 6:51

1 Answer 1

7

This is not an official API, but it is not too hard to simulate the login via OAuth2 and use the session to start executing a query, poll the job that executes it and retrieve the results in the end. I do most of my Stack Exchange development work in Java, but it should be easy to port to other languages even if you don't know Java. I'm using fairly standard libraries: Apache HTTP client, JSON parser org.json and HTML parser Jsoup.

The source code can be found here on GitHub; you'll need to provide your own email address and password which you use for logging in to Stack Exchange at the bottom. To run a query, you'll need the site ID which you can obtain from this list; the query ID (visible in the URL) and the revision ID of the query (visible when you edit the query (left), or otherwise in the HTML of the 'Run Query' form (right)).

Here is the main part of the code, to give you an idea on the complexity.

final CloseableHttpClient client = HttpClients.createDefault();

// Start OAuth2 session
HttpPost postRequest = new HttpPost("https://data.stackexchange.com/user/authenticate");
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("oauth2url", "https://stackoverflow.com/oauth"));
params.add(new BasicNameValuePair("openid", ""));
postRequest.setEntity(new UrlEncodedFormEntity(params));
String loginURL;
try (CloseableHttpResponse httpResponse = client.execute(postRequest)) {
    loginURL = httpResponse.getFirstHeader("Location").getValue();
}

// We need to read and set some cookies in between redirections
RequestConfig noRedirectRequestConfig = RequestConfig.custom().setRedirectsEnabled(false).build();

// Providence cookie
String providenceCookie = null;
HttpGet getRequest = new HttpGet(loginURL);
getRequest.setConfig(noRedirectRequestConfig);
try (CloseableHttpResponse httpResponse = client.execute(getRequest)) {
    loginURL = new URL(new URL(loginURL), httpResponse.getFirstHeader("Location").getValue()).toString();
    for (Header header : httpResponse.getHeaders("Set-Cookie")) {
        String cookie = header.getValue().split(";")[0];
        if (cookie.startsWith("prov=")) {
            providenceCookie = cookie;
            break;
        }
    }
}
if (providenceCookie == null) {
    throw new Exception("No providence cookie found.");
}

// Load login form
getRequest = new HttpGet(loginURL);
getRequest.setHeader("Cookie", providenceCookie);
String fkey, loginActionURL;
try (CloseableHttpResponse httpResponse = client.execute(getRequest);
        InputStream inputStream = httpResponse.getEntity().getContent()) {
    Document document = Jsoup.parse(inputStream, "UTF-8", loginURL);
    loginActionURL = document.getElementById("login-form").absUrl("action");
    fkey = document.selectFirst("input[name='fkey']").val();
}

// Submit login form
postRequest = new HttpPost(loginActionURL);
postRequest.setHeader("Cookie", providenceCookie);
params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("fkey", fkey));
params.add(new BasicNameValuePair("email", EMAIL));
params.add(new BasicNameValuePair("password", PASSWORD));
postRequest.setEntity(new UrlEncodedFormEntity(params));
String redirectURL, accountCookie = null;
try (CloseableHttpResponse httpResponse = client.execute(postRequest)) {
    redirectURL = httpResponse.getFirstHeader("Location").getValue();
    for (Header header : httpResponse.getHeaders("Set-Cookie")) {
        String cookie = header.getValue().split(";")[0];
        if (cookie.startsWith("acct=")) {
            accountCookie = cookie;
            break;
        }
    }
}
if (accountCookie == null) {
    throw new Exception("No account cookie found.");
}

// OAuth2 redirection
String location = redirectURL, authenticationCookie = null;
// 1. https://stackoverflow.com/oauth?client_id=...
// 2. https://data.stackexchange.com/user/oauth/stackapps?code=...
// => /
for (int i = 1; i <= 2; i++) {
    // the state parameter contains JSON, and Java doesn't like that unless it's %-encoded:
    URL url = new URL(URLDecoder.decode(location, "UTF-8"));
    URI uri = new URI(url.getProtocol(), url.getUserInfo(), url.getHost(), url.getPort(), url.getPath(), url.getQuery(), url.getRef());
    getRequest = new HttpGet(uri);
    getRequest.addHeader("Cookie", providenceCookie + (i == 1 ? "; " + accountCookie : ""));
    getRequest.setConfig(noRedirectRequestConfig);
    try (CloseableHttpResponse httpResponse = client.execute(getRequest)) {
        location = httpResponse.getFirstHeader("Location").getValue();
        System.out.println(location);
        if (i == 2) {
            for (Header header : httpResponse.getHeaders("Set-Cookie")) {
                String cookie = header.getValue().split(";")[0];
                if (cookie.startsWith(".ASPXAUTH=")) {
                    authenticationCookie = cookie;
                    break;
                }
            }
        }
    }
}
if (authenticationCookie == null) {
    throw new Exception("No authentication cookie found.");
}

// Execute query
int siteID = 1; // 1 = Stack Overflow; see https://data.stackexchange.com/sites
int queryID = 748298, revisionID = 1445524;
postRequest = new HttpPost(
        "https://data.stackexchange.com/query/run/" + siteID + "/" + queryID + "/" + revisionID);
params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("badgename", "Legendary"));
postRequest.setEntity(new UrlEncodedFormEntity(params));
JSONObject jRoot;
String jobID;
try (CloseableHttpResponse httpResponse = client.execute(postRequest)) {
    jRoot = new JSONObject(IOUtils.toString(httpResponse.getEntity().getContent(), "UTF-8"));
    jobID = jRoot.has("job_id") ? jRoot.getString("job_id") : null;
    if (jobID != null) {
        // Results not in cache yet
        System.out.println("Starting job: " + jobID);
    }
}
while (jRoot.optBoolean("running")) {
    Thread.sleep(1000);
    // Poll job
    getRequest = new HttpGet(
            "https://data.stackexchange.com/query/job/" + jobID + "?_=" + new Date().getTime());
    try (CloseableHttpResponse httpResponse = client.execute(getRequest)) {
        jRoot = new JSONObject(IOUtils.toString(httpResponse.getEntity().getContent(), "UTF-8"));
    }
}

// Process results
if (jRoot.has("resultSets")) {
    String messages = jRoot.getString("messages");
    int totalResults = jRoot.getInt("totalResults"); // NOTE: only populated when the results weren't cached
    int executionTime = jRoot.getInt("executionTime");
    JSONArray jResultSets = jRoot.getJSONArray("resultSets");
    for (int i = 0; i < jResultSets.length(); i++) {
        JSONObject jResultSet = jResultSets.getJSONObject(i);
        JSONArray jColumns = jResultSet.getJSONArray("columns");
        for (int j = 0; j < jColumns.length(); j++) {
            JSONObject jColumn = jColumns.getJSONObject(j);
            String name = jColumn.getString("name"), type = jColumn.getString("type");
        }
        JSONArray jRows = jResultSet.getJSONArray("rows");
        for (int j = 0; j < jRows.length(); j++) {
            JSONArray jRow = jRows.getJSONArray(j);
        }
    }
} else {
    String error = jRoot.has("error") ? jRoot.getString("error") : null;
}
System.out.println(jRoot);
12
  • 2
    This post seems to be saying that OpenID-based login is not supported anymore ( or will be stopped to be supported soon) - meta.stackexchange.com/questions/307647/…. I see that your approach still seems to work when I tried it out. But will this be continued to be supported or is going to be deprecated soon?
    – raswamin
    Jul 9, 2020 at 6:37
  • 1
    @raswamin yes, when they phase out OpenID I'll need to redesign it (or perhaps invoke another OpenID provider). They planned to do so 2 years ago but it's still working ...
    – Glorfindel Mod
    Jul 9, 2020 at 6:43
  • Thanks. Also I don't see how pagination would work when the result sets are huge: For example, I get a result like this: { "resultSets": [ { "rows": [], "truncated": true } ], "messages": "(50000 row(s) returned)\n\n", "siteId": 1, "siteName": "stackoverflow", "queryId": 0, "totalResults": 0, "maxResults": 0, "firstRun": "Jul 9 2020", "truncated": false, "revisionId": 1552395, "fromCache": true, "querySetId": 1260731, }
    – raswamin
    Jul 9, 2020 at 6:46
  • There is no pagination on SEDE. The maximum # of results is 50,000.
    – Glorfindel Mod
    Jul 9, 2020 at 6:47
  • Ok... But is it possible to pass an argument to the QueryID/RevisionID combination - Say some date-based filter which I can run in loop to get small subsets of results at a time to not exceed 50K count?
    – raswamin
    Jul 9, 2020 at 6:51
  • 1
    Yes. The source code above contains an example: params = new ArrayList<NameValuePair>(); params.add(new BasicNameValuePair("badgename", "Legendary"));
    – Glorfindel Mod
    Jul 9, 2020 at 6:51
  • 1
    Cool... Thanks!
    – raswamin
    Jul 9, 2020 at 6:52
  • @Glorfindel the script doesn't work anymore. At this line, it crashed because Location is not found. Can you please update the script? Oct 25, 2020 at 2:44
  • @NearHuscarl I'm pretty sure it's still working for me: web.archive.org/web*/data.stackexchange.com/stackoverflow/query/1236884/… (that's essentially the same script). Have you already done a manual login via OAuth2? What exactly is that httpResponse giving in your case (status code, response body, relevant headers)?
    – Glorfindel Mod
    Oct 25, 2020 at 10:28
  • @Glorfindel I managed to get the .ASPXAUTH=... cookie by converting your code to Javascript (NodeJs) after reading the Java code line by line very carefully, I don't use Java so I don't know why your code doesn't work on my machine, but it's likely my fault somewhere. Anyway thank you very much for the guidance. This is one of the most useful answers I've read, I'd been stuck very badly before then. Oct 26, 2020 at 1:38
  • @NearHuscarl Great! It's by far not the most elegant code I wrote (basically "works on my machine", but I'm glad somebody actually used it. Perhaps you could share your solution as well – no doubt more people will try this with JavaScript and you can give them a head start.
    – Glorfindel Mod
    Oct 26, 2020 at 7:06
  • 2
    @Glorfindel here is the project in the early state, it's mostly your implementation rewritten in javascript, I'll make a public post once I fix incoming bugs (if any). Keep in mind that I'm not a back-end developer so some coding practices may need to be improved overtime. Oct 27, 2020 at 9:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .