On 4 Jan 2013, at 03:08, James Hartley <jjhart...@gmail.com> 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 = ?
>
How about:
print session.query(func.max(func.length(Word.word)),
func.min(func.length(Word.word))).all()
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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.
