0

I have two tables, Table A and Table B. I have added one column to Table A, record_id. Table B has record_id and the primary ID for Table A, table_a_id. I am looking to deprecate Table B.

Relationships exist between Table B's table_a_id and Table A's id, if that helps.

Currently, my solution is:

db.execute("UPDATE table_a t 
   SET record_id = b.record_id 
   FROM table_b b 
   WHERE t.id = b.table_a_id")

This is my first time using this ORM -- I'd like to see if there is a way I can use my Python models and the actual functions SQLAlchemy gives me to be more 'Pythonic' rather than just dumping a Postgres statement that I know works in an execute call.

MikeJannino
  • 1,292
  • 12
  • 23
  • Possible duplicate of [How do you express a multi table update (UPDATE FROM) in SQLAlchemy ORM?](https://stackoverflow.com/questions/42543223/how-do-you-express-a-multi-table-update-update-from-in-sqlalchemy-orm) – Ilja Everilä Jan 24 '18 at 07:55
  • You refer to having tried various query strategies. Please include *how* they failed, or did not work for you. – Ilja Everilä Jan 24 '18 at 08:08
  • 1
    For example [`Query.update()`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.update) takes the values as a mapping in the first positional argument, not as keyword arguments. `TableA.query()` seems also suspect, since at least in Flask-SQLAlchemy the [`query` attribute is not callable](https://stackoverflow.com/questions/40918479/querying-with-function-on-flask-sqlalchemy-model-gives-basequery-object-is-not-c). If `TableA.query` is something else, you'll have to provide a [mcve]. And Postgresql does not allow UPDATE on a join anyway, except with UPDATE FROM. – Ilja Everilä Jan 24 '18 at 08:28
  • @IljaEverilä Thank you for the comments -- I am using SQLAlchemy core in Python with PostgreSQL. Your last comment made me realize I'm trying to get to the wrong solution. I am going to change the question a bit to be a little more clear -- I've identified a solution and just need a way to get there. (I'm currently poring over the SQLAlchemy docs) – MikeJannino Jan 24 '18 at 15:35
  • 1
    Have a look at the dupe candidate target. SQLAlchemy supports UPDATE FROM through implicitly referencing other tables, for example in `filter()` criteria. Your original attempt was very close to a correct solution. – Ilja Everilä Jan 24 '18 at 15:48
  • Thank you again @IljaEverilä , I'm going to add my final solution as an answer to this post but I would not have known where to look without your help. – MikeJannino Jan 24 '18 at 19:57

1 Answers1

0

My solution ended up being as follows:

(db.query(TableA)
        .filter(TableA.id == TableB.table_a_id,
        TableA.record_id.is_(None))
        .update({TableA.record_id: TableB.record_id}, synchronize_session=False))

This leverages the ability of PostgreSQL to do updates based on implicit references of other tables, which I did in my .filter() call (this is analogous to a WHERE in a JOIN query). The solution was deceivingly simple.

MikeJannino
  • 1,292
  • 12
  • 23