Reflecting tables with foreign keys

[Don Dwiggins]

I'm running SA 0.4.3 on Win XP, accessing a MSSQL database using pyodbc,
and I want to autoload some table definitions. In the Metadata
documentation, I read:
"Note that if a reflected table has a foreign key referencing another
table, the related Table object will be automatically created within the
MetaData object if it does not exist already."

This doesn't seem to work. When I reflect a table with FKs, it comes in
OK, and the metadata seems to know about the FK, but eval'ing
'referenced_table' in meta.tables
(as in the example) returns False.

Also, reflecting the referenced table without the autoload=True
parameter doesn't work -- the table I get is empty (but it does come in
OK if I use autoload).

I also tried using meta.table_iterator, and got a message like "Could
not find table xxx with which to generate a foreign key". The table its
asking for is indeed referenced, and if I autoload it (and all the other
referenced tables), the iterator works fine.

I can understand that it might not be a good idea, when autoloading a
table, to try to bring in the entire "FK tree" with it; in my database,
doing that on some tables would bring in several dozen other tables.
I'm really just trying to reconcile what I read in the docs with how the
code works.

Thanks for any good words,
--
Don Dwiggins
Advanced Publishing Technology


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Michael Bayer]

hi don -

heres a script using SQLite which illustrates how the foreign key
reflection works. This same sort of thing should be working on MS-SQL
as well but I dont have access to an MS-SQL server here to test. If
the example below is not working for MS-SQL, please file a trac ticket
- we have some MS-SQL developers who can take a look.

- mike

from sqlalchemy import *

engine = create_engine('sqlite:///', echo=True)

metadata = MetaData(engine)

Table('t1', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(60)))

Table('t2', metadata,
Column('id', Integer, primary_key=True),
Column('t1_id', Integer, ForeignKey('t1.id')),
Column('name', String(60)))
metadata.create_all()


meta2 = MetaData(engine)

# reflect t2, t1 gets loaded too
t2 = Table('t2', meta2, autoload=True)
assert 't1' in meta2.tables
assert meta2.tables['t1'].c.id.primary_key

# reflect an entire DB
meta3 = MetaData(engine)
meta3.reflect()

print [t for t in meta3.tables]



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Don Dwiggins]

Post by Michael Bayer
hi don -
heres a script using SQLite which illustrates how the foreign key
reflection works. This same sort of thing should be working on MS-SQL
as well but I dont have access to an MS-SQL server here to test. If
the example below is not working for MS-SQL, please file a trac ticket
- we have some MS-SQL developers who can take a look.

OK, thanks. I tried it with MySQL, and it worked fine, so it does seem
to be mssql-specific. I've filed Ticket #979. (I can work around this
without too much trouble.)

Post by Michael Bayer
# reflect an entire DB
meta3 = MetaData(engine)
meta3.reflect()

This worked on a large mssql DB -- took several minutes to autoload 760
tables and views. I don't think I'll be doing that very often. 8^)
--
Don Dwiggins
Advanced Publishing Technology


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Rick Morrison]

Ugg, I am not a big table reflection fan....:-(

I am in the middle of readying a presentation, so unless someone else wants
to jump on this, I'll take a look at this later on this week, along with
integrating a reflection speed-up patch I remember from a while back.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Don Dwiggins]

Post by Rick Morrison
Ugg, I am not a big table reflection fan....:-(
I am in the middle of readying a presentation, so unless someone else
wants to jump on this, I'll take a look at this later on this week,
along with integrating a reflection speed-up patch I remember from a
while back.

Thanks. I'm willing to use something other than reflection, if there's
a good way I can avoid having to duplicate my schema (or significant
chunks of it) in SA declarations. I'd like to stick to the DRY
principle if at all possible. (I'm dealing with a legacy DB, and can't
really take SA declarations as "ground truth".)

OTOH, I have the schema scripted out in some SQL files (for
configuration management purposes). If it turns out to be worth the
effort, I can imagine writing a script to parse them to construct the
equivalent SA Table declarations and keep them up to date with schema
changes.

Any ideas along these lines?
--
Don Dwiggins
Advanced Publishing Technology


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Rick Morrison]

I actually keep most of my DB schemas in SA syntax these days: Python is
everywhere and SA does a great job of issuing DDL creates in the correct
sequence based on a dependancy sort that I would otherwise have to myself.

