Help convert my SQL query to SQLAlchemy

[Richie Ward]

I would like to convert this query to SQLAlchemy:
SELECT revision_id, modulename, content

FROM content

WHERE revision_id IN (

SELECT MAX(revision_id) FROM content c

GROUP BY modulename

)

AND content != ''

ORDER BY modulename

with this table:
CREATE TABLE content (

revision_id INTEGER NOT NULL,

modulename VARCHAR(256),

content VARCHAR(102400),

summary VARCHAR(256),

created TIMESTAMP,

PRIMARY KEY (revision_id)

)

or:

class Content(DeclarativeBase):
__tablename__ = 'content'

revision_id = Column(Integer, primary_key=True)
modulename = Column(Unicode(256))
content = Column(Unicode(102400), default='')
summary = Column(Unicode(256))
users = relation(User, secondary=ContentUser.__table__,
backref='content')
created = Column(DateTime, default=datetime.now)

I had a go at it myself and heres what I managed to do:
revision_ids = DBSession.query(func.max(Content.revision_id)).group_by
(

Content.modulename).subquery()

pages = DBSession.query(Content.modulename, Content.revision_id,

Content.content).filter(and_(

Content.revision_id.in_(revision_ids),

Content.content != '')).order_by(

Content.modulename)

It worked (as in does not error out) if I remove .subquery() but then
pages does not work as expected, I think I need to use a subquery to
make it work 100%.

Heres the error:
OperationalError: (OperationalError) near "SELECT": syntax error
u'SELECT content.modulename AS content_modulename, content.revision_id
AS content_revision_id, content.content AS content_content \nFROM
content, (SELECT max(content.revision_id) AS max_1 \nFROM content
GROUP BY content.modulename) AS anon_1 \nWHERE content.revision_id IN
SELECT max(content.revision_id) AS max_1 \nFROM content GROUP BY
content.modulename AND content.content != ? ORDER BY
content.modulename' ['']

There is a forum thread on my favorable forum where I asked how to
design the query, as I got a bit stuck on the theory behind such a
complex query. This should give you a idea of what I am trying to
achieve.
http://forums.overclockers.co.uk/showthread.php?t=18050830

Thanks alot for your help :)

[Michael Bayer]

when I run it i get the correct:

SELECT content.modulename AS content_modulename, content.revision_id
AS content_revision_id, content.content AS content_content

FROM content
WHERE content.revision_id IN (SELECT max(content.revision_id) AS max_1
FROM content GROUP BY content.modulename) AND content.content != ?
ORDER BY content.modulename

it works identically regardless of subquery() being present. are you
on the latest version ?

[Richie Ward]

0.5.1, I will try with a later version if there is one.

Heres full test code:
from datetime import datetime

from sqlalchemy import *
from sqlalchemy.orm import mapper, relation
from sqlalchemy import Table, ForeignKey, Column
from sqlalchemy.types import Integer, Unicode, DateTime
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

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

# Global session manager: DBSession() returns the Thread-local
# session object appropriate for the current web request.
maker = sessionmaker(autoflush=True, autocommit=False)
DBSession = scoped_session(maker)

# Base class for all of our model classes: By default, the data model is
# defined with SQLAlchemy's declarative extension, but if you need more
# control, you can switch to the traditional method.
DeclarativeBase = declarative_base()

# There are two convenient ways for you to spare some typing.
# You can have a query property on all your model classes by doing this:
# DeclarativeBase.query = DBSession.query_property()
# Or you can use a session-aware mapper as it was used in TurboGears 1:
# DeclarativeBase = declarative_base(mapper=DBSession.mapper)

# Global metadata.
# The default metadata is the one from the declarative base.
metadata = DeclarativeBase.metadata

class Content(DeclarativeBase):
__tablename__ = 'content'

revision_id = Column(Integer, primary_key=True)
modulename = Column(Unicode(256))
content = Column(Unicode(102400), default='')
summary = Column(Unicode(256))

created = Column(DateTime, default=datetime.now)

def __init__(self, modulename, content, summary):
self.modulename = modulename
self.content = content
self.summary = summary

DBSession.configure(bind=engine)
metadata.create_all(engine)

DBSession.add(Content("Front_Page","Test","New Entry"))
DBSession.commit()

revision_ids = DBSession.query(func.max(Content.revision_id)).group_by(

Content.modulename).subquery()

pages = DBSession.query(Content.modulename, Content.revision_id,
Content.content).filter(and_(
Content.revision_id.in_(revision_ids),
Content.content != '')).order_by(

Content.modulename).all()

2009/9/7 Michael Bayer <mik...@zzzcomputing.com>:

--
Thanks, Richie Ward

[Richie Ward]

WOW it works with 0.5.5, I am surprised they found the bug and
actually fixed it before I did! I am impressed!
That was a complex query, keep up the good work guys!

2009/9/12 Richie Ward <ric...@gmail.com>:

--
Thanks, Richie Ward