I'm not sure if this is a bug or I am just setting up the attribute
wrong. Anyways, I'm having a problem defining count-type SQL mapped
attribute in a situation with many-to-many relationships.
The example here uses the classic book-author many-to-many relation.
An author can have many books, and a book can be by more than one
author.
Again, the code is a self-contained test case.
<code>
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, backref, column_property,
sessionmaker
from sqlalchemy.sql import select, func
DB_URI='postgres://postg...@localhost/postgres' #Replace this
accordingly
db_engine=create_engine(DB_URI, echo=True)
metadata = MetaData()
books=Table('books', metadata,
Column('id', Integer, primary_key=True),
Column('title', String(100), nullable=False)
)
class Book(object):
def __init__(self, title):
self.title=title
authors=Table('authors', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100))
)
class Author(object):
def __init__(self, name):
self.name=name
book_authors=Table('book_authors', metadata,
Column('book_id', Integer, ForeignKey('books.id'),
primary_key=True),
Column('author_id', Integer, ForeignKey('authors.id'),
primary_key=True)
)
mapper(Book, books)
mapper(Author, authors, properties={
'books': relation(Book, secondary=book_authors,
backref='authors'),
'book_count': column_property(select([func.count(books.c.id)],
book_authors.c.author_id==books.c.id).label('books_count'))
})
if __name__ == '__main__':
metadata.create_all(db_engine)
s=sessionmaker(bind=db_engine)()
b1=Book('I, Robot')
b2=Book('Foundation')
b3=Book('Rendezvous with Rama')
a1=Author('Isaac Asimov')
a2=Author('Arthur C. Clarke')
a1.books=[b1, b2]
a2.books=[b3]
s.add_all([b1, b2, b3, a1, a2])
s.commit()
try:
#Isaac Asimov has two books
assert len(a1.books)==2
#But reading book_count would return the total number of books
in books table (in this case 3)
assert a1.book_count==2 #Fails
finally:
s.close()
metadata.drop_all(db_engine)
</code>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
