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 [email protected] <javascript:>
> > > <mailto:[email protected] <javascript:>
> <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
> > <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 [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.
