Replace existing records in SQLAlchemy Core

Question

I want to replace existing records in a table with records from a new table that have the same key.

In SQL, for example, I would just execute the following two statements:

DELETE O FROM OLD_TABLE T1
    WHERE EXISTS (SELECT * FROM NEW_TABLE T2
                  WHERE T1.KeyPart1 = T2.KeyPart1
                  AND   T1.KeyPart2 = T2.KeyPart2 )

INSERT OLD_TABLE 
     SELECT * FROM NEW_TABLE T2
     WHERE NOT EXISTS (SELECT * FROM OLD_TABLE T1
                        WHERE T1.KeyPart1 = T2.KeyPart1
                        AND   T1.KeyPart2 = T2.KeyPart2)

In Python, How would I do this using SQLAlchemy Core (NOT the ORM)?

OldTable.delete().????   
OldTable.insert(NewTable.select()) ????

I'm afraid I'm totally lost.

P.S. I'm doing this in SQLAlchemy CORE because (a) there's a log of records, and (b) I want SQLAlchemy to handle the database dialect dependence.

zzzeek · Accepted Answer · 2014-08-30 20:46:08Z

1

an "exists" is handled in the WHERE clause using exists(), and "INSERT .. FROM SELECT" is via from_select().

conn.execute(
   old_table.delete().where(exists().where(
              old_table.c.col == new_table.c.col
             ).where(
              old_table.c.col2 == new_table.c.col2).
              correlate(old_table)
   )
)

conn.execute(
      old_table.insert().from_select(['col', 'col2'], new_table.select().where(
      ~exists().where(old_table.c.col == new_table.c.col).correlate(new_table)
      )    
)

answered Aug 30, 2014 at 20:46

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace existing records in SQLAlchemy Core

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related