[SOLVED] (Python 3.4.3,SQLite3):SELECT command returns 'no such column' error

A/S-H · 04-26-2017, 05:18 PM

This function is supposed to select all recipe names from a table 'recipe', which has 3 columns for each of 3 ingredients, and the 3 possible ingredients each have their own table. This sounds fairly limited, but for now I am just trying to build and test a basic SQL database program with a GUI.

The names contain the search term entered in a tkinter Entry widget. So something akin to searching the database, and returning results.

The function is called when a tkinter Button widget is pressed.

Code:

def search_byrecipe():  #03/03/17
    search = a.get() #a.get() gets the string from the variable
    query = conn.execute('''
    SELECT recipe.name AS rn
    FROM recipe
    INNER JOIN ingredients_list AS il ON recipe.ingredient1 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il1 ON recipe.ingredient2 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il2 ON recipe.ingredient3 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il3 ON recipe.ingredient1 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il4 ON recipe.ingredient2 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il5 ON recipe.ingredient3 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il6 ON recipe.ingredient1 = ingredients_list3.ingredient
    INNER JOIN ingredients_list AS il7 ON recipe.ingredient2 = ingredients_list3.ingredient
    INNER JOIN ingredients_list AS il8 ON recipe.ingredient3 = ingredients_list3.ingredient 
    WHERE (?) in recipe''',(search)) #06/03/17 must use recipe
    list1 = conn.fetchall()

However, when I press the corresponding button to run the function, I get the error:

Code:

  File "C:\Python34\lib\tkinter\__init__.py", line 1533, in __call__
    return self.func(*args)
  File "F:\ComputingAlevel\CompProject\PROJECT.py", line 97, in search_byrecipe
    WHERE (?) in recipe''',(search)) #06/03/17 must use recipe
sqlite3.OperationalError: no such column: recipe.ingredient1

I've looked around on the net and I think that I have my ? placeholder right, there definitely is a column recipe.ingredient1 and I have used aliases. Or is something wrong with my usage of JOIN?
The code for the Entry and Button widgets are:

Code:

a = tk.StringVar()
key_term = tk.Entry(master, textvariable = a) #entry widget
b1 = tk.Button(master, text="Search by recipe", command=search_byrecipe)
blBgColor = b1["bg"] #set b1 background colour

So my question is, what is causing the 'no such column' error, and what amendments should I make to fix it?

If you would like to see the whole database to get a clearer picture of the tables, I will paste the code. Thanks for taking the time to read this.

Laserbeak · 04-26-2017, 07:40 PM

Quote:

Originally Posted by A/S-H

This function is supposed to select all recipe names from a table 'recipe', which has 3 columns for each of 3 ingredients, and the 3 possible ingredients each have their own table. This sounds fairly limited, but for now I am just trying to build and test a basic SQL database program with a GUI.

The names contain the search term entered in a tkinter Entry widget. So something akin to searching the database, and returning results.

The function is called when a tkinter Button widget is pressed.

Code:

def search_byrecipe():  #03/03/17
    search = a.get() #a.get() gets the string from the variable
    query = conn.execute('''
    SELECT recipe.name AS rn
    FROM recipe
    INNER JOIN ingredients_list AS il ON recipe.ingredient1 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il1 ON recipe.ingredient2 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il2 ON recipe.ingredient3 = ingredients_list.ingredient
    INNER JOIN ingredients_list AS il3 ON recipe.ingredient1 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il4 ON recipe.ingredient2 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il5 ON recipe.ingredient3 = ingredients_list2.ingredient
    INNER JOIN ingredients_list AS il6 ON recipe.ingredient1 = ingredients_list3.ingredient
    INNER JOIN ingredients_list AS il7 ON recipe.ingredient2 = ingredients_list3.ingredient
    INNER JOIN ingredients_list AS il8 ON recipe.ingredient3 = ingredients_list3.ingredient 
    WHERE (?) in recipe''',(search)) #06/03/17 must use recipe
    list1 = conn.fetchall()

However, when I press the corresponding button to run the function, I get the error:

Code:

  File "C:\Python34\lib\tkinter\__init__.py", line 1533, in __call__
    return self.func(*args)
  File "F:\ComputingAlevel\CompProject\PROJECT.py", line 97, in search_byrecipe
    WHERE (?) in recipe''',(search)) #06/03/17 must use recipe
