[sqlalchemy] SQLAlchemy's merge doesn't fill foreign keys with ids of new objects in relationship

[Юрий Пайков]

My question is when I have in a session a newly created object(doesn't have
primary key yet, but will obtain it upon flush) and I merge to that session
another object referring to the first one by relationship (*b* in the
example) SQLAlchemy doesn't populate latter object with the primary key
from the former. Instead it just generate next value from the sequence. Why
is it the case ?

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker



engine = create_engine("postgresql+psycopg2://psql_admin:***@localhost/fm")


from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Integer, ForeignKey, VARCHAR, TEXT, Boolean, DateTimefrom sqlalchemy.orm import relationshipfrom sqlalchemy.sql.schema import Column
class B(Base):
__tablename__='B'
id_=Column(Integer, primary_key=True)
data = Column(VARCHAR(30))
class Rel(Base):
__tablename__='Rel'
id_a=Column(Integer, primary_key=True)
id_b=Column(Integer, ForeignKey('B.id_'), primary_key=True)
b = relationship(B)
rel_data=Column(VARCHAR(30))

Session = sessionmaker(bind=engine)
session = Session()Base.metadata.create_all(engine, checkfirst=True)


first_b=B(id_=1, data='ololo')
session.add(first_b)
session.commit()

session.add(Rel(id_a=800,id_b=1, rel_data='first relation data'))

second_b=B(data='foooo')
session.add(second_b)
x=session.merge(Rel(id_a=800, rel_data="second", b=second_b))
session.commit()

Here I have an error

IntegrityError: (raised as a result of Query-invoked autoflush; consider
using a session.no_autoflush block if this flush is occuring prematurely)
(IntegrityError) duplicate key value violates unique constraint "B_pkey"
DETAIL: Key (id_)=(1) already exists. 'INSERT INTO "B" (data) VALUES
(%(data)s) RETURNING "B".id_' {'data': 'foooo'}

[Mike Bayer]

Post by Юрий Пайков
My question is when I have in a session a newly created object(doesn't
have primary key yet, but will obtain it upon flush) and I merge to
that session another object referring to the first one by relationship
(/b/ in the example) SQLAlchemy doesn't populate latter object with
the primary key from the former. Instead it just generate next value
from the sequence. Why is it the case ?

that's not what I see happening here. I see very simply that the B.id_
column is a SERIAL so is linked to a sequence, however you are inserting
a row with a hardcoded "1" for a primary key; so the second B object,
which relies on the sequence, fails due to an identity conflict.

So let's repair the test case first, and that first B.id we'll set to
"10" so that it doesn't conflict.

Now we get the error you probably intended to send:

SELECT "Rel".id_a AS "Rel_id_a", "Rel".id_b AS "Rel_id_b",
"Rel".rel_data AS "Rel_rel_data"
FROM "Rel"
WHERE "Rel".id_a = %(param_1)s AND "Rel".id_b = %(param_2)s
2015-04-14 11:05:11,850 INFO sqlalchemy.engine.base.Engine {'param_1':
800, 'param_2': symbol('NEVER_SET')}

where this is, the merge() is proceeding to attempt to locate the object
by primary key but the PK is not filled in. This is the expected
behavior. The primary key of an object is never auto-populated until
it is flushed. So here, if you are passing in a transient object, you
need to set the PK yourself:

second_b = B(data='foooo')
session.add(second_b)
session.flush()
x = session.merge(Rel(id_a=800, rel_data="second", id_b=second_b.id_))

Post by Юрий Пайков
|fromsqlalchemy importcreate_engine fromsqlalchemy.orm
importsessionmaker engine
importdeclarative_base Base=declarative_base()fromsqlalchemy
importInteger,ForeignKey,VARCHAR,TEXT,Boolean,DateTimefromsqlalchemy.orm
importrelationship fromsqlalchemy.sql.schema
importColumnclassB(Base):__tablename__='B'id_=Column(Integer,primary_key=True)data
=Column(VARCHAR(30))classRel(Base):__tablename__='Rel'id_a=Column(Integer,primary_key=True)id_b=Column(Integer,ForeignKey('B.id_'),primary_key=True)b
=relationship(B)rel_data=Column(VARCHAR(30))Session=sessionmaker(bind=engine)session
=Session()Base.metadata.create_all(engine,checkfirst=True)first_b=B(id_=1,data='ololo')session.add(first_b)session.commit()session.add(Rel(id_a=800,id_b=1,rel_data='first
relation
data'))second_b=B(data='foooo')session.add(second_b)x=session.merge(Rel(id_a=800,rel_data="second",b=second_b))session.commit()|
Here I have an error
IntegrityError: (raised as a result of Query-invoked autoflush;
consider using a session.no_autoflush block if this flush is
occuring prematurely) (IntegrityError) duplicate key value
violates unique constraint "B_pkey" DETAIL: Key (id_)=(1) already
exists. 'INSERT INTO "B" (data) VALUES (%(data)s) RETURNING
"B".id_' {'data': 'foooo'}
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Юрий Пайков]

Oh, it seems that merge() actually does populate the b_id if that B is
non-conflicting... Seems I have another problem .
Thank you anyway, Michael

втПрМОк, 14 апреля 2015 г., 20:08:04 UTC+5 пПльзПватель Michael Bayer

Post by Mike Bayer
that's not what I see happening here. I see very simply that the B.id_
column is a SERIAL so is linked to a sequence, however you are inserting a
row with a hardcoded "1" for a primary key; so the second B object, which
relies on the sequence, fails due to an identity conflict.
So let's repair the test case first, and that first B.id we'll set to "10"
so that it doesn't conflict.
SELECT "Rel".id_a AS "Rel_id_a", "Rel".id_b AS "Rel_id_b", "Rel".rel_data
AS "Rel_rel_data"
FROM "Rel"
WHERE "Rel".id_a = %(param_1)s AND "Rel".id_b = %(param_2)s
800, 'param_2': symbol('NEVER_SET')}
where this is, the merge() is proceeding to attempt to locate the object
by primary key but the PK is not filled in. This is the expected
behavior. The primary key of an object is never auto-populated until it
is flushed. So here, if you are passing in a transient object, you need
second_b = B(data='foooo')
session.add(second_b)
session.flush()
x = session.merge(Rel(id_a=800, rel_data="second", id_b=second_b.id_))