thank you very much michael.
both ways worked like a charm.
i have implemented the other way, though this is better and more general: any
constraint with onupdate or ondelete attributes should have these attributes set
to none if the dialect name is mssql.
thanks a lot,
alex
On 07/16/2012 05:10 PM, Michael Bayer wrote:
> thanks - though one thought I had is, why not just modify an existing
> ForeignKeyConstraint.onupdate directly in the event, instead of trying to add
> the constraint later and confusing the ORM. you wouldn't need the
> relationship arguments then.
>
> the constraints for the table are in table.constraints, its a set and you'd
> iterate through to find the ForeignKeyConstraint with the name you're looking
> for.
>
> if you can try that out let me know.
>
>
>
>
> 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.
