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 lthe 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.
Urgh. Looks like I spoke too soon. In my previous email I said your
suggestion worked. That was only partially correct. Tables in the
second schema (the one not specified in the engine definition)
autoload fine and the correlated update across the two schemas works.
However, if a table in the second schema has a constraint on another
table in that same schema then autoloading fails. Looking at the (pdb)
traceback in ipython, somewhere schema='schema2' becomes schema=None
where FK constraints are being handled.
I am using SA 0.5.0beta2. But the same problem occurs with 0.4.6.
Here's the standard python traceback:
Traceback (most recent call last):
File "/usr/lib/python2.5/runpy.py", line 95, in run_module
filename, loader, alter_sys)
File "/usr/lib/python2.5/runpy.py", line 52, in _run_module_code
mod_name, mod_fname, mod_loader)
File "/usr/lib/python2.5/runpy.py", line 32, in _run_code
exec code in run_globals
File "/var/tmp/dbconnect.py", line 61, in <module>
autoload=True, schema='ps_final')
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py",
line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py",
line 242, in __init__
_bind_or_error(metadata).reflecttable(self, include_columns=include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py",
line 1282, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py",
line 1655, in reflecttable
only=include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py",
line 2135, in reflect
self._set_constraints(table, constraints, connection, only)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py",
line 2295, in _set_constraints
autoload=True, autoload_with=connection)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py",
line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py",
line 240, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py",
line 999, in reflecttable
return self.engine.reflecttable(table, self, include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py",
line 1282, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py",
line 1646, in reflecttable
sql = self._show_create_table(connection, table, charset)
File
"/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py",
line 1817, in _show_create_table
raise exc.NoSuchTableError(full_name)
sqlalchemy.exc.NoSuchTableError: manufacturer
Just to sum up:
dbengine = create_engine('mysql://u:[EMAIL PROTECTED]/schema1')
meta = MetaData()
meta.bind = dbengine
Table('tableX', meta, autoload=True)
...
Table('tableA', meta, autoload=True, schema='schema2')
Table('tableB', meta, autoload=True, schema='schema2')
tableX is okay, and tableA loads okay too. However tableB has a FK
referencing tableA. Bang! I get a NoSuchTableError for tableA.
I'll try get some ipython output. Or figure out how pdb works.
Kind 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
-~----------~----~----~----~------~----~------~--~---
