Deprecated - This page is no longer relevant, See: Latest

How it Works: Python DBI

How to Query from Python

RdbHost provides databases as a webservice. The easy way to access your RdbHost database from Python is via the DBI module Rdbhdb.

Rdbhdb

The module follows the conventions of the Python DB API version 2 as far as possible. It does not work exactly the same as other PostgreSQL DB API modules, in particular with regard to transactions.

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 rdbhdb import rdbhdb as db 2 import rdbhdb.extensions 3 4 # connect to the RdbHost server 5 role = 'r0000000004' 6 authcode = '-' 7 conn = db.connect (role, authcode=authcode) 8 9 # create a dictionary cursor 10 cur = conn.cursor(rdbhdb.extensions.DictCursor) 11 12 # execute the addition query 13 cur.execute ("SELECT 1+1 AS sum") 14 15 # get the result record 16 rec = cur.fetchone() 17 18 # print results 19 print 'The sum of 1 and 1 is: ', rec['sum'] download source

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

Details on DB API

There is complete documentation for the module in the module itself. The DB API itself is documented in Pep 249.

Differences from other PostgreSQL DB API modules.

As a webservice, RdbHost does not offer true persistent database connections that can carry a transaction. It instead starts and completes a transaction for each .execute() method call. You can take advantage of PostgreSQL's transactions by writing blocks of SQL code with conditional commits and rollbacks and executing those blocks with .execute() calls.

The webservice limits the number of records returned from a query to 100. If you need more records than that, you can either write a sequence of queries to get the necessary records, or you can use a handy feature of the DB API module: the autorefilling cursor. Just set the autorefill attribute on the connection to enable autorefilling.

conn.autorefill = True

Any cursor can be used in autorefill mode. The autorefill mode catches the limit exception, rewrites the query with new offsets and limits, and requests more records. This mode is not enabled by default, because a few queries will give undesirable results when used like this.

Alternative

If you do not wish to use the DBI module, database usage can be accomplished via Python http calls, through the standard library. See further details on that approach here.