Python Forum
SQLALCHEMY - Column doesn't exist
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQLALCHEMY - Column doesn't exist
#1
Hi all,

Very simple issue - code in question is:

for data in engine.execute('select style_no from data where style_size_no_in = "' +myVariable[0] + '"'):
    print(x) 
The below error is being flagged:
Error:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "style_no" does not exist
The problem is that this column definitely exists as I've got PGAdmin4 open on another screen. If I change the style_no to * then it says that style_size_no_in doesn't exist either.

Please help.

James
while dad_has_cigs == True:
    happiness = True
    if dad_has_cigs == False:
    print("Dad come home!")
    happiness = not happiness
    break
Reply
#2
Figured out a fix - if I put the column names in quotes then it works fine.

Thanks,
James
while dad_has_cigs == True:
    happiness = True
    if dad_has_cigs == False:
    print("Dad come home!")
    happiness = not happiness
    break
Reply
#3
You also shouldn't be concatenating strings to build SQL statements, as that's vulnerable to SQL injection. Bobby Tables can educate you on this.
buran likes this post
Reply
#4
(Nov-01-2021, 12:45 PM)ndc85430 Wrote: You also shouldn't be concatenating strings to build SQL statements, as that's vulnerable to SQL injection. Bobby Tables can educate you on this.

Hi there,

I understand. My workaround (on flask) is to pull the data from sql, use that to build an html string, close the connection and then return the html string.

That way nothing on the user's end relates directly to the db.
while dad_has_cigs == True:
    happiness = True
    if dad_has_cigs == False:
    print("Dad come home!")
    happiness = not happiness
    break
Reply
#5
The vulnerability that @ndc85430 mentions has nothing to do with closing connection before returning anything to frontend.
Assuming you run query based on some query parameters that you receive from user you are exposed to SQL injection (also on Wikipedia)

Also, why do you construct html string at the backend, don't you use templates?
ndc85430 likes this post
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
(Nov-01-2021, 02:00 PM)buran Wrote: The vulnerability that @ndc85430 mentions has nothing to do with closing connection before returning anything to frontend.
Assuming you run query based on some query parameters that you receive from user you are exposed to SQL injection (also on Wikipedia)

Also, why do you construct html string at the backend, don't you use templates?

Hi,

No, the website itself doesn't need to look nice, just to display data. There isn't any query being constructed by user input, the page's role is to dynamically display data.

Thanks,
James
while dad_has_cigs == True:
    happiness = True
    if dad_has_cigs == False:
    print("Dad come home!")
    happiness = not happiness
    break
Reply
#7
Still, there's no downside to constructing queries correctly and it doesn't take much effort.
Reply
#8
(Nov-02-2021, 08:43 AM)jamesaarr Wrote: the page's role is to dynamically display data.
I still think you don't understand. How does it display data dynamically, without query parameters? E.g. where myVariable[0] value comes from?
ndc85430 likes this post
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#9
(Nov-02-2021, 11:20 AM)buran Wrote:
(Nov-02-2021, 08:43 AM)jamesaarr Wrote: the page's role is to dynamically display data.
I still think you don't understand. How does it display data dynamically, without query parameters? E.g. where myVariable[0] value comes from?

Hi mate,

The query uses variable parameters from seperate tables. It runs a query for all on one table, then searches the other tables for data using a list. These are fixed, there is no user input on this page, and the connection is closed before the HTML is returned in flask.

Thanks,
James
while dad_has_cigs == True:
    happiness = True
    if dad_has_cigs == False:
    print("Dad come home!")
    happiness = not happiness
    break
Reply
#10
I don't understand the aversion to doing the correct thing, especially when it isn't complicated.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  UndefinedEnvironmentName: 'extra' does not exist in evaluation environment EarthAndMoon 3 1,621 Oct-09-2023, 05:38 PM
Last Post: snippsat
  check if a file exist on the internet and get the size kucingkembar 6 1,714 Apr-16-2022, 05:09 PM
Last Post: kucingkembar
  pathlib destpath.exists() true even file does not exist NaN 9 4,565 Dec-01-2020, 12:43 PM
Last Post: NaN
  SQLAlchemy Flask ERROR: no such column XXX marcello86 0 2,809 Aug-31-2020, 09:21 AM
Last Post: marcello86
  Syntax not exist noorpy 1 1,970 Nov-14-2019, 09:23 AM
Last Post: perfringo
  Shutil attempts to copy directories that don't exist ConsoleGeek 5 4,450 Oct-29-2019, 09:26 PM
Last Post: Gribouillis
  get value if it's exist [python] senait 1 1,730 Aug-21-2019, 06:47 AM
Last Post: buran
  Python says module doesn't exist... I say it does! EricMichel 2 3,036 May-14-2019, 03:26 AM
Last Post: EricMichel
  pathlib: resolving a path that does not exist Skaperen 6 5,387 Sep-08-2018, 12:25 AM
Last Post: Skaperen
  Does stack overflow exist in python ? sylas 3 3,755 Jul-23-2017, 09:21 AM
Last Post: sylas

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020