to many statements for collection.append?

[sector119]

Hi.

Why SA produce last UPDATE if I set report_id value?

And why it produces those two SELECTs if I do not perform read access to report and report.comments?

Should not it exec only one insert?

report = session.query(TripReport).get(id)

comment = TripReportComment(content=form.content.data, user=request.user, report_id=form.report_id.data)

report.comments.append(comment)

session.add(report)

SELECT trip_reports.id AS trip_reports_id, trip_reports.title AS trip_reports_ti

tle, trip_reports.content AS trip_reports_content, trip_reports.route AS trip_reports_route, trip_reports.date_start AS t

rip_reports_date_start, trip_reports.date_end AS trip_reports_date_end, trip_reports.type_id AS trip_reports_type_id, tri

p_reports.user_id AS trip_reports_user_id, trip_reports.schedule_id AS trip_reports_schedule_id, trip_reports.create_date

 AS trip_reports_create_date, trip_reports.comments_count AS trip_reports_comments_count

        FROM trip_reports

        WHERE trip_reports.id = '7'

INSERT INTO trip_report_comments (content, user_id, report_id) VALUES ('test content', 6, '7') RETURNING trip_report_comments.id

SELECT trip_report_comments.id AS trip_report_comments_id, trip_report_comments.

content AS trip_report_comments_content, trip_report_comments.create_date AS trip_report_comments_create_date, trip_repor

t_comments.user_id AS trip_report_comments_user_id, trip_report_comments.report_id AS trip_report_comments_report_id

        FROM trip_report_comments

        WHERE 7 = trip_report_comments.report_id ORDER BY trip_report_comments.id

UPDATE trip_report_comments SET report_id=7 WHERE trip_report_comments.id = 5

[Michael Bayer]

On Nov 8, 2011, at 1:47 PM, sector119 wrote:

Hi.

Why SA produce last UPDATE if I set report_id value?

And why it produces those two SELECTs if I do not perform read access to report and report.comments?

Should not it exec only one insert?

again, no idea.  the get() will emit a SELECT.  The TripReportComment constructor will do nothing, unless something else is going on that is causing it to be flushed.  The append to report.comments() will emit another SELECT.  The add() will do nothing.  I'd assume theres a flush() or commit() happening somewhere causing the second UPDATE, which would only occur if the previous value of report_id was not 7, or was expired.

I would greatly appreciate if you could provide full, succinct examples for these, thanks !

report = session.query(TripReport).get(id)

comment = TripReportComment(content=form.content.data, user=request.user, report_id=form.report_id.data)

report.comments.append(comment)

session.add(report)

SELECT trip_reports.id AS trip_reports_id, trip_reports.title AS trip_reports_ti

tle, trip_reports.content AS trip_reports_content, trip_reports.route AS trip_reports_route, trip_reports.date_start AS t

rip_reports_date_start, trip_reports.date_end AS trip_reports_date_end, trip_reports.type_id AS trip_reports_type_id, tri

p_reports.user_id AS trip_reports_user_id, trip_reports.schedule_id AS trip_reports_schedule_id, trip_reports.create_date

 AS trip_reports_create_date, trip_reports.comments_count AS trip_reports_comments_count

        FROM trip_reports

        WHERE trip_reports.id = '7'

INSERT INTO trip_report_comments (content, user_id, report_id) VALUES ('test content', 6, '7') RETURNING trip_report_comments.id

SELECT trip_report_comments.id AS trip_report_comments_id, trip_report_comments.

content AS trip_report_comments_content, trip_report_comments.create_date AS trip_report_comments_create_date, trip_repor

t_comments.user_id AS trip_report_comments_user_id, trip_report_comments.report_id AS trip_report_comments_report_id

        FROM trip_report_comments

        WHERE 7 = trip_report_comments.report_id ORDER BY trip_report_comments.id

UPDATE trip_report_comments SET report_id=7 WHERE trip_report_comments.id = 5

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/YKVkfJu5Q80J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[sector119]

### TEST 1 STATEMENTS

2011-11-09 13:30:24 EET LOG:  statement: BEGIN

2011-11-09 13:30:24 EET LOG:  statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id

        FROM report

        WHERE report.id = 1

2011-11-09 13:30:24 EET LOG:  statement: SELECT users.id AS users_id, users.username AS users_username

        FROM users

        WHERE users.username = 'sector119'

2011-11-09 13:30:24 EET LOG:  statement: SELECT comment.id AS comment_id, comment.content AS comment_content, comment.report_id AS comment_report_id, comment.user_id AS comment_user_id

        FROM comment

        WHERE 1 = comment.report_id ORDER BY comment.id

2011-11-09 13:30:24 EET LOG:  statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', 1, 1) RETURNING comment.id

2011-11-09 13:30:24 EET LOG:  statement: COMMIT

### TEST 2 STATEMENTS

2011-11-09 13:30:24 EET LOG:  statement: BEGIN

