Update from select via SqlAclchemy session query or Expression Language

[Praveen]

Hello,

I have below use where

Employer and EmployerPhone the one emp can have multiple phone number out of which at any given point of time there should be
only one 'is_active' due to concurrency load, emp_phone got multiple 'is_active' as 'Y' in emp_phone i want to update the emp_phone table
records to have only is_active record for a given emp.  I tried out query and Expression Language to fix this, but i am not lucky enough to fix this.

Could anyone suggest me to write query in SqlAlchemy to fix this issue.

I have also attached sample python file which i tried with SqlLite.

####################################
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper

#Actual use case is with SQL Server 2008 and Oracle
#uri = "mssql+mxodbc://sa:sa@master"
#engine = create_engine(uri)

#Tried with SQL Lite
engine = create_engine('sqlite:///:memory:', echo=True)
session = sessionmaker(bind=engine)
session = session()

#Define tables....
metadata = MetaData()

emp = Table("emp", metadata,
            Column("emp_idn", Integer, primary_key=True, autoincrement=True),
            Column("emp_name", String),
            Column("is_active", String),
            )
emp_phone = Table("emp_phone", metadata,
    Column("phone_no", String),
    Column("emp_phone_idn", Integer, primary_key=True, autoincrement=True),
    Column("emp_idn", Float, ForeignKey('emp.emp_idn')),
    Column("is_active", String))

metadata.create_all(engine)

#Define model.
class Emp(object):
    pass

class EmpPhone(object):
    pass

#mapping...
mapper(Emp, emp)
mapper(EmpPhone, emp_phone)

#My goal is to Use Session to create Emp and Emp Phone.
emp = Emp()
emp.emp_name = 'Royal'
emp.is_active = 'Y'
session.add(emp)
session.flush()

emp = Emp()
emp.emp_name = 'Royal Eng'
emp.is_active = 'Y'
session.add(emp)
session.flush()

empPhone = EmpPhone()
empPhone.emp_idn = emp.emp_idn
empPhone.phone_no = '12345'
empPhone.is_active = 'Y'
session.add(empPhone)
session.flush()

empPhone = EmpPhone()
empPhone.emp_idn = emp.emp_idn
empPhone.phone_no = '67890'
empPhone.is_active = 'Y'
session.add(empPhone)
session.flush()


#Update all the Duplicate is_active records with 'N' which are duplicted per emp_idn
#There should be only one active phone number per emp
sub_qry = session.query(EmpPhone.emp_idn).filter(EmpPhone.is_active=='Y')
sub_qry = sub_qry.group_by(EmpPhone.emp_idn)
sub_qry = sub_qry.having(func.count(EmpPhone.emp_idn) > 1)
upd_qry = session.query(EmpPhone).filter(EmpPhone.emp_idn.in_(sub_qry)).update({'is_active': 'N'}, False)

session.commit()
for each in session.query(EmpPhone).all():
    print each.emp_idn, each.phone_no, each.is_active

##My Goal is to update the emp_phone records having multiple is_active to have
##only once is_active as 'Y' based on last loaded record for one employee.
##I figured out raw sql, how ever i am not able get this done in SQ Query or SQ expression langauge.

#====Raw SQL====#  SQ Query or SQ expression langauge
#Update emp_phone set is_active='Y'
#from emp_phone b inner join (
#Select emp_idn, max(emp_phone_idn) max_emp_phone_idn from emp_phone group by emp_idn)
#a on a.max_emp_phone_idn =b.phone_num_idn
#====Raw SQL====#


####################################


Thanks in advance,

Praveen

[Michael Bayer]

On Oct 12, 2012, at 9:40 AM, Praveen wrote:

Hello,

I have below use where

Employer and EmployerPhone the one emp can have multiple phone number out of which at any given point of time there should be
only one 'is_active' due to concurrency load, emp_phone got multiple 'is_active' as 'Y' in emp_phone i want to update the emp_phone table
records to have only is_active record for a given emp.  I tried out query and Expression Language to fix this, but i am not lucky enough to fix this.

There's a lot going on in this question.  I want to show you how to get the SQL you're looking for, but also there's better ways to do this.  I apologize for the length here, but this was my thought process as I went through your case one step at a time.  The "answer" you probably want is at the end.

So first let's deal with the query that is setting the "max" record to "Y".  I see that you have a separate query doing the "N" part, though there are ways to do both at once.

As far as UPDATE..FROM, I'd recommend reading all the answers to this question: http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql  which will give you a great overview of FROM/JOIN in UPDATE and how all over the place it is on different databases.   SQLAlchemy supports plain UPDATE and recently has added UPDATE...FROM.     However it does *not* support Oracle's syntax, which is radically different, except for a single-column UPDATE, which is already standard SQL.

Also note SQLite has no support for any UPDATE..FROM syntax at all.

So below, I'll show you what we have for UPDATE..FROM, but if your target is sqlite/mssql/oracle, you're probably going to want to stick with ANSI syntax which handles this very easily in any case (the Y and N all at once in fact).

