oh also your foreign_keys is wrong. PageData has a foreign key to Lang. So
foreign_keys=[lang_code], lose the "uselist" which is never necessary except
for one-to-one.
On Jul 16, 2012, at 5:18 AM, alex bodnaru wrote:
>
> hello michael, friends,
>
> here is my test case.
> thanks a lot for your consideraion. please take a look.
>
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, Integer, String, Unicode, ForeignKeyConstraint
> from sqlalchemy.orm import relation, backref, sessionmaker
> from sqlalchemy.event import listen
>
>
> engine = create_engine('sqlite:///:memory:', echo=True)
> Base = declarative_base()
>
> class Lang(Base):
> __tablename__ = 'lang'
> lang_code = Column(String(20), primary_key=True)
> lang_name = Column(Unicode(64))
>
> class PageData(Base):
> __tablename__ = 'pagedata'
> lang_code = Column(String(20), primary_key=True)
> lang = relation('Lang', backref='pages',
> primaryjoin=lang_code==Lang.lang_code,
> foreign_keys=[Lang.lang_code], uselist=False)
> data = Column(Unicode(64), nullable=True)
>
> def add_lang_fk(table, conn, **kw):
> if 'added_lang_fk' not in table.info:
> params = dict(ondelete='cascade')
> if conn.dialect.name != 'mssql':
> params['onupdate'] = 'restrict'
> table.append_constraint(
> ForeignKeyConstraint(
> ['lang_code'], [Lang.__tablename__ + '.lang_code'], **params))
> table.info['added_lang_fk'] = True
>
> listen(PageData.__table__, "before_create", add_lang_fk)
>
> Base.metadata.create_all(engine)
>
>
> Session = sessionmaker(bind=engine)
> session = Session()
>
> de = Lang(lang_code='de', lang_name='Deutsch')
> session.add(de)
>
> data = PageData(lang_code='de', data=u"Vielen Dank im Voraus") #this works
> data = PageData(lang=de, data=u"Vielen Dank im Voraus") #this fails
> session.add(data)
>
> session.flush()
>
>
> thanks in advance,
> alex
>
> On 07/15/2012 07:08 PM, Michael Bayer wrote:
>>
>> On Jul 15, 2012, at 5:53 AM, alex bodnaru wrote:
>>
>>>
>>> hello michael, friends,
>>>
>>> On 07/11/2012 10:31 AM, alex bodnaru wrote:
>>>>
>>>> hello michael,
>>>>
>>>> now it works. i also had to add uselist=False.
>>>>
>>>> i tried it the longest way possible, by adding a Pool first_connect
>>>> listener,
>>>> but this was not really needed. just the uselist.
>>>>
>>>> thanks a lot,
>>>> alex
>>>>
>>> sorry, not yet:
>>>
>>> the relationship should also allow assignment like this:
>>>
>>> class Lang(DeclarativeBase):
>>> lang_code = Column(String(20), primary_key=True)
>>> lang_name = Column(String(20))
>>>
>>> class PageData(DeclarativeBase):
>>> lang_code = Column(String(20), primary_key=True)
>>> lang = relation('Lang', backref='pages',
>>> primaryjoin=lang_code==Lang.lang_code, foreign_keys=[Lang.lang_code],
>>> uselist=False)
>>>
>>> the PageData.lang_code foreign key is being added in an event on before
>>> create.
>>>
>>> before delaying creation of the foreign key, i could do like this:
>>>
>>> p = PageData()
>>> p.lang = Lang.get('en')
>>>
>>> and p.lang_code got assigned. why isn't lang_code being assigned now
>>> anymore?
>>
>> it would imply the relationship is not working at all.
>>
>> like before:
>>
>>>>> can you just throw these two classes, the event, and some imports into a
>>>>> file for me ? I can just run it.
>>
>> much quicker than lots of back and forth.
>>
>>
>>
>>
>>
>>>
>>> thanks in advance,
>>> alex
>>>> On 07/09/2012 04:25 PM, Michael Bayer wrote:
>>>>>
>>>>> On Jul 9, 2012, at 4:48 AM, alex bodnaru wrote:
>>>>>
>>>>>>
>>>>>> hello michael, friends,
>>>>>>
>>>>>> after successfuly fixing the ddl by the append_constraint event, the
>>>>>> relations
>>>>>> that needed the said foreign keys remained orphan, asking for a
>>>>>> foreign_keys
>>>>>> argument and failing to load the remote table:
>>>>>>
>>>>>> class Lang(DeclarativeBase):
>>>>>> lang_code = Column(String(20), primary_key=True)
>>>>>> lang_name = Column(String(20))
>>>>>>
>>>>>> class PageData(DeclarativeBase):
>>>>>> lang_code = Column(String(20), primary_key=True) # this foreign key is
>>>>>> being
>>>>>> successfully appended on before_create.
>>>>>> lang = relation('Lang', backref='pages',
>>>>>> primaryjoin=lang_code==Lang.lang_code) #this relationship won't work,
>>>>>> since at
>>>>>> the moment the class is being made, the foreign key is not there yet.
>>>>>>
>>>>>> the foreign_keys=Lang.lang_code arg does calm the exception, but doesn't
>>>>>> do the
>>>>>> work.
>>>>>> could i add the relationship to the mapper on the same event?
>>>>>
>>>>> I would think "foreign_keys" should fix the problem totally, what do you
>>>>> mean "doesn't do the work"? I'd have to work up a test case, can you
>>>>> just throw these two classes, the event, and some imports into a file for
>>>>> me ? I can just run it.
>>>>>
>>>> well, almost totally ;)
>>>> it also needed uselist=False.
>>>>
>>>>>
>>>>>>
>>>>>> thank in advance,
>>>>>> alex
>>>>>>
>>>>>> On 07/07/2012 05:13 PM, Michael Bayer wrote:
>>>>>>> sure engine and connection have .dialect.name. Foreign key
>>>>>>> constraints don't matter on SQLite unless you've actually enabled them,
>>>>>>> which is rare. I'd still use an event though so at least the behavior
>>>>>>> is transparent.
>>>>>>>
>>>>>>> @event.listens_for(my_table, "before_create")
>>>>>>> def add_fk(table, conn, **kw):
>>>>>>> if conn.dialect.name != 'mssql':
>>>>>>> table.append_constraint(ForeignKeyConstraint(...))
>>>>>>>
>>>>>>> tricky though to modify the table metadata within a "create" event in
>>>>>>> the case that the table is created multiple times in an app. you can
>>>>>>> put a flag in table.info, like "table.info['added_the_fk'] = True", to
>>>>>>> keep track of things.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> hello mike and thanks for your answer.
>>>>>>>>
>>>>>>>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go
>>>>>>>> the alter
>>>>>>>> way? in this case, it will be ignored by the sqlite dialect.
>>>>>>>>
>>>>>>>> what i was looking for was more like:
>>>>>>>>
>>>>>>>> from sqlalchemy... import get_dialect
>>>>>>>>
>>>>>>>> ....
>>>>>>>> fk_parms = dict(.....)
>>>>>>>> if get_dialect() != 'mssql':
>>>>>>>> fk_parms.update(onupdate='restrict')
>>>>>>>> fk = ForeignKey(**fk_parms)
>>>>>>>>
>>>>>>>> would the dialect be accessible from the engine, metadata etc?
>>>>>>>>
>>>>>>>> thanks in advance,
>>>>>>>> alex
>>>>>>>>
>>>>>>>>
>>>>>>>> On 07/06/2012 11:39 PM, Michael Bayer wrote:
>>>>>>>>> you'd use ForeignKeyConstraint along with the AddConstraint
>>>>>>>>> directive, and limit it per-dialect using create/drop events as
>>>>>>>>> documented at
>>>>>>>>> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>>>>>>>>> .
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> hello friends,
>>>>>>>>>>
>>>>>>>>>> i need to define a foreign key differently for different dialects:
>>>>>>>>>> ondelete='restrict' for most engines, but nothing (implied and not
>>>>>>>>>> recognized)
>>>>>>>>>> for mssql.
>>>>>>>>>>
>>>>>>>>>> could you help?
>>>>>>>>>>
>>>>>>>>>> thanks in advance,
>>>>>>>>>> alex
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>>>> Groups "sqlalchemy" group.
>>>>>>>>>> To post to this group, send email to sqlalchemy@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.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>> Groups "sqlalchemy" group.
>>>>>>>> To post to this group, send email to sqlalchemy@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.
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "sqlalchemy" group.
>>>>>> To post to this group, send email to sqlalchemy@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.
>>>>>>
>>>>>
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalchemy@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.
>>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
