On Thursday, January 3, 2013 10:08:19 PM UTC-5, ocicat wrote: > > Embarrassingly, I'm gotten lost in calling SQL functions in SQLAlchemy > 0.7.1. > > I can boil the problem down to the following table structure: > > CREATE TABLE words ( > id INTEGER NOT NULL, > timestamp DATETIME NOT NULL, > word TEXT NOT NULL, > PRIMARY KEY (id), > UNIQUE (word) > ); > > ...where I would like to find the maximum & minimum stored string lengths. > eg. > > SELECT MAX(LENGTH(word), MAX(LENGTH(word)) FROM words; > > The code below constructs & populates the table correctly, but translating > the above SQL into something more Pythonic is eluding me. Any suggestions > would be welcomed, as I'm in a rut. > > Thanks. > > #====8<------------ > > #!/usr/bin/env python > > from datetime import datetime > > from sqlalchemy import create_engine, Column, func > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import sessionmaker > from sqlalchemy.dialects.sqlite import INTEGER, DATETIME, TEXT > > Base = declarative_base() > > def get_dbname(): > return 'test.db' > > class Word(Base): > __tablename__ = 'words' > > id = Column(INTEGER, primary_key=True) > timestamp = Column(DATETIME, nullable=False, default=datetime.now()) > word = Column(TEXT, nullable=False, unique=True) > > def __init__(self, word): > self.word = word > > def __repr__(self): > return '<Word(%d, %s, "%s")>' % (self.id, self.timestamp, > self.word) > > if __name__ == '__main__': > engine = create_engine('sqlite:///' + get_dbname(), echo=True) > Base.metadata.create_all(engine) > Session = sessionmaker(bind=engine) > session = Session() > > words = """THE OF AND TO A IN THAT IS WAS HE FOR IT WITH AS HIS ON BE > AT > SAME ANOTHER KNOW WHILE LAST""".split() > > for w in words: > session.add(Word(w)) > session.commit() > > print 'total words = %d' % session.query(Word).count() > > # minimum length = ? > # maximum length = ? >
Would something like this work: from sqlalchemy import func session.query(func.max(func.length(Word.word)), func.min(func.length(Word.word))) -- Jason -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/eZI3kq2cjUMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.