Starting Python, Elixir, and SQLite

When I did the post about Storm, someone suggested that I look into Elixir. Since I didn’t have time to at the time, I made a note of looking into it at a later time.  That time is now. :)

Elixir and Storm are very similar, they’re both object relational mappers that provide an easy way to map your objects to database tables.  In a future post, I’ll do a more in depth comparison between the two in a future post.

Starting out, Elixir uses SQL Alchemy as a backend.  While working with the tool you will probably find yourself running into things you may not understand if you’re not familiar with SQL Alchemy.  Keeping open a tab in firefox pointed at the SQL Alchemy documentation can be useful.  It does show through in certain instances.

There are two main starting points for an ORM tool.  There is the case where you’re starting with an existing database, and the case where you’re setting up the database from scratch.  Mapping to a table that already exists with Elixir can be a little tricky depending on the relationships.

It’s as simple as this to connect to a database:

metadata.bind = "sqlite:///../sizedb.sqlite"

Here is a simple example:

class Location(Entity):
    using_options(tablename='TABLE_LOC')
    loc_id = Field(Integer, primary_key=True)
    location = Field(UnicodeText)

And here is a more complex example of connecting to an existing database table:

class Comparison(Entity):
    using_options(tablename='TABLE_COMP')
    comp_id = Field(Integer, primary_key=True)
    date_added = Field(DateTime, default=datetime.datetime.now)
    hits = Field(Integer, default=1)
 
    smaller = ManyToOne('Phrase', colname='smaller_id')
    larger = ManyToOne('Phrase', colname='larger_id')
    sentences = ManyToMany('Sentence', tablename='TABLE_COMP_SENT',
                           local_side='comp_id', remote_side='sent_id', column_format="%(key)s")

I left out most of the class specific code to focus on Elixir.  One thing that took a while to figure out was how to setup a ManyToMany relationship with specific columns in my database.  The column_format parameter is the key to being able to specify your column names directly.  I really didn’t have to use any other options besides what you see above when connecting to an existing database.  Overall, I had about five database tables to connect.

Now if it was not being setup with an existing database, many of the parameters in the different relationships are unnecessary.  For comparison here is the same example if Elixir is used to create the database tables:

class Comparison(Entity):
    comp_id = Field(Integer, primary_key=True)
    date_added = Field(DateTime, default=datetime.datetime.now)
    hits = Field(Integer, default=1)
 
    smaller = ManyToOne('Phrase')
    larger = ManyToOne('Phrase')
    sentences = ManyToMany('Sentence')

As you can see, it gets quite a bit simpler.  The underlying table information is no longer needed.  It created tables that were very similar to my hand-created tables that I had used with Storm.  When it comes to queries on the database, SQL Alchemist shows through.

I found the documentation on the Elixir webpage to be a little bit lacking in terms of queries.  SQL Alchemist has a page that more fully describes the query functions.  AND and OR operators are named and_ and or_, respectively, probably because and and or are reserved in Python.  I thought this was worth mentioning because they are common SQL operators.