[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: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='f')
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': 'f'}
--
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 an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] polymorphic inheritance and unique constraints
sorry, i mean i couldn't test it earlier, when i first asked the
question :) it was not another co-worker, lol.
cheers,
richard.
On 04/13/2015 06:30 PM, Mike Bayer wrote:
On 4/13/15 4:59 PM, Richard Gerd Kuesters | Pollux Automation wrote:
well, this didn't work with upstream 1.0 - sorry, I was in another
project and couldn't test it myself.
you're not doing the same thing this user was doing in any case...
Traceback (most recent call last):
File database_test.py, line 46, in module
from plx.db.core import *
File ../src/plx/db/core.py, line 901, in module
UniqueConstraint(ContainerInstance.batch_id,
ContainerAggregation.container_descriptor_id,)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2464, in __init__
ColumnCollectionMixin.__init__(self, *columns,
_autoattach=_autoattach)
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2393, in __init__
self._check_attach()
File
/home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py,
line 2429, in _check_attach
table.description)
sqlalchemy.exc.ArgumentError: Column(s)
'container_aggregation.fk_container_descriptor_id' are not part of
table 'container_instance'.
I got sqlalchemy from git, today.
sqlalchemy.__version__
'1.0.0'
container_aggretation is a subclass of container_instance. I'm not
using concrete inheritance here, may this be the problem?
anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1.
cheers,
richard.
On 03/24/2015 08:49 PM, Michael Bayer wrote:
are these two separate constraints? I just looked and it seems like they are
distinct.
I just added a fix to 1.0 because someone was hacking around something similar
to this.
The easiest way to get these for the moment is just to create the
UniqueConstraint outside of the class definition.
class Foo(Base):
# …
class Bar(Foo):
# …
UniqueConstraint(Bar.x, Foo.y)
that way all the columns are set up, should just work.
Richard Gerd Kuesters | Polluxrich...@pollux.com.br wrote:
well, understanding better the docs for column conflicts, can i use a
declared_attr in a unique constraint? if yes, my problem is solved :)
On 03/24/2015 10:33 AM, Michael Bayer wrote:
Richard Gerd Kuesters | Pollux
rich...@pollux.com.br
wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique along
either types. is there a way to move this column to the parent and use a
constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts
.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things), this
works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is
in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is
in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64),
[sqlalchemy] evaluate strategy for a bulk delete seems to mishandle cases where column and attribute names differ
Hi,
I think I've found a bug triggered by bulk deletes that use the (default)
evaluate strategy.
For example:
from sqlalchemy import Column, Integer, Text, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class Parent(Base):
__tablename__ = parent
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = child
_id_parent = Column(id_parent, Integer, ForeignKey(Parent.id),
primary_key=True)
name = Column(Text, primary_key=True)
parent = relationship(Parent)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.bind.echo = True
# Make a parent
p = Parent(id=1)
session.add(p)
session.commit()
# Add a child
c = Child(name=foo, parent=p)
session.add(c)
session.commit()
# c is still in the session
session.query(Child).filter(Child.parent == p).delete(evaluate)
...give the following traceback:
File user!winfis!test_bed.py, line 34, in :
session.query(Child).filter(Child.parent == p).delete(evaluate)
file:\user!winfis!test_bed.py:34:exception
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2670, in delete : delete_op.exec_()
file:\R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\query.py:2670:exception
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 896, in exec_ : self._do_pre_synchronize()
file:\R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py:896:exception
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 958, in _do_pre_synchronize : eval_condition(obj)]
file:\R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py:958:exception
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py,
line 116, in evaluate : right_val = eval_right(obj)
file:\R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py:116:exception
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py,
line 72, in : return lambda obj: get_corresponding_attr(obj)
file:\R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py:72:exception
AttributeError: 'Child' object has no attribute 'id_parent'
...because the attribute lookup on Child is attempted using the column
name, rather than the attribute name. The actual delete action works fine
when I switch the strategy to False or fetch, or if there are no Child
objects in the session (so no evaluation is invoked).
As you can see, this is v0.9.7, but I've not seen anything relevant in the
changelog since then.
Cheers,
Steve.
--
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 an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQLAlchemy's merge doesn't fill foreign keys with ids of new objects in relationship
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
написал:
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='f')
session.add(second_b)
session.flush()
x = session.merge(Rel(id_a=800, rel_data=second, id_b=second_b.id_))
--
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 an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] evaluate strategy for a bulk delete seems to mishandle cases where column and attribute names differ
Fantastic, thanks!
On Tuesday, April 14, 2015 at 4:00:01 PM UTC+1, Michael Bayer wrote:
On 4/14/15 6:38 AM, Steven Winfield wrote:
Hi,
I think I've found a bug triggered by bulk deletes that use the
(default) evaluate strategy.
a bug is created at
https://bitbucket.org/zzzeek/sqlalchemy/issue/3365/evaluator-cant-locate-orm-entity-when
and for now you need to compare using the columns, not the relationship,
e.g. Child._id_parent == parent.id, if you want to use evaluate there.
For example:
from sqlalchemy import Column, Integer, Text, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class Parent(Base):
__tablename__ = parent
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = child
_id_parent = Column(id_parent, Integer, ForeignKey(Parent.id),
primary_key=True)
name = Column(Text, primary_key=True)
parent = relationship(Parent)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.bind.echo = True
# Make a parent
p = Parent(id=1)
session.add(p)
session.commit()
# Add a child
c = Child(name=foo, parent=p)
session.add(c)
session.commit()
# c is still in the session
session.query(Child).filter(Child.parent == p).delete(evaluate)
...give the following traceback:
File user!winfis!test_bed.py, line 34, in :
session.query(Child).filter(Child.parent == p).delete(evaluate)
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2670, in delete : delete_op.exec_()
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 896, in exec_ : self._do_pre_synchronize()
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 958, in _do_pre_synchronize : eval_condition(obj)]
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py,
line 116, in evaluate : right_val = eval_right(obj)
File
R:\sw\external\20150407-0\python27\lib\site-packages\sqlalchemy-0.9.7-py2.7-win32.egg\sqlalchemy\orm\evaluator.py,
line 72, in : return lambda obj: get_corresponding_attr(obj)
AttributeError: 'Child' object has no attribute 'id_parent'
...because the attribute lookup on Child is attempted using the column
name, rather than the attribute name. The actual delete action works fine
when I switch the strategy to False or fetch, or if there are no Child
objects in the session (so no evaluation is invoked).
As you can see, this is v0.9.7, but I've not seen anything relevant in
the changelog since then.
Cheers,
Steve.
--
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 an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQLAlchemy's merge doesn't fill foreign keys with ids of new objects in relationship
On 4/14/15 3:55 AM, Юрий Пайков wrote:
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='f')
session.add(second_b)
session.flush()
x = session.merge(Rel(id_a=800, rel_data=second, id_b=second_b.id_))
|fromsqlalchemy importcreate_engine fromsqlalchemy.orm
importsessionmaker engine
=create_engine(postgresql+psycopg2://psql_admin:psql_admin@localhost/fm)fromsqlalchemy.ext.declarative
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='f')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': 'f'}
--
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
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
