Here is a tutorial for Macaron. Macaron is a simplified ORM, so there are few things you need to learn.
Macaron needs tables in SQLite database. To make it simple, Macaron does not provides methods for creating tables. In this section, table creation SQL and definition is below.
SQL:
CREATE TABLE team (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE member (
id INTEGER PRIMARY KEY,
table_id INTEGER REFERENCES team (id),
first_name TEXT,
last_name TEXT,
part TEXT,
age INT
);
You need creating tables into members.db file with the SQL.
Model definition in Python code:
import macaron
class Team(macaron.Model):
"""Definition of Team table"""
def __str__(self):
return "<Team '%s'>" % self.name
class Member(macaron.Model):
"""Definition of Member table
team is a class property and accessor for parent 'Team' object.
Macaron detects some field types, but if you want to validate, defining criteria.
"""
team = macaron.ManyToOne("team_id", Team, "id", "members")
age = macaron.IntegerField(min=15, max=18)
def __str__(self):
return "<Member '%s %s : %s'>" % (self.first_name, self.last_name, self.part)
In these case, Team and Member classes correspond to ‘team’ and ‘member’ tables, respectively. So Macaron uses uncapitalized class names as table name. If not so, define _table_name for corresponding table name as a class property. Like this,
class MyTest(macaron.Model):
_table_name = "my_profile"
The class property of _table_name is removed when initializing in Meta-class ModelMeta.
Okay, we have created some tables and defined model classes. Team class is the model related to team table in database and Member class is to member. After that, we will create a new team called “Houkago Tea Time” and append starting members.
import macaron
# A macaron needs 'macaronage' process.
# First of all, call macaron.macaronage() to initialize macaron. This method
# connect to 'members.db' SQLite database file.
macaron.macaronage("members.db")
# Create a new team named 'Houkago Tea Time'.
# A new team is created with Model.create(). The create() is a class method and is called
# with key word arguments which consist of field name and value pairs. It returns created Team
# object.
new_team = Team.create(name="Houkago Tea Time")
# Append new members to the team.
# The Team object is into a variable named 'new_team', you will call new_team.members.append().
# 'new_team.members' is an accessor for Members. The append() can be used with key word
# arguments like create() method and returns new created Member object.
member1 = new_team.members.append(first_name="Ritsu", last_name="Tainaka", part="Dr")
member2 = new_team.members.append(first_name="Mio", last_name="Akiyama", part="Ba")
# Yeah, all tasks has been done. Let's bake the macaron.
# At last, we have created a team with initial members and should commit it. Call
# macaron.bake() which is a very wrapper to call sqlite3.Connection#commit().
macaron.bake()
Where the members propery is defined? The property is defined automatically in Member class definition. The team property of Member is set as an instance of ManyToOne and it works as accessor to many to one relationship. The ManyToOne add a property for accessing reverse relationship to Team class. In this case, the property is named members.
Now, we have a small database members.db. In this section, we try fetching records.
import macaron
macaron.macaronage(dbfile="members.db")
# The simplest way is get() with record ID.
ritsu = Member.get(1)
# <Member 'Ritsu Tainaka : Dr'>
# Fetching Team object Ritsu belongs to.
ourband = ritsu.team
# <Team 'Houkago Tea Time'>
# And listing members who belongs to the team.
for member in ourband.members:
print members
# <Member 'Ritsu Tainaka : Dr'>
# <Member 'Mio Akiyama : Ba'>
# You can get the member with index.
mio = ourband.members[1]
# <Member 'Mio Akiyama : Ba'>
# Of course, you can SELECT with WHERE clause.
# The get() returns a single object and select() returns generator.
mio = Member.get("last_name=?", ["Akiyama"])
members = Member.select("team_id=?", [ourband.pk])
# [<Member object 1>, <Member object 2>]
# Oops, Mio desides to sing the song.
mio.part = "Vo"
mio.save()
print "Mio's part is %s." % mio.part
# Mio's part is Vo.
# But she canceled it.
macaron.rollback()
# Done.
macaron.db_close()
These are how to use aggregation methods. Aggregation is conducted with aggregate() method. The aggregate method takes single argument which is sub class of AggregateFunction. Currently, there are Sum(), Ave(), Max(), and Min(). The constructor of AggregateFunction class takes column name as argument.
# Count
count = Team.get(1).members.all().count()
# Sum
sum_of_ages = Team.get(1).members.all().aggregate(macaron.Sum("age"))
# And you can use: average, max, and min are Ave(), Max(), Min(), respectively.