0

I'm interested in analyzing various aspects of the content in our Drupal site involving node meta data such as type, name, creation date, etc. I know I can create views to show some informaiton and I know I can dump out the database and analyze it separately, but I'd really like to query the database live (for read purposes only) from a remote machine so that I can use the software of my choice to do the analysis, reporting, etc.

Does Drupal have a remotely accessible API that I can use query its database?

(Note: I suspect the "db-api" tag is inappropriate for this question, but I couldn't figure out anything else to use.)

  • Why can't you connect directly to MySQL / Postgres? – Mołot Sep 16 '13 at 20:18
  • That's possible, but while I've been given admin privileges in Drupal so that I can do some limited admin functions, the site is being maintained by someone else and I have no idea whether the database is configured to be remotely accessible. I was also hoping to avoid raw SQL if I could. – Peter Alfvin Sep 16 '13 at 20:46
1

If

  • you want to programatically work with data,
  • reading them is enough,
  • you don't have direct DB access (which is not such a good idea anyways, for security reasons - you'd have to set up SSL tunnels to access the DB), and
  • you can dig all the data you need using Views (you can always create some custom module to dig data which Views wouldn't have direct support),

I would suggest using Services Views module. Services Views turns a View into REST service endpoint. You can then query LIVE data you need from an external application. For enhanced security the endpoint (and service authentication, etc.) should be wrapped in HTTPS.

  • Thanks, David. This does look to be the most promising. One question: When I followed the link, it appears that there's only a "development" version for the Drupal 6. Do you know if they are going to be "releasing" a version for Drupal 6 any time soon? – Peter Alfvin Sep 16 '13 at 21:54
  • From the issue queue of the module, it seems that some work on porting features from D7 to D6 version was done. Also there is a number of sites reporting usage of D6 version of the module. Unfortunately I can't confirm the state of D6 version, we've used only D7 version. Nevertheless I think it's worth a try :-). – David Lukac Sep 17 '13 at 15:40
1

If you want easily importable data, consider using Views data export:

This module is designed to provide a way to export large amounts of data from views. It provides a display plugin that can rendered progressively in a batch. Style plugins are included that support exporting in the following types:

  • CSV
  • Microsoft XLS
  • Microsoft DOC
  • Basic TXT
  • XML

You can import CSV or XML to pretty much any data analysis tool you may need.

  • Thanks, but I really need the data to be live. – Peter Alfvin Sep 16 '13 at 21:54
  • @PeterAlfvin what do you mean "live"? You are not forced to cache views data. The fact it will be refreshed on request? SQL queries are no more live anyway. – Mołot Sep 17 '13 at 6:40
  • I apologize. I made a bad assumption that this was something that just exported locally. After looking into it, I see that you can create a display with its own path for getting to the data via the web. I think I still prefer the Services Views approach, but +1 for the answer. – Peter Alfvin Sep 17 '13 at 17:20
  • @PeterAlfvin no need to apologize. Services are more flexible and powerful all right. Views Data Export is simpler to set up. Depends on particular needs. – Mołot Sep 17 '13 at 19:45
0

In the views UI settings, you can check an option in this url :

admin/structure/views/settings

check "Show the SQL query"

So, in the view's UI live preview, you can copy/paste and re-use theses queries in another code

It is maybe not the best way but it is one, and you do not have to write complex queries joins, views UI do it for you

Theses queries can be executed from anywhere (even directly in mysql), without the drupal boostrap

  • 1
    The SQL from Views isn't always accurate anymore. Some views results are built up after doing an entity_load_multiple(), and then using the Field API to get data. – mpdonadio Sep 16 '13 at 15:20
  • 1
    This also doesn't seem to address the need to be able to retrieve the data from a remote machine. – Peter Alfvin Sep 16 '13 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.