Got it, thanks! One last question if you have the time: for some reason it's looking at the wrong schema when parsing my UDF func.non_conflict_middlename in the update statement, despite parsing fine in the base query and subquery. I suspect that this is because my first table in the update statement belongs to a different schema than my UDF. Is there any way to "hardcode" my schema when specifying that UDF or somehow include my other schema in that update statement?
relevant code: q = sess.query(md_users.id.label('md_users_id'), abms_biog.id.label( '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).\ group_by(abms_biog.id).\ having(func.count() == 1).subquery() which parses using the schema/engine bound to md_users which is correct. However, On Friday, August 12, 2016 at 1:40:54 PM UTC-7, Mike Bayer wrote: > > 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:> > <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+...@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. > > 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.