rowproxy not gracefully handling processor errors

[Wichert Akkerman]

I am trying to generate a query which aggregates data by day based on a datetime column. Casting the column appears to break the query though. I boiled this down to this test case:

import datetime
import sqlalchemy
from sqlalchemy import schema
from sqlalchemy import sql
from sqlalchemy import types
from sqlalchemy.sql import func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

metadata = schema.MetaData()
BaseObject = declarative_base(metadata=metadata)

class Article(BaseObject):
    __tablename__ = "article"

    id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
    created = schema.Column(types.DateTime(timezone=False),
            default=datetime.datetime.utcnow)

engine = sqlalchemy.create_engine('sqlite:///:memory:')
metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()
session.add(Article())

day = sql.cast(Article.created, types.Date())
query = session.query(day, func.count(Article.id))
print query.all()

Running that results in:

Traceback (most recent call last):
  File "/tmp/x.py", line 29, in <module>
    print query.all()
  File "/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1947, in all
    return list(self)
  File "/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2181, in instances
    labels) for row in fetch]
  File "/home/wichert/lib/buildout/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 3165, in proc
    return row[column](column)
TypeError: expected string or Unicode object, int found

[Mike Bayer]

the CAST in SQLite here, which doesn't support dates, is returning the integer 2012. So the SQLite handler for datestrings fails. The issue here is just one of error reporting being hard to see.

[Mike Bayer]

without the c extension, this makes it clearer, but need advice on how the cextension might be changed here.

diff -r 8c05a3bf6599f92bbf8d2246123597e8966f3a52 lib/sqlalchemy/processors.py
--- a/lib/sqlalchemy/processors.py  Wed Jan 18 12:42:54 2012 -0500
+++ b/lib/sqlalchemy/processors.py  Sun Jan 22 10:40:25 2012 -0500
@@ -24,9 +24,12 @@
         if value is None:
             return None
         else:
-            m = rmatch(value)
+            try:
+                m = rmatch(value)
+            except TypeError:
+                m = None
             if m is None:
-                raise ValueError("Couldn't parse %s string." % type_.__name__)
+                raise ValueError("Couldn't parse %s string: %r" % (type_.__name__ , value))
             return type_(*map(int, m.groups(0)))
     return process

[Mike Bayer]

OK I think I'll have the error reporting better here. But note your original issue with the CAST is that SQLite doesn't support CAST of the string datetime like that as it has no native date support. You'd need to work around that in a different way, unfortunately.

[Mike Bayer]

OK 959d7cbe33ee2e39e2a43b6e7a9c1a750664d569 has test coverage and implementation for very clear error messages both with/without C extensions. Closing this as "fixed" though I'm going to get some people to look over the C changes too.

[Mike Bayer]

Removing milestone: 0.7.5 (automated comment)