mapping to a dictionary-based collection using a select

[Chris Withers]

Hi All,

I have a model roughly like this:

class Instrument(Base):
__tablename__ = 'instrument'
id = Column(String(10), primary_key=True)
name = Column(String(100))

class Symbol(Base):
__tablename__ = 'symbol'

instrument_id = Column(String(10),
ForeignKey('instrument.id', onupdate='cascade'),
primary_key=True)
instrument = relationship("Instrument")
name = Column(String(20), nullable=False)
period = Column(TSRANGE(), nullable=False, primary_key=True)

How can I set up a 'symbols' dictionary-based collection on Instrument
which uses the following query to populate:

session.query(Symbol).filter(
instrument_id==self.id,
Symbol.period.contains(func.now())
)

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Michael Bayer]

On Jun 17, 2013, at 2:36 AM, Chris Withers <ch...@simplistix.co.uk> wrote:

> Hi All,
>
> I have a model roughly like this:
>
> class Instrument(Base):
> __tablename__ = 'instrument'
> id = Column(String(10), primary_key=True)
> name = Column(String(100))
>
> class Symbol(Base):
> __tablename__ = 'symbol'
>
> instrument_id = Column(String(10),
> ForeignKey('instrument.id', onupdate='cascade'),
> primary_key=True)
> instrument = relationship("Instrument")
> name = Column(String(20), nullable=False)
> period = Column(TSRANGE(), nullable=False, primary_key=True)
>
> How can I set up a 'symbols' dictionary-based collection on Instrument which uses the following query to populate:
>
> session.query(Symbol).filter(
> instrument_id==self.id,
> Symbol.period.contains(func.now())
> )

well you're going to have relationship with "primaryjoin=and_(Symbol.instrument_id==Instrument.id, Symbol.period.contains(func.now()))" (what does contains(func.now()) do ? havent seen that one before). then collection_class=attribute_mapped_collection(<attribute you want to key on>).

[Chris Withers]

On 17/06/2013 17:35, Michael Bayer wrote:
>
> On Jun 17, 2013, at 2:36 AM, Chris Withers<ch...@simplistix.co.uk> wrote:
>
>> Hi All,
>>
>> I have a model roughly like this:
>>
>> class Instrument(Base):
>> __tablename__ = 'instrument'
>> id = Column(String(10), primary_key=True)
>> name = Column(String(100))
>>
>> class Symbol(Base):
>> __tablename__ = 'symbol'
>>
>> instrument_id = Column(String(10),
>> ForeignKey('instrument.id', onupdate='cascade'),
>> primary_key=True)
>> instrument = relationship("Instrument")
>> name = Column(String(20), nullable=False)
>> period = Column(TSRANGE(), nullable=False, primary_key=True)
>>
>> How can I set up a 'symbols' dictionary-based collection on Instrument which uses the following query to populate:
>>
>> session.query(Symbol).filter(
>> instrument_id==self.id,
>> Symbol.period.contains(func.now())
>> )
>
>
> well you're going to have relationship with "primaryjoin=and_(Symbol.instrument_id==Instrument.id, Symbol.period.contains(func.now()))" (what does contains(func.now()) do ?

# select now();
now
-------------------------------
2013-06-21 18:38:06.015838+01
(1 row)

> then collection_class=attribute_mapped_collection(<attribute you want to key on>).

Okay, and how does assignment to that dictionary work? I need to modify
the period of the old row, if there is one, and then insert the new row.

[Michael Bayer]

On Jun 21, 2013, at 1:39 PM, Chris Withers <ch...@simplistix.co.uk> wrote:

>>
>>
>> well you're going to have relationship with "primaryjoin=and_(Symbol.instrument_id==Instrument.id, Symbol.period.contains(func.now()))" (what does contains(func.now()) do ?
>
> # select now();
> now
> -------------------------------
> 2013-06-21 18:38:06.015838+01
> (1 row)
>
>
>> then collection_class=attribute_mapped_collection(<attribute you want to key on>).
>
> Okay, and how does assignment to that dictionary work? I need to modify the period of the old row, if there is one, and then insert the new row.

you just assign to the dictionary with the correct key and it will work.

if it doesnt work then send a simple test case and I'll make it work.