joined table inheritance child table query return different object after insertion

[Kevin Qiu]

I create a unit test that add new ProjApp object to the database, but the returned object shows it is not the same object inserted, I don't follow why it's like this because I have others tests on inserting a row to a different table, and returned shows the same row object.

Test result:

        self.assertIs(internship_app_rst, internship_app)

    AssertionError: <project.models.ProjectApp object at 0x0000000003EE7E10> is not <project.models.ProjectApp object at 0x0

    000000003EE7710> 

Test:

    def create_new_internship_app(self, student):

    internship_app = ProjectApp(

    app_id=None,

    created_datetime=datetime.date.today(),

    proj_title='Long story'

    )

    mydb.session.add(internship_app)

    mydb.session.commit()

    internship_app_rst = mydb.session.query(ProjectApp).first()

    self.assertIs(internship_app_rst, internship_app)

Model:

    class Application(mydb.Model):

    __tablename__ = 'APPLICATION'

    app_id = mydb.Column(mydb.Integer, primary_key = True)

    created_datetime = mydb.Column(mydb.DateTime(), primary_key = True) 

        app_description = mydb.Column(mydb.Text())

        app_category = mydb.Column(mydb.String(30))

    case_owner_obj = mydb.relationship('Staff', backref='application_list')

    __mapper_args__ = {

          'polymorphic_identity':'application',

          'polymorphic_on':app_category

      }

    class ProjectApp(Application):

    __tablename__ = 'PROJECT_APP'

    __table_args__ = (

    mydb.ForeignKeyConstraint(

    ['app_id','created_datetime'],

    ['APPLICATION.app_id', 'APPLICATION.created_datetime']

    ),

   

    )

    app_id = mydb.Column(mydb.Integer, primary_key = True)

    created_datetime = mydb.Column(mydb.DateTime(), primary_key = True) 

        proj_title = mydb.Column(mydb.Text())

    __mapper_args__ = {

          'polymorphic_identity':'projApp',

      }

[Mike Bayer]

did you try adding an ORDER BY to that query?  calling query.first() will return only the first result in an unordered list.  It is essentially random.

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Kevin Qiu]

It's a test db, there's only one record being added to the table. And I ensure that with a assertIn() test and it passes.

The original code was:

class Application(mydb.Model):

__tablename__ = 'APPLICATION'

app_id = mydb.Column(mydb.Integer, primary_key = True)#app_id = mydb.Column(mydb.String(30), primary_key = True)

created_datetime = mydb.Column(mydb.DateTime(), primary_key = True)

case_owner = mydb.Column(mydb.String(20), mydb.ForeignKey('STAFF.staff_id')) 

applicant = mydb.Column(mydb.String(20), mydb.ForeignKey('STUDENT.study_no'))

app_description = mydb.Column(mydb.Text())

case_priority  = mydb.Column(mydb.String(20))

case_status  = mydb.Column(mydb.String(10))

app_decision = mydb.Column(mydb.String(10))

deadline_datetime = mydb.Column(mydb.DateTime())

close_datetime = mydb.Column(mydb.DateTime())

std_comment = mydb.Column(mydb.Text())

std_document = mydb.Column(mydb.LargeBinary())

staff_comment = mydb.Column(mydb.Text())

staff_document = mydb.Column(mydb.LargeBinary())

app_category = mydb.Column(mydb.String(30))

applicant_obj = mydb.relationship('Student', backref='application_list')

case_owner_obj = mydb.relationship('Staff', backref='application_list')

__mapper_args__ = {

      'polymorphic_identity':'application',

      'polymorphic_on':app_category

  }

class ProjectApp(Application):

__tablename__ = 'PROJECT_APP'

__table_args__ = (

mydb.ForeignKeyConstraint(

['app_id','created_datetime'],

['APPLICATION.app_id', 'APPLICATION.created_datetime']

),

)

app_id = mydb.Column(mydb.Integer, primary_key = True)

created_datetime = mydb.Column(mydb.DateTime(), primary_key = True) 

c_supervisor = mydb.Column(mydb.String(62), mydb.ForeignKey('C_SUPERVISOR.c_sup_email'))

u_supervisor = mydb.Column(mydb.String(20), mydb.ForeignKey('STAFF.staff_id'))

proj_module = mydb.Column(mydb.String(40), mydb.ForeignKey('MODULE.proj_module'))

proj_title = mydb.Column(mydb.Text())

proj_description = mydb.Column(mydb.Text())

proj_salary = mydb.Column(mydb.Text())

proj_contract = mydb.Column(mydb.LargeBinary())

proj_insurance = mydb.Column(mydb.LargeBinary())

proj_s_date = mydb.Column(mydb.Date())

proj_e_date = mydb.Column(mydb.Date())

proj_h_date = mydb.Column(mydb.Date())

csupervisor_obj = mydb.relationship('CSupervisor', backref='projectapp_list')

usupervisor_obj = mydb.relationship('Staff', backref='projectapp_list')

module_obj = mydb.relationship('Module', backref='projectapp_list')

__mapper_args__ = {

      'polymorphic_identity':'projApp',

  }

#test

def create_new_internship_app(self, student):