2011-11-09 13:30:24 EET LOG:  statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id

        FROM report

        WHERE report.id = 1

2011-11-09 13:30:24 EET LOG:  statement: SELECT users.id AS users_id, users.username AS users_username

        FROM users

        WHERE users.username = 'sector119'

2011-11-09 13:30:24 EET LOG:  statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', NULL, 1) RETURNING comment.id

2011-11-09 13:30:24 EET ERROR:  null value in column "report_id" violates not-null constraint

2011-11-09 13:30:24 EET STATEMENT:  INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', NULL, 1) RETURNING comment.id

2011-11-09 13:30:24 EET LOG:  statement: ROLLBACK

### TEST CASE

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, ForeignKey

from sqlalchemy import Integer, String, UnicodeText

from sqlalchemy import event

from sqlalchemy.orm import relationship

engine = create_engine('postgresql+psycopg2://USER:PASS...@127.0.0.1:5432/test', echo=True)

Session = sessionmaker()

Base = declarative_base()

Session.configure(bind=engine)

Base.metadata.bind = engine

class User(Base):

    __tablename__ = 'users'

   

    id = Column(Integer, primary_key=True)

    username = Column(String(255), unique=True, nullable=False)

class Comment(Base):

    __tablename__ = 'comment'

   

    id = Column(Integer, primary_key=True)

    content = Column(UnicodeText, nullable=False)

    report_id = Column(Integer, ForeignKey('report.id'), nullable=False)

    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)

   

    user = relationship('User', backref='comments')

class Report(Base):

    __tablename__ = 'report'

   

    id = Column(Integer, primary_key=True)

    content = Column(UnicodeText, nullable=False)

    comments_count = Column(Integer, server_default='0', nullable=False)

    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)

   

    user = relationship('User', backref='reports')

    comments = relationship('Comment', backref='report', cascade='all, delete-orphan', order_by='Comment.id')

Base.metadata.create_all(engine)

session = Session()

user = User(username='sector119')

session.add(user)                          

      session.flush()

session.commit()

session.close()

session = Session()

user = session.query(User).filter_by(username='sector119').one()

report = Report(content=u'test report', user=user)

session.add(report)

session.flush()

session.commit()

session.close()

session = Session()

### TEST 1

report = session.query(Report).get(1)

user = session.query(User).filter_by(username='sector119').one()

comment = Comment(content=u'test comment', user_id=user.id)

report.comments.append(comment)

session.add(report)

session.flush()

session.commit()

session.close()

### TEST 2

# HERE I GOT ERROR if I uncomment "user = ..." line

session = Session()

report = session.query(Report).get(1)

#user = session.query(User).filter_by(username='sector119').one()

comment = Comment(content=u'test comment', user=user)

report.comments.append(comment)

session.add(report)

session.flush()

session.commit()

session.close()

2011-11-09 13:22:10,270 INFO sqlalchemy.engine.base.Engine INSERT INTO comment (content, report_id, user_id) VALUES (%(content)s, %(report_id)s, %(user_id)s) RETURNING comment.id

2011-11-09 13:22:10,270 INFO sqlalchemy.engine.base.Engine {'content': u'test comment', 'user_id': 1, 'report_id': None}

2011-11-09 13:22:10,270 INFO sqlalchemy.engine.base.Engine ROLLBACK

Traceback (most recent call last):

  File "test_append.py", line 92, in <module>

    report.comments.append(comment)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 168, in __get__

    return self.impl.get(instance_state(instance),dict_)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 453, in get

    value = self.callable_(state, passive)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line 563, in _load_for_state

    result = q.all()

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/query.py", line 1922, in all

    return list(self)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2031, in __iter__

    self.session._autoflush()

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 973, in _autoflush

    self.flush()

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1547, in flush

    self._flush(objects)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1616, in _flush

    flush_context.execute()

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 328, in execute

    rec.execute(self)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 472, in execute

    uow

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2221, in _save_obj

    execute(statement, params)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1399, in execute

    params)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1640, in _execute_context

    context)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1633, in _execute_context

    context)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/engine/default.py", line 330, in do_execute

    cursor.execute(statement, parameters)

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

 'INSERT INTO comment (content, report_id, user_id) VALUES (%(content)s, %(report_id)s, %(user_id)s) RETURNING comment.id' {'content': u'test comment', 'user_id': 1, 'report_id': None}

                        

[sector119]

Can't get why, but in Pyramid, the same code works, but generate on more UPDATE statement

report = session.query(TripReport).get(id)

user = session.query(User).filter_by(username='sector119').one()

comment = TripReportComment(content=form.content.data, user=user)

report.comments.append(comment)

session.add(report)

2011-11-09 13:36:35 EET LOG:  statement: BEGIN

