it's a Core selectable so you need to use the .c. prefix -
alias.c.columnname
On 08/12/2016 02:03 PM, Rahul Ahuja wrote:
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 <http://selecta.id>,u.id <http://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 <http://groupbya.id> having count(*)=1)m on m.id
<http://m.id> =a.id <http://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
<http://md_users.id>,abms_biog.id <http://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 <http://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 <http://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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<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.
