Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Tue, Nov 8, 2011 at 6:59 AM, Michael Bayer mike...@zzzcomputing.com wrote: OK, here's my attempt. Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. Oh wow, thanks a lot Michael. That looks much cleaner. I'll integrate it in our codebase and see if I can complete the tests. We will bench it in our infra to see how it goes with the real Sync app/data. For the reconnect stuff, I am not sure to understand how things currently work: in case of a connection error in MySQL (2013 and the likes) the engine.execute() method will throw the error and unless I have done things wrong, the error bubbles up and the pool does not attempt to recreate a new connection and run the query again. The small block I have here: https://hg.mozilla.org/services/server-core/file/2.6.1/services/util.py#l621 Does exactly this. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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] per-host pooling vs per-engine pooling
On Nov 8, 2011, at 2:05 AM, Tarek Ziadé wrote: On Tue, Nov 8, 2011 at 6:59 AM, Michael Bayer mike...@zzzcomputing.com wrote: OK, here's my attempt.Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. Oh wow, thanks a lot Michael. That looks much cleaner. I'll integrate it in our codebase and see if I can complete the tests. We will bench it in our infra to see how it goes with the real Sync app/data. For the reconnect stuff, I am not sure to understand how things currently work: in case of a connection error in MySQL (2013 and the likes) the engine.execute() method will throw the error and unless I have done things wrong, the error bubbles up and the pool does not attempt to recreate a new connection and run the query again. OK so statement executions occur in engine/base.py, usually inside of _execute_context(): http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1583 Exceptions are all caught here, and passed to _handle_dbapi_exception: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1634 http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1727 the exception itself is run through the dialect's is_disconnect() method. if this returns True, the entire pool is disposed: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1754 and the exception, re-raised, will have the connection_invalidated flag set: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/exc.py#l219 the MySQL dialects all have a DBAPI-specific way to get at the error message, and examine whether or not its a disconnect. The 2013 code is then checked here: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/dialects/mysql/base.py#l1826 There was a bug regarding this on the MySQL side that was fixed in 0.6.3 in case you're on a very old version. There's no feasible way a database tool could transparently try the query again - a new connection means the transaction has been discarded. It would also require silently squashing very severe errors which can't be assumed to be recoverable.But your own code can check the connection_invalidated flag on the raised exception. -- 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] after_insert mapper event: can't populate target.BACKREF.attr
Hello.
Why comments_after_insert_listener return None for target.report - backref
for TripReport.comments?
class TripReportComment(Base):
__tablename__ = 'trip_report_comments'
id = Column(Integer, primary_key=True)
content = Column(UnicodeText, nullable=False)
report_id = Column(Integer, ForeignKey('trip_reports.id'),
nullable=False)
def comments_after_insert_listener(mapper, connection, target):
print target.report
event.listen(TripReportComment, 'after_insert',
comments_after_insert_listener)
class TripReport(Base):
__tablename__ = 'trip_reports'
id = Column(Integer, primary_key=True)
...
comments = relationship('TripReportComment', backref='report',
cascade='all, delete-orphan', order_by='TripReportComment.id')
--
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/-/xlOoyFWOAZUJ.
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.
Re: [sqlalchemy] after_insert mapper event: can't populate target.BACKREF.attr
On Nov 8, 2011, at 1:34 PM, sector119 wrote:
Hello.
Why comments_after_insert_listener return None for target.report - backref
for TripReport.comments?
when you do...what exactly? this is just a mapping, looks fine.Can you
provide a fully working example please ?
class TripReportComment(Base):
__tablename__ = 'trip_report_comments'
id = Column(Integer, primary_key=True)
content = Column(UnicodeText, nullable=False)
report_id = Column(Integer, ForeignKey('trip_reports.id'), nullable=False)
def comments_after_insert_listener(mapper, connection, target):
print target.report
event.listen(TripReportComment, 'after_insert',
comments_after_insert_listener)
class TripReport(Base):
__tablename__ = 'trip_reports'
id = Column(Integer, primary_key=True)
...
comments = relationship('TripReportComment', backref='report',
cascade='all, delete-orphan', order_by='TripReportComment.id')
--
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/-/xlOoyFWOAZUJ.
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.
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.
Re: [sqlalchemy] A big in the use of the recent mutable feature support?
On Nov 8, 2011, at 4:11 PM, rivka wrote:
Hi,
I am using the new features of the recent 0.7.3 release for support
for mutation tracking (section 2.10.3 in the documentation) verbatim -
following the example in the documentation. So I have:
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
session.py, line 1222, in merge
Before I try anything, this is 0.7b3. There were many bugs in that version.
Can you try the latest 0.7.3 ?
Base = declarative_base()
class JSONEncodedDict(TypeDecorator):
Represents an immutable structure as a json-encoded string.
impl = VARCHAR
def process_bind_param(self, value, dialect) :
if value is not None:
value = json.dumps(value)
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
class MutationDict(Mutable, dict):
@classmethod
def coerce(cls, key, value):
Convert plain dictionaries to MutationDict.
if not isinstance(value, MutationDict) :
if isinstance(value, dict) :
return MutationDict(value)
## this call will raise ValueError
return Mutable.coerce(key, value)
else :
return value
def __setitem__(self, key, value):
Detect dictionary set events and emit change events.
dict.__setitem__(self, key, value)
self.changed()
def __delitem__(self, key):
Detect dictionary del events and emit change events.
dict.__delitem__(self, key)
self.changed()
MutationDict.associate_with(JSONEncodedDict)
==
followed by a number of class / tables that use the JSONEncodedDict to
pack dictionaries into the database:
class PatentInfo(Base):
The main patent info table with references to the secondary tables
__tablename__ = pat_info_main
##id = Column(Integer, primary_key=True)
pNum = Column(Integer, primary_key=True)
pStatus = Column(String)
pAppDate = Column(Integer)
pPubDate = Column(Integer)
pEC = Column(JSONEncodedDict)
pTreeStats = Column(JSONEncodedDict)
pBibInfo = relationship(BibInfo, backref=pat_info_main,
uselist=False)
pClassification = relationship(Classification,
backref=pat_info_main, uselist=False)
pCited = relationship('CitedInfo', backref=pat_info_main,
uselist=False)
def __init__(self, pDat):
PatentInfo Constructor
if isinstance(pDat, PatentDatItem):
self.pNum = pDat.pat_num
self.pStatus = pDat.status
etc.
In the code I am merging newly acquired date into the databas:
pat = PatentInfo(self.pDat)
bib_dat = BibInfo(self.pDat.bib_dat)
class_dat =
Classification(self.pDat.bib_dat.classification)
cite_dat = CitedInfo(self.pDat.cited)
bib_dat.pat_info_main = pat
class_dat.pat_info_main = pat
cite_dat.pat_info_main = pat
self.db.session.merge(pat)
self.db.session.commit()
=
In a number of cases where an actual merge takes place (as opposed to
addition of new data) I got an error message as the following:
Traceback (most recent call last):
File stdin, line 1, in module
File /var/folders/mp/mp8U9z96G784zzlU07ugOU+++TI/-Tmp-/
Python485iEc.py, line 272, in module
File /var/folders/mp/mp8U9z96G784zzlU07ugOU+++TI/-Tmp-/
Python485iEc.py, line 133, in Acquire
File ParseResponse.py, line 107, in ParseXML
self.db.session.merge(pat)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
session.py, line 1222, in merge
load=load, _recursive=_recursive)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
session.py, line 1314, in _merge
load, _recursive)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
properties.py, line 135, in merge
impl.set(dest_state, dest_dict, value, None)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
attributes.py, line 498, in set
value, old, initiator)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/
attributes.py, line 504, in fire_replace_event
value = fn(state, value, previous, initiator or self)
File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/
lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ext/
mutable.py, line 372, in set
