Elixir is a lightweight declarative layer on top of SqlAlchemy that’s been around since 2006, well before SqlAlchemy released their own built-in Declarative syntax. Elixir was created by a collaboration between Jonathan LaCour, Daniel Haus and Gaëtan de Menten who had a passion for making SqlAlchemy even easier to use. In this tutorial, we’ll look at how to to create our own database with Elixir and how to communicate with a pre-existing database.

Note: This is my first time using Elixir so I wouldn’t be surprised if there are better ways to do some of the stuff mentioned herein.

Creating a Database with Elixir

For this section, we’re going to look at creating a super simple bookmark database where each record is made up of a title, a url and a description. It will also have an id column that will be our primary key. Elixir does this for us automatically whenever we create a database without a primary key specified and it will auto-increment the id as well. Let’s get this party started with some code!

from elixir import metadata, Entity, Field
from elixir import Unicode, UnicodeText
 
metadata.bind = "sqlite:///bookmarks.sqlite"
metadata.bind.echo = True
 
########################################################################
class Bookmarks(Entity):
    """
    Custom made bookmark example
    """
    title = Field(Unicode)
    url = Field(Unicode)
    description = Field(UnicodeText)
 
    #----------------------------------------------------------------------
    def __repr__(self):
        """"""
        return "<Bookmark '%s' (%s) - %s" % (self.title, self.url, 
                                             self.description)
 
if __name__ == "__main__":
    from elixir import create_all, setup_all, session
    setup_all()
    create_all()
 
    # Adding records
    Bookmarks(title="Google", url="http://www.google.com", 
              description="Search engine")
    Bookmarks(title="Digital Photography School", 
              url="http://www.digital-photography-school.com",
              description="An Australian Photography blog")
    Bookmarks(title="Mouse Vs. the Python",
              url="http://www.blog.pythonlibrary.org/",
              description="Mike Driscoll's Python blog")
    session.commit()
 
    # --------------------------------------------------
    # Simple Queries
 
    # get all the records
    records = Bookmarks.query.all()
    for record in records:
        print "-" * 20
        print record.title
        print record.url
        print record.description
 
    # find a specific record
    qry = Bookmarks.query.filter_by(title=u'Google')
    record = qry.first()
    print "%s (%s)" % (record.title, record.url)
 
    # delete the record
    record.delete()
    session.commit()

Let’s take a moment to study this code and see if we can figure it out. In this example, we import just the classes we want from the elixir package.

After the imports are two lines dealing with metadata. According to Gaëtan de Menten, the metadata is “a container of python objects representing database tables.” Anyway, what happens in those lines is the creation of the sqlite database file (assuming it doesn’t already exist) and the setting of the echo to True. That means that all the SQL that Elixir emits will be sent to stdout so you can read it too.

Next we subclass the Entity object in our Bookmarks class. Here we create our three fields (or columns). The “__repr__” basically makes the printing of the Bookmarks objects more friendly for humans such that it prints out the title, url and description of the Bookmark rather than just something like “Bookmark object “.

Lastly, in the “if” statement at the end, we setup the mappings and such and create the database. Then we add three rows and commit them to said database. Next, we do a couple of simple queries against the database and at the very end, we delete a record. Hopefully you can follow all that without explicit description.

For our next trick, we will look at how to read an already existing database with Elixir.

Reading a Pre-Existing Database

In this example, we will use a copy of the places.sqlite database that is generated by Mozilla Firefox. It holds bookmarks and (I think) your browsing history. The code in this example will assume that the database file has been copied into the same location as the Python code file. Let’s take a look at that code now:

from elixir import metadata, using_options, Entity, Field
from elixir import Integer, Unicode, UnicodeText
 
metadata.bind = "sqlite:///places.sqlite"
metadata.bind.echo = True
 
########################################################################
class Bookmarks(Entity):
    """
    Firefox Bookmarks class
    """
    using_options(tablename="moz_bookmarks")
 
    _id = Field(Integer, colname="id", primary_key=True)
    _type = Field(Integer, colname="type")
    fk = Field(Integer)
    parent = Field(Integer)
    position = Field(Integer)
    title = Field(Unicode)
    keyword_id = Field(Integer)
    folder_type = Field(UnicodeText)
    dateAdded = Field(Integer)
    lastModified = Field(Integer)
 
    #----------------------------------------------------------------------
    def __repr__(self):
        """Constructor"""
        return '<Bookmark "%s" - "%s"' % (self._id, self.title)
 
 
if __name__ == "__main__":
    from elixir import setup_all
    setup_all()
    result = Bookmarks.query.all()
    x = 0
    print "-" * 10
    for item in result:
        if x > 10:
            break
        print item.title
        x += 1

The first few lines are basically the same as the ones in the previous example. The only real difference is that we specify a database that exists this time and we imported a few more classes. In the Bookmark class, we need to mirror the columns in the “moz_bookmarks” database. There’s a difference right there. We use the “using_options” class from Elixir to specify which table we want. Then we create a matching field name. The first two fields would actually shadow Python builtins, so we give them an underscore to differentiate them and set the column name by passing it in the colname keyword argument of the Field class.

When testing this code, I noticed that some of the titles were nulls, so the __repr__ may not work as expected. Anyway, in the “if” statement at the end, we do a query against the table and print out the first 10 records we find.

As a side note, if you don’t want to declare all the field names, you don’t have to. Elixir supports the same autoload functionality that SqlAlchemy does. So instead of declaring the field names, you would do something like this: using_options(tablename=”moz_bookmarks”, autoload=True)

Wrapping Up

As you can see, using Elixir is pretty simple. SqlAlchemy has since added its own built-in declarative method, but for some, Elixir proves to be a potent help. There are many more query examples and relational examples on their official website (see link below). I encourage you to check it out and see if you find it helpful in learning SqlAlchemy.

Further Reading

This code was tested on the following:

  • Windows 7 Home Premium with Python 2.6.4, SqlAlchemy 0.6.5, Elixir 0.7.1
  • Windows XP Professional with Python 2.5, SqlAlchemy 0.5.0, Elixir 0.7.0
Print Friendly