Several database backends are supported with htables. The most natural is PostgreSQL because it has a native hstore column type. SQLite is also supported, and it’s useful when running unit tests, because setup, teardown, and operations on small amounts of data are much faster with SQLite, especially if the database is in-memory.
We instantiate a DB object to connect to a database, passing in a connection string. We actually have a session pool at this point. It’s a good idea to activate debug mode if the application is running in development or unit test mode. This will make sure that we don’t save integers or other non-string values in the database.
>>> database = htables.SqliteDB(':memory:', debug=True)
To actually read and write data we need a Session. It’s a proxy to access Table objects, blob files, and handles transactions. The session pool gives out session objects and collects them back when we’re done. Be aware that put_session() aborts the current transaction.
>>> session = database.get_session()
>>> try:
... # do some stuff
... session.commit()
... except:
... session.rollback()
... finally:
... database.put_session()
Tables are collections of rows. They are in fact SQL tables with a simple schema: an integer auto-incremented unique id, and a data column. The create_table() and drop_table() methods are available on the Table object:
>>> person_table = session['person']
>>> person_table.create_table()
Tables also have methods for creating and fetching rows. new() creates a record, find() fetches all matching records, find_first() and find_single() are useful when we only need to get one record, and get() fetches a record based on its id:
>>> person = person_table.new(name="Joe")
>>> person.id
1
>>> people = list(person_table.find())
>>> first_person = person_table.find_first()
>>> joe = person.find_single(name="Joe")
>>> joe
{u'name': u'Joe'}
>>> joe = person_table.get(1)
find() performs equality comparison for its keyword arguments and returns an iterator over all matching rows. The convenience methods find_first() and find_single() return a single row. If no row is found they raise a RowNotFound exception. Additionally, with find_single(), if several rows match the query, it raises a MultipleRowsFound exception. The exceptions are conveniently aliased on the Table object.
A Row represents a record in a table. It has a unique integer id that is generated by the database backend, there is no support for custom id values. Rows are never instantiated directly; they are obtained via methods of Table.
The Row object is a Python dict whose keys and values are restricted to unicode strings. This restriction is enforced at save() time if the database was opened with debug=True. The database backend may also refuse to store non-string values.
Rows are created by calling Table.new() which takes the same arguments as the Python dict constructor. This method will actually save the row in the database in order to generate its id. The returned row object is identical to a row returned by the Table.find() methods. After changing a row, call its save() method to write it to the database. delete() removes the row. All changes are written in a transaction so they only become permanent after calling Session.commit().
>>> person['name']
u'Joe'
>>> person['email'] = 'joe@example.com'
>>> person.update({'some': 'more', 'data': ''})
>>> person.save()
Note
Any changes to Row objects are only made in Python memory. They are written to the database (pending transaction commit) when calling Row.save(). This means that any unsaved changes are not reflected in calls to Table.find(), it will just return new copies of the old rows from the database.