Archive for category Packages

“dulce” becomes “littletable”

It’s funny how a little experiment can start to take on momentum all by itself. After looking at other Python databases, it wasn’t long before Google’s BigTable cropped in my searches.  This suggested to me a more descriptive and maybe more appopriate name for my experiment – littletable.  It’s expectations are modest, and so it has a fairly modest-sounding name.

Tables of objects are created simply by creating an empty table and loading like objects into it.  No schema, no SQL.  The attributes of the objects themselves, and the attributes used in the queries and joins, describe an organic, emergent schema.  I loaded a data table of zipcodes by state (from xxx), and a table of states.  There are a total of over 42,000 defined zipcodes (data as of 1999).  Here is a query of zipcodes:

fullzips = (zips.join_on("statecode") + states)()

A table can keep an index on a particular attribute, with the option to require uniqueness or not.  Indexes are used at join time to optimize the join performance, by minimizing the number of records that have to be sifted through.

The latest version of littletable (0.3) now includes table pivoting.  This makes it very easy to look at data in a large table to see how it is distributed across particular keys.  For instance, here is a table of the top 20 states with the most zip codes:

    TX Texas             2676
    CA California        2675
    NY New York          2238
    PA Pennsylvania      2224
    IL Illinois          1595
    OH Ohio              1470
    FL Florida           1449
    VA Virginia          1253
    MO Missouri          1192
    MI Michigan          1169
    NC North Carolina    1083
    IA Iowa              1073
    MN Minnesota         1036
    KY Kentucky          1016
    IN Indiana            992
    GA Georgia            975
    WV West Virginia      930
    WI Wisconsin          914
    AL Alabama            847
    TN Tennessee          806

created by “pivoting” the zip code table on the single attribute stateabbr.

The states with the fewest zip codes are:

    GU Guam                21
    VI Virgin Islands      16
    FM Federated State      4
    MP Northern Marian      3
    MH Marshall Island      2
    AS American Samoa       1
    PW Palau                1

And this query:

    nozips = states.where(lambda o:o.statecode not in zips.statecode)

returns a single record:

    ['UM', None]

(“UM” is the postal state abbreviation for the U.S. Minor Outlying Islands, a group of uninhabited islands SW of Hawaii – see  http://en.wikipedia.org/wiki/U.S._Minor_Outlying_Islands).

A nice characteristic of littletable queries and joins is that they each return a new fully-functional table, containing the joined and/or filtered records described in the query.  Tables can then be exported to CSV files, making it easy to save and restore the results of a particular query.  Tables are just wrappers around Python lists, so it is still possible to access parts of them using slice notation.

Here is a query from a database of US place names, retrieving all of the tunnels in the US, sorted by descending elevation.

    tunnels = us_names.query(feature="TNL", _orderby="elev desc")

Using basic python slicing, we can then find the 15 highest and 15 lowest tunnels in the country:

    for t in tunnels[:15]+tunnels[-15:]:
        print "%-30.30s %s %5d" % (t.name, t.state, t.elev)

Giving:

    Twin Lakes Reservoir and Canal CO  4003
    Harold D Roberts Tunnel        CO  3763
    Eisenhower Memorial Tunnel     CO  3728
    Twin Lakes Reservoir Tunnel Nu CO  3709
    Ivanhoe Tunnel                 CO  3680
    Vasquez Tunnel                 CO  3653
    Old Alpine Tunnel (historical) CO  3639
    Hagerman Tunnel                CO  3637
    McCullough Tunnel              CO  3635
    Strickler Tunnel               CO  3608
    August P Gumlick Tunnel        CO  3605
    Charles H Boustead Tunnel      CO  3603
    Quandary Tunnel                CO  3574
    Chapman Tunnel                 CO  3561
    Hoosier Pass Tunnel            CO  3552
    Harvey Tunnel                  LA     2
    Posey Tube                     CA     2
    Harbor Tunnel                  MD     0
    Baytown Tunnel                 TX     0
    Chesapeake Channel Tunnel      VA     0
    Downtown Tunnel                VA     0
    Midtown Tunnel                 VA     0
    Thimble Shoal Channel Tunnel   VA     0
    Holland Tunnel                 NJ     0
    Lincoln Tunnel                 NJ     0
    Brooklyn-Battery Tunnel        NY     0
    Pennsylvania Tunnnels          NY     0
    Queens Midtown Tunnel          NY     0
    Webster Street Tube            CA     0
    Rapid Transit Trans-Bay Tube   CA    -2

So it isn’t necessary to support every SQL feature in the litletable API, since the objects *are* in what is essentially a list structure.

So far littletable has been a handy little tool for quick data manipulation, and maybe some simple database concept experimentation. Not sure if there is much more I really need to add – we’ll see if there are many takers out there for this little recipe.

1 Comment

dulce – a little Python ORM

Not sure how I got started with this, I think I was looking at some ORM-style APIs and wanted to try my hand at it. Not too surprising, my result is reminiscent of pyparsing – using operator ‘+’ to define table joins, and __call__ to execute joins and queries. I called this little project “dulce”, as it is really little more than a syntactic sweet, a wrapper around a Python list of Python objects. But here’s two things I like:

  • a simple join syntax:
    wishlists = customers.join_on("id") + wishitems.join_on("custid") + catalog.join_on("sku")
  • a simple query-by-key syntax:
    print customers.id["0030"].name

Also, all queries and joins return a new full-fledged dulce Table object, so chaining queries, or exporting the output of joins is very easy.  And there is no schema definition, the schema “emerges” based on the object attributes used in queries and joins.

As it is pure Python, I think it would be ridiculous to make claims about how fast this is, although with psyco acceleration, loading and joining a table with 10,000+ zip codes takes about 2 seconds. I guess the biggest advantages would be:

  • pure Python portability
  • small Python footprint – single script file, about 500 lines long, including docs
  • quick start to organize a collection of objects
  • simple join interface

I created a SourceForge project for this little thing, you can find it here. I haven’t even packaged any releases yet, but the source can be extracted from the project SVN repository.

Leave a comment