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.