2011-11-09 13:36:35 EET LOG:  statement: SELECT trip_reports.id AS trip_reports_id, trip_reports.title AS trip_reports_title, trip_reports.content AS trip_reports_content, trip_reports.route AS trip_reports_route, trip_reports.date_start AS trip_reports_date_start, trip_reports.date_end AS trip_reports_date_end, trip_reports.type_id AS trip_reports_type_id, trip_reports.user_id AS trip_reports_user_id, trip_reports.schedule_id AS trip_reports_schedule_id, trip_reports.create_date AS trip_reports_create_date, trip_reports.comments_count AS trip_reports_comments_count

        FROM trip_reports

        WHERE trip_reports.id = '7'

2011-11-09 13:36:35 EET LOG:  statement: SELECT users.password AS users_password, users.id AS users_id, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birthday AS users_birthday, users.sex AS users_sex, users.email AS users_email, users.organization AS users_organization, users.website AS users_website, users.signature AS users_signature, users.subscribed AS users_subscribed, users.create_date AS users_create_date, users.last_login AS users_last_login, users.disabled AS users_disabled

        FROM users

        WHERE users.username = 'sector119'

2011-11-09 13:36:35 EET LOG:  statement: INSERT INTO trip_report_comments (content, user_id, report_id) VALUES ('test3', 6, NULL) RETURNING trip_report_comments.id

2011-11-09 13:36:35 EET LOG:  duration: 1.014 ms

2011-11-09 13:36:35 EET LOG:  statement: SELECT trip_report_comments.id AS trip_report_comments_id, trip_report_comments.content AS trip_report_comments_content, trip_report_comments.create_date AS trip_report_comments_create_date, trip_report_comments.user_id AS trip_report_comments_user_id, trip_report_comments.report_id AS trip_report_comments_report_id

        FROM trip_report_comments

        WHERE 7 = trip_report_comments.report_id ORDER BY trip_report_comments.id

### HERE IS UPDATE :/

2011-11-09 13:36:35 EET LOG:  statement: UPDATE trip_report_comments SET report_id=7 WHERE trip_report_comments.id = 18

2011-11-09 13:36:35 EET LOG:  statement: COMMIT

[sector119]

In testcase above this works too:

session = Session()

user = session.query(User).filter_by(username='sector119').one()

session.close()

session = Session()

report = session.query(Report).get(1)

comment = Comment(content=u'test comment', user=user)

report.comments.append(comment)

session.add(report)

session.flush()

session.commit()

session.close()

2011-11-09 13:43:28 EET LOG:  statement: BEGIN

2011-11-09 13:43:28 EET LOG:  statement: INSERT INTO users (username) VALUES ('sector119') RETURNING users.id

2011-11-09 13:43:28 EET LOG:  statement: COMMIT

2011-11-09 13:43:28 EET LOG:  statement: BEGIN

2011-11-09 13:43:28 EET LOG:  statement: SELECT users.id AS users_id, users.username AS users_username

        FROM users

        WHERE users.username = 'sector119'

2011-11-09 13:43:28 EET LOG:  statement: INSERT INTO report (content, user_id) VALUES ('test report', 1) RETURNING report.id

2011-11-09 13:43:28 EET LOG:  statement: COMMIT

2011-11-09 13:43:28 EET LOG:  statement: BEGIN

2011-11-09 13:43:28 EET LOG:  statement: SELECT users.id AS users_id, users.username AS users_username

        FROM users

        WHERE users.username = 'sector119'

2011-11-09 13:43:28 EET LOG:  statement: ROLLBACK

2011-11-09 13:43:28 EET LOG:  statement: BEGIN

2011-11-09 13:43:28 EET LOG:  statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id

        FROM report

        WHERE report.id = 1

2011-11-09 13:43:28 EET LOG:  statement: SELECT comment.id AS comment_id, comment.content AS comment_content, comment.report_id AS comment_report_id, comment.user_id AS comment_user_id

        FROM comment

        WHERE 1 = comment.report_id ORDER BY comment.id

2011-11-09 13:43:28 EET LOG:  statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', 1, 1) RETURNING comment.id

2011-11-09 13:43:28 EET LOG:  statement: COMMIT

[Michael Bayer]

On Nov 9, 2011, at 3:37 AM, sector119 wrote:

>
> ### TEST 2
> # HERE I GOT ERROR if I uncomment "user = ..." line
>
> session = Session()
>
> report = session.query(Report).get(1)
> #user = session.query(User).filter_by(username='sector119').one()
> comment = Comment(content=u'test comment', user=user)
> report.comments.append(comment)
> session.add(report)
> session.flush()
> session.commit()
>
> session.close()
>

issue is here:

# create new Comment object, user=user means "comment" is now present
# in the Session. The "report" reference is None.

comment = Comment(content=u'test comment', user=user)

# steps here are:
# 1. access report.comments
# 2. report.comments not present, loads from the database
# 3. autoflush
# 4. append "comment" to collection
report.comments.append(comment)

the above fails as autoflush flushes the incomplete "comment" object. this can be seen in the stack trace:

> self.session._autoflush()
> File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 973, in _autoflush

solution is to set the "report" on the "comment" instead, or to temporarily disable autoflush, such as at

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush