I've got a MySQL warning that's sabotaging my table-update program.
The program does a bunch of ALTER TABLE (via engine.execute()) and
then does:
q = session.query(Incident).select(order_by=[Incident.c.orr_id])
for i in q:
# Check and update several fields in i.
session.flush()
The warning occurs when it flushes the first record, and is
meaningless: it says "warnings: 0". Normally MySQL raises a warning
if a value is too long for a field, is autoconverted from an illegal
value, etc. MySQLdb turns these into exceptions, though there is an
optional CursorNW cursor that ignores the warnings. But I wouldn't
know how to set that cursor in SQLAlchemy, or to get it to "SHOW
WARNINGS" in the middle of the flush. (Apparently the warnings list
is cleared before each SQL statement that raises warnings, and is not
accessible from another connection.) The actual traceback is:
2007-05-11 16:17:54,025 INFO sqlalchemy.engine.base.Engine.0x..b4
UPDATE `Incident` SET name=%s, location=%s, is_prfa=%s,
ptl_is_mass=%s, actl_is_mass=%s WHERE `Incident`.orr_id = %s
INFO:sqlalchemy.engine.base.Engine.0x..b4:UPDATE `Incident` SET
name=%s, location=%s, is_prfa=%s, ptl_is_mass=%s, actl_is_mass=%s
WHERE `Incident`.orr_id = %s
2007-05-11 16:17:54,025 INFO sqlalchemy.engine.base.Engine.0x..b4
[u'Tarballs', u'Lake Worth, FL', False, False, False, 1001L]
INFO:sqlalchemy.engine.base.Engine.0x..b4:[u'Tarballs', u'Lake Worth,
FL', False, False, False, 1001L]
2007-05-11 16:17:54,026 INFO sqlalchemy.engine.base.Engine.0x..b4 ROLLBACK
INFO:sqlalchemy.engine.base.Engine.0x..b4:ROLLBACK
Traceback (most recent call last):
File "bin/adhoc_change_fields.py", line 125, in ?
session.flush()
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/session.py",
line 302, in flush
self.uow.flush(self, objects)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/unitofwork.py",
line 219, in flush
flush_context.execute()
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/unitofwork.py",
line 409, in execute
UOWExecutor().execute(self, head)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/unitofwork.py",
line 1027, in execute
self.execute_save_steps(trans, task)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/unitofwork.py",
line 1041, in execute_save_steps
self.save_objects(trans, task)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/unitofwork.py",
line 1032, in save_objects
task.mapper.save_obj(task.polymorphic_tosave_objects, trans)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/mapper.py",
line 1166, in save_obj
c = connection.execute(statement, params)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py",
line 509, in execute
return Connection.executors[c](self, object, *multiparams, **params)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py",
line 549, in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py",
line 560, in execute_compiled
self._execute_raw(context)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py",
line 573, in _execute_raw
self._execute(context)
File
"/usr/local/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py",
line 591, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (Warning) Rows matched: 1 Changed: 1
Warnings: 0 u'UPDATE `Incident` SET name=%s, location=%s, is_prfa=%s,
ptl_is_mass=%s, actl_is_mass=%s WHERE `Incident`.orr_id = %s'
[u'Tarballs', u'Lake Worth, FL', False, False, False, 1001L]
Of course this works perfectly on my development machine but crashes
on the production server, where I've shut down the website to make
these structural changes. Since it's either not showing the warning
or the warning incomprehensible, I'll have to redo the modification
code in non-ORM or if that doesn't work, straight MySQLdb, so I can
get the site up ASAP.
I'm not sure what SQLAlchemy should do in this case, but maybe
somebody has an idea.
--
Mike Orr <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
