Re: [sqlalchemy] max() min() string lengths?
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.
[sqlalchemy] max() min() string lengths?
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 = ?
--
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.