sqlite3.OperationalError: no such column: recipe.ingredient1

I've looked around on the net and I think that I have my ? placeholder right, there definitely is a column recipe.ingredient1 and I have used aliases. Or is something wrong with my usage of JOIN?
The code for the Entry and Button widgets are:

Code:

a = tk.StringVar()
key_term = tk.Entry(master, textvariable = a) #entry widget
b1 = tk.Button(master, text="Search by recipe", command=search_byrecipe)
blBgColor = b1["bg"] #set b1 background colour

So my question is, what is causing the 'no such column' error, and what amendments should I make to fix it?

If you would like to see the whole database to get a clearer picture of the tables, I will paste the code. Thanks for taking the time to read this.

This doesn't look like any SQL I've seen before, but I've never seen any query like this either....

Don't you need to make separate queries like:

Code:

$sth = $dbh->prepare ( q(
SELECT 
      recipe_name
FROM 
       recipe
WHERE 
       ingredient1 = ?
  AND ingredient2 = ?
  AND ingredient3 = ?
) );

($recipe_name[1]) = $sth->execute ($ingredient[1][1], $ingredient[1][2], $ingredient[1][3]);
($recipe_name[2]) = $sth->execute ($ingredient[2][1], $ingredient[2][2], $ingredient[2][3]);
($recipe_name[3]) = $sth->execute ($ingredient[3][1], $ingredient[3][2], $ingredient[3][3]);

$sth->finish;
$dbh->disconnect;

That was a lazy Perlish pseudocode snippet.

dugan · 04-26-2017, 07:54 PM

You definitely want to start by rewriting the query to be more like Laserbeak's:

Code:

SELECT 
      recipe_name
FROM 
       recipe
WHERE 
       ingredient1 = ?
  AND ingredient2 = ?
  AND ingredient3 = ?
) );

NevemTeve · 04-26-2017, 11:23 PM

Try this query directly (with sqlite3). Most likely it says the truth: you don't have such column. Nonetheless your database-design is plain wrong.

astrogeek · 04-27-2017, 12:08 AM

As others have said, that query is definitely no good!

The first question to ask is this:

Quote:

What data _exactly_ do I want to select with this query?

All recipe names? All recipe names which use certain ingredients? Recipe names and their ingredients? ...?

Then, once you have clearly stated what data (columns) you want, look at the database schema (table structure) and decide how to write the query.

First identify which tables must be included to get the columns that you want.

Next, figure out how the tables must be joined if multiple tables are involved (i.e. the keys or columns to join on).

Finally, identify what columns you must match to restrict just the rows that you want in your result (i.e. ingredient='sugar').

Put that all together and select your data!

If you can't figure it out, please post the answer to my first question (What data do you want?) and the structure (CREATE TABLE statement) of only the tables you need to get that data.

grail · 04-27-2017, 05:44 AM

All of the above is good advice, so I would only add, why did we start with Goliath when David would have been a good start, ie. instead of getting everything in one hit, try building up from a single
select/from/where and then build it up. Also, joins can be complicated to get your head around so I wouldn't use this until you are more familiar with your tables.

A/S-H · 04-27-2017, 08:39 AM

