"""
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 [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.
