Re: [sqlalchemy] Re: insertion into association table, giving integrityerror
On 04/09/2017 02:18 PM, shrey.chau...@invicto.in wrote: Traceback (most recent call last): File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask_limiter/extension.py", line 442, in __inner return obj(*a, **k) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/views.py", line 84, in view return self.dispatch_request(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/views.py", line 149, in dispatch_request return meth(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/api/v2_0/groups.py", line 119, in patch db.session.commit() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit self.transaction.commit() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit self._prepare_impl() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl self.session.flush() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements execute(statement, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1394, in _handle_dbapi_exception exc_info File
[sqlalchemy] Re: insertion into association table, giving integrityerror
Traceback (most recent call last): File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask_limiter/extension.py", line 442, in __inner return obj(*a, **k) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/views.py", line 84, in view return self.dispatch_request(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/flask/views.py", line 149, in dispatch_request return meth(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/api/v2_0/groups.py", line 119, in patch db.session.commit() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit self.transaction.commit() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit self._prepare_impl() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl self.session.flush() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements execute(statement, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/home/schauhan/NewOrchestrator/session_broker/session_broker/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1394, in _handle_dbapi_exception exc_info File
Re: [sqlalchemy] move objects between two sessions
On 04/09/2017 10:52 AM, SG wrote: > I have two databases with one session for each of them. > I would like to move objects contained in one of them to the other. > I tried to get an object from the first with something like: > > | > obj =sessionA.query(MyClass).filter(MyClass.name=='some name') > | > > and then I tried to add this object to the second session with: > > | > sessionB.add(obj) > sessionB.commit() > | > > but I get this exception: > > | > |Object''isalready attached to session '1'(thisis'2')| > | > > How could I overcome this exception and be able to move this object? > Is there a way to do this? This use case is covered here: http://stackoverflow.com/questions/11213665/unbind-object-from-session Call expunge() first. Cheers, M -- 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 https://groups.google.com/d/optout. signature.asc Description: OpenPGP digital signature
[sqlalchemy] move objects between two sessions
I have two databases with one session for each of them. I would like to move objects contained in one of them to the other. I tried to get an object from the first with something like: obj = sessionA.query(MyClass).filter(MyClass.name == 'some name') and then I tried to add this object to the second session with: sessionB.add(obj) sessionB.commit() but I get this exception: Object '' is already attached to session '1' (this is '2') How could I overcome this exception and be able to move this object? Is there a way to do this? Thanks -- 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 https://groups.google.com/d/optout.
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