Following astrogeek's advice, I'm guessing I really want only the recipe table as I am selecting names from the recipe table, after the user has entered a search term for the name of a particular recipe (apologies that I didn't make this clear earlier).

So if a user searches for 'chicken', the function is supposed to select the names of recipes which include this term.
i.e. names of recipes like 'Chicken Casserole' will be selected.

So are the ingredients_list tables and corresponding joins unnecessary?

If this is the case, I'm assuming the code would look like this:

Code:

def search_byrecipe():  #03/03/17
    search = a.get() #a.get() gets the string from the variable
    query = conn.execute('''
    SELECT recipe.name AS name
    FROM recipe
    WHERE (?) in recipe
    ''',(search)) #06/03/17 must use recipe
    list1 = conn.fetchall()

The problem I have with this code is that the shell returns this error:

Code:

  File "D:\ComputingAlevel\CompProject\errorTest.py", line 89, in search_byrecipe
    ''',(search)) #06/03/17 must use recipe
sqlite3.OperationalError: only a single result allowed for a SELECT that is part of an expression

Does this mean I can only select one result at a time? This would be problematic as I would like to display row after row of clickable results by the user in the tkinter GUI using a Listbox. If that is the case, what workaround should I do so that I can select several results?

Laserbeak · 04-27-2017, 08:57 AM

Quote:

Originally Posted by A/S-H

Following astrogeek's advice, I'm guessing I really want only the recipe table as I am selecting names from the recipe table, after the user has entered a search term for the name of a particular recipe (apologies that I didn't make this clear earlier).

So if a user searches for 'chicken', the function is supposed to select the names of recipes which include this term.
i.e. names of recipes like 'Chicken Casserole' will be selected.

So then your code would be more like this:

Code:

$search_term = "chicken";

$sth = $dbh->prepare ( q(

SELECT
       recipe_name
FROM
       recipe
WHERE
       LOWER(ingredient1) LIKE ?
OR  LOWER(ingredient2) LIKE ?
OR LOWER(ingredient3) LIKE  ?

) );

$search_term = "%" . lc($search_term) . "%";

@results = $sth->execute ($search_term, $search_term, $search_term);
print "Here are your results:\n";
foreach (@results) {
    print "\t$_\n"
}

Again, that's pseudocode and not tested in any way.

astrogeek · 04-27-2017, 01:14 PM

Quote:

Originally Posted by A/S-H

I'm guessing I really want only the recipe table as I am selecting names from the recipe table, after the user has entered a search term for the name of a particular recipe (apologies that I didn't make this clear earlier).

So if a user searches for 'chicken', the function is supposed to select the names of recipes which include this term.
i.e. names of recipes like 'Chicken Casserole' will be selected.

Then the query reduces to something like this...

Code:

SELECT name
FROM recipe
WHERE name LIKE '%chicken%'
ORDER BY name;

That should do it.

I would suggest developing and testing your queries directly from a shell, then integrate the working query into your scripting language (i.e. python) with substitution of search variables. This will separate the SQL concerns from scripting language concerns and make life much easier!

sundialsvcs · 04-27-2017, 04:47 PM

I immediately notice that in your original query you say things like this:

JOIN ingredients_list AS il ON recipe.ingredient1 = ingredients_list.ingredient

You said you were going to refer to it as "il," but your query refers to ingredients_list. (This alias, of course, is never defined.)

Try this:

JOIN ingredients_list AS il ON recipe.ingredient1 = il.ingredient

- - -
Also, "repeating fields" like this one are not necessarily the best design. A better design would use a second, recipe_ingredients table which contains the recipe-id, a sequence number, and an ingredient-id. This allows a recipe to have any number of ingredients. (The recipe table identifies the recipe but does not list the ingredients. The ingredients table identifies the ingredients but does not specify which recipes (if any) they appear in.)

Code:

SELECT r.recipe_id, ri.sequence, i.ingredient_name
FROM recipes r
  INNER JOIN recipe_ingredients ri 
    ON r.recipe_id = ri.recipe_id
  INNER JOIN ingredients i
    ON ri.ingredient_id = i.ingredient_id
WHERE r.recipe_id = 123
ORDER BY ri.sequence

Laserbeak · 04-27-2017, 09:54 PM

Quote:

Originally Posted by astrogeek

Then the query reduces to something like this...

Code:

SELECT name
FROM recipe
WHERE name LIKE '%chicken%'
ORDER BY name;

That should do it.

I would suggest developing and testing your queries directly from a shell, then integrate the working query into your scripting language (i.e. python) with substitution of search variables. This will separate the SQL concerns from scripting language concerns and make life much easier!

You could theoretically have a dish that doesn't have chicken in its name, but has chicken in it -- that's why I looked at each ingredient not the name. For example, perhaps "Pot Pie" might have cubed cooked chicken as an ingredient.

But this database schema sucks (sorry to the OP), it's so far from being normalized, it's laughable. But I guess he's just trying to play around with SQL.

astrogeek · 04-27-2017, 11:36 PM

Quote:

Originally Posted by Laserbeak

You could theoretically have a dish that doesn't have chicken in its name, but has chicken in it -- that's why I looked at each ingredient not the name. For example, perhaps "Pot Pie" might have cubed cooked chicken as an ingredient.

Yes, but we are not talking about tasty dishes, we are talking about strings of characters we call data, organized in structures we call tables (which are hopefully also "relations").

That was the point of trying to get the OP to focus on what data _exactly_ they wanted to select.

Quote:

What data _exactly_ do I want to select with this query?

They concluded...

Quote:

Originally Posted by A/S-H

I'm guessing I really want only the recipe table as I am selecting names from the recipe table, after the user has entered a search term for the name of a particular recipe...

So if a user searches for 'chicken', the function is supposed to select the names of recipes which include this term. i.e. names of recipes like 'Chicken Casserole' will be selected.

So if we only want names, selected by terms in the names, then it would be an error to consider what was in the ingredients.

When writing queries in SQL it is always a good idea to try to express what you want to select in plain language first, as precisely as possible. The query will often closely follow the structure of the natural language sentence, and will only include corresponding terms.

But it is also predicated on the tables being normalized (i.e. being relations, not just tables), as you note.

A/S-H · 04-28-2017, 04:37 AM

I tried astrogeek's suggestion:

Code:

def search_byrecipe():  #03/03/17
    search = a.get() #a.get() gets the string from the variable
    query = conn.execute('''
    SELECT name AS n
    FROM recipe
    WHERE name LIKE '%'+search+'%'
    ORDER BY name;
    ''') 
    list1 = cur.fetchall()

Where 'search' is the value taken from an entry widget, textvariable = a, with a.get()
But I get a similar error, only the program thinks that search itself is a column:

Code:

  File "F:\ComputingAlevel\CompProject\errorTest.py", line 86, in search_byrecipe
    ''')
