Re: [sqlalchemy] insertion into association table, giving integrityerror
On 04/09/2017 09:23 AM, shrey.chau...@invicto.in wrote:
I am working with a python application where I am using flask-sqlalchemy
as ORM, here I am facing a situation due to some reason I am getting
integrity error,
i ll show the models:
(I have a many to many relationship between these 2 models)
class Package(db.Model, BaseMixin):
__tablename__ = 'packages'
__bind_key__ = 'broker_db'
__repr_attrs__= ["id","name","deletepkg"]
__track_attrs__ = ["name","versions"]
#attributes
id= db.Column(db.Integer, primary_key=True,
autoincrement=True)
name = db.Column(db.String(100),
index=True,unique=True, nullable=False)
deletepkg = db.Column(db.Boolean,
index=True,nullable=False)
instances = db.relationship('Instance',
cascade="all,delete",secondary=PACKAGE_INSTANCE_RELATION,back_populates="packages")
groups= db.relationship('Group',cascade="all,delete",
secondary=PACKAGE_GROUP_RELATION,back_populates="packages")
versions = db.relationship('Version',lazy='dynamic')
events= db.relationship('Event',
backref=db.backref('package', uselist=False),lazy='dynamic')
def __init__(self, name,deletepkg):
self.name = name
self.deletepkg = deletepkg
PACKAGE_GROUP_RELATION = db.Table('package_group_relation',
db.Column('package_id', db.Integer, db.ForeignKey('packages.id')),
db.Column('groupt_id', db.Integer, db.ForeignKey('groupts.id')),
info={'bind_key': 'broker_db'}
)
class Group(db.Model,BaseMixin):
__tablename__ = 'groupts'
__repr_attrs__= ["id","name"]
__bind_key__ = 'broker_db'
__track_attrs__ = ["name","packages"]
id= db.Column(db.Integer, primary_key=True,
autoincrement=True)
name = db.Column(db.String(100),unique=True)
packages =
db.relationship("Package",cascade="all,delete",secondary=PACKAGE_GROUP_RELATION,back_populates="groups")
events= db.relationship('Event',
backref=db.backref('group', uselist=False), lazy='dynamic')
def __init__(self, name):
self.name = name
These are my models, now this is the method which i am running
def patch(self,group_id):
args=self.get_parsed_request_data()
flag=db.session.query(group_models.Group.query.filter_by(id=group_id).exists()).scalar()
g=group_models.Group.query.filter_by(id=group_id).first()
if g is not None:
g=group_models.Group.query.filter_by(id=group_id).first()
packagelist=args['packages']
applogger.debug(len(packagelist))
for p in packagelist:
# if p in Package.query.all():
# group.packages.append(p)
# applogger.debug(p.name)
pkg=p.name.strip()
s=db.session.query(package_models.Package).filter_by(name=pkg).first()
if s is not None:
if not s.deletepkg:
applogger.debug(g)
applogger.debug(s)
g.packages.append(s)
db.session.commit()
args['success']='Patches successfuly added'
args['status']='202'
return jsonify(args)
else:
args['error']='Group ix not present'
return jsonify(args)
my patch request is this
{ "packages" : [{ "name": "7zip", "deletepkg":false }]}
request validation is all happening fine, issue is insertion here
I am confused here,
applogger.debug(g) is giving output as
applogger.debug(s) is giving output as
which is perfect those are the 2 database model objects, but after
appending when I commit i get this error:
IntegrityError: (psycopg2.IntegrityError) duplicate key value violates
unique constraint "ix_packages_name"
DETAIL: Key (name)=(7zip) already exists.
[SQL: 'INSERT INTO packages (name, deletepkg) VALUES (%(name)s,
%(deletepkg)s) RETURNING packages.id'] [parameters: {'deletepkg': False,
'name': u'7zip'}]
My question is why is it trying to do an insert on package table?...is
there some problem in my understanding, because i thought append() will
insert in the association table, if the model object is already present
that's not the association table IIUC, that's the main "package" table.
So somewhere, a Package object is being created and is being added to
the Session.
The above code example does not illustrate this. It could be before you
called this method, or it could be some side effect of the methods
you're calling such as get_parsed_request_data(). Also, I'm not sure if
the flask-sqlalchemy extensions have the bad habit of automatically
adding objects to a Session as soon as they are created, which would
make this more likely if some method is innocently creating a Package
object to be discarded. The stack trace (not given here) should always
indicate the source of the flush, such as an autoflush before you even
[sqlalchemy] insertion into association table, giving integrityerror
I am working with a python application where I am using flask-sqlalchemy as
ORM, here I am facing a situation due to some reason I am getting integrity
error,
i ll show the models:
(I have a many to many relationship between these 2 models)
class Package(db.Model, BaseMixin):
__tablename__ = 'packages'
__bind_key__ = 'broker_db'
__repr_attrs__= ["id","name","deletepkg"]
__track_attrs__ = ["name","versions"]
#attributes
id= db.Column(db.Integer, primary_key=True,
autoincrement=True)
name = db.Column(db.String(100),
index=True,unique=True, nullable=False)
deletepkg = db.Column(db.Boolean,
index=True,nullable=False)
instances = db.relationship('Instance',
cascade="all,delete",secondary=PACKAGE_INSTANCE_RELATION,back_populates="packages")
groups= db.relationship('Group',cascade="all,delete",
secondary=PACKAGE_GROUP_RELATION,back_populates="packages")
versions = db.relationship('Version',lazy='dynamic')
events= db.relationship('Event',
backref=db.backref('package', uselist=False),lazy='dynamic')
def __init__(self, name,deletepkg):
self.name = name
self.deletepkg = deletepkg
PACKAGE_GROUP_RELATION = db.Table('package_group_relation',
db.Column('package_id', db.Integer, db.ForeignKey('packages.id')),
db.Column('groupt_id', db.Integer, db.ForeignKey('groupts.id')),
info={'bind_key': 'broker_db'}
)
class Group(db.Model,BaseMixin):
__tablename__ = 'groupts'
__repr_attrs__= ["id","name"]
__bind_key__ = 'broker_db'
__track_attrs__ = ["name","packages"]
id= db.Column(db.Integer, primary_key=True,
autoincrement=True)
name = db.Column(db.String(100),unique=True)
packages =
db.relationship("Package",cascade="all,delete",secondary=PACKAGE_GROUP_RELATION,back_populates="groups")
events= db.relationship('Event',
backref=db.backref('group', uselist=False), lazy='dynamic')
def __init__(self, name):
self.name = name
These are my models, now this is the method which i am running
def patch(self,group_id):
args=self.get_parsed_request_data()
flag=db.session.query(group_models.Group.query.filter_by(id=group_id).exists()).scalar()
g=group_models.Group.query.filter_by(id=group_id).first()
if g is not None:
g=group_models.Group.query.filter_by(id=group_id).first()
packagelist=args['packages']
applogger.debug(len(packagelist))
for p in packagelist:
# if p in Package.query.all():
# group.packages.append(p)
# applogger.debug(p.name)
pkg=p.name.strip()
s=db.session.query(package_models.Package).filter_by(name=pkg).first()
if s is not None:
if not s.deletepkg:
applogger.debug(g)
applogger.debug(s)
g.packages.append(s)
db.session.commit()
args['success']='Patches successfuly added'
args['status']='202'
return jsonify(args)
else:
args['error']='Group ix not present'
return jsonify(args)
my patch request is this
{ "packages" : [{ "name": "7zip", "deletepkg":false }]}
request validation is all happening fine, issue is insertion here
I am confused here,
applogger.debug(g) is giving output as
applogger.debug(s) is giving output as
which is perfect those are the 2 database model objects, but after
appending when I commit i get this error:
IntegrityError: (psycopg2.IntegrityError) duplicate key value violates
unique constraint "ix_packages_name"
DETAIL: Key (name)=(7zip) already exists.
[SQL: 'INSERT INTO packages (name, deletepkg) VALUES (%(name)s,
%(deletepkg)s) RETURNING packages.id'] [parameters: {'deletepkg': False,
'name': u'7zip'}]
My question is why is it trying to do an insert on package table?...is
there some problem in my understanding, because i thought append() will
insert in the association table, if the model object is already present
can anyone help me in this
Thanks in advance
Shrey
--
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.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit
