Yes, the problem was to implement polymorphic relationship. The
relationship() was a misunderstanding, I thought I need to specify it still
explicitly. The user to staff is one to one relationship, so is the user to
student. I changed my design so the email act as primary key instead, and
set staff_id, student_no as index. There appears a error when I override
the default __init__ method of the User class in order to hash the password.
When I create a new staff object:
admin = Staff(
staff_id = 'admin',
first_name="admin",
last_name="admin",
email="ad...@admin.com",
password="123456")
It complains
File ".\db_create.py", line 14, in <module>
password="123456")
TypeError: __init__() got an unexpected keyword argument 'staff_id'
class User(mydb.Model):
__tablename__ = 'USER'
__table_args__ = (Index('SearchNameIndices', "last_name", "first_name"), )
email = mydb.Column(mydb.String(62), primary_key=True)
first_name = mydb.Column(mydb.String(64))
last_name = mydb.Column(mydb.String(64))
password = mydb.Column(mydb.String(), nullable = False)
user_type = mydb.Column(mydb.String)
__mapper_args__ = {
'polymorphic_identity':'user',
'polymorphic_on':user_type
}
def __init__(
self,
email,
first_name,
last_name,
birthdate,
password
):
self.email = email
self.first_name = first_name
self.last_name = last_name
self.birthdate = birthdate
self.password = bcrypt.generate_password_hash(password)
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return unicode(self.uid)
class Student(User):
__tablename__ = 'STUDENT'
email = mydb.Column(mydb.String(62), mydb.ForeignKey('USER.email'),
primary_key=True)
study_no = mydb.Column(mydb.String(20), index = True)
birthdate = mydb.Column(mydb.Date(), index=True)
__mapper_args__ = {
'polymorphic_identity':'STUDENT',
}
class Staff(User):
__tablename__ = 'STAFF'
email = mydb.Column(mydb.String(62), mydb.ForeignKey('USER.email'),
primary_key=True)
staff_id = mydb.Column(mydb.String(20), index = True)
__mapper_args__ = {
'polymorphic_identity':'STAFF',
}
On Thu, Jun 25, 2015 at 8:17 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 6/25/15 2:08 PM, Kevin Qiu wrote:
>
> In the SQLalchemy documentation
> <http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried>,
> it states
>
> "It is standard practice that the same column is used for both the role of
> primary key as well as foreign key to the parent table, and that the column
> is also named the same as that of the parent table. However, both of these
> practices are optional. Separate columns may be used for primary key and
> parent-relationship, the column may be named differently than that of the
> parent, and even a custom join condition can be specified between parent
> and child tables instead of using a foreign key."
>
> So I here have a parent table: user, child table: staff and student. Staff
> has staff id, student has student id which follow different format so they
> can't be mixed. I tried two ways to solve the problem.
>
> wait, what is "the problem" ? what is the relationship of these three
> tables intended to be? is a staff/student one-to-one with a user? do you
> want to use class inheritance here? I see you are using relationship()
> below, so maybe not.
>
>
>
>
> Approach 1: I could introduce a surrogate key for user table, name it
> uid. And it's used as foreign key in the child table. But then I introduce
> a composite primary key since student id and staff id was designed to be
> primary key already. And in one of the dependent table, it have foreign
> keys to both student table and staff table, which refers to the same uid.
> This where the problem comes.
>
> And I receive error:
> sqlalchemy.exc.ArgumentError: ForeignKeyConstraint on PROJECT_APP(study_no,
> staff_id, uid) refers to multiple remote tables: STAFF and STUDENT
>
>
> then also what is PROJECT_APP in relation to these? I don't understand
> the intent of that constraint.
>
> Approach 2: I use children's primary keys as foreign keys in the user table
> and discard inherit parent's primary key:
>
>
> If this were inheritance, then you'd follow the form in the docs; the User
> class has no ForeignKey constraints on it. Each of Student and Staff
> contain an individual foreign key constraint referring to User. You would
> follow the form at
> http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#joined-table-inheritance
> .
>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/EZrrKXdS-p0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
