Hello -
I've reproduced your code fragments as a complete MCVE, it runs fine.
Please run this script and try to see where your own application
differs.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from uuid import uuid4 as function_to_generate_uuid
import uuid
class UUID(TypeDecorator):
impl = CHAR
def process_bind_param(self, value, dialect):
if value is None:
return value
else:
if not isinstance(value, uuid.UUID):
return "%.32x" % uuid.UUID(value).int
else:
# hexstring
return "%.32x" % value.int
def process_result_value(self, value, dialect):
if value is None:
return value
else:
if not isinstance(value, uuid.UUID):
value = uuid.UUID(value)
return value
Base = declarative_base()
class EmployeeType(Base):
__tablename__ = 'employee_types'
emp_type = Column(String(50), primary_key=True)
emp_type_description = Column(Text)
class Employee(Base):
__tablename__ = 'employees'
emp_id = Column(UUID, primary_key=True)
emp_type = Column(String(50), ForeignKey(EmployeeType.emp_type))
emp_groups = relationship("EmployeeMembership", backref='employee')
__mapper_args__ = {
'polymorphic_on': emp_type
}
class EmployeeMembership(Base):
__tablename__ = 'employee_membership'
emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
group = Column(String(25), primary_key=True)
class Engineer(Employee):
__tablename__ = 'engineers'
emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
emp_name = Column(String(50), unique=True, nullable=False)
engineer_attr = Column(Text)
__mapper_args__ = {
'polymorphic_identity': 'engineer'
}
# create a new UUID if I don't give one (new Engineer/Employee)
def __init__(self, *args, **kwargs):
if 'emp_id' not in kwargs:
self.emp_id = function_to_generate_uuid()
super(Engineer,self).__init__(*args, **kwargs)
class Manager(Employee):
__tablename__ = 'manager'
emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
emp_name = Column(String(50), unique=True, nullable=False)
manager_blah = Column(Text)
__mapper_args__ = {
'polymorphic_identity': 'manager'
}
# create a new UUID if I don't give one (new Manager/Employee)
def __init__(self, *args, **kwargs):
if 'emp_id' not in kwargs:
self.emp_id = function_to_generate_uuid()
super(Manager, self).__init__(*args, **kwargs)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([EmployeeType(emp_type='engineer'), EmployeeType(emp_type='manager')])
s.commit()
new_emp = Engineer(emp_name='Leeroy Jenkins')
session = Session(e)
session.add(new_emp)
session.commit()
output, after create tables and initial setup:
BEGIN (implicit)
2018-03-20 11:34:39,588 INFO sqlalchemy.engine.base.Engine INSERT INTO
employees (emp_id, emp_type) VALUES (?, ?)
2018-03-20 11:34:39,589 INFO sqlalchemy.engine.base.Engine
('8717f41097d140b28a6f46842e774279', 'engineer')
2018-03-20 11:34:39,590 INFO sqlalchemy.engine.base.Engine INSERT INTO
engineers (emp_id, emp_name, engineer_attr) VALUES (?, ?, ?)
2018-03-20 11:34:39,590 INFO sqlalchemy.engine.base.Engine
('8717f41097d140b28a6f46842e774279', 'Leeroy Jenkins', None)
2018-03-20 11:34:39,590 INFO sqlalchemy.engine.base.Engine COMMIT
On Mon, Mar 19, 2018 at 11:04 PM, Peter Lai <[email protected]> wrote:
> As seen at:
> https://stackoverflow.com/questions/49374458/how-to-propagate-subclass-primary-key-to-base-class-primary-key-when-creating-ne
>
> I'm running into a problem where I am using joined table inheritance where
> the base class carries "upstream" relationships:
>
> class EmployeeType(Base):
> __tablename__ = 'employee_types'
>
> emp_type = Column(String(50), primary_key=True)
> emp_type_description = Column(Text)
> # we want to constrain the EmployeeTypes
>
> # UUID is a custom TypeDecorator that generates a UUID correctly for all
> backends
>
> class Employee(Base):
>
> __tablename__ = 'employees'
>
> emp_id = Column(UUID, primary_key=True)
> emp_type = Column(String(50), ForeignKey(EmployeeType.emp_type))
>
> emp_groups = relationship(EmployeeMembership, backref='employee')
>
> __mapper_args__ = {
> 'polymorphic_on': emp_type
> }
>
> # this is a one-to-many association membership-type of object
> class EmployeeMembership(Base):
> __tablename__ = 'employee_membership'
>
> emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
> group = Column(String(25), primary_key=True)
>
>
> class Engineer(Employee):
> __tablename__ = 'engineers'
>
> emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
>
> # emp_name belongs to subclass because within the subclass it needs to
> be unique, but not unique across subclasses
> # There can be 2 different employees (emp_id are different) named
> 'Leeroy Jenkins' one is a manager and one is an engineer.
>
> emp_name = Column(String(50), unique=True, nullable=False)
>
> engineer_attr = Column(Text)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'engineer'
> }
>
> # create a new UUID if I don't give one (new Engineer/Employee)
> def __init__(self, *args, **kwargs):
> if 'emp_id' not in kwargs:
> self.emp_id = function_to_generate_uuid()
>
> super(Engineer,self).__init__(*args, **kwargs))
>
> class Manager(Employee):
> __tablename__ = 'manager'
>
> emp_id = Column(UUID, ForeignKey(Employee.emp_id), primary_key=True)
>
> # emp_name belongs to subclass because within the subclass it needs to
> be unique, but not unique across subclasses
> # There can be 2 different employees (emp_id are different) named
> 'Leeroy Jenkins' one is a manager and one is an engineer.
> emp_name = Column(String(50), unique=True, nullable=False)
>
> manager_blah = Column(Text)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'manager'
> }
>
> # create a new UUID if I don't give one (new Manager/Employee)
> def __init__(self, *args, **kwargs):
> if 'emp_id' not in kwargs:
> self.emp_id = function_to_generate_uuid()
>
> super(Manager, self).__init__(*args, **kwargs))
>
>
> The problem is when I go to create either an Engineer or Manager:
>
> new_emp = Engineer(emp_name='Leeroy Jenkins')
> session = Session()
> session.add(new_emp)
> session.commit()
>
>
> I get:
>
> python27\lib\site-packages\sqlalchemy\sql\crud.py:692: SAWarning: Column
> 'employees.emp_id'
> is marked as a member of the primary key for table 'employees', 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.
> util.warn(msg)
> (psycopg2.IntegrityError) null value in column "emp_id" violates not-null
> constraint
> DETAIL: Failing row contains (null, engineer).
> [SQL: 'INSERT INTO employees (emp_type) VALUES (%(emp_type)s)'] [parameters:
> {'emp_type': u'engineer'}]
>
>
> What do I need to do to get my instance of base class created properly when
> I create my subclass? I'm still on SQLAlchemy 1.1.13 and py27
>
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
