[SOLVED] (Python 3.4.3,SQLite3):SELECT command returns 'no such column' error
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
(Python 3.4.3,SQLite3):SELECT command returns 'no such column' error
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 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....
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.
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.
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?
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.
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!
Last edited by astrogeek; 04-27-2017 at 01:27 PM.
Reason: typos
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
Last edited by sundialsvcs; 04-27-2017 at 04:55 PM.
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.
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.
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.
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?
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;
>>>
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.