0

I have a flask application that is connected to a postgresql database. My issue is that when I query the database using SQLAlchemy to insert an item, this is what happens:

from sqlalchemy import insert

catInfoString = json.dumps(catInfo)
productsString = json.dumps(products)

stmt = (
    insert(Category).
    values(catInfo=str(catInfoString), products=str(productsString))
)

I get this error: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "catInfo" of relation "category" does not exist

So, I tried to see if catInfo exists with this:

allCatInfo = db.engine.execute("SELECT catInfo FROM Category")
print(allCatInfo)

This worked perfectly. So why is it that I can query this table, but not insert?

Variable outputs

catInfoString: {"name": "test", "shortDesc": "cat desc"}

productsString: [["test", {"title": "holderProduct", "path": ["Products", "test"], "desc": "product desc"}]]

type(catInfoString): <class 'str'>

type(productsString): <class 'str'>

Case Sensitive

Also, when I try to use `catinfo` instead of `catInfo`, I get this error message:

sqlalchemy.exc.CompileError: Unconsumed column names: catinfo

Using `INSERT INTO ...`

I tried using this line instead too... 
qry = "INSERT INTO Category (catInfo, products) VALUES (%s, %s)" % (str(catInfoString), str(productsString))
db.engine.execute(qry)

However, this did not work either and raised the error below: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "{"

safnasfsa
  • 107
  • 9
  • 2
    The raw sql query you're using does not (double) quote `catInfo`. As such, postgresql interprets it as `catinfo` (unquoted identifiers are lowercased). My guess is that psycopg2 always double-quotes identifiers, so the query is looking for `"catInfo"`, which is not the same as `"catinfo"`. You can check this by running `db.engine.execute('SELECT "catInfo" FROM Category')` (which, if I'm right, should fail). My personal recommendation is to never use uppercase characters in database objects (at least when using postgresql), use snake_case instead. – Marth Jun 22 '22 at 21:49
  • 1
    Does this answer your question? [Are PostgreSQL column names case-sensitive?](https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive) – Adrian Klaver Jun 22 '22 at 21:57
  • No unfortunately not @AdrianKlaver – safnasfsa Jun 22 '22 at 22:02
  • @Marth I have updated the question with what you have requested. – safnasfsa Jun 22 '22 at 22:02
  • Actually it does. I'm guessing if you change `catInfo=str(catInfoString)` to `catinfo=str(catInfoString)` it will work. – Adrian Klaver Jun 22 '22 at 22:05
  • Actually, that does not work... I have updated my question to show you what happens when I do that @AdrianKlaver – safnasfsa Jun 22 '22 at 22:05
  • Then you are going to need to show how you define `Category`. There are two ways you get this error: 1) The case is not matching 2) The column does not actually exist in the table. You can get to 2) by pointing at the wrong database or the wrong instance of the table, think non-schema qualified table name and `search_path` picking the wrong table. – Adrian Klaver Jun 22 '22 at 22:11
  • But that does not explain why the query: `SELECT catInfo from Category' works, but inserting into it doesnt... @AdrianKlaver – safnasfsa Jun 22 '22 at 22:12
  • Because `SELECT catInfo FROM Category` will down case `catInfo` to `catinfo`. Reading the link I sent explains this. Obviously, the other SQLAlchemy method is preserving the case of `catInfo`. – Adrian Klaver Jun 22 '22 at 22:14
  • Ok... that means that if I were to swap `catInfo` to `catinfo`, it would work as that is all the `select` is doing. So why does it not work when I do that. Alternatively, what if I used `INSERT INTO Category`...? @AdrianKlaver – safnasfsa Jun 22 '22 at 22:16
  • As I said earlier you need to show how you define `Category`? – Adrian Klaver Jun 22 '22 at 22:17
  • Please check the update @AdrianKlaver – safnasfsa Jun 22 '22 at 22:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245832/discussion-between-adrian-klaver-and-safnasfsa). – Adrian Klaver Jun 22 '22 at 22:21
  • You could be missing the single quotes around your values in your SQL statement i.e `... VALUES ('%s', '%s')` – PGHE Jun 23 '22 at 01:15

0 Answers0