Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlalchemy easy way to insert or update?

I have a sequence of new objects. They all look like similar to this:

Foo(pk_col1=x, pk_col2=y, val='bar')

Some of those are Foo that exist (i.e. only val differs from the row in the db) and should generate update queries. The others should generate inserts.

I can think of a few ways of doing this, the best being:

pk_cols = Foo.table.primary_key.keys() for f1 in foos:     f2 = Foo.get([getattr(f1, c) for c in pk_cols])     if f2 is not None:         f2.val = f1.val # update         # XXX do we need to do session.add(f2)          # (or at least keep f2 alive until after the commit?)     else:         session.add(f1) # insert   session.commit() 

Is there an easier way?

like image 332
Eloff Avatar asked Sep 05 '09 04:09

Eloff


People also ask

How do I update data in SQLAlchemy?

Update table elements in SQLAlchemy. Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.

What command do you need to use to insert a row in a table when using SQLAlchemy?

In SQL, we use the INSERT command to add records/rows into table data.

Is SQLAlchemy good for ETL?

One of the key aspects of any data science workflow is the sourcing, cleaning, and storing of raw data in a form that can be used upstream. This process is commonly referred to as “Extract-Transform-Load,” or ETL for short.


1 Answers

I think you are after new_obj = session.merge(obj). This will merge an object in a detached state into the session if the primary keys match and will make a new one otherwise. So session.save(new_obj) will work for both insert and update.

like image 145
David Raznick Avatar answered Sep 26 '22 08:09

David Raznick