But I think a pretty useful tool, and fairly easy to create would be to have
SA generate the DDL to a file instead of issuing it to the server -- there's
your create script, and as a bonus, in the correct sequence. Given this,
then SA could then also act as a kind of reverse-engineering tool. Turn it
loose on a database with table reflection, and then after it has sucked in
all the table definitions, have it output an SQL DDL script. Most databases
can do that anyway today, but not always in the right dependancy order. And
with a bit of work making things like sequences and defaults more generic,
you would able to slurp in, say a Postgresql schema, and output MSSQL or
DB2, and so on.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Waldemar Osuch]

Post by Don Dwiggins

Post by Rick Morrison
Ugg, I am not a big table reflection fan....:-(
I am in the middle of readying a presentation, so unless someone else
wants to jump on this, I'll take a look at this later on this week,
along with integrating a reflection speed-up patch I remember from a
while back.

Thanks. I'm willing to use something other than reflection, if there's
a good way I can avoid having to duplicate my schema (or significant
chunks of it) in SA declarations. I'd like to stick to the DRY
principle if at all possible. (I'm dealing with a legacy DB, and can't
really take SA declarations as "ground truth".)

The approach I have taken is to to dump the legacy database
definitions into python module using autocode.
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode.
(I had to tweak the recipe slightly to work with Oracle)

The resulting code needed clean up but still it was a win - no more
waiting for the reflection to finish. Now if DB schema changes I have
to update my module but I do not mind, the changes are rare.
It is a legacy database after all :-).

Waldemar
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Rick Morrison]

Well here's a patch against trunk that should autoload referenced tables as
well. It's only lightly tested, and I'm out of town for a few days, so I
didn't want to commit it just yet. Give it a try if you'd like. Note that
the metadata.reflect() line in Mike's test script won't work in
case-sensitive databases in mssql, as it issues the fetch queries in
lowercase.

I'm going to pass on the reflection speed-up patch. It handles column loads
only, ignored case-sensitivity issues, and the FK check hits the information
schema views anyway, so I really doubt we'd see much of a boost.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Don Dwiggins]

Post by Rick Morrison
Well here's a patch against trunk that should autoload referenced tables
as well. It's only lightly tested, and I'm out of town for a few days,
so I didn't want to commit it just yet. Give it a try if you'd like.
Note that the metadata.reflect() line in Mike's test script won't work
in case-sensitive databases in mssql, as it issues the fetch queries in
lowercase.

Thanks much! I've tested it a bit more heavily, and it holds up nicely.

Post by Rick Morrison
I'm going to pass on the reflection speed-up patch. It handles column
loads only, ignored case-sensitivity issues, and the FK check hits the
information schema views anyway, so I really doubt we'd see much of a boost.

Speaking of speed-up, there's another point that you might want to
consider: in testing this, I reflected a table that had an "FK tree"
that wound up loading about 10 other tables -- this took a few seconds,
and in general I wouldn't need all of them. The DB I'm working with has
some other tables that could cascade into loading 40 or so.

I suspect it'd be useful to provide an option to make this process lazy
(in general, not just in mssql), so that tables that don't get
referenced don't slow down the process. (On the other hand, it might be
better for the app developer to just bite the bullet and use autocode on
the DB up front. I'm still feeling my way along here.)
--
Don Dwiggins
Advanced Publishing Technology


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

[Michael Bayer]

Post by Don Dwiggins
Speaking of speed-up, there's another point that you might want to
consider: in testing this, I reflected a table that had an "FK tree"
that wound up loading about 10 other tables -- this took a few
seconds,
and in general I wouldn't need all of them. The DB I'm working with has
some other tables that could cascade into loading 40 or so.
I suspect it'd be useful to provide an option to make this process lazy
(in general, not just in mssql), so that tables that don't get
referenced don't slow down the process. (On the other hand, it might be
better for the app developer to just bite the bullet and use
autocode on
the DB up front. I'm still feeling my way along here.)

ive considered this but then, its nice to know that once you've said
"autoload=True", the loading is entirely complete, and there will be
no suprises loads/errors at a random point later in time.
Additionally, we like to support a use case such as Table(...,
autoload=True, autoload_with=some_connection) - it would be very bad
to hold onto that connection beyond the scope of the call.

If you know which columns you want to deal with, Table supports an
include_columns argument which will set which cols get loaded upon
reflection.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---