Hello All,
I am trying to count related objects, I have an Option (understand
option as a party) and Candidate Table, related with an many to many
relation, Above of this message y attach a python script trying to do
that, my issue is in following lines
subquery = session.query('option_senators', func.count('*').label
('senators_count')).group_by('option_senators.option_id').subquery()
query = session.query(Option)
query.outerjoin((subquery, Option.id == subquery.c.option_id)) #
<--- Here it raise the error
query.order_by(Option.name)
query.all()
In my understanding, I have to do an outerjoin with the Option table
and a subquery that holds something like this:
SELECT option_senators.option_id, count( * ) AS senators_count
FROM option_senators
GROUP BY option_senators.option_id
The result I expect is having the following set:
id|name|created|updated|senators_count
1|Option X|2009-09-11 13:07:37.604377|2009-09-11 13:07:37.604377|
100
Sure I am missing something, in the moment it tried doing the join,
raises:
Traceback (most recent call last):
File "mm.py", line 60, in <module>
query.outerjoin((subquery, Option.id == subquery.c.option_id))
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/util.py", line 649, in __getattr__
raise AttributeError(key)
AttributeError: option_id
How can accomplish Getting the count of the related senators for each
Option?
Here is the script I am using to test
#!/usr/bin/python
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.sql import func
from datetime import datetime
Base = declarative_base()
metadata = Base.metadata
option_senators = Table('option_senators', metadata,
Column('option_id', Integer, ForeignKey('options.id')),
Column('candidate_id', Integer, ForeignKey('candidates.id')),
Column('departament', Integer),
)
class Candidate(Base):
__tablename__ = 'candidates'
id = Column(Integer, primary_key=True)
first_name = Column(String(255))
last_name = Column(String(255))
def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name
class Option(Base):
__tablename__ = 'options'
id = Column(Integer, primary_key=True)
name = Column(String(255))
senators = relation('Candidate', secondary=option_senators,
backref="senator_option")
created = Column(DateTime, default=datetime.now)
updated = Column(DateTime, default=datetime.now)
def __init__(self, name):
self.name = name
if __name__ == '__main__':
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///hola.bd', echo=True)
#engine = create_engine('mysql://votodb:vot...@localhost/votodb',
echo=True)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
option = Option('Option X')
for n in range(100):
option.senators.append(Candidate('Jhon %s' % n, 'Doe %s' % n))
session.add(option)
session.commit()
subquery = session.query('option_senators', func.count('*').label
('senators_count')).group_by('option_senators.option_id').subquery()
query = session.query(Option)
query.outerjoin((subquery, Option.id == subquery.c.option_id))
query.order_by(Option.name)
query.all()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
