Web 以外で SQL 使うにはこれかな。

SQLAlchemy 0.5rc4 + Python 2.6 で ManyToMany のお勉強。
OneToMany は id:perezvon のをぱくれば OK

Django の ORM と比べると鬼のように柔軟という印象は前と変わってない。declarative_base が投入されたことでいっそう使いやすくなった感じがする。

# vim: fileencoding=utf8

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(engine)

metadata = Base.metadata

users_groups = Table('users_groups', metadata,
  Column('users_id', Integer, ForeignKey('users.id')),
  Column('group_id', Integer, ForeignKey('groups.id')),
)

class User(Base):
  __tablename__ = 'users'

  id = Column('id', Integer, primary_key=True)
  name = Column('name', String(30))
  password = Column('password', String(30))
  groups = relation('Group', secondary=users_groups, backref='users')

  def __init__(self, name, password):
    self.name = name
    self.password = password

  def __repr__(self):
    return 'users: %s', self.name

class Group(Base):
  __tablename__ = 'groups'

  id = Column('id', Integer, primary_key=True)
  name = Column('name', String(30))

  def __init__(self, name):
    self.name = name

  def __repr__(self):
    return 'groups: %s', self.name

Base.metadata.create_all()

if __name__ == '__main__':
  Session = sessionmaker(transactional=True, autoflush=False, bind=engine)
  session = Session()

  group = Group(name='django-ja')
  session.add(group)

  user = User(name='voluntas', password='pass')
  user.groups.append(group)

  session.commit()

  session.clear()

  user = session.query(User).first()
  assert user.name == 'voluntas'
  assert user.groups[0].name == 'django-ja'

  group = session.query(Group).first()
  assert group.name == 'django-ja'
  assert group.users[0].name == 'voluntas'

  session.close()