UPDATE..FROM is supported in the SQL expression language as of 0.7.  The ORM handles this usage as of 0.8 which will have it's first beta releases before month's end.    UPDATE...FROM ... JOIN is not supported right now, but a JOIN can always be stated implicitly in any case.

Let's talk about your query now.   We start with this (note, I corrected "phone_num_idn" to read "emp_phone_idn"):

update emp_phone set is_active='Y' 

from emp_phone b inner join (

select emp_idn, max(emp_phone_idn) max_emp_phone_idn 

from emp_phone group by emp_idn

) a 

on a.max_emp_phone_idn =b.emp_phone_idn

the JOIN here can be stated implicitly, as in the WHERE clause:

update emp_phone set is_active='Y' 

from emp_phone b, (

select emp_idn, max(emp_phone_idn) AS max_emp_phone_idn 

from emp_phone group by emp_idn

) a 

WHERE a.max_emp_phone_idn = b.emp_phone_idn

you also don't need "emp_idn" in the SELECT there, it's just for grouping.

The ANSI sql version, IMO is a lot easier.  It's just this:

update emp_phone set is_active='Y' where emp_phone_idn IN

(

   SELECT max(emp_phone_idn) FROM emp_phone group by emp_idn

)

Surprisingly, MySQL can't do the ANSI version.    Seems you're not on MySQL so you should be OK.

Here's how SQLAlchemy can render these.

First assume SQLAlchemy 0.8.   The ORM there can do UPDATE..FROM like this:

subq = session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')).group_by(EmpPhone.emp_idn).subquery()

session.query(EmpPhone).filter(EmpPhone.emp_phone_idn == subq.c.max_emp_phone_idn).update({'is_active':'Y'}, synchronize_session=False)

The SQL won't be the same as the above, but it's equivalent.  I've added a test for this case just to make sure.  In SQL Server you get this (note, my example here uses "documents" since it's from a test case):

UPDATE documents SET flag=? FROM documents, (SELECT max(documents.title) AS title 

FROM documents GROUP BY documents.user_id) AS anon_1 WHERE documents.title = anon_1.title

in MySQL, they want the other FROMs in the UPDATE clause, so you get this:

UPDATE documents, (SELECT max(documents.title) AS title 

FROM documents GROUP BY documents.user_id) AS anon_1 SET documents.flag=%s WHERE documents.title = anon_1.title

and in Postgresql, the table being updated is not re-stated so we get this:

UPDATE documents SET flag=%(flag)s FROM (SELECT max(documents.title) AS title 

FROM documents GROUP BY documents.user_id) AS anon_1 WHERE documents.title = anon_1.title

The UPDATE..FROM above will not work in SQLite or Oracle.

Since I'm assuming you're on 0.7, we can adapt the above so that it executes via the expression language completely:

# this is the same

subq = session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')).group_by(EmpPhone.emp_idn).subquery()

# create an UPDATE directly against the"emp_phone" Table object.  Can still use the mapped classes

# for the WHERE clause

upd = emp_phone.update().values(is_active='Y').where(EmpPhone.emp_phone_idn == subq.c.max_emp_phone_idn)

# execute

session.execute(upd)

Next, the ANSI version, which IMO is the simplest, and we can also get the whole Y/N thing to happen in one step.    We keep working with that same subquery, which because it only returns one column can be used in a scalar context:

subq = session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')).group_by(EmpPhone.emp_idn).subquery()

session.query(EmpPhone).filter(EmpPhone.emp_phone_idn.in_(subq)).update({"is_active":"Y"}, synchronize_session=False)

this gives you the SQL (on SQL Server):

UPDATE emp_phone SET is_active='Y' WHERE emp_phone_idn IN (SELECT max(emp_phone_idn) AS max_emp_phone_idn

FROM emp_phone GROUP BY emp_idn)

So all of that, is to get your "Y" flag.   Let's now see how we can get the "Y" and "N" in there all at once.    One easy way is to just run the same IN query in reverse, to get the "N":

session.query(EmpPhone).filter(~EmpPhone.emp_phone_idn.in_(subq)).update({"is_active":"N"}, synchronize_session=False)

But another, is do to the Y/N all at once using a column-level expression:

UPDATE emp_phone SET is_active=CASE WHEN (emp_phone_idn in 

(SELECT max(emp_phone_idn) AS max_emp_phone_idn FROM emp_phone GROUP BY emp_idn)

             ) THEN 'Y' ELSE 'N' END

Again, the above works everywhere except MySQL.    We can take our subq and stick it into a case():

# note: THIS IS PROBABLY THE ANSWER YOU WANT

from sqlalchemy import case

subq = session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')).group_by(EmpPhone.emp_idn).subquery()

case_stmt = case([(EmpPhone.emp_phone_idn.in_(subq), 'Y')], else_='N')

session.query(EmpPhone).update({"is_active":case_stmt}, synchronize_session=False)

So above, except for on MySQL, you can get the Y/N all in there in one shot using ANSI SQL just by putting a subquery into a CASE statement.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/R_pRZBMlVDgJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<to_mail.py>

[pavi ena]

Thanks a lot, The Query worked for me. Thanks for giving nice explanation about ANSI Standard on SQL, which helped
me to understand standards across different RDMS.