Updating value in table sqlalchemy postgres

Question

I am trying to update a value in my table, and add it back in into the database. However, it does not seem to be updating the table when I do db.session.commit.

Example of Category table:

Category(id=1, catInfo="{}", products = "[{}]")

Here are my steps:

products2 = json.dumps([{'info': 'info'}])

# I am trying to update products in Category with an ID of 1 with a new list
db.engine.execute("UPDATE products FROM Category WHERE id = 1" + " SET products = " + products2)

# commit the database
db.session.commit()

I am getting TypeError: dict is not a sequence No idea why

You are modifying a Python list, not the data in the database. You'll have to do an `INSERT` or `SELECT` or something, _then_ commit. — ChrisGPT was on strike, Jun 19 '22 at 11:57
Well it's not entirely clear what you are trying to do. But `allProducts` is just a list in memory. How did you get data into your database in the first place? You probably ran some `INSERT`s, right? You'll need to do that again to modify the data in the database. In this case you might actually want to do an `UPDATE` or a `DELETE` and an `INSERT`. You'll want some unique identifier to do that, usually an ID. — ChrisGPT was on strike, Jun 19 '22 at 12:02
I am trying to `UPADTE` a particular item in the database. Do you have the line to use for this? — safnasfsa, Jun 19 '22 at 12:16
I am aware of how to add and delete, jus tnot sure how to update... — safnasfsa, Jun 19 '22 at 12:21
@safnasfsa, how did you learn about `select`, `insert`, and `delete`? Surely you can learn about `update` in the same way? — ChrisGPT was on strike, Jun 19 '22 at 12:28
Maybe answer is in SQLAlchemy manuals ? https://docs.sqlalchemy.org/en/14/core/dml.html — Kadet, Jun 19 '22 at 12:42
@safnasfsa, please also include the structure of the Category table, a few example data rows, and a specific end state you're trying to achieve. I'm not clear on what `Category.products` is supposed to be, or how we can uniquely identify the row you wish to modify. — ChrisGPT was on strike, Jun 19 '22 at 12:46

score -1 · Answer 1 · answered Jun 19 '22 at 12:01

-1

As Chris commented, you need to write a query to insert the data back in the database. Here is an example on how to insert an object in sqlalchemy:

customer = Customer(
    first_name='Todd',
    last_name='Birchard',
    email='fake@example.com',
    preferred_language='English',
    join_date=datetime.now()
)
session.add(customer)
session.commit()

If you need to add multiple objects, consider add_all instead. Reference

answered Jun 19 '22 at 12:01

Sorin Burghiu

715
1
7
26

2

The use of `Customer()` here assumes OP is using the SQLAlchemy ORM and has a `Customer` model defined. The question shows a raw SQL query (not ORM) and refers to products and categories (not customers). And OP is trying to _replace_ one record with another, not add a new one. Can you provide a better example? – ChrisGPT was on strike Jun 19 '22 at 12:03
You are correct. I meant `UPDATE`. So far, I have this... `db.engine.execute("UPDATE Category WHERE id = catID")` but I am just not sure how to set the value of products in category to a list I have stored. @Chris – safnasfsa Jun 19 '22 at 12:28
@safnasfsa, please edit this into your question. All relevant information needs to be in one place, and comments can be deleted at any time for any reason. Please also include the structure of the `Category` table and a few example data rows. – ChrisGPT was on strike Jun 19 '22 at 12:37

Updating value in table sqlalchemy postgres

1 Answers1