My patch did not add it to external functions, I made the reflection
use actually use the schema I had already specified in the Table()
call (in a very intrusive way, admittedly =) ). Before making this
change, the schema is set to None in the call to Table() in
_set_constraints (line 2336 in databases/mysql.py), because the schema
specified is not passed along.
The schema used for the reflected table is set by these lines
( 2314-2315 in databases/mysql.py):
ref_name = spec['table'][-1]
ref_schema = len(spec['table']) > 1 and spec['table'][-2]
or None
I don't quite understand what this is supposed to do, but they result
in None for this case anyway. I just made ref_schema be the schema I
specified in the original call to Table() from my code.
This is what I'm trying to do:
db = sqlalchemy.create_engine("mysql://r...@localhost/otherschema")
metadata = sqlalchemy.MetaData(db)
metadata.reflect()
products = Table("products", metadata, autoload = True, schema="r")
And this is the relevant parts of the table definitions (lots of
fields omitted for reasons of clarity):
CREATE TABLE `r`.`sources` (
`Source_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`Source_ID`),
UNIQUE KEY `pn` (`Source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `r`.`products` (
`ID` int(11) NOT NULL auto_increment,
`Source_id` int(11) NOT NULL default '0',
`ProductName` varchar(150) collate utf8_unicode_ci NOT NULL default
'',
PRIMARY KEY (`ID`),
UNIQUE KEY `uidx_products_pn_s` USING BTREE
(`Source_id`,`ProductName`),
CONSTRAINT `FK_products_sid` FOREIGN KEY (`Source_id`) REFERENCES
`sources` (`Source_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27583192 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm using MySQL 5.0.51a-3ubuntu5.4 for the server, and libmysqlclient
version 5.0.67-0ubuntu6 on the client.
-- Joakim
PS: The line numbers are for SVN trunk with my patch applied, but it
should be easy to find.
On 5 Jan, 16:00, Michael Bayer <[email protected]> wrote:
> foreign key reflection across schemas is implemented for MySQL, and
> the Table object itself takes a "schema" argument - no need to add
> this to external methods. What version of MySQL are you running and
> what is the output of SHOW CREATE TABLE for the tables in question ?
>
> On Jan 5, 2009, at 6:02 AM, Joakim Lundborg wrote:
>
> > Hi!
>
> > I have a database with foreign keys in tables not in the default
> > schema. when reflecting these tables, sqlalchemy only looks in the
> > default schema for the fk tables, so it can't find them. is there a
> > way to tell sqlalchemy to look for fk tables in another schema than
> > the default?
>
> > i.e. I have a database with schema A and schema B, A is the default
> > schema. In B I have defined Ba and Bb. Ba has a foreign key to Bb.
> > When doing sqlalchemy.Table("Ba", metadata, autoload = True, schema =
> > "B") I get NoSuchTableError: Bb
>
> > Is this a bug or am I missing something?
>
> > I have written a patch that fixes the problem for me, probably not the
> > best way to fix the problem, but I don't know much about SQLAlchemy
> > internals to do it nicely. Any suggestions on how to fix this
> > properly?
>
> > -- Joakim Lundborg
>
> > <fk_non-default_schema_reflection>
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
