How to Query from Python

RdbHost provides databases as a webservice. Accessing your RdbHost database can be accomplished via Python http calls, through the standard library.

The recommended way to access RdbHost databases from Python is via the DB API module, but this page documents lower-level methods available where the DB API will not serve.

Python Standard Library

The urllib and urllib2 modules provide the tools to retrieve xml pages from remote servers.

Here is a simple example that uses the server as a simple calculator, adding 1 and 1. This will work with any valid role on any database, as no specific privileges are required on any resource within the database.

1 from urllib import urlencode 2 from urllib2 import Request, urlopen 3 4 def postit(url,fields): 5 6 postdata = urlencode(fields) 7 headers = {'Content-Type': 'application/x-www-form-urlencoded', 8 'Content-Length': str(len(postdata))} 9 r = Request(url, postdata, headers) 10 11 pg = urlopen(r) 12 text = 13 return text 14 15 role = 'r000004' 16 flds = [ ('q', 'SELECT 1+1 as sum') ] 17 url = ''+role 18 val = postit(url,flds) 19 print val download source

The above example is a complete working script that executes the addition query on the server and prints the result page.

The customization per-query is in lines 11 and 12. Other query parameters can be added to the flds list in line 12. Now let's look at the returned page:

1 <xml xmlns=""> 2 <status value="complete">OK</status> 3 <row_count value="1">1 Rows Affected</row_count> 4 <records> 5 <header> 6 <fld type="23">sum</fld> 7 </header> 8 <rec> 9 <fld>2</fld> 10 </rec> 11 </records> 12 </xml>

Line 2 contains the status. That value will be 'complete', 'incomplete', or 'error'.

The next tag (line 3) is the rowcount tag, showing how many affected rows the server reported.

Since this query returned one result, there is a 'records' container tag, containing a header and a row. In line 6, the only field is described as type "23", which is PostgreSQL's identifier for array-of-int, and named 'sum', per the query.

If there was an error, the text of the status tag would contain the name of any error, and the next tag would be an error tag, with error code and error message.

1 <xml xmlns=""> 2 <status value="error">DatabaseNotOpened</status> 3 <error code="None">FATAL: password authentication failed for user "r000004" 4 </error> 5 </xml>

The error code (see line 3) for RdbHost's own errors is always '-'. Other error codes and error messages are pass-throughs from the relevant subsystem, such as PostgreSQL or the XML library.

A slightly more elaborate example uses the super account, with a password and an argument, and gets results formatted as JavaScript Object Notation JSON. Lines 11-13 from the example above change to:

11 role = 's000004' 11b password = 'abcABCdefDEF' 12 flds = [ ('q', 'SELECT %s+1 as sum'), 12b ('format', 'json'), 12c ('authcode', authcode), 12d ('arg000',1)] 13 url = ''+role download source

The example above will not function as is, because the example password is invalid. It will work for you, though, if you create a free account and substitute the correct role and password.

Line 12d provides one argument. The query, 'q', in line 12 has an interpolation token '%s'. arg000, 1, will be safely interpolated into the query before execution of a query.

The results page follows. The content is the same, but is JSON. It can be deserialized by javascript 'eval', or by JSON libraries in most web programming languages.

1 { 2 "records": { 3 "header": [ 4 [ 5 23, 6 "sum" 7 ] 8 ], 9 "rows": [ 10 [ 11 2 12 ] 13 ] 14 }, 15 "row_count": [ 16 1, 17 "1 Rows Affected" 18 ], 19 "status": [ 20 "complete", 21 "OK" 22 ] 23 }

Here is a JSON-style error page.

1 { 2 "error": [ 3 "None", 4 "FATAL: authentication failed \"r000004\"\n" 5 ], 6 "status": [ 7 "error", 8 "DatabaseNotOpened" 9 ] 10 }

See Error Codes for more information on errors and Result Formats for information on the different formats available.