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.
