Hi all,
I'm having a problem with a non primary key sequence involved in a
relation. The failing environment is sqlalchemy 0.5.5 on ms sql server
2005 with pymssql 0.8.
I have a "parent" table with its own pk and a sequence field and a
"child" table with a pk composed by the parent's sequence (NOT the pk)
and another unrelated column. This is a test:
from sqlalchemy import *
from sqlalchemy import orm
md = MetaData()
parent = Table('parent', md,
Column('code', String(10), primary_key=True),
Column('id', Integer, Sequence('parent_seq'),
unique=True, nullable=False),
Column('field', String(254)))
child = Table('child', md,
Column('parent_id', Integer, ForeignKey(parent.c.id),
primary_key=True),
Column('akey', String(10), primary_key=True),
Column('test', String(254)))
class Parent(object):
pass
class Child(object):
def __init__(self, akey, test):
self.akey = akey
self.test = test
orm.mapper(Child, child)
orm.mapper(Parent, parent,
properties=dict(
children=orm.relation(Child, cascade='all')))
Session = orm.sessionmaker(autoflush=True, autocommit=False)
def run(conn):
md.create_all(bind=conn)
try:
c1 = Child('abcd', 'Test1')
c2 = Child('efgh', 'Test2')
p = Parent()
p.code = 'code'
p.field = 'test'
p.children.append(c1)
p.children.append(c2)
s = Session(bind=conn)
s.add(p)
s.flush()
assert c1.parent_id == c2.parent_id == p.id
print p.id
finally:
md.drop_all(bind=conn)
e = create_engine("postgres://localhost")
conn = e.connect()
try:
run(conn)
finally:
conn.close()
e = create_engine("mssql://XXXX")
conn = e.connect()
try:
run(conn)
finally:
conn.close()
When it runs on postgresql, everything works fine, while on mssql I
get:
sqlalchemy.exc.DatabaseError: (DatabaseError) internal error: SQL
Server message 515, severity 16, state 2, line 1:
Cannot insert the value NULL into column 'parent_id', table
'XXXX.child'; column does not allow nulls. INSERT fails.
DB-Lib error message 515, severity 16:
General SQL Server error: Check messages from the SQL Server
SQL Server message 3621, severity 0, state 0, line 1:
The statement has been terminated.
'INSERT INTO child (akey, test) VALUES (%(akey)s, %(test)s)' {'test':
'Test1', 'akey': 'abcd'}
It seems that the child instances are not updated with the right
sequence number. Moreover, if I turn the parent's "id" column into the
primary key, the error disappears.
Is it a known issue? Can i fix it in some way (except for normalize
the db schema)?
Thanks
Antonio
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
