Why my SQLAlchemy query.flter works only on some attribute?

Question

I am learning SQLAlchemy of Python.

Below is an example I am useing.

First I generate a datafile contains puppy information like below:

class Puppy(Base):
    __tablename__ = 'puppy'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    gender = Column(String(6), nullable = False)
    dateOfBirth = Column(Date)
    shelter_id = Column(Integer, ForeignKey('shelter.id'))
    weight = Column(Numeric(10))


male_names = ["Bailey", "Max", ...just some names..., "Luke", "Henry"]

female_names = ['Bella', 'Lucy', ...just some names..., 'Honey', 'Dakota']

def CreateRandomAge():
    today = datetime.today()
    days_old = randint(0,540)
    birthday = today - timedelta(days = days_old)
    return birthday

def CreateRandomWeight():
    return random.uniform(1.0, 40.0)

for i,x in enumerate(male_names):
    new_puppy = Puppy(name = x, gender = "male", dateOfBirth = CreateRandomAge(), weight= CreateRandomWeight())
    session.add(new_puppy)
    session.commit()

for i,x in enumerate(female_names):
    new_puppy = Puppy(name = x, gender = "female", dateOfBirth = CreateRandomAge(), weight= CreateRandomWeight())
    session.add(new_puppy)
    session.commit()

Now I want to filter some kinds of puppies as below:

testpuppy = session.query(Puppy).filter_by(name='Lucy')
print(testpuppy)

birthdate = datetime.today() - timedelta(days=180)
smallpuppy = session.query(Puppy).filter_by(dateOfBirth < birthdate)
print(smallpuppy)

Then it is strange, because the testpuppy passed, I can get Lucy, but the dateofBirth can not pass, every time I want to get these smallpuppies, I just got an error

NameError: name 'dateOfBirth' is not defined

I really can not understand, why my filter can only be operated on some attribute, where is wrong?

score 0 · Accepted Answer · edited May 23 '17 at 12:26

The problem is that you need to use filter instead of filter_by like this:

smallpuppy = session.query(Puppy).filter(Puppy.dateOfBirth < birthdate)

For filter, the criterion should use ClassName.propertyName to access the column, and you can use < or >.

For filter_by, the criterion could be use propertyName directly to access the column, but you cannot use < or >.

Please refer to this answer, it will give you more details about the difference between filter and filter_by.

Why my SQLAlchemy query.flter works only on some attribute?

1 Answers1