""" Hello, I've been trying to map two tables that have 25 columns each in addition to keys. The parent table's (descriptions) column values represent the key in a dictionary and the child table's (values) column values represent the values of the dictionary.
i.e.: table 'descriptions': id SERIAL description1 Text description2 Text ... description25 Text table 'values': id SERIAL descriptions_id INTEGER NOT NULL FOREIGN KEY descriptions.id value1 Text value2 Text ... value25 Text I wanted to map the join of the descriptions table and the values table to a single class and make two composite properties that would represent the 50 columns. Below I stared to do a prototype of this and ran into a problem with composite properties not working on mapped join (select alias). I never got around to making the dictionary I planned. And I know that the design of the descriptions table and values table is not the best but it's what I have to work with at this point. """ from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, mapper, relationship, composite from sqlalchemy.schema import Table, Column, MetaData, ForeignKey from sqlalchemy.sql import select from sqlalchemy.types import Integer, Text engine = create_engine('sqlite:///') session = sessionmaker(bind=engine, autocommit=True)() metadata = MetaData(engine) # Tables descriptions_table = Table('descriptions', metadata, Column('id', Integer, primary_key=True), Column('d1', Text), Column('d2', Text), ) values_table = Table('values', metadata, Column('id', Integer, primary_key=True), Column('description_id', Integer, ForeignKey('descriptions.id'), nullable=False), Column('v1', Text), Column('v2', Text), ) desc_values = select( [values_table, descriptions_table.c.d1, descriptions_table.c.d2], descriptions_table.c.id == values_table.c.description_id ).alias('descriptions_values') # Classes class Descriptions(object): pass class Values(object): pass class CustomValues(list): def __init__(self, *args): self.extend(args) def __composite_values__(self): return self # Mappers mapper(Descriptions, descriptions_table, properties={ 'values': relationship(Values, lazy='dynamic'), 'custom_descriptions': composite(CustomValues, descriptions_table.c.d1, descriptions_table.c.d2), }) mapper(Values, desc_values, properties={ 'custom_descriptions': composite(CustomValues, desc_values.c.v1, desc_values.c.v2), }) # Testing metadata.create_all() engine.echo = True descriptions = Descriptions() descriptions.custom_descriptions = CustomValues('Color', 'Number') values1 = Values() values1.custom_values = CustomValues('Red', '5') values2 = Values() values2.custom_values = CustomValues('Blue', '1') descriptions.values.append(values1) descriptions.values.append(values2) session.add(descriptions) session.flush() # Output """ 2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690 BEGIN (implicit) 2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690 INSERT INTO descriptions (d1, d2) VALUES (?, ?) 2010-12-07 13:32:04,933 INFO sqlalchemy.engine.base.Engine.0x...e690 ('Color', 'Number') 2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690 INSERT INTO "values" (description_id, v1, v2) VALUES (?, ?, ?) 2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690 (1, None, None) 2010-12-07 13:32:04,934 INFO sqlalchemy.engine.base.Engine.0x...e690 INSERT INTO "values" (description_id, v1, v2) VALUES (?, ?, ?) 2010-12-07 13:32:04,935 INFO sqlalchemy.engine.base.Engine.0x...e690 (1, None, None) 2010-12-07 13:32:04,935 INFO sqlalchemy.engine.base.Engine.0x...e690 COMMIT >From this ouput we can see that v1 and v2 are not being set, they are being left at None, but descriptions.custom_descriptions IS being set properly. I'm hoping it's just something I missed. """ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.