Table of Contents
Tutorial: Diving in
In this tutorial, we will show you how to create a small and simple model. Before we start please make sure that you already have the Elixir package installed. If not, do so by typing1:
1 If you don't even have "easy_install" yet, please visit the EasyInstall website first and find out how to use it (it's pretty easy, like the name promises).
2. A very simple model
Now fire up your favorite text editor and create a file "model.py" containing the following lines:
from elixir import * metadata.bind = "sqlite:///movies.sqlite" metadata.bind.echo = True class Movie(Entity): title = Field(Unicode(30)) year = Field(Integer) description = Field(UnicodeText) def __repr__(self): return '<Movie "%s" (%d)>' % (self.title, self.year)
What does this snippet do? First of all it connects to an SQLite-database2. Then it turns on echo on our connection to that database. This means that all SQL statements that get sent to that database will also be displayed on the console. Pretty handy to understand what happens behind the scene and for debugging. Remember you can turn it off whenever you like by simply setting it to False.
Then it declares a Movie-entity (ie. a class, that inherits Elixir's Entity-baseclass). This entity will hold three fields:
- holds up to 30 unicode chars, which represent the movie's title
- an integer containing the year the movie was released
- this could be a plot summary, a review, or any long string of text that you like.
The __repr__()-method below is totally unrelated to Elixir, it just tells the python interpreter to print objects in a human-readable way. It's nice to have, but fully optional. We have put this into our model so that we can easily trace what is happening in an interactive python interpreter.
We'll see more about defining fields for your entities later in this tutorial, but let's first try what we got here.
What time is it? It's database-table-creation-time! Fire up your python interpreter of choice (preferably IPython) and hack in the lines below3 (only lines starting with ">>>", of course):
>>> from model import * >>> setup_all() >>> create_all()
With that setup_all() call, you have created an SQLAlchemy Table object as well as a Mapper object for the Movie class. The Table object is a python object describing what the table in your database will look like. The mapper is another python object describing how your class maps to that table. Both of these objects are automatically generated based on the definition of your class.
Then, by calling, create_all(), you've told Elixir to take all the Table objects it knows of, and create real tables by issuing SQL statements on the database. As you'll see on your console, here is the SQL that gets generated for the above entity:
CREATE TABLE model_movie ( id INTEGER NOT NULL, title VARCHAR(30), year INTEGER, description TEXT, PRIMARY KEY (id) )
There are two important points to notice here. First, the name of the table: By default Elixir, takes the name of the module followed by the name of your class lower cased. You can change that by using options, as we'll see later on. The other point is that Elixir generated an id column and used it as the primary key for our Entity. This is because we didn't provide a primary key ourselves. If you want to specify the primary key yourself, no problem just pass primary_key=True to the field(s) you want to act as the primary key.
Now, we'll instantiate our first movie-object. You could add more movies here, but so far "Blade Runner" does the job.
>>> Movie(title=u"Blade Runner", year=1982) <Movie "Blade Runner" (1982)>
Because SQLAlchemy tries to do as many operations as possible in one single operation (a so called Unit of Work), which is very efficient, the data has not been written to the database table yet. You can tell SQLAlchemy to do so by typing:
This will tell SQLAlchemy to generate all of the SQL to insert the Movie into the database, and then execute that SQL and finally commit the current transaction. Now, to see a list of all the movies in our database, simply type:
>>> Movie.query.all() [<Movie "Blade Runner" (1982)>]
Not many, but exactly what we expected. Now let us modify our test movie:
>>> movie = Movie.query.first() # .first() is equivalent to (but nicer than) .all() >>> movie.year = 1983 >>> session.commit() >>> Movie.query.all() [<Movie "Blade Runner" (1983)>]
We can even delete it easily:
>>> movie.delete() >>> session.commit()
There should not be any movie left in the database now !
>>> Movie.query.all() 
Close the interpreter now and delete the database file (movies.sqlite)4, we will recreate and populate it in the next step.
So far you've seen how to declare simple entities, create objects, store them to the database and retrieve them again, modify them, or even delete them. Not too much magic, but a lot more pleasant to the eye compared to calling lowlevel SQL-statements.
3 Make sure, you're running your interpreter from the directory where you saved the model.py file.
4 If you're using any DBMS other than SQLite, just drop the created table (most probably something like "DROP TABLE model_movie;").
4. Simple relationships
Now we will do something more advanced. Movies need a director, so we'll add a new entity "Director" to our model.py:
class Director(Entity): name = Field(Unicode(60)) def __repr__(self): return '<Director "%s">' % self.name
The __repr__()-method is totally optional, again. Now, we could start the interpreter again and instantiate some directors, but before we do that, we want to tell Elixir how to relate movies and directors to add more interest. Add two lines to your model.py, so it reads:
class Movie(Entity): title = Field(Unicode(30)) year = Field(Integer) description = Field(UnicodeText) director = ManyToOne('Director') # <-- add this line def __repr__(self): return '<Movie "%s" (%d)>' % (self.title, self.year) class Director(Entity): name = Field(Unicode(60)) movies = OneToMany('Movie') # <-- and this one def __repr__(self): return '<Director "%s">' % self.name
In each case here, the first parameter to a relationship is the name of the class that is being related to. The consequence to what we have done here is that a Movie object will now contain a reference to the appropriate Director object called 'director', and Director objects will contain a list of Movie objects called 'movies'.
Again, start your python interpreter, ensure that the old database file (movies.sqlite) has been deleted, and then create your new model's tables:
>>> from model import * >>> setup_all(True)
Remember what we did in step 3? We used two separate calls to first create the python objects representing the Table (and Mapper), then create the SQL tables out of that. Now we used the shortcut syntax: setup_all(True) which does both in one step. Pretty handy for testing purposes5.
CREATE TABLE model_director ( id INTEGER NOT NULL, name VARCHAR(60), PRIMARY KEY (id) ) CREATE TABLE model_movie ( id INTEGER NOT NULL, title VARCHAR(30), year INTEGER, description TEXT, director_id INTEGER, PRIMARY KEY (id), CONSTRAINT model_movie_director_id_fk FOREIGN KEY(director_id) REFERENCES model_director (id) )
Notice the director_id column was generated automatically, as well as the corresponding foreign key constraint. That "foreign key column" will, by default, be named after a concatenation of the name of the relationship (director in this case) and the name of the primary key of the target (id).
Now let's create a director and a couple of titles. For your convenience, from now on in this tutorial, you'll find each block of data to input in attached files so that you can copy-paste them all at once. The data for this section can be found in section4.py.
>>> rscott = Director(name=u"Ridley Scott") >>> glucas = Director(name=u"George Lucas") >>> alien = Movie(title=u"Alien", year=1979) >>> swars = Movie(title=u"Star Wars", year=1977) >>> brunner = Movie(title=u"Blade Runner", year=1982)
Now add the movies to their respective director. Notice how the movies attribute of the director can be used just as a plain old python list.
>>> rscott.movies.append(brunner) >>> rscott.movies.append(alien) >>> swars.director = glucas
Let's demonstrate some magic now, enter this line:
>>> glucas.movies [<Movie "Star Wars" (1977)>]
Since Elixir detected that the movies relationship on a Director object is the inverse of the director relationship on Movie objects, it keeps those in sync, whichever side you modify, and this even before you flush the data to the database.
Speaking about flushing, we'd rather do it now if we want the data to be persisted on the database. Since we are using Elixir's default session, which is configured (as of Elixir 0.6!) as autocommit=False and autoflush=True, we need to commit the (implicitly created) transaction explicitly, but we do not need to flush data before committing as it is done automatically upon commit. In short, let's commit our data to the database:
5 You don't usually use this in a normal application as you don't usually recreate your SQL tables everytime you start your application.
5. More querying
Now, putting data into a database isn't of much use if you can't use it afterwards, is it?. As the observant reader will have noticed, you usually query the database by using the query attribute on your entities (for example: Movie.query.all())6. But what is that query attribute? It's a property which gives you an SQLAlchemy Query object. This object lets you query your database in a lot of interesting and powerful ways. Here are only a few examples of what you could do7:
>>> Movie.query.filter_by(title=u"Alien").one() <Movie "Alien" (1979)> >>> Movie.query.filter(Movie.year > 1980).all() [<Movie "Blade Runner" (1982)>] >>> Movie.query.filter(Movie.director.has(name=u'Ridley Scott')).all() [<Movie "Alien" (1979)>, <Movie "Blade Runner" (1982)>] >>> Movie.query.filter(Movie.director.has(Director.name.endswith(u'Scott'))).all() [<Movie "Alien" (1979)>, <Movie "Blade Runner" (1982)>]
Queries are also generative. This means that you can build a query step by step and reuse parts of it.
>>> d = Director.get_by(name=u'Ridley Scott') # Class.get_by(xxx) is a shortcut for Class.query.filter_by(xxx).first() >>> q = Movie.query.filter_by(director=d) >>> q.filter_by(year=1979).all() [<Movie "Alien" (1979)>] >>> from sqlalchemy import desc >>> q.order_by(desc(Movie.year)).all() [<Movie "Blade Runner" (1982)>, <Movie "Alien" (1979)>]
For more information on what you can do with query objects, please look at the corresponding SQLAlchemy documentation.
6 Note that this is only a shortcut syntax and is equivalent to SQLAlchemy's way of getting a Query object: session.query(Movie).
7 Note that some of these queries use features that are only present in SQLAlchemy 0.4 and above.
6. Many to Many relationships (and composite primary keys)
Now, we think it would make a nice addition to our little application to relate movies to their genres. As most movies fall into several genres and (hopefully) several movies belong to the same genre, we'll model that with a ManyToMany relationship.
We would also like to not use the default surrogate 'id' primary key that we've had in the Movie entity so far. We would like to accept two movies with the same title but not on the same year. So a composite primary key on title and year seems like a good idea.
So we'll add a new entity "Genre" and modify the existing Movie entity in our model.py:
class Genre(Entity): name = Field(Unicode(15), primary_key=True) movies = ManyToMany('Movie') def __repr__(self): return '<Genre "%s">' % self.name class Movie(Entity): title = Field(Unicode(30), primary_key=True) # <-- modify this line year = Field(Integer, primary_key=True) # <-- and this one description = Field(UnicodeText) director = ManyToOne('Director') genres = ManyToMany('Genre') # <-- and add this one def __repr__(self): return '<Movie "%s" (%d)>' % (self.title, self.year)
You'll notice that we also made the name field the primary_key of the Genre entity. For both the Genre and Movie entity, now that we have provided "manual" primary_keys, the extra "id" primary key is not added.
After doing the, now usual, cycle of dropping the database (delete movies.sqlite) and recreating it with:
>>> from model import * >>> setup_all(True)
we will re-add some data (you'll find this batch in section6.py).
>>> scifi = Genre(name=u"Science-Fiction") >>> rscott = Director(name=u"Ridley Scott") >>> glucas = Director(name=u"George Lucas") >>> alien = Movie(title=u"Alien", year=1979, director=rscott, genres=[scifi, Genre(name=u"Horror")]) >>> brunner = Movie(title=u"Blade Runner", year=1982, director=rscott, genres=[scifi]) >>> swars = Movie(title=u"Star Wars", year=1977, director=glucas, genres=[scifi]) >>> session.commit()
Notice how the relationships can also be set using keyword arguments of the constructor, including list-relationships.
>>> Movie.query.filter(Movie.genres.any(name=u"Horror")).all() [<Movie "Alien" (1979)>]
7. Using options
Let's suppose you don't like the default naming scheme for your entities tables, as we've seen in section 3 of this tutorial. You can provide a custom name for your table by using the corresponding option. Options are given to an entity by using the using_options statement, as below:
class Movie(Entity): using_options(tablename='movies') title = Field(Unicode(30)) year = Field(Integer) description = Field(UnicodeText)
There are quite a few available options to control many aspects of your entities: specify the ordering of results; the inheritance type or simply, as above, the table name; load the table columns from the database instead of defining them as python attribute; add the necessary hooks to check for concurrent access, ...
For the full list of available options and a detailed description for each of them, please look at the options section of the API documentation.
8. More on sessions
Throughout this tutorial we've used the global session provided by Elixir. But what's a session exactly?
To quote SQLAlchemy documentation:
In the most general sense, the Session establishes all conversations with the database and represents a "holding zone" for all the mapped instances which you've loaded or created during its lifespan. It implements the Unit of Work pattern, which means it keeps track of all changes which occur, and is capable of flushing those changes to the database as appropriate. Another important facet of the Session is that it's also maintaining unique copies of each instance, where "unique" means "only one object with a particular primary key" - this pattern is called the Identity Map.
Elixir provides a default global contextual (thread-local) session for you to use, and Elixir entities are by default linked to that session by using that session's contextual mapper. It just means that whenever you create an instance of any of your entities, that instance is automatically added (saved) to the session. If you want to use another session (for example, to not use a global/contextual session at all), that's fine, you just need to tell Elixir by using the corresponding option.
You'll find much more information about sessions and what you can do with sessions in the corresponding section of SQLAlchemy documentation.
Let's suppose you want to add actors to your model. You first think of your Actor class as something like this:
class Actor(Entity): name = Field(Unicode(60)) movies = ManyToMany('Movie')
then you realize that both actors and directors are persons and you want to factor a class out of that. You can do so pretty easily:
class Person(Entity): using_options(inheritance='multi') name = Field(Unicode(60)) def __repr__(self): return '<Person "%s">' % self.name class Actor(Person): using_options(inheritance='multi') movies = ManyToMany('Movie') def __repr__(self): return '<Actor "%s">' % self.name class Director(Person): using_options(inheritance='multi') movies = OneToMany('Movie') def __repr__(self): return '<Director "%s">' % self.name
Several points are important to notice. First, we used multi-table inheritance. Which means that each of these entities will get a table for themselves (with foreign keys to link them). For an explanation of the different kind of inheritance you can do, and what that involves, please refer once again to the corresponding SQLAlchemy documentation. Note that Elixir currently only supports "single table" and "joined/multi-table" inheritance (both in their polymorphic and non polymorphic versions). Elixir generates the correct tables and mappers for you but it's still important for you to realize what that is.
Secondly, when using inheritance with Elixir (and not using the default values), you have to tell Elixir what kind of inheritance you want in each and every entity, including the parent one. This is because some work also needs to be done on the parent for the inheritance to work and that entity needs to know that this extra processing needs to be done.
Let's input more data (you'll find this batch in section9.py).
>>> rscott = Director(name=u"Ridley Scott") >>> glucas = Director(name=u"George Lucas") >>> hford = Actor(name=u"Harrison Ford") >>> mhamill = Actor(name=u"Mark Hamill") >>> sweaver = Actor(name=u"Sigourney Weaver") >>> session.commit()
And do some querying...
>>> Person.query.all() [<Director "Ridley Scott">, <Director "George Lucas">, <Actor "Harrison Ford">, <Actor "Mark Hamill">, <Actor "Sigourney Weaver">]
>>> Actor.query.all() [<Actor "Harrison Ford">, <Actor "Mark Hamill">, <Actor "Sigourney Weaver">]
It's pretty much what we expected. As you can see in the first snippet, even though we are doing the query on the Person class, we get instances of the Director and Actor classes back (that is the "more specialized" classes). This is called polymorphism. As of version 0.5 of Elixir, this is the default behavior.
10. Where to go from here?
By now, you should know how to create simple models and set up relationships between them. The next step would be to play a bit more with the model we've created. In case you lost it, or didn't follow the whole tutorial, you can use the complete model, as well as an aggregated data set you can play with. Then we suggest you to explore the possibilities of Elixir further by looking at our rather thorough API-docs and the testcases which provide some nice examples. Additionaly, more experienced (or adventurous) programmers will probably be interested in what happens behind the scenes.
Furthermore, please remember than once the setup phase is done. Working with Elixir is just like working with plain SQLAlchemy (with a few syntactic shortcuts provided for convenience), so reading the SQLAlchemy documentation is a good idea.
Enjoy database development the easy way and let us know when you created something cool!