Tagging with SQLAlchemy

I wanted to use tags with SQLAlchemy, so I looked up previous examples and found Wayne's post on how he did it. I adapted his code into a single file example so you can see better how it works. For any given page, there can be any number of tags .appended to it. For any given tag, you can .append it to any number of pages.

For example:

page = Page(u"Example Page")
page.append(Tag(u"examples"))

from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://')
metadata = MetaData(engine)
 
#engine.echo =True 
 
page_table = Table("page", metadata,
    Column("id", Integer, Sequence('page_seq_id', optional=True), primary_key=True),
    Column("name", Unicode(100), nullable=False),
)
 
tag_table = Table("tag", metadata,
    Column("id", Integer, Sequence('taq_seq_id', optional=True), primary_key=True),
    Column("name", Unicode(50), nullable=False, unique=True),
)
 
pagetag_table = Table("pagetag", metadata,
    Column("id", Integer, Sequence('pagetag_seq_id', optional=True), primary_key=True),
    Column("pageid", Integer, ForeignKey('page.id')),
    Column("tagid", Integer, ForeignKey('tag.id')),
)
 
class Tag(object):
    def __init__(self, name):
        self.name = name
 
    def __repr__(self):
        return "Tag(\"%s\")" % self.name
 
class Page(object):
    def __init__(self, name):
        self.name = name
 
    def __repr__(self):
        return "Page(\"%s\")" % self.name
 
mapper(Tag, tag_table)
mapper(Page, page_table, properties = {
    'tags':relation(Tag, secondary=pagetag_table, cascade="all"),
    #    'tags':relation(Tag, secondary=pagetag_table, cascade="all,delete-orphans"),
})
 
metadata.create_all()
 
sess = create_session()
 
page = Page(u"Tags with SQLAlchemy Example")
page2 = Page(u"Hot New Video Game Consists Solely Of Shooting People Point-Blank In The Face")
page3 = Page(u"Congressman's War Hero Son Would Have Wanted Highway Bill Passed")
 
tag = Tag(u"examples")
tag2 = Tag(u"onion")
 
page.tags.append(tag)
page2.tags.append(tag2)
page3.tags.append(tag2)
 
sess.add(page)
sess.add(page2)
sess.add(page3)
sess.flush()
 
tag_q = sess.query(Tag)
tags = tag_q.all()
print "Number of tags:", len(tags)
 
# filter pages by tag(s)
page_q = sess.query(Page)
pages = page_q.join('tags').filter_by(name=u"tag").all()
 
print
print "First Page"
print page_q.first()
print page_q.first().tags
 
print
print "Second Page"
print page_q.all()[1]
print page_q.all()[1].tags
 
print
print "Third Page"
print page_q.all()[2]
print page_q.all()[2].tags
 
# delete-orphans does the work for us here...
#sess.delete(pages[0]
#sess.flush()
 
print
print "All tags"
tags = tag_q.all()
print tags, "Count:", len(tags)
 
print
print "Tag cloud anyone?"
# see the source code linked below for a properly weighted tag cloud.
tag_q = sess.query(func.count("*").label(u"tagcount"), Tag)
tag_r = tag_q.filter(Tag.id==pagetag_table.c.tagid).group_by(Tag.id).all()
#print tag_q
print tag_r
 
# what about pages with related tags?
page_q = sess.query(Page)
 
taglist = [u"tag1", u"tag2"]
tagcount = len(taglist)
page_q.join(Page.tags).filter(Tag.name.in_(taglist)).\
group_by(Page.id).having(func.count(Page.id) == tagcount).all()

I know tag clouds are passe, but I still think from an information architecture perspective, tags still better than categories.