21

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.

  • 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. – Awesome Poodles Sep 2 '15 at 19:18
  • 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 '17 at 1:43
  • @JasonC where can we find this API you mention? – Anoroah Feb 2 '19 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 '19 at 6:51
3

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. http://data.stackexchange.com/user/oauth/stackapps?code=...
// 3. https://data.stackexchange.com/user/oauth/stackapps?code=...
// => /
for (int i = 1; i <= 3; i++) {
    getRequest = new HttpGet(location);
    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 == 3) {
            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);
| improve this answer | |
  • 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 at 6:37
  • @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 Jul 9 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 at 6:46
  • There is no pagination on SEDE. The maximum # of results is 50,000. – Glorfindel Jul 9 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 at 6:51
  • 1
    Yes. The source code above contains an example: params = new ArrayList<NameValuePair>(); params.add(new BasicNameValuePair("badgename", "Legendary")); – Glorfindel Jul 9 at 6:51
  • 1
    Cool... Thanks! – raswamin Jul 9 at 6:52

You must log in to answer this question.

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