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()