[sqlalchemy] Resetting primay-key id
Hi all, I have been using sqlalchemy for my projects, recently I came across a situation where my id(BigInteger) has over flown, I use postgres, I know in postgres I can reset my autoincrement using this query: 'ALTER SEQUENCE tablename_id_seq RESTART WITH 1', is there anything like this in sqlalchemy where I can reset a table's id once it reaches the limit, as I anyway have process which is purging old data. can anyone please help on this? -- 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.
[sqlalchemy] Re: Not able to filter json column filter in flask-sqlalchemy
One more doubt is, is there a way to filter on the full json
something like this:
in your example only:
print(Session.query(Student).filter(Student.data_test
=={'foo':'bar'}).first())
--
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.
[sqlalchemy] Re: Not able to filter json column filter in flask-sqlalchemy
*VERSION* it was, was working with pg9.2, upgraded to pg9.6 and everything works fine now. Thank you so much. -- 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.
[sqlalchemy] Re: Not able to filter json column filter in flask-sqlalchemy
Hi,
I tried using direct plain JSON:
my model
class Student(db.Model):
__tablename__= 'students'
id=db.Column(db.Integer, primary_key=True,autoincrement=True)
name=db.Column(db.String(200))
roll_no=db.Column(db.Integer)
data_test=db.Column(db.JSON)
I tried this:
*a = Student.query.filter(cast(Student.__table__.c.data_test["foo"],
String) =="bar").**first**()*
*error*:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does
not exist: json -> unknown
LINE 3: WHERE CAST((students.data_test -> 'foo') AS VARCHAR) = 'bar'
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
[SQL: 'SELECT students.id AS students_id, students.name AS students_name,
students.roll_no AS students_roll_no, students.data_test AS
students_data_test \nFROM students \nWHERE CAST((students.data_test ->
%(data_test_1)s) AS VARCHAR) = %(param_1)s'] [parameters: {'param_1':
'bar', 'data_test_1': 'foo'}]
tried this:
*a = Student.query.filter(Student.data_test.op('->>')('foo') ==
'bar').first()*
*error*:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does
not exist: json ->> unknown
LINE 3: WHERE (students.data_test ->> 'foo') = 'bar'
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
[SQL: 'SELECT students.id AS students_id, students.name AS students_name,
students.roll_no AS students_roll_no, students.data_test AS
students_data_test \nFROM students \nWHERE (students.data_test ->>
%(data_test_1)s) = %(param_1)s \n LIMIT %(param_2)s'] [parameters:
{'param_1': 'bar', 'data_test_1': 'foo', 'param_2': 1}]
is this some versioning issue?
I am not able to understand, where am i going wrong?
Thanks for any help in this
--
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.
[sqlalchemy] Re: Not able to filter json column filter in flask-sqlalchemy
Hi Mike, as you said I tried this: *from sqlalchemy.dialects import postgresql class Student(db.Model): # ...data_test=db.Column(postgresql.JSON) * and I tried querying like this: *a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()* tried this as well: *a = Student.query.filter(Student.data_test["foo"].astext.cast(String)=="bar").first()* But still I am getting error: Traceback (most recent call last): File "sqlalchemyjson.py", line 46, in a = Student.query.filter(Student.data_test["foo"].astext =="bar").first() File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2690, in first ret = list(self[0:1]) File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2482, in __getitem__ return list(res) File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2790, in __iter__ return self._execute_and_instances(context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2813, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception exc_info File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json ->> unknown LINE 3: WHERE (students.data_test ->> 'foo') = 'bar' When I use plain json, what should be my query? *a = Student.query.filter(cast(**Student**.c.data_test["foo"], String) =="bar").first()* I am getting this: AttributeError: type object 'Student' has no attribute 'c' Where am I going wrong? -- 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.
[sqlalchemy] Not able to filter json column filter in flask-sqlalchemy
Hi,
I am using flask-sqlalchemy in my project, but I am not able to understand
how to query(filter_by) on a json column
test_example:
#I am using Postgresql backend
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =
'postgresql://postgres:postgres@localhost:5432/test_db'
db = SQLAlchemy(app)
#this is my test class
class Student(db.Model):
__tablename__= 'students'
id=db.Column(db.Integer, primary_key=True, autoincrement=True)
name=db.Column(db.String(200))
roll_no=db.Column(db.Integer)
data_test=db.Column(db.JSON)
#to put data in table is used
s= Student(name='shrey',roll_no=100, data_test={'foo':'bar'})
db.session.add(s)
db.session.commit()
#I read in some links and i tried this
a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()
here the error I am getting is :
Traceback (most recent call last):
File "sqlalchemyjson.py", line 44, in
a = Student.query.filter(Student.data_test["foo"].astext
=="bar").first()
File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py",
line 682, in __getattr__
key)
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object
has an attribute 'astext'
I tried few other operations also, but nothing worked
can someone help me on this?
--
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.
[sqlalchemy] Re: ImportError: cannot import name postgresql
this is the stack trace :
[Mon Jun 19 18:11:01.431528 2017] [:error] [pid 18592] [remote
10.11.12.15:128] Traceback (most recent call last):
[Mon Jun 19 18:11:01.431548 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File
"/var/lib/enclouden-orchestrator/enclouden-orchestrator.wsgi", line 10, in
[Mon Jun 19 18:11:01.431616 2017] [:error] [pid 18592] [remote
10.11.12.15:128] imports =
__import__("app",fromlist=['create_and_initialize_app'])
[Mon Jun 19 18:11:01.431627 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File "/var/lib/enclouden-orchestrator/app.py", line 6,
in
[Mon Jun 19 18:11:01.431677 2017] [:error] [pid 18592] [remote
10.11.12.15:128] from database import db, track_session_deletes
[Mon Jun 19 18:11:01.431691 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File
"/var/lib/enclouden-orchestrator/database/__init__.py", line 10, in
[Mon Jun 19 18:11:01.431728 2017] [:error] [pid 18592] [remote
10.11.12.15:128] from . import events, instances, pools, users, roles
[Mon Jun 19 18:11:01.431748 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File
"/var/lib/enclouden-orchestrator/database/events.py", line 5, in
[Mon Jun 19 18:11:01.431802 2017] [:error] [pid 18592] [remote
10.11.12.15:128] from .instances import Instance, OpenstackProject
[Mon Jun 19 18:11:01.431816 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File
"/var/lib/enclouden-orchestrator/database/instances.py", line 2, in
[Mon Jun 19 18:11:01.431893 2017] [:error] [pid 18592] [remote
10.11.12.15:128] from .types import GUID
[Mon Jun 19 18:11:01.431903 2017] [:error] [pid 18592] [remote
10.11.12.15:128] File
"/var/lib/enclouden-orchestrator/database/types.py", line 5, in
[Mon Jun 19 18:11:01.431950 2017] [:error] [pid 18592] [remote
10.11.12.15:128] from sqlalchemy.dialects import postgresql
[Mon Jun 19 18:11:01.431968 2017] [:error] [pid 18592] [remote
10.11.12.15:128] ImportError: cannot import name postgresql
and yes this is mod_wsgi and daemon mode is present
this is the wsgi file
WSGIDaemonProcess ecnorchestrator user=apache processes=10 threads=1
WSGIScriptAlias /enclouden/orchestrator
/var/lib/enclouden-orchestrator/enclouden-orchestrator.wsgi
WSGIScriptReloading On
WSGIPassAuthorization On
WSGIProcessGroup ecnorchestrator
WSGIApplicationGroup %{GLOBAL}
Order deny,allow
Require all granted
AddOutputFilterByType DEFLATE application/json
--
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.
[sqlalchemy] ImportError: cannot import name postgresql
Hi, I am using a remote postgres server: *PostgreSQL 9.6.2sqlalchemy 1.1.10* running my app on Apache webserver, but whe i try hitting any API i get this error- *ImportError:* *cannot import name postgresql* this is the line which is creating issues :-* from sqlalchemy.dialects import postgresql* I am using sqlalchemy.dialects, I am not able to understand why is this happening, when i was using locally it was working fine, does remote server makes a difference? or the reason is httpd can someone help on this? -- 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.
[sqlalchemy] Re: insertion into association table, giving integrityerror
Sorry not the first one, but the second one -- 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.
[sqlalchemy] Re: insertion into association table, giving integrityerror
Thanks Mike for suggestions, was able to solve both the issues with slight changes in the db models added lazy='dynamic' on both sides where back_populates is being used -- 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] Re: insertion into association table, giving integrityerror
Yes Mike will do that, mostly its marshmallow model schema which is
creating the object, as I am using Flask + marshmallow for APIs,
apart from that I have another issue, which i am not able to uderstand:
I have a many-to-many relationship between 2 models, same as posted above,
still ill put the models here
package model:
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')
group model:
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
in this model when i do something like this:
p = Package.query.filter_by(name='firefox').first() //here p is
Package object, and it has some groups mapped to it
p.groups=[] //when i try to empty it, i get this exception
*AssertionError: Collection was loaded during event
handling. *//though the action is getting
performed, putting a try catch is solving it, but why is it coming?
though,
g.packages=[]this is working fine with no exception
Thanks for any kind of help, in advance
--
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.
[sqlalchemy] Re: insertion into association table, giving integrityerror
A package object is getting created, and its inserting it into database, have to debug why is it inserting into db Somehow i got it working, added: db.session.rollback(), before append statement Dont know whether its the right approach 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.
[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
[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
