Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Python Using Databases in Python Gettin' CRUD-y With It CRUD: Search Function

Code Challenge: database searching with peewee

The objective was to write a function that would "Return all Challenges [database records] where the name field contains name argument and where the language field is equal to the language argument. "

Since this is a Python course, I interpreted "and" as boolean, to mean both the name and language must match in a single record. Unaware (and too lazy to find out) how to combine criteria in a single search/filter, I chose to filter the criteria sequentially:

from models import Challenge


def create_challenge(name, language, steps=1):
    Challenge.create(name=name,
                     language=language,
                     steps=steps)


def search_challenges(name, language):
    challenges = Challenge.select().where(Challenge.name == name)
    return challenges.where(Challenge.language == language)  

The "check work" feedback responded with "Bummer! Didn't find the right number of records".

Trying to understand why, I wrote a script to test filter criteria as arguments in a .where() function.

my test database has only 5 entries and 2 fields:
name: 'a' or 'b'
language: '1', '2', or '3'
I combined name == 'a' and language = '1' in various searches. My script follows the output below.

2 questions:
1) Why did sequential filtering of name then language fail the Treehouse challenge?

2) Can someone explain the 'and' and 'or' search results below?

output:
.select()
a 1
a 2
a 3
b 1
b 2

name == 'a'
a 1
a 2
a 3

name == 'a' followed by language == '1'
a 1

(name == 'a') and (language == '1')
a 1
b 1

(name == 'a') or (language == '1')
a 1
a 2
a 3

(name == 'a') & (language == '1')
a 1

(name == 'a') | (language == '1')
a 1
a 2
a 3
b 1

from peewee import *

db = SqliteDatabase('throwaway.db')

class Test(Model):
    name = CharField(max_length=10)
    language = CharField(max_length=10)

    class Meta:
        database = db

db.connect()
db.create_tables([Test], safe=True)

data = [('a', '1'), 
        ('a', '2'), 
        ('a', '3'), 
        ('b', '1'), 
        ('b', '2')]
for f1, f2 in data:
    Test.create(name=f1, language=f2)


def show(label, entries):
    print(label)
    for entry in entries:
        print('{} {}'.format(entry.name, entry.language))
    print()


all_entries = Test.select()
show('.select()', all_entries)

only_a = all_entries.where(Test.name == 'a')
show("name == 'a'", only_a)

only_1_in_only_a = only_a.where(Test.language == '1')
show("name == 'a' followed by language == '1'", only_1_in_only_a)

only_1_and_a = all_entries.where((Test.name == 'a') and 
                                 (Test.language == '1'))
show("(name == 'a') and (language == '1')", only_1_and_a)

only_1_or_a = all_entries.where((Test.name == 'a') or 
                                (Test.language == '1'))
show("(name == 'a') or (language == '1')", only_1_or_a)

only_1_amp_a = all_entries.where((Test.name == 'a') & 
                                 (Test.language == '1'))
show("(name == 'a') & (language == '1')", only_1_amp_a)

only_1_pipe_a = all_entries.where((Test.name == 'a') | 
                                 (Test.language == '1'))
show("(name == 'a') | (language == '1')", only_1_pipe_a)

cleanup = Test.delete().where((Test.name == 'a'))
cleanup.execute()
cleanup = Test.delete().where((Test.name == 'b'))
cleanup.execute()
db.close()
Chris Freeman
Chris Freeman
Treehouse Moderator 68,455 Points

WOW!! Thank you for such an indepth look into peewee queries. It got me to dig deeper into the peewee docs to understand. The sematics were driving me crazy. See my answer below.

The killer is that both answers below pass the challenge:

# Using logical-OR
def search_challenges(name, language):
    challenges = Challenge.select()
    challenges = challenges.where( (Challenge.name==name) | (Challenge.language==language))
    return challenges

# Using python-and
def search_challenges(name, language):
    challenges = Challenge.select()
    challenges = challenges.where( (Challenge.name==name) and (Challenge.language==language))
    return challenges

4 Answers

Kenneth Love
STAFF
Kenneth Love
Treehouse Guest Teacher

You actually only need to include both criteria in your where.

Challenge.select().where(
    Challenge.name.contains(name),
    Challenge.language==language
)

But it's pretty awesome that you all went so far in researching/experimenting for this. I'll see about rewording the prompt a bit to make it less ambiguous.

Chris Freeman
Chris Freeman
Treehouse Moderator 68,455 Points

The "comma" notation makes a lot of sense now that I've seen an example. I had not seen it in any of the other examples or docs.

Yuda Leh
Yuda Leh
7,618 Points

Kenneth Love I got all that, exept I do not understand why we use .select()?

