Hi Michael
2008/7/17 Michael Bayer <[EMAIL PROTECTED]>:
>
>
> 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.
Ahhh! Works like a charm. It seems I missed the obvious. Thank you
kindly for speedy assistance!
Just a note on something I picked up concerning stored procedures and
MySQL. There's a thread (which I seem unable to locate now) which
ended with the suggestion that to get SPs working with SA and MySQL
one should edit databases/mysql.py and add the CALL keyword to the
regex which handles SELECTs, etc.
SQLAlchemy-0.5.0beta1-py2.5.egg/sqlalchemy/databases/mysql.py
Diff:
224c224
< r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER)',
---
> r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER|CALL)',
The suggested patch works (for the way that I am using SPs) but has
not been applied to the main SA branch -- I have had to apply the
patch to a number of SA versions which have come out since the patch
was originally suggested. Is this an oversight or is there a reason
why the patch has not been applied. Just don't want to be shooting
myself in the foot with a patch that solves one problem and introduces
another.
Thanks again for the help and much gratitude for the totally awesome
SQLAlchemy. Even without using the orm component it makes working with
databases almost a pleasure ;-)
Cheers,
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
-~----------~----~----~----~------~----~------~--~---
