build an update out of abms_biog:
stmt = update(abms_biog)
use prefixes to get the "ignore" part:
stmt = stmt.prefix_with("ignore", dialect="mysql")
make sure your subquery is a Core selectable:
subq = my_orm_query.subquery()
add your subquery to the WHERE clause. e.g. the JOIN keyword won't be
there, it will just be an implicit join:
stmt = stmt.where(ambs_bloc.c.foo = my_subquery.c.bar)
Also, this SQL is totally specific to MySQL so there isn't really a
strong need for this to be in expression language in any case, if your
textual SQL works fine.
On 08/12/2016 12:07 AM, Rahul Ahuja wrote:
I'm trying to recreate the following SQL in SQLAlchemy:
|
update ignore abms_biog a join
(selecta.id,u.id asuser_id fromabms_biog a
join 3md.users u on u.firstname =a.firstname
andu.lastname =a.lastname
and3md.non_conflict_middlename(u.middlename,a.middlename)=1
andu.credentials in(4,5)anda.name_suffix =replace(u.namesuffix,'.','')
wherea.user_id isnull
anda.name_suffix <>''
groupbya.id having count(*)=1)m on m.id =a.id
seta.user_id =m.user_id
|
As you can see it's currently done in raw SQL with a subquery. I've got
the join and filter conditions down with:
|
base_match =sess.query(md_users.id,abms_biog.id).\
filter(md_users.firstname ==abms_biog.firstname).\
filter(md_users.lastname ==abms_biog.lastname).\
filter(func.non_conflict_middlename(md_users.middlename,abms_biog.middlename)==1).\
filter(md_users.credentials.in_([4,5])).\filter(abms_biog.user_id ==None).\
filter(abms_biog.name_suffix ==func.replace(md_users.namesuffix,'.','')).\
filter(abms_biog.name_suffix !='').\
group_by(abms_biog.id).\having(func.count()==1)
|
But I'm having trouble forming an update statement on this query that
SQLAlchemy accepts. It doesn't seem to allow updates on GROUP BY's, and
I've tried creating a subquery as well to no avail. I've tried the obvious
|
update(abms_biog).
\where(abms_biog.user_id ==abms_ids).\
values(user_id =md_users.id)
|
and a new select to isolate the column I'm updating
|
select(base_match).
with_only_columns([abms_biog.user_id])
|
This doesn't seem to be that unusual/complex of an operation so I'm
assuming that I'm missing something obvious. Any help would be much
appreciated!
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