internship_app = ProjectApp(

app_id=None,

created_datetime=datetime.date.today(),

#deadline_datetime=datetime.date.today() + datetime.timedelta(days=PROCESS_TIMEFRAME[CASE_PRIORITY['Project app']]),

c_supervisor=None,

u_supervisor=None,

proj_module=None,

proj_title='Internship',

proj_description='It\'s long story',

proj_salary='No salary',

proj_contract=None,

proj_insurance=None,

proj_s_date=datetime.date(2014,2,1), 

proj_e_date=datetime.date(2014,5,30), 

proj_h_date=datetime.date(2014,6,1),

#std_comment=None,

#std_document=None

)

mydb.session.add(internship_app)

mydb.session.commit()

internship_app_rst = mydb.session.query(ProjectApp).one()

self.assertIs(internship_app_rst, internship_app)

self.assertEquals(internship_app_rst.proj_s_date , internship_app.proj_s_date)

                return internship_app

[Mike Bayer]

is it the same row?  same primary key?   otherwise what is the SQL being emitted, what are the results?  try echo='debug'?   

[Mike Bayer]

On 6/21/15 1:46 PM, Kevin Qiu wrote:

It's a test db, there's only one record being added to the table. And I ensure that with a assertIn() test and it passes.

The original code was:

class Application(mydb.Model):

__tablename__ = 'APPLICATION'

app_id = mydb.Column(mydb.Integer, primary_key = True)#app_id = mydb.Column(mydb.String(30), primary_key = True)

created_datetime = mydb.Column(mydb.DateTime(), primary_key = True)

class ProjectApp(Application):

__tablename__ = 'PROJECT_APP'

__table_args__ = (

mydb.ForeignKeyConstraint(

['app_id','created_datetime'],

['APPLICATION.app_id', 'APPLICATION.created_datetime']

),

)

app_id = mydb.Column(mydb.Integer, primary_key = True)

created_datetime = mydb.Column(mydb.DateTime(), primary_key = True) 

c_supervisor = mydb.Column(mydb.String(62), mydb.ForeignKey('C_SUPERVISOR.c_sup_email'))

def create_new_internship_app(self, student):

internship_app = ProjectApp(

app_id=None,

created_datetime=datetime.date.today(),

you are assigning a Date object to a DateTime column which is critically part of the primary key.   The row that comes back therefore has a different primary key since date(Y, m, d) != datetime(Y, m, d, 0, 0, 0):

>>> import datetime
>>> d1 = datetime.date(2015, 5, 10)
>>> d2 = datetime.datetime(2015, 5, 10, 0, 0, 0)
>>> d1 == d2
False
>>> d1 == d2.date()
True

I'd try to avoid using dates/datetimes as parts of primary keys because they are difficult to equate to each other, for this reason as well as issues like microseconds present / non-present, etc.

sample instance keys:

(<class '__main__.ProjectApp'>, (2, datetime.date(2015, 6, 21)))

(<class '__main__.ProjectApp'>, (2, datetime.datetime(2015, 6, 21, 0, 0)))

[Kevin Qiu]

Thanks for response. How do you delete the joined table object of ProjApp. 

A direct delete won't do:

mydb.session.delete(projApp)

mydb.session.commit()

the following error arises:

"sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "APPLICATION" does not exist

LINE 2: FROM "APPLICATION" JOIN "PROJECT_APP" ON "APPLICATION".app_i..."

I tried google and look at the official documentation http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#eager-loading-of-specific-or-polymorphic-subtypes there weren't any information on that.

--
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/ggKUdxcvVog/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On 6/23/15 6:06 AM, Kevin Qiu wrote:

Thanks for response. How do you delete the joined table object of ProjApp. 

A direct delete won't do:

mydb.session.delete(projApp)

mydb.session.commit()

the following error arises:

"sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "APPLICATION" does not exist

LINE 2: FROM "APPLICATION" JOIN "PROJECT_APP" ON "APPLICATION".app_i..."

name your table names in the SQLAlchemy model, in lower case, which means "case insensitive".   Right now it is looking for a table name "APPLICATION" and won't match the name "application".

[Kevin Qiu]

Thanks a lot. I assume you mean polymorphic_identity refers to the table name not the class name. It would be nice that the document can be point this out explicitly, because the example in the document shows both table name and class name are both small letter, can't tell the difference . 

In my humble suggestion on the joinedload topic on http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

It would be more illustrative to give an example of such, since they are many to one relationship

address = session.query(Addresses).options(joinedload(Addresses.user)).filter_by(email_address='ja...@google.dk')

However, I can see why you choose that example to illustrate that only none repeated record are returned.

One more thing, on the documentation, it's a excellent document, however, it would be great if some essential parts can be extracted and put separately for beginner to get started. For instance, I need the ORM part, the whole ORM section is 400 pages. It's a bit overwhelming when I started.

[Mike Bayer]

On 6/25/15 1:34 PM, Kevin Qiu wrote:

Thanks a lot. I assume you mean polymorphic_identity refers to the table name not the class name. It would be nice that the document can be point this out explicitly, because the example in the document shows both table name and class name are both small letter, can't tell the difference .

I was referring to the table name.   The docs under Core need a section on case sensitivity of table and column in general, this is not explained well enough.

One more thing, on the documentation, it's a excellent document, however, it would be great if some essential parts can be extracted and put separately for beginner to get started. For instance, I need the ORM part, the whole ORM section is 400 pages. It's a bit overwhelming when I started.

The ORM tutorial (http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html) is the "starting" part, just read that.   The front page of the docs (http://docs.sqlalchemy.org/en/rel_1_0/index.html) say "Read This First" in boldface for both ORM and Core, referring to the tutorials, in order to encourage people to consider these two single pages as the starting points.   The rest is all reference.