I m still having problems with this vertical partitioning malarky (I
think the previous problem was due to a redundant copy of one of the
tables existing in the wrong database) , and it seems to currently be
when I have a secondary table involved between the two vertical
partitions....
engine1 = create_engine(database_string)
engine2 = create_engine(database_string2)
Base = declarative_base()
metadata = Base.metadata
celllines_table = Table("celllines", metadata,
Column("cellline_id", String(14), primary_key=True),
Column("name", Text)
)
celllines_to_species_table = Table("cll2spe_table", metadata,
Column("cellline_id", String(14),
ForeignKey("celllines.cellline_id")),
Column("taxa_id", Integer,
ForeignKey("taxonomy_species.taxa_id")),
)
species_table = Table('taxonomy_species', metadata,
Column('taxa_id', Integer, primary_key=True),
Column('rank', Text)
)
celllines_to_species_table.create(bind = engine1)
species_table.create(bind=engine2)
celllines_table.create(bind = engine1)
mapper(Taxa, species_table)
mapper(CellLine, celllines_table, properties = {
"species":relation(Taxa, secondary=celllines_to_species_table)
})
Session = sessionmaker(twophase=True)
Session.configure(binds={Taxa:engine2, CellLine:engine1})
So when I try to access the species, the following queries are issued ....
2009-08-18 13:49:51,150 INFO sqlalchemy.engine.base.Engine.0x...77d0 XA BEGIN %s
2009-08-18 13:49:51,151 INFO sqlalchemy.engine.base.Engine.0x...77d0
['_sa_0590dc58dd5fe4e11919839594ae35f3']
2009-08-18 13:49:51,152 INFO sqlalchemy.engine.base.Engine.0x...77d0
SELECT celllines.cellline_id AS celllines_cellline_id, celllines.name
AS celllines_name
FROM celllines
WHERE celllines.cellline_id = %s
LIMIT 0, 2000
2009-08-18 13:49:51,152 INFO sqlalchemy.engine.base.Engine.0x...77d0
['CellLine_5162']
CellLine_51622009-08-18 13:49:51,246 INFO
sqlalchemy.engine.base.Engine.0x...7c10 XA BEGIN %s
2009-08-18 13:49:51,246 INFO sqlalchemy.engine.base.Engine.0x...7c10
['_sa_79f9588fb7d56ddea80688cc02faa7d0']
2009-08-18 13:49:51,249 INFO sqlalchemy.engine.base.Engine.0x...7c10
SELECT taxonomy_species.taxa_id AS taxonomy_species_taxa_id,
taxonomy_species.rank AS taxonomy_species_rank
FROM taxonomy_species, cll2spe_table
WHERE %s = cll2spe_table.cellline_id AND taxonomy_species.taxa_id =
cll2spe_table.taxa_id
2009-08-18 13:49:51,249 INFO sqlalchemy.engine.base.Engine.0x...7c10
['CellLine_5162']
[]
With nothing in the species......I can't understand whats going on ...
my only thought is that cll2spe_table isn't being found correctly. Is
there something going wrong. I am using 0.5.3 of SQLAlchemy.
Any help greatly appreciated yet again,
Nathan
PS I continued the old thread as its still regarding the same
topic...I hope this is ok.
2009/8/14 Michael Bayer <mike...@zzzcomputing.com>:
>
> Nathan Harmston wrote:
>>
>> HI,
>>
>> I m currently trying to vertical partition my database and I have
>> split it into two groups of tables (say { documents, table1, table2 }
>> and { table3 }.
>> So I have
>>
>> s_engine = create_engine(t_database_string)#, echo=True)
>> #echo="debug")#, echo=True)
>> t_engine = create_engine(t_database_string)
>>
>> Base = declarative_base()
>>
>> metadata = Base.metadata
>>
>> documents = Table("documents', metadata,
>> Column("document_id", Integer, primary_key = True),
>> }
>>
>> table1 = Table("table1", metadata,
>> Column("interval_id", Integer, primary_key=True),
>> Column("document_id", Integer,
>> ForeignKey("documents.document_id")),
>> )
>>
>> table3 = Table("table3", metadata,
>> Column("foo_id", Integer, primary_key = True),
>> )
>>
>> table2 = Table("table2", metadata,
>> Column("id", Integer, primary_key = True),
>> Column("interval_id", Integer, ForeignKey("table1.interval__id"))
>> Column("foo_id", Integer, ForeignKey("table3.foo_id"))
>> )
>>
>> secondary = table1.join(table2,
>> onclause=table1.c.interval_id==table2.c.interval_id).alias("spe_join")
>>
>> table3.create(bind=t_engine)
>> ........ etc
>>
>>
>>
>> mapper(Foo, table3)
>> mapper(Document, documents_table, properties={
>> "foos":relation(Foo,
>> primaryjoin=documents_table.c.document_id ==
>> secondary.c.intervals_document_id,
>>
>> secondaryjoin=secondary.c.table2_foo_id == table3.c.foo_id,
>> viewonly =
>> True,
>>
>> secondary=secondary)
>
>> })
>> session = sessionmaker(binds={Document:s_engine, Foo:t_engine})
>>
>> however what happens is that it complains about the foos relation in
>> that it says that
>> sqlalchemy.exc.ArgumentError: Could not determine relation direction
>> for secondaryjoin condition 'spe_join.table2_foo_id = foo.foo_id', on
>> relation Document.foos. Specify the foreign_keys argument to indicate
>> which columns on the relation are foreign.
>>
>> When I had all the tables in the same database I had no problems
>> involving having to specify foreign keys. What does SQLalchemy do it
>> the background here? Is my problem that it doesnt know that table3 is
>> accessed via the t_engine. How can I make it explicitly know this
>> here? I have tried using different metadata bindings but to no good.
>> Any pointers/help would be greatly appreciated?
>
> once I fix all the typos in your example it works fine in the latest
> release of SQLAlchemy. in theory its looking to know that both columns
> in "secondary" are foreign and wants you to specify that...but this should
> not be necessary based on the example.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