Kenneth Love
Kenneth Love
Treehouse Guest Teacher

Yuda Leh You use .select() because it's a SELECT query.

i have been following this lessons with the documentation but I couldnt find .contains() in the documentation so how did it come to play?

Chris Freeman
Chris Freeman
Treehouse Moderator 68,455 Points

The method .contains() is mentioned in the teacher’s notes and at 6:35 in the video. The documentation isn’t much but can be found Query Operators, methods

Cristian Romero
Cristian Romero
11,911 Points

yes, the wording on a couple of the question could be better thereby making the problem easier to answer!!

thank you for showing the simple way of going about it!

(This is a comment, but posted as an answer because I have no idea how to post a single comment to multiple answers. My ignorance of Markdown, HTML, tagging, and forums conspire to make this response very awkward. I especially apologize to Kenneth Love and Chris Freeman if they were not tagged in this response; neither editor nor preview mode shows any indication that my selecting their names from the pop-up menu was successful.)

Thank you chris freeman and Kenneth Love for your help. I wouldn't have understood my problem without both of your responses. Chris, if this discussion has value to the forum, I think it will be because of the insights you shared and summarized so neatly.

I now see that my confusion about the challenge was due to misreading, arising from a bias towards the ways one might search search a string, list, dict, etc with two criteria, and from my unfamiliarity with peewee. My mind locked onto the 'and' as a familiar python concept but overlooked the peewee meaning of 'contains'. I also thought the statement to use '==' meant we were to apply it to each criteria.

My tests only aggravated my misunderstanding because of a flawed premise; I failed to include a name that would match contains(name) but does not == name.

I notice Kenneth has thoughtfully changed the wording of the challenge.

(ifIKnewHowToIndentI'dStartHere)
Create a function named search_challenges that takes two arguments, name and language. Return all Challenges where the name field contains name argument and where the language field is equal to the language argument. Use == for equality. You don't need boolean and or binary & for this, just put both conditions in your where().
(ifIKnewHowToIndentI'dEndHere)

But, of all the people who have taken the challenge, no one else expressed any difficulty with it, so his re-wording is for people who think like me (a frightening thought). The complication is, (a) the new wording might not have helped my original confusion, and (b) I worry that adding extra instruction may simply plant unnecessary concerns in people's minds, like telling someone to go to the grocery store and not to worry about elephants: it is more distraction than help. Perhaps simply monospacing the word 'contains' and make the tip about using '==' parenthetical inside the preceding sentence:

(ifIKnewHowToIndentI'dStartHere)
Create a function named search_challenges that takes two arguments, name and language. Return all Challenges where the name field contains name argument and where the language field is equal (Use ==) to the language argument.
(ifIKnewHowToIndentI'dEndHere)

It's just a thought.

p.s.
Kenneth, while I have your attention, I just finished your Python Track and wanted to say,

        kenneth_love_videos == (clear * educational * FUN)

Thank you!

Chris Freeman
MOD
Chris Freeman
Treehouse Moderator 68,455 Points

The CRUD: Search Function challenge definitely stretches the readers interpretation.

Original wording: Return all Challenges where the name field contains name argument and where the language field is equal to the language argument. Use == for equality.

It wasn't until after trying to logically "and" the two requirements, that I eventually tried a logical-OR.

The wording should have include the wording:

using either/or: Return all Challenges where either the name field contains name argument or where the language field is equal to the language argument. Use == for equality.

or it should have had a comma after the first where to emphasize a "plus" type "and" instead of a logical-and, but that is also very subtle:

Adding a comma: Return all Challenges where the name field contains name argument*,* and where the language field is equal to the language argument. Use == for equality.

As for your questions regarding the difference between "and" vs "&"; "or" vs "|", I looked through the peewee documentation. In your test examples the logical operators "&", and "|" appear to work as expected.. Specifically the "&" matching the chaining of where() filters. So what is the difference between "&" and "and"?

The peewee docs section on query operators has a stern warning:

Warning

Although you may be tempted to use python’s in, and, or and not operators in your query expressions, these will not work. The return value of an in expression is always coerced to a boolean value. Similarly, and, or and not all treat their arguments as boolean values and cannot be overloaded.

So just remember:

  • Use << instead of in
  • Use & instead of and
  • Use | instead of or
  • Use ~ instead of not
  • Don’t forget to wrap your comparisons in parentheses when using logical operators.

Perhaps, Kenneth Love might want to consider rewording the challenge question.

Kenneth Love
STAFF
Kenneth Love
Treehouse Guest Teacher

Oh, also, if I mean the and boolean, it'll be in our code font (like the one in this sentence) and not just in the regular font.