Hi
I would like to do a correlated update involving tables located in two
logical databases on the same MySQL server.
The commented out code below would work except that the mysql ends up
looking for the one table in the wrong database. customer is defined
as Table('customer', ps_final_meta, autoload=True) and sdf_customer is
defined as Table('sdf_customer, ps_staging_meta, autoload=True).
How can I tell sqlalchemy to include the database names in the sql it
generates? For the moment I am just using SQL directly in an
execute().
file_ids_str = makeSQLList(tuple(file_ids))
# sqlalchemy correlated update
# TODO: figure out to do correlated updates across databases
#
#s = select([customer.c.MP_Code],
# and_(customer.c.CustomerAccNo1==sdf_customer.c.CustomerAccNo1,
# customer.c.WholesalerID==sdf_customer.c.WholesalerID
# )).limit(1)
#rc = sdf_customer.update(
# and_(sdf_customer.c.StatusID.in_([8, 12]),
# sdf_customer.c.FileID.in_(file_ids)
# ),
# values={sdf_customer.c.MP_Code:s}).execute().rowcount
sql = """
update
sdf_customer
set
sdf_customer.MP_Code = (
select
fc.MP_Code
from
ps_final.customer fc
where
sdf_customer.CustomerAccNo1=fc.CustomerAccNo1
and
sdf_customer.WholesalerID=fc.WholesalerID)
where
sdf_customer.StatusID in (8, 12)
and
sdf_customer.FileID in %s""" % (file_ids_str,)
rc = dbengine.execute(sql).rowcount
Any help would be much appreciated.
Regards,
Ryan
--
Ryan Tracey
Citizen: The World
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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
-~----------~----~----~----~------~----~------~--~---
