1

I have a short SQL script which "copies" selected columns from a SQL table from one id (main_id=1) to two other ids (main_id=3 and 4) of the same table. There are also some other ids which are part of the primary key of the table.

The script works fine using a PostgreSQL DB. However, i would like to replace this using SQLAlchemy ORM, but i don't know how to do this.

UPDATE "MyTable" AS T
SET "Varname_1" = Cell."Varname_1",
    "Varname_2" = Cell."Varname_2"
FROM "MyTable" AS Cell
WHERE T.id_A = Cell.id_A AND
      T.id_B = Cell.id_B AND
      Cell.main_id = 1 AND
      T.main_id IN (3, 4);

Can anyone help me to "translate" this?

Nickolay
  • 31,095
  • 13
  • 107
  • 185
boseki
  • 13
  • 2
  • Why don't you execute this query directly? – Nickolay Aug 14 '19 at 13:12
  • First, if i could do this in SQLAlchemy, i would be using SQLAlchemy for all database actions in my program, instead of a mix of SQLAlchemy and raw SQL. Second, portability: (as far as i know) the script above does not work for all SQL dialects. Before using Postgres i used a MS SQL server, for which i had to use a different script. – boseki Aug 16 '19 at 11:52
  • I meant [executing the raw SQL via SQLAlchemy](https://stackoverflow.com/a/18808942/1026), and I don't think this SQL should differ a lot between dialects (looks like valid T-SQL to me), but see my answer. – Nickolay Aug 16 '19 at 15:11

1 Answers1

0

Not sure what you were having problems with, as I was able to do this by following the examples from Multiple Table Updates and Using Aliases and Subqueries sections of the tutorial:

import sqlalchemy
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import alias
metadata = MetaData()

my_table = Table('MyTable', metadata,
    Column('id_A', Integer),
    Column('id_B', Integer),
    Column('main_id', Integer),
    Column('varname_1', String),
    Column('varname_2', String),
)
cell = my_table.alias("cell")
stmt = my_table.update(). \
    where(my_table.c.id_A == cell.c.id_A). \
    where(my_table.c.id_B == cell.c.id_B). \
    where(cell.c.main_id == 1). \
    where(my_table.c.main_id.in_([3, 4])). \
    values(varname_1=cell.c.varname_1,
           varname_2=cell.c.varname_2)
print(str(stmt))
print(stmt.compile().params)
Nickolay
  • 31,095
  • 13
  • 107
  • 185
  • Thank you very much, this works perfectly. (My main problem are my very limited SQL skills... ;-) ) – boseki Aug 19 '19 at 11:51
  • @boseki Glad to hear that! Feel free to mark the answer as accepted if it solves your problem. – Nickolay Aug 19 '19 at 12:00