Second example usign sqlalchemy

sqlalchemy_many_to_many.py

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker

engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
metadata = MetaData()
Base = declarative_base()

# association table
post_keywords = Table('post_keywords', Base.metadata,
    Column('post_id', Integer, ForeignKey('posts.id')),
    Column('keyword_id', Integer, ForeignKey('keywords.id'))
)

class User(Base):
    
    __tablename__ = 'users'
    
    id = Column( Integer, primary_key=True)
    name =  Column( String)
    fullname =  Column( String)
    password =  Column( String)
    
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = SHA256.new(password).hexdigest()
    
    def __repr__(self):
       return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password[:10]+'...')


class Address(Base):
    
    __tablename__ = 'addresses'
    
    id =  Column( Integer, primary_key=True)
    email_address =  Column( String, nullable=False)
    user_id =  Column( Integer,  ForeignKey('users.id'))
    
    user = relationship("User", backref=backref('addresses', order_by=id))
    
    def __init__(self, email_address):
        self.email_address = email_address
    
    def __repr__(self):
        return "<Address('%s')>" % self.email_address


class BlogPost(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    # many to many BlogPost<->Keyword
    keywords = relationship('Keyword', secondary=post_keywords, backref='posts')

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body

    def __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)

class Keyword(Base):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)

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


BlogPost.author = relationship(User, backref=backref('posts', lazy='dynamic'))
Base.metadata.create_all(engine)

session.commit()

# wendy = session.query(User).filter_by(name='wendy').one() 
# post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
# session.add(post) 

# post.keywords.append(Keyword('wendy'))
# post.keywords.append(Keyword('firstpost'))