sqlite3.OperationalError: no such column: search

Is it the way 'search is placed in the LIKE clause? If I put '%search%' it works, but wouldn't that select recipes that have the string 'search' in their names, rather than the string value stored in search?

On a tangent here, but in response to sundialsvcs, if I were to use a second recipe_ingredients table, what exactly would the 'sequence' field contain? Also I'm supposing I would replace the ingr1, ingr2, ingr3 tables with one ingredients table?
Currently, the units of measurement of an ingredient's quantity in a recipe has its own table, with units as the sole column. Would I remove this table and make units a column in the 2nd recipe_ingredients table?

To laserbreak: Yeah, no offense taken. I appreciate that you are trying to help me here, thanks.

Laserbeak · 04-28-2017, 07:40 AM

I guess I missed the part where he said he just wanted to search the names, I thought he wanted any recipe with that ingredient.

Can you make a string out of the SQL and print it out? There may be a problem. Also, are you sure you're connecting to the database correctly and doing whatever you may need to do to select the right database?

norobro · 04-28-2017, 07:58 AM

Quote:

Originally Posted by A/S-H

Is it the way 'search is placed in the LIKE clause? If I put '%search%' it works, but wouldn't that select recipes that have the string 'search' in their names, rather than the string value stored in search?

Yep and yep. Try it this way:

Code:

>>> search = "chicken"
>>> print('''select name as n 
...          from recipe 
...          where name like \"%%%s%%\"
...          order by name;''' 
...          % (search, ))
select name as n 
         from recipe 
         where name like "%chicken%"
         order by name;
>>>

%% escapes a percent sign so you get a literal %

For your reading pleasure: https://docs.python.org/3/library/st...ing-formatting