CODEWITHSUNDEEP
pythonsqlalchemy
David H
David H

Reputation: 1605

Replace existing records in SQLAlchemy Core

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.

Upvotes: 1

Views: 698

Answers (1)

zzzeek
zzzeek

Reputation: 75117

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)
      )    
)

Upvotes: 1

Related Questions