Cheers
Simon
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import *
Base = declarative_base()
association_table = Table('association', Base.metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id')),
PrimaryKeyConstraint('left_id','right_id'),
UniqueConstraint('left_id','right_id'),
)
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship(
"Child",
secondary=association_table,
back_populates="parents")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
parents = relationship(
"Parent",
secondary=association_table,
back_populates="children")
def main ():
print ('main()')
e = create_engine("postgresql://app:Pa55w0rd@localhost/test",
echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
# initialise database with Parent(1) associated with Child(1)
p = Parent ()
c = Child ()
p.children.append (c)
s.add (p)
s.add (c)
s.commit ()
# now simulate a request to associate Parent(1) with Child(1)
# When commit no exception thrown
theParent = s.query(Parent).get(1)
theChild = s.query(Child).get(1)
theParent.children.append (theChild)
s.commit ()
if __name__ == "__main__":
main()
On Tuesday, 6 December 2016 17:21:33 UTC, Mike Bayer wrote:
the formatting of your code is coming out largely unreadable for me,
here is a short example illustrating how to get an IntegrityError when
appending to a collection. Try running this to confirm it works, and
then seeing what's different about your own application versus this
example.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id <http://a.id>'))
some_unique_thing = Column(Integer)
__table_args__ = (
UniqueConstraint("some_unique_thing", name="uq_1"),
)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
a1 = A(bs=[B(some_unique_thing=1)])
s.add(a1)
s.commit()
a1.bs.append(B(some_unique_thing=1))
s.commit()
On 12/06/2016 12:10 PM, 'dcs3spp' via sqlalchemy wrote:
> Hi Mike,
>
>
> Thanks again for responding :)
>
> Currently testing with default collection of list. Have also tried
> previously with set before original post, but still no exception
> generated. Difference is the set silently ignores duplicates in
memory
> when adding items.
>
>
> Hmmm interesting. Sees the model as in the dirty state for both
> associating table rows. SQLAlchemy only generating INSERT SQL for
> outcome (4), i.e. not already present in the relationship. Outcome
2 is
> ignored, i.e. no INSERT statement generated. Outcome 2 is already
> present in association table.
>
>
> Cheers
>
> Simon
>
>
> assignment object=Transient: False, Pending: False, Persistent: True,
> Detatched: False
>
> outcome_1 object=Transient: False, Pending: False, Persistent: True,
> Detatched: False
>
> outcome_4 object=Transient: False, Pending: False, Persistent: True,
> Detatched: False
>
> dbsession.dirty=IdentitySet([<LearningOutcome('1014760','2', '1',
> 'Understand the components of computer systems')>,
> <Assignment('1','1014760', '2', '2015-12-10 00:00:00+00:00',
'Assignment
> 1')>, <LearningOutcome('1014760','2', '4', 'Unit 2 : Learning Outcome
> 4')>]), .new=IdentitySet([]), .deleted=IdentitySet([])
>
> 2016-12-06 17:00:33,878 INFO sqlalchemy.engine.base.Engine INSERT
INTO
> proposed_coursemanagement.assignmentoutcome ("CourseID", "UnitID",
> "OutcomeID", "AssignmentID") VALUES (%(CourseID)s, %(UnitID)s,
> %(OutcomeID)s, %(AssignmentID)s)
>
> 2016-12-06 17:00:33,878 INFO
> [sqlalchemy.engine.base.Engine:109][MainThread] INSERT INTO
> proposed_coursemanagement.assignmentoutcome ("CourseID", "UnitID",
> "OutcomeID", "AssignmentID") VALUES (%(CourseID)s, %(UnitID)s,
> %(OutcomeID)s, %(AssignmentID)s)
>
> 2016-12-06 17:00:33,878 INFO sqlalchemy.engine.base.Engine
{'CourseID':
> 1014760, 'OutcomeID': 4, 'AssignmentID': 1, 'UnitID': 2}
>
> 2016-12-06 17:00:33,878 INFO
> [sqlalchemy.engine.base.Engine:109][MainThread] {'CourseID': 1014760,
> 'OutcomeID': 4, 'AssignmentID': 1, 'UnitID': 2}
>
> 2016-12-06 17:00:33,879 INFO sqlalchemy.engine.base.Engine COMMIT
>
> 2016-12-06 17:00:33,879 INFO
> [sqlalchemy.engine.base.Engine:109][MainThread] COMMIT
>
> Test Script complete...
>
>
> On Tuesday, 6 December 2016 16:27:35 UTC, Mike Bayer wrote:
>
>
>
> On 12/06/2016 11:21 AM, 'dcs3spp' via sqlalchemy wrote:
> > Hi,
> >
> > Good suggestion by Mike, regarding possibility that problem
could be
> > with postgreSQL. Tried inserting duplicate record in
association
> table
> > from within postgreSQL. This correctly raises a duplicate
key error.
> >
> > proposed_coursemanagement=> INSERT INTO assignmentoutcome
VALUES (1,
> > 1014760, 2, 1);
> >
> > ERROR: duplicate key value violates unique constraint
> > "assignmentoutcome_pkey"
> >
> > DETAIL: Key ("AssignmentID", "OutcomeID", "UnitID",
> "CourseID")=(1, 1,
> > 2, 1014760) already exists.
>
>
> OK then when you set up your model state and do a
session.commit(),
> turn
> on echo=True and watch the SQL that is emitted. You would
ideally see
> it attempting to insert this row as well, assuming the
collection on
> your relationship allows duplicates in the first place (e.g.
it's a
> list, not a set). Since you aren't getting an integrity
error, it's
> likely not doing that as yet.
>
>
>
>
> >
> >
> > Cheers
> >
> > Simon
> >
> > On Tuesday, 6 December 2016 14:36:15 UTC, dcs3spp wrote:
> >
> > Hi,
> >
> >
> > I am running SQLAlchemy 1.1.4 connected to postgreSQL
9.6 on OSX
> > Sierra 10.12.1. I am experiencing difficulty getting
> SQLAlchemy to
> > raise an integrity error when a persistent object is
appended
> to a
> > relationship for which it is already associated with.
> >
> >
> >
> > Is the accepted method to manually raise an exception by
> testing for
> > existing membership in the collection_class of the
relationship
> > before inserting the persistent object into the
relationship?
> I have
> > tested with default list and also setting the
collection_class to
> > set. Would be grateful if anyone could help with this and
> point out
> > where I am going wrong :)
> >
> >
> > I have included details of code and DB structure below.
> Currently,
> > duplicate associations are silently ignored, i.e. no INSERT
> > statements generated.
> >
> >
> > Cheers
> >
> >
> > Simon
> >
> >
> >
> >
> > DB Structure
> >
> > Assignment —< AssignmentOutcome >— LearningOutcome
> >
> >
> > TABLE KEYS
> >
> > Assignment identified by AssignmentID
> >
> > LearningOutcome identified by CourseID, UnitID, OutcomeID
> >
> > AssignmentOutcome identified by AssignmentID, CourseID,
UnitID,
> > OutcomeID
> >
> >
> > ASSOCIATION TABLE STATE BEFORE TEST SCRIPT RUN
> >
> > proposed_coursemanagement=> SELECT * FROM assignment
outcome;
> >
> > AssignmentID | CourseID | UnitID | OutcomeID
> >
> > --------------+----------+--------+-----------
> >
> > 1 | 1014760 | 2 | 1
> >
> > 1 | 1014760 | 2 | 2
> >
> > 2 | 1014760 | 2 | 1
> >
> > 2 | 1014760 | 2 | 3
> >
> > 3 | 1014760 | 2 | 2
> >
> > 4 | 1014760 | 2 | 3
> >
> > (6 rows)
> >
> >
> > TEST SCRIPT PURPOSE: Try appending outcome 1 again to
> assignment 1.
> > Should SQLAlchemy raise and exception since this is already
> mapped
> > in association table? Current behaviour is that
duplicate is
> > silently ignored. When I append outcome 4 for assignment
1 it is
> > mapped to the association table.
> >
> >
> > TEST SCRIPT CODE:
> >
> > def usage(argo):
> >
> > cmd = os.path.basename(argv[0])
> >
> > print('usage: %s <config_uri> [var=value]\n'
> >
> > '(example: "%s development.ini")' % (cmd, cmd))
> >
> >
> > def main(argv=sys.argv):
> >
> >
> >
> > if len(argv) < 2:
> >
> > usage(argv)
> >
> >
> > print ("Initialising SQLAlchemy engine instance and
> tables.......")
> >
> > config_uri = argv[1]
> >
> > options = parse_vars(argv[2:])
> >
> > setup_logging(config_uri)
> >
> > settings = get_appsettings(config_uri, options=options)
> >
> >
> > engine = get_engine(settings)
> >
> > Base.metadata.create_all(engine)
> >
> > session_factory = get_session_factory(engine)
> >
> > print ("Completed initialisation.....")
> >
> > print ("Starting Test Script...")
> >
> > with transaction.manager:
> >
> > dbsession = get_tm_session(session_factory,
> transaction.manager)
> >
> >
> > # load into session assignment 1 and learning
outcomes
> 1 and 4
> >
> > # outcome 1 already exists in the association
table for
> > assignment 1
> >
> > # outcome 4 does not exist yet in the association
> table for
> > assignment 1
> >
> > assignment =
dbsession.query(AssignmentModel).get(1)
> >
> > outcome_1 =
> > dbsession.query(LearningOutcomeModel).get((1014760, 2, 1))
> >
> > outcome_4 =
> > dbsession.query(LearningOutcomeModel).get((1014760, 2, 4))
> >
> >
> > # add outcome 1 and outcome 4 to assignmentoutcomes
> relationship
> >
> > assignment.assignmentoutcomes.append(outcome_1)
> >
> > assignment.assignmentoutcomes.append(outcome_4)
> >
> >
> > # inspect and display state for assignment and
outcome
> objects
> >
> > assignmentIns = inspect (assignment)
> >
> > outcome1Ins = inspect (outcome_1)
> >
> > outcome4Ins = inspect (outcome_4)
> >
> > print("assignment object=Transient: {0},
Pending: {1},
> > Persistent: {2}, Detatched: {3}".format(
> >
> > assignmentIns.transient, assignmentIns.pending,
> > assignmentIns.persistent, assignmentIns.detached))
> >
> > print("outcome_1 object=Transient: {0}, Pending:
{1},
> > Persistent: {2}, Detatched: {3}".format(
> >
> > outcome1Ins.transient, outcome1Ins.pending,
> > outcome1Ins.persistent, outcome1Ins.detached))
> >
> > print("outcome_4 object=Transient: {0}, Pending:
{1},
> > Persistent: {2}, Detatched: {3}".format(
> >
> > outcome4Ins.transient, outcome4Ins.pending,
> > outcome4Ins.persistent, outcome4Ins.detached))
> >
> >
> > # inspect and display session states .dirty,
.new and
> .deleted
> >
> > print ("dbsession.dirty={0}, .new={1},
> .deleted={2}".format(
> >
> > dbsession.dirty, dbsession.new,
dbsession.deleted))
> >
> > print ("Test Script complete...")
> >
> >
> > TEST SCRIPT OUTPUT
> >
> > assignment object=Transient: False, Pending: False,
Persistent:
> > True, Detatched: False
> >
> > outcome_1 object=Transient: False, Pending: False,
Persistent:
> True,
> > Detatched: False
> >
> > outcome_4 object=Transient: False, Pending: False,
Persistent:
> True,
> > Detatched: False
> >
> >
dbsession.dirty=IdentitySet([<LearningOutcome('1014760','2', '4',
> > 'Unit 2 : Learning Outcome 4')>,
<LearningOutcome('1014760','2',
> > '1', 'Understand the components of computer systems')>,
> > <Assignment('1','1014760', '2', '2015-12-10
00:00:00+00:00',
> > 'Assignment 1')>]), .new=IdentitySet([]),
> .deleted=IdentitySet([])
> >
> > 2016-12-06 12:33:10,691 INFO
> > [sqlalchemy.engine.base.Engine:1097][MainThread] INSERT
INTO
> > proposed_coursemanagement.assignmentoutcome ("CourseID",
> "UnitID",
> > "OutcomeID", "AssignmentID") VALUES (%(CourseID)s,
%(UnitID)s,
> > %(OutcomeID)s, %(AssignmentID)s)
> >
> > 2016-12-06 12:33:10,691 INFO
> > [sqlalchemy.engine.base.Engine:1100][MainThread]
{'OutcomeID': 4,
> > 'CourseID': 1014760, 'UnitID': 2, 'AssignmentID': 1}
> >
> > 2016-12-06 12:33:10,692 INFO
> > [sqlalchemy.engine.base.Engine:686][MainThread] COMMIT
> >
> > Test Script complete...
> >
> >
> >
> >
> > ASSOCIATION TABLE STATE AFTER TEST SCRIPT RUN
> >
> > proposed_coursemanagement=> SELECT * FROM
assignmentoutcome;
> >
> > AssignmentID | CourseID | UnitID | OutcomeID
> >
> > --------------+----------+--------+-----------
> >
> > 1 | 1014760 | 2 | 1 * No
duplicate
> raised
> > - does SQLAlchemy silently ignore?
> >
> > 1 | 1014760 | 2 | 2
> >
> > 2 | 1014760 | 2 | 1
> >
> > 2 | 1014760 | 2 | 3
> >
> > 3 | 1014760 | 2 | 2
> >
> > 4 | 1014760 | 2 | 3
> >
> > 1 | 1014760 | 2 | 4
> >
> > (7 rows)
> >
> >
> >
> > MODEL CODE:
> >
> >
> > assignmentoutcomeallocation = Table('assignmentoutcome',
> Base.metadata,
> >
> > Column('CourseID', BigInteger, primary_key=True),
> >
> > Column('UnitID', Integer, primary_key=True),
> >
> > Column('OutcomeID', Integer, primary_key=True),
> >
> > Column('AssignmentID', BigInteger,
> > ForeignKey('assignment.AssignmentID'), primary_key=True),
> >
> > UniqueConstraint('CourseID', 'UnitID', 'OutcomeID',
> 'AssignmentID'),
> >
> > ForeignKeyConstraint(
> >
> > ['CourseID', 'UnitID', 'OutcomeID'],
> >
> > ['learningoutcome.CourseID',
'learningoutcome.UnitID',
> > 'learningoutcome.OutcomeID']),
> >
> > )
> >
> >
> > class AssignmentModel(Base):
> >
> > __tablename__ = 'assignment'
> >
> > __table_args__ = (
> >
> > ForeignKeyConstraint(['CourseID','UnitID'],
> > ['unit.CourseID', 'unit.UnitID']),
> >
> > )
> >
> >
> > AssignmentID = Column(BigInteger, primary_key=True,
> >
> >
> >
>
server_default=text("nextval('proposed_coursemanagement.\"assignment_AssignmentID_seq\"::regclass)"))
>
> >
> > CourseID = Column(BigInteger, nullable=False)
> >
> > UnitID = Column(Integer, nullable=False)
> >
> > AssignmentSetDate = Column(DateTime(timezone=True),
> nullable=False)
> >
> > AssignmentSubmissionDate =
Column(DateTime(timezone=True),
> > nullable=False)
> >
> > AssignmentResubmissionDate =
Column(DateTime(timezone=True),
> > nullable=False)
> >
> > AssignmentTitle = Column(String(200), nullable=False)
> >
> >
> > indx = Index('assignment_indx', 'CourseID', 'UnitID',
> > text('lower(\"AssignmentTitle\")'), 'AssignmentSetDate')
> >
> >
> > unit = relationship('UnitModel')
> >
> > assignmentcriteria = relationship('UnitCriteriaModel',
> >
> >
> > secondary=assignmentcriteriaallocation,
> >
> >
> > back_populates='criteria_assignments')
> >
> > assignmentoutcomes =
relationship('LearningOutcomeModel',
> >
> >
> > secondary=assignmentoutcomeallocation,
> >
> >
> >
> >
> > back_populates='outcome_assignments')
> >
> >
> >
> > def __repr__(self):
> >
> > return "<Assignment('%s','%s', '%s', '%s',
'%s')>" %
> > (self.AssignmentID, self.CourseID, self.UnitID,
> > self.AssignmentSetDate, self.AssignmentTitle)
> >
> >
> > def __str__(self):
> >
> > return "<Assignment('%s','%s', '%s', '%s',
'%s')>" %
> > (self.AssignmentID, self.CourseID, self.UnitID,
> > self.AssignmentSetDate, self.AssignmentTitle)
> >
> >
> >
> > class LearningOutcomeModel(Base):
> >
> > __tablename__ = 'learningoutcome'
> >
> > __table_args__ = (
> >
> > ForeignKeyConstraint(['CourseID','UnitID'],
> > ['unit.CourseID', 'unit.UnitID']),
> >
> > )
> >
> >
> > CourseID = Column(BigInteger, primary_key=True,
> nullable=False)
> >
> > UnitID = Column(Integer, primary_key=True,
nullable=False)
> >
> > OutcomeID = Column(Integer, primary_key=True,
nullable=False)
> >
> > LearningOutcome = Column(String(100), nullable=False)
> >
> >
> > unit = relationship('UnitModel')
> >
> > outcome_assignments = relationship (
> >
> > 'AssignmentModel',
> >
> > secondary=assignmentoutcomeallocation,
> >
> > back_populates='assignmentoutcomes')
> >
> >
> > def __repr__(self):
> >
> > return "<LearningOutcome('%s','%s', '%s', '%s')>" %
> > (self.CourseID, self.UnitID, self.OutcomeID,
> self.LearningOutcome)
> >
> >
> > def __str__(self):
> >
> > return "<LearningOutcome('%s','%s', '%s', '%s')>" %
> > (self.CourseID, self.UnitID, self.OutcomeID,
> self.LearningOutcome)
> >
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal,
Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve>
> <http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve>> for a full
> > description.
> > ---
> > 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:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com
<javascript:> <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at
https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>
> <https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>>.
> > For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>
> <https://groups.google.com/d/optout
<https://groups.google.com/d/optout>>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> for a full
> description.
> ---
> 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:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.