are you thinking of this ?
histogram_table_mapper = mapper(histogram, histogram_table) document_table_mapper = mapper(document, document_table, properties={ 'histogram':relation(histogram_table_mapper) })
if you want just one histogram per document, then its like this:
histogram_table_mapper = mapper(histogram, histogram_table) document_table_mapper = mapper(document, document_table, properties={ 'histogram':relation(histogram_table_mapper, uselist=False) })
Did my earlier note address your question? I wrote:
I am trying to figure out how to use SQLalchemy for a composite class that
includes lists in its fields (most importantly of strings). I suspect two or
three obvious ways of doing it:
1: Do what is usually the wrong way in Python, making a relatively low level
specification of "This object field is stored in this database." There is
one table for the "document" class, and a separate table for the "histogram"
class which is a field in the "document" class.
2: Find out how to solve it the right way, and use a mapper (I'm not clear on
how) so that the "document" table seamlessly handles not only the strings,
but i.e. the histogram (the histogram being a Python class).
(3: Some kind of hybrid?)
What I have now is an object that I'd like to be in the database but not in
memory unless it is being used, which contains (besides Strings and Floats) a
histogram, which can be my choice of a wrapper for a hash mapping words to the
positive number of occurrences, or in finalized form a tuple of pairs of (word,
frequency).
Does the mapper ignore fields that aren't specified as part of the schema? If
so, can I make a function to enter an object into memory, which enters all the
String/Float fields automatically, and then does extra work and translates the
word histogram into its own table?
What I have now are:
document_table = sqlalchemy.Table("document", \
sqlalchemy.Column("file_contents", sqlalchemy.String), \
sqlalchemy.Column("filename", sqlalchemy.String), \
sqlalchemy.Column("html_name", sqlalchemy.String, \
primary_key = True), \
sqlalchemy.Column("last_modified", sqlalchemy.Float(20)), \
sqlalchemy.Column("last_viewed", sqlalchemy.Float(20)), \
sqlalchemy.Column("permanent_ID", sqlalchemy.String), \
sqlalchemy.Column("relative_filename", sqlalchemy.String), \
sqlalchemy.Column("root", sqlalchemy.String), \
sqlalchemy.Column("section", sqlalchemy.String), \
sqlalchemy.Column("text", sqlalchemy.String), \
sqlalchemy.Column("title", sqlalchemy.String), \
sqlalchemy.Column("tokens", sqlalchemy.String), \
sqlalchemy.Column("version", sqlalchemy.Integer), \
)
document_table_mapper = mapper(document, document_table)
histogram_table = sqlalchemy.Table("histogram", \
sqlalchemy.Column("html_name", sqlalchemy.String, \
foreignkey("document.html_name")), \
sqlalchemy.Column("word", sqlalchemy.String), \
sqlalchemy.Column("count", sqlalchemy.Integer),
)
histogram_table_mapper = mapper(histogram, histogram_table)
How do I say "Make a document_table row for this document and for each word in
the histogram make a histogram_table row"?
--
Without loading all the results in memory, how do I get a cursorlike object for (if this SQL is wrong, PLEASE correct me) "SELECT document_table.* from document_table, histogram_table WHERE document_table.html_name = histogram_table.html_name AND histogram_table.word = 'xyz' SORT BY histogram.last_viewed DESC;" that will preferably load objects and not just rows?
On 3/28/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
can you show us an example of a particular object relationship and the
tables you want it to map to ? the simplest of course is one object per
table.
Jonathan Hayward http://JonathansCorner.com wrote:
> I am trying to retrofit an existing application to use SQLalchemy; it uses
> its own database which is painfully slow.
>
> I am presently trying to get lists working correctly; objects have lists
> of
> strings/other objects, and so I am trying to make a separate table for
> each
> list item, so that a row in the secondary table which has the foreign
> primary key of the object that has the list item, and the string/primary
> key
> of what it is that is listed.
>
> Question: once I have done that, how do I set up a function to translate
> from the existing object to the table row? Or perhaps a better question to
> ask is, should I be trying to create a micromanaging function that will
> step
> by step convert my object to a number of database tables, or am I missing
> a
> way that the Mapper should be able to handle that?
>
> My (pseudo)-code for now is (corrections welcome):
>
> document_table = sqlalchemy.Table("document", \
> sqlalchemy.Column("file_contents", sqlalchemy.String), \
> sqlalchemy.Column("filename", sqlalchemy.String), \
> sqlalchemy.Column("html_name", sqlalchemy.String, \
> primary_key = True), \
> sqlalchemy.Column("last_modified", sqlalchemy.Datetime), \
> sqlalchemy.Column("last_viewed", sqlalchemy.Datetime ), \
> sqlalchemy.Column("permanent_ID", sqlalchemy.String), \
> sqlalchemy.Column("relative_filename", sqlalchemy.String), \
> sqlalchemy.Column("root", sqlalchemy.String), \
> sqlalchemy.Column("section", sqlalchemy.String), \
> sqlalchemy.Column("text", sqlalchemy.String), \
> sqlalchemy.Column("title", sqlalchemy.String), \
> sqlalchemy.Column("tokens", sqlalchemy.String), \
> sqlalchemy.Column("version", sqlalchemy.Integer), \
> )
> histogram_table = sqlalchemy.Table("histogram", \
> sqlalchemy.Column("html_name", sqlalchemy.String, \
> foreignkey("document.html_name")), \
> sqlalchemy.Column ("word", sqlalchemy.String), \
> sqlalchemy.Column("count", sqlalchemy.Integer),
> )
> histogram_total_table = sqlalchemy.Table("histogram_total", \
> sqlalchemy.Column("html_name", sqlalchemy.String, \
> foreignkey("document.html_name")), \
> sqlalchemy.Column("count", sqlalchemy.Integer ),
> )
> aliases_table = sqlalchemy.Table("aliases", \
> sqlalchemy.Column("old", sqlalchemy.String), \
> sqlalchemy.Column("new", sqlalchemy.String) \
> )
> # identifier is primary key
> section_table = sqlalchemy.Table("section", \
> #sqlalchemy.Column("documents", sqlalchemy.String ), \
> sqlalchemy.Column("html_name", sqlalchemy.String, \
> primary_key = True), \
> sqlalchemy.Column("parent", sqlalchemy.String), \
> sqlalchemy.Column("root", sqlalchemy.String),
> #sqlalchemy.Column("subsections",
> LIST_OF_IDENTIFIER_PRIMARY_KEYS), \
> #sqlalchemy.Column("text_patterns_to_accept", LIST_OF_STRINGS),
> \
> #sqlalchemy.Column("text_patterns_to_deny", LIST_OF_STRINGS), \
> sqlalchemy.Column("url_prefix", sqlalchemy.String), \
> sqlalchemy.Column("url_root", sqlalchemy.String) \
> )
> section_table_documents("section_table_documents", \
> sqlalchemy.Column("section_html_name", sqlalchemy.String), \
> sqlalchemy.Column("document_html_name", sqlalchemy.String) \
> )
> section_table_subsections("section_table_subsections", \
> sqlalchemy.Column("section_html_name", sqlalchemy.String), \
> sqlalchemy.Column("subsection_html_name", sqlalchemy.String) \
> )
> section_table_text_patterns_to_accept( \
> "section_table_text_patterns_to_accept", \
> sqlalchemy.Column("section_html_name", sqlalchemy.String), \
> sqlalchemy.Column("text_pattern_to_accept", sqlalchemy.String) \
> )
> section_table_text_patterns_to_deny( \
> "section_table_text_patterns_to_deny", \
> sqlalchemy.Column("section_html_name", sqlalchemy.String), \
> sqlalchemy.Column("text_pattern_to_deny", sqlalchemy.String) \
> )
>
>
> --
> ++ Jonathan Hayward, [EMAIL PROTECTED]
> ** To see an award-winning website with stories, essays, artwork,
> ** games, and a four-dimensional maze, why not visit my home page?
> ** All of this is waiting for you at http://JonathansCorner.com
>
> ** If you'd like a Google Mail (gmail.com) account, please tell me!
>
--
++ Jonathan Hayward, [EMAIL PROTECTED]
** To see an award-winning website with stories, essays, artwork,
** games, and a four-dimensional maze, why not visit my home page?
** All of this is waiting for you at http://JonathansCorner.com
** If you'd like a Google Mail ( gmail.com) account, please tell me!
|
