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.
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()
parses using the schema/engine associated with md_users, which is correct.
However,
u = update(abms_biog).\
prefix_with("ignore", dialect="mysql").\
where(abms_biog.id == q.c.abms_biog_id).\
values(user_id = q.c.md_users_id)
parses using using the schema/engine associated with abms_biog, which is
incorrect. Is there any way to "hardcode" my schema when specifying that
UDF or somehow include my other schema in that update statement?
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.
