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
--
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
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====#
