[sqlalchemy] Re: vertical partitioning
On Wed, Sep 30, 2009 at 1:04 PM, Adrian von Bidder avbid...@fortytwo.chwrote: Heyho! Is there a tutorial on vertical partitioning? I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? Obviously I can trivially do the relation stuff to get entry.flags.myflag, but nicer would be having the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Thanks in advance. -- vbi -- The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offence. -- E. Dijkstra, 1975 Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-properties If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Kevin Horn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: vertical partitioning
On Wednesday 30 September 2009 21.58:55 Kevin Horn wrote: I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? [...] the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-propert ies If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Hmm. Mapping an outer join to my actual model class would be part of it. Not sure how to easily assign default values for the part of the row not backed by a real row on the EntryFlags side (they shouldn't just be NULL; default values on the EntryFlags table wouldn't be of much use since they'd be applied only when the row would be generated and not for access to the outer join, afaict) (And as an aside: this has become a a bit an academic question for now, I've thought about more about what I'm trying to do and redesigned the schema.) cheers -- vbi Kevin Horn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Do you understand now why we attacked Iraq? Because war is good for the economy, which means war is good for America. Also, since God is on America's side, anyone who opposes war is a godless un-American Communist. -- excerpt from one of those 'joke' mails floating around. signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Vertical partitioning, mappers and foreign keys
not sure what the exact setup is here, but if you're trying to load
from A-B across multiple tables that cross from one database to
another, you'll have to roll that by hand i.e. using a plain Python
method on your parent class; its not possible to do a join across two
different databases and relation() always issues a single query
targeted to the remote mapper's database.
On Aug 18, 2009, at 8:56 AM, Nathan Harmston wrote:
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,
[sqlalchemy] Re: Vertical partitioning, mappers and foreign keys
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
[sqlalchemy] Re: Vertical partitioning, mappers and foreign keys
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
-~--~~~~--~~--~--~---
