Hello.
I have 2 tables with one-to-one relation, and I got some unexpected
behaviour from sqlalchemy.
In attach there is demonstration script and its log.
If I try to add object into table "right" for already existing key I
expect error on DB level, telling about violation of unique
constraint, but sqlalchemy before try to insert new row, set relation
key to NULL for already existing object...
Is it right?
How can I avoid such behavior?
PS sqlalchemy version is 0.5.8
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
INFO:sqlalchemy.engine.base.Engine.0x...deac:PRAGMA table_info("left")
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:PRAGMA table_info("right")
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:
CREATE TABLE "left" (
idnr INTEGER NOT NULL,
data VARCHAR(32) NOT NULL,
PRIMARY KEY (idnr)
)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:
CREATE TABLE "right" (
idnr INTEGER NOT NULL,
left_idnr INTEGER,
data VARCHAR(32) NOT NULL,
PRIMARY KEY (idnr),
FOREIGN KEY(left_idnr) REFERENCES "left" (idnr) ON DELETE CASCADE ON
UPDATE CASCADE,
UNIQUE (left_idnr)
)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO "left" (data) VALUES
(?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:['abc']
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO "right" (left_idnr,
data) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1, 'cde']
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT "left".idnr AS left_idnr,
"left".data AS left_data
FROM "left"
WHERE "left".idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('left_idnr', 'left_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, u'abc')
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT "left".idnr AS left_idnr,
"left".data AS left_data
FROM "left"
WHERE "left".idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('left_idnr', 'left_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, u'abc')
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT "right".idnr AS right_idnr,
"right".left_idnr AS right_left_idnr, "right".data AS right_data
FROM "right"
WHERE ? = "right".left_idnr
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('right_idnr',
'right_left_idnr', 'right_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, 1, u'cde')
INFO:sqlalchemy.engine.base.Engine.0x...deac:UPDATE "right" SET left_idnr=?
WHERE "right".idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[None, 1]
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO "right" (left_idnr,
data) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1, '012']
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
#!/usr/bin/env python
#-*- coding:utf-8 -*-
#import pdb
import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint
# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
decl = declarative_base()
class left(decl):
__tablename__ = 'left'
idnr = Column(Integer, primary_key=True)
data = Column(String(32), nullable=False)
rel = orm.relation('right', uselist=False,
backref=orm.backref('left'),
passive_updates=True, passive_deletes=True, lazy=True, cascade='all')
class right(decl):
__tablename__ = 'right'
idnr = Column(Integer, primary_key=True)
left_idnr = Column(Integer, ForeignKey(left.idnr, onupdate='CASCADE', ondelete='CASCADE'), unique=True)
data = Column(String(32), nullable=False)
def main():
logging.basicConfig()
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)
eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)
decl.metadata.create_all(eng)
ses = sm()
l = left()
l.data = 'abc'
ses.add(l)
r = right()
r.left = l
r.data = 'cde'
ses.add(r)
ses.commit()
l_idnr = l.idnr
ses.close()
ses = sm()
l = ses.query(left).get(l_idnr)
r2 = right()
r2.left = l
r2.data = '012'
ses.flush()
ses.commit()
if __name__ == '__main__':
main()
