Thank you so much for the reply! I'm almost there but am having trouble
selecting the abms_biog.id column from my subquery - I'm getting an
"AttributeError: 'Alias' object has no attribute 'abms_biog'" when I try:
u = update(abms_biog)
u = u.prefix_with("ignore", dialect="mysql")
q = q.subquery()
z = u.where(abms_biog.id == q.abms_biog.id)
Does the subquery alias inherit the query's columns as attributes? Do I
need a select() somewhere? Thanks once again for your time.
On Friday, August 12, 2016 at 7:07:19 AM UTC-7, Mike Bayer wrote:
>
> 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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
