Agfdhyk

I have the following code that is attempting to use an association object to create parent-child many-to-many relationships. The ParentChild (association table) is to have a composite key comprising of parent_id, child_id and a unc

timestamp.

import os

import sys

import psycopg2

from datetime import datetime

from sqlalchemy import Column, ForeignKey, Integer, String, Date

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship, Session

from sqlalchemy import create_engine



Base = declarative_base()



class ParentChild(Base):

   __tablename__ = 'parentchild'

   parent_id = Column(Integer, ForeignKey('parent.id'), primary_key=True)

   child_id = Column(Integer, ForeignKey('child.id'), primary_key=True)

   timestamp = Column(Date, primary_key=True)

   child = relationship("Child", back_populates="parents")

   parent = relationship("Parent", back_populates="children")



class Parent(Base):

   __tablename__ = 'parent'

   id = Column(Integer, autoincrement=True, primary_key=True)

   name = Column(String)

   age = Column(Integer)

   children = relationship('ParentChild', back_populates="parent")



class Child(Base):

   __tablename__ = 'child'

   id = Column(Integer, autoincrement=True, primary_key=True)

   name = Column(String)

   age = Column(Integer)

   parents = relationship('ParentChild', back_populates='child')





engine = create_engine('blah blah blah')

Base.metadata.create_all(engine)

session = Session(engine)



parent1 = Parent(name='Mark', age='28')

session.add(parent1)



child1 = Child(name='Jack', age='3')

session.add(child1)



family1 = ParentChild(timestamp = datetime.utcnow())

family1.child = child1.id 

parent1.children.append(family1)



session.add(family1)

session.commit()

This results in the error message;

SAWarning: Column 'parentchild.child_id' is marked as a member of the primary key for table 'parentchild', 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 "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 1193, in _execute_context

    context)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginedefault.py", line 509, in do_execute

    cursor.execute(statement, parameters)

psycopg2.IntegrityError: null value in column "child_id" violates not-null constraint

DETAIL:  Failing row contains (2, null, 2018-11-18).





The above exception was the direct cause of the following exception:



Traceback (most recent call last):

  File "c:/Users/markp/OneDrive/Code/agile_metrics/test_assoc.py", line 50, in <module>

    session.commit()

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 954, in commit

    self.transaction.commit()

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 467, in commit

    self._prepare_impl()

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 447, in _prepare_impl

    self.session.flush()

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 2313, in flush

    self._flush(objects)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 2440, in _flush

    transaction.rollback(_capture_exception=True)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyutillanghelpers.py", line 66, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyutilcompat.py", line 249, in reraise

    raise value

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormsession.py", line 2404, in _flush

    flush_context.execute()

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormunitofwork.py", line 395, in execute

    rec.execute(self)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormunitofwork.py", line 560, in execute

    uow

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormpersistence.py", line 181, in save_obj

    mapper, table, insert)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyormpersistence.py", line 872, in _emit_insert_statements

    execute(statement, params)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 948, in execute

    return meth(self, multiparams, params)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemysqlelements.py", line 269, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 1060, in _execute_clauseelement

    compiled_sql, distilled_params

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 1200, in _execute_context

    context)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 1413, in _handle_dbapi_exception

    exc_info

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyutilcompat.py", line 265, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyutilcompat.py", line 248, in reraise

    raise value.with_traceback(tb)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginebase.py", line 1193, in _execute_context

    context)

  File "C:UsersmarkpAppDataLocalProgramsPythonPython37libsite-packagessqlalchemyenginedefault.py", line 509, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "child_id" violates not-null constraint

DETAIL:  Failing row contains (2, null, 2018-11-18).

 [SQL: 'INSERT INTO parentchild (parent_id, timestamp) VALUES (%(parent_id)s, %(timestamp)s)'] [parameters: {'parent_id': 2, 'timestamp': datetime.datetime(2018, 11, 18, 11, 25, 34, 667000)}] (Background on this error at: http://sqlalche.me/e/gkpj)

Any ideas on how to resolve this?

Thank you
Mark