This is called a "vertical table format" and we have a few recipes that
illustrate this, including the examples introduced at
http://www.sqlalchemy.org/docs/orm/examples.html#vertical-attribute-mapping as
well as http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap . The
basic premise is to map a class to "users_table" that represents just a
key/value pair, then to use them to construct a dictionary. Your specific
layout is a little tricky in that there's no "parent" table - so queries for a
specific "username" will need a little bit of a facade (like,
get_user(username) would query a set of UserKeyValue objects, make a dict-like
interface out of them, return it - the dict would then alter/delete/create
UserKeyValue objects based on __setitem__, __delitem__, etc.).
Another option is if you had only a limited set of "attr", you could make a
database view which represents a "row" for a specific username - i.e. "select
u1a.val as u1, u2a.val as u2, u3a.val as u3 from users as u1a join users as u2a
on u1a.username=u2a.username join users as u3a on u2a.username=u3a.username
where u1a.attr='attr1' and u2a.attr='attr2' and u3a.attr='attr3'". Then map
to that. The join approach doesn't really scale to more than a small handful
of attributes though.
On May 30, 2011, at 2:56 PM, Teemu Yli-Elsilä wrote:
> Hello,
>
>
> I am trying to map a legacy schema. There are a few tables that I cannot
> figure out how to map. Basically the rows are key-value pairs that
> should really be columns in the table (see code examples below). The
> rows of the "ideal" table would match the distinct values in the
> physical table's first column (username), which would be natural to use
> as the primary key.
>
> I would like to know what needs to be done in order to make this kind of
> mapping work, so that object add and changes to session would still do
> the right thing when flush()ed etc. So far I haven't been able to figure
> out exactly how that could be done from the docs, and I am not familiar
> enough with the inner workings of SQLAlchemy.
>
> Any advice would be greatly appreciated.
>
>
> # Real table. Contains rows like:
> # 'jsmith', 'realname', 'John Smith'
> # 'jsmith', 'address', 'Park Avenue'
> # 'jsmith', 'phone', '555-9876'
> # 'tkelly', 'realname', 'Tim Kelly'
> # etc.
> users_table = Table('users', metadata,
> Column('username', Unicode, primary_key=True),
> Column('attr', Unicode, primary_key=True),
> Column('val', Unicode)
> )
>
> # Ideal - what I'd like the mapping to "act like"
> users_table = Table('users', metadata,
> Column('username', Unicode),
> Column('realname', Unicode),
> Column('address', Unicode)
> Column('phone', Unicode)
> # etc...
> )
>
>
> Thanks,
>
> --
> Teemu Yli-Elsilä
>
> --
> 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.
>
--
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.
