On Aug 11, 2011, at 2:57 PM, Daniel Robbins wrote:
> based on the sample code in the O'Reilly book
Just FYI the Oreilly book is super, duper, extremely old and out of date. A
good read through the documentation on the SQLAlchemy site should be more
complete at this point and up to date, also stressing a whole set of new
techniques that weren't available when the OReilly book was written (it was
written against version 0.3).
>
> engine1 = create_engine('mysql://car:foo@localhost/car_fac')
> engine2 = create_engine('mysql://car:foo@localhost/car_res')
> engine1.echo = engine2.echo = True
>
> metadata = MetaData()
>
> product_table = Table( 'product', metadata,
> Column('sku', String(20), primary_key=True),
> Column('msrp', Numeric),
> mysql_engine='InnoDB')
>
> product_summary_table = Table( 'product_summary', metadata,
> Column('sku', String(20), ForeignKey('product.sku'),
> primary_key=True),
> Column('name', Unicode(255)),
> Column('description', Unicode(255)),
> mysql_engine='InnoDB')
>
> product_table.create(bind=engine1,checkfirst=True)
> product_summary_table.create(bind=engine2,checkfirst=True)
>
> As you can see, I'm using the same mysql account, but with two different
> engines in a vertical partitioning configuration. MySQL is happy with the
> foreign key relationships and creates the tables.
>
> So... I don't understand your suggestion of not emitting ForeignKey at table
> creation time. It appears to work in my basic test.
that would mean that the "car_res" database has access to a table called
"product". As I don't know how you've configured things there's no way to know
if its the same "product" table as that of "car_fac" or a different table local
to "car_res".
> Shouldn't SQLA detect that the tables are vertically partitioned, treat the
> ForeignKey relationships as NO-OPs to MySQL (because the tables may not be
> able to *see* each other, since you could be using different MySQL accounts
> with different permissions), and just use the ForeignKey definitions to help
> set up the mappers properly?
SQLAlchemy knows very little about any of that, it only knows that you told it
to use engine1 for table #1, engine2 for table #2 when using the Session. The
Session chooses which engine to use based on which entity your query is
against, and that's the extent of "vertical" partitioning in SQLAlchemy. If
you told it to write a JOIN between table #1 and table #2, and it was using
engine1, it would happily construct a JOIN between the two tables, send it off
to the first engine, and then whatever happens happens. Normally the operation
would be rejected by the database as "Table 2 doesn't exist !" but your
configuration would appear like you might have the same tables in multiple DBs.
This all also has nothing to do with the table.create() command you're using,
where again you pass an Engine to it, the "create" command issues CREATE TABLE,
unconditionally, including all the foreign keys you've given it. "schema" is
just an extra word it prepends to the table name, if specified.
How your database reacts to these "schema" names is all MySQL configuration
stuff. Sometimes "schema" means "query from some other physical database",
i.e. a "database link" sort of thing, I know Oracle does this but not sure what
MySQL can do in that regard, sometimes it just means "query this other database
partition on the same server" . You'd have to work out how you'd like
databases and SQL interaction with them to work.
I think the core insight here is that SQLAlchemy is much simpler than some
people would expect. It can only emit one SQL string at a time on one
connection. If you were to first work with your configuration given two
DBAPI connections to each database, that would make it more apparent what
SQLAlchemy has available in this regard.
>
> I have a more complex application that is basically doing the same thing as
> this example code, actually using three engines to connect to three different
> MySQL databases on the same server with the same account. This is probably
> not the best way to do things, as schema prefixing would be better. But as
> the above sample code shows, this *can* work. But my more complex app is
> failing with the errno 105, which is what is confusing me. I can't figure out
> the difference between my large application and this simple example, and why
> the simple example works but my application does not, when they are
> essentially doing the same thing. The sample code above emits SQL to MySQL
> that defines the ForeignKey relationship and does not need a "database."
> prefix. But my big app seems to need that "database." prefix. Maybe I have
> the binds messed up?
I think understanding what it is you'd like to achieve would be of help here,
why "vertical partitioning" is one of the requirements at all. If this is
just a set of tables that happen to be in different local "schemas", just use
the "schema" argument on each Table, use just one engine.
--
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.
