Re: [sqlalchemy] to many statements for collection.append?
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 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] to many statements for collection.append?
### 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:PASSWORD@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
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(
Re: [sqlalchemy] to many statements for collection.append?
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 sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] to many statements for collection.append?
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
--
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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
