Overwrite entire row in Sqlalchemy

Question

I have been reading up on ways to do this in sqlalchemy and there seem to be many solutions, including the most common .update({...}). However I don't want to specify which fields to update, because I simply want to overwrite the entire row that is associated with the primary key. What is a good way to do this?

One way I can think of is delete the matched row, and commit the new one. However, I'm not clear if there are any weird consequences of doing it this way, since my primary key is autogen not user defined id = Column(Integer, primary_key=True, autoincrement=True).

In what format do you have your data? Do you have instance of your `Model`? Do you have a `dict` with new values? — van, May 04 '21 at 07:24

score 0 · Accepted Answer · answered May 04 '21 at 08:09

I'm thinking you could create a dict full of empty values, then commit that row after adding your new values:

First, create a dictionary full of empty values for every column in the table:

some_table = db.Table('exact_table_name', metadata, autoload=True)
existing_fields = {column: " " for column in some_table.c}

then add the values you want to update, and create a query

existing_fields.update(**known_update_dict)
query = some_table.update()
# just adding some where-condition here... 
query = query.where(getattr(some_table.c, "primary_key") == 99)
query.values(**existing_fields)

and then execute that. A little convoluted, but I dont know a better way to do this either - I dont think there's a dedicated function or query word to do this.

Overwrite entire row in Sqlalchemy

1 Answers1