Flask SQLAlchemy Many to Many association object error

0
votes

I am working on a project using python 3.4, Flask and SQLAlchemy that is based on project management. I have the following classes that need to be linked to each other in a many to many relationship. User and Project modules are functioning as expected individually. The user model code is included below

class User(db.Model):
__tablename__='users'
    id = db.Column(db.Integer, primary_key =True)
    firstname = db.Column(db.String(80))
    lastname = db.Column(db.String(80))
    email = db.Column(db.String(35), unique =True)
    username = db.Column(db.String(80), unique= True)
    password = db.Column(db.String(80))
    organisation_id = db.Column(db.Integer, db.ForeignKey('organisations.id'))
    organisation = db.relationship('Organisation', backref='users')
    is_admin = db.Column(db.Boolean)

    def __init__(self, firstname, lastname, email, username, password, organisation_id, is_admin=False):
        self.firstname = firstname
        self.email = email
        self.lastname = lastname
        self.password = password
        self.is_admin = is_admin
        self.username = username
        organisation_id = organisation_id

the code for the project is

class Project(db.Model):
    __tablename__ ="projects"
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(80), unique=True)
    name = db.Column(db.String(80))
    owner = db.Column(db.Integer, db.ForeignKey('users.id'))
    description = db.Column(db.Text)
    start = db.Column(db.DateTime)
    finish = db.Column(db.DateTime)
    cycle_id = db.Column(db.Integer, db.ForeignKey('reportingcycles.id'))
    cycle= db.relationship('ReportingCycle', backref='project')
    org_id = db.Column(db.Integer, db.ForeignKey('organisations.id'))
    organisation= db.relationship('Organisation', backref='project')
    status = db.Column(db.Boolean)
    users = db.relationship("UserProject", backref="project")

    def __init__(self, code, name, description, owner, start, finish, cycle, organisation, status):
        self.code = code
        self.name = name
        self.owner = owner
        self.description = description
        self.start = start
        self.finish = finish
        self.status = status
        self.org_id= organisation.id
        self.cycle_id= cycle.id

I have created an association object as per the SQLAlchemy tutorial in this link Association Object The code for the association class is 

class UserProject(db.Model):
    __tablename__ = 'user_project'
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    role_id = db.Column(db.Integer)

    user = db.relationship("User", backref="project_assocs")

When I tried to test this relationship in command line by typing the following code

prj = Project.query.first()
usr = User.query.first()
asso = UserProject(role_id =1)
asso.user = usr
prj.users.append(asso)

I get the following error on trying to commit these changes to the DB.

/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/sql/crud.py:692: SAWarning: Column 'user_project.project_id' is marked as a member of the primary key for table 'user_project', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed. Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends. util.warn(msg) Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 237, in get return self.impl.get(instance_state(instance), dict_) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 584, in get value = self.callable_(state, passive) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/strategies.py", line 557, in _load_for_state return self._emit_lazyload(session, state, ident_key, passive) File "", line 1, in File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/strategies.py", line 635, in _emit_lazyload result = q.all() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2703, in all return list(self) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2854, in iter self.session._autoflush() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1375, in _autoflush util.raise_from_cause(e) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1365, in _autoflush self.flush() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements execute(statement, params) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1394, in _handle_dbapi_exception exc_info File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/cursors.py", line 146, in execute result = self._query(query) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/cursors.py", line 296, in _query conn.query(q) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/connections.py", line 781, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/connections.py", line 942, in _read_query_result result.read() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/connections.py", line 1138, in read first_packet = self.connection._read_packet() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/connections.py", line 906, in _read_packet packet.check_error() File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/connections.py", line 367, in check_error err.raise_mysql_exception(self._data) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/err.py", line 120, in raise_mysql_exception _check_mysql_exception(errinfo) File "/home/ubuntu/workspace/colp/venv/lib/python3.4/site-packages/pymysql/err.py", line 112, in _check_mysql_exception raise errorclass(errno, errorvalue) sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (colp.user_project, CONSTRAINT user_project_ibfk_1 FOREIGN KEY (project_id) REFERENCES projects (id))') [SQL: 'INSERT INTO user_project (user_id, role_id) VALUES (%s, %s)'] [parameters: (1, 1)]

Any idea what I am doing wrong in this code

2
python-3.xflaskflask-sqlalchemy
What the significance of role_id in association table? If its not mandatory to have then you see my answer below. – Pradeepb
@Pradeepb I need to have the role_id. In reality, it should be a third foreign key to define permission for each user in every individual project. – Hassan Emam

2 Answers

1
votes

I have figured out a work around inspired by @Pradeepb previous response where he found that the code works only if new objects where defined. Not sure it is the best but it works. This was achieved by modifying the UserProject class by adding a constructor

class UserProject(db.Model):
    __tablename__ = 'user_project'
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    role_id = db.Column(db.Integer)
    user = db.relationship("User", backref="parent_assocs")
    project = db.relationship("Project", backref="assoc")

def __init__(self, project, user, role):
    self.project_id = project.id
    self.user_id = user.id
    self.role_id = role.id

to add an association using existing resource from DB I have used the code

prj = Project.query.first()
usr = User.query.first()
asso = UserProject(project = prj, user=usr, role_id =1)
0
votes

After analyzing the problem for the error, I got to know that, it is working only when you create a new user(object),a new/existing project(object) and then if you associate, it will work fine. I got it working, when I did like below:

prj = Project(status=True //with other parameters) or prj = Project.query.first()
usr = User(is_admin=True //with other parameters)
asso = UserProject(role_id =1)
asso.user = usr
prj.users.append(asso)

But If I try to do the same with existing objects, I get same error as yours. May be this helps for further investigation :)