On Jul 17, 2008, at 7:12 AM, Ryan Tracey wrote:
>
> 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
>
OK, you have two MetaData objects which makes me think each one has
its own engine pointing to an environment with a particular default
schema. If you'd like one SQL statement to be generated, referencing
tables in both "schemas" and executeable within a single environment,
all the Table objects need to be defined in terms of one default
schema. Those which are in a different schema should include the
Table keyword argument "schema='somename'". You should get the whole
thing working using just one Engine and one MetaData object which
contains all tables.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
