LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 04-26-2017, 05:18 PM   #1
A/S-H
LQ Newbie
 
Registered: Jun 2014
Location: Southeast England
Distribution: Ubuntu
Posts: 24

Rep: Reputation: Disabled
(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.
 
Old 04-26-2017, 07:40 PM   #2
Laserbeak
Member
 
Registered: Jan 2017
Location: Manhattan, NYC NY
Distribution: Mac OS X, iOS, Solaris
Posts: 508

Rep: Reputation: 143Reputation: 143
Quote:
Originally Posted by A/S-H View Post
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.
 
1 members found this post helpful.
Old 04-26-2017, 07:54 PM   #3
dugan
LQ Guru
 
Registered: Nov 2003
Location: Canada
Distribution: distro hopper
Posts: 9,985

Rep: Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568Reputation: 4568
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 = ?
) );

Last edited by dugan; 04-26-2017 at 07:58 PM.
 
2 members found this post helpful.
Old 04-26-2017, 11:23 PM   #4
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,149

Rep: Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523Reputation: 1523
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.
 
2 members found this post helpful.
Old 04-27-2017, 12:08 AM   #5
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,583
Blog Entries: 11

Rep: Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600
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.
 
2 members found this post helpful.
Old 04-27-2017, 05:44 AM   #6
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
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.
 
2 members found this post helpful.
Old 04-27-2017, 08:39 AM   #7
A/S-H
LQ Newbie
 
Registered: Jun 2014
Location: Southeast England
Distribution: Ubuntu
Posts: 24

Original Poster
Rep: Reputation: Disabled
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?
 
Old 04-27-2017, 08:57 AM   #8
Laserbeak
Member
 
Registered: Jan 2017
Location: Manhattan, NYC NY
Distribution: Mac OS X, iOS, Solaris
Posts: 508

Rep: Reputation: 143Reputation: 143
Quote:
Originally Posted by A/S-H View Post
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.

Last edited by Laserbeak; 04-27-2017 at 08:59 AM.
 
1 members found this post helpful.
Old 04-27-2017, 01:14 PM   #9
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,583
Blog Entries: 11

Rep: Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600
Quote:
Originally Posted by A/S-H View Post
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
 
1 members found this post helpful.
Old 04-27-2017, 04:47 PM   #10
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,078
Blog Entries: 4

Rep: Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187Reputation: 3187
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.
 
1 members found this post helpful.
Old 04-27-2017, 09:54 PM   #11
Laserbeak
Member
 
Registered: Jan 2017
Location: Manhattan, NYC NY
Distribution: Mac OS X, iOS, Solaris
Posts: 508

Rep: Reputation: 143Reputation: 143
Quote:
Originally Posted by astrogeek View Post
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.
 
1 members found this post helpful.
Old 04-27-2017, 11:36 PM   #12
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,583
Blog Entries: 11

Rep: Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600Reputation: 3600
Quote:
Originally Posted by Laserbeak View Post
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 View Post
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.
 
1 members found this post helpful.
Old 04-28-2017, 04:37 AM   #13
A/S-H
LQ Newbie
 
Registered: Jun 2014
Location: Southeast England
Distribution: Ubuntu
Posts: 24

Original Poster
Rep: Reputation: Disabled
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.
 
Old 04-28-2017, 07:40 AM   #14
Laserbeak
Member
 
Registered: Jan 2017
Location: Manhattan, NYC NY
Distribution: Mac OS X, iOS, Solaris
Posts: 508

Rep: Reputation: 143Reputation: 143
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?
 
1 members found this post helpful.
Old 04-28-2017, 07:58 AM   #15
norobro
Member
 
Registered: Feb 2006
Distribution: Debian Sid
Posts: 792

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
Quote:
Originally Posted by A/S-H View Post
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

Last edited by norobro; 04-28-2017 at 01:04 PM. Reason: %chicken% should be enclosed in quotes
 
1 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] MySQL run SELECT on a table if column A form table 1 equals column A from table 2 robertjinx Linux - Software 1 01-15-2016 10:48 AM
Python script using tar from a list returns error: has no attribute 'startswith' linux1103 Linux - Newbie 3 04-05-2011 07:17 PM
php and sqlite3 can select but not insert file is set rw gruessle Programming 2 01-26-2007 05:13 AM
the ./ command returns error msg in 6.06 Thane Ubuntu 1 09-26-2006 10:30 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 06:01 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration