max() & min() string lengths?

[James Hartley]

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 = ?

[Jason]

Would something like this work:

from sqlalchemy import func

session.query(func.max(func.length(Word.word)), func.min(func.length(Word.word))) 

--

Jason

 

[Simon King]

How about:

print session.query(func.max(func.length(Word.word)),
func.min(func.length(Word.word))).all()

Hope that helps,

Simon