Hi SQLAlchemy,
Our data team wants us to use a (SQL Server 2008 R2) stored procedure to
perform our major query, which is all well and good, except it's preventing
SQLAlchemy's type processing from being applied. This is on SQLAlchemy
0.9.1, using pyodbc and FreeTDS.
For example, say we are trying to map this class (using Flask-SQLAlchemy):
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
random_time = db.Column(UTCDateTime)
def __init__(self, username, email):
self.username = username
self.email = email
self.random_time = datetime.now()
Using this (trivial) user-defined type:
class UTCDateTime(db.TypeDecorator):
impl = db.DateTime
def process_result_value(self, value, dialect):
print "AWOOOOOGA"
return value
Create the table and populate it with some values:
db.create_all()
db.session.add(User('alice', 'al...@gmail.com'))
db.session.add(User('bob', 'b...@gmail.com'))
db.session.commit()
users = db.session.query(User).all()
Two AWOOGAs are output, as expected.
Then, create a stored procedure like this:
CREATE PROCEDURE "GetUser" AS
SELECT
*
FROM "user"
GO
And query into User objects using the procedure:
db.session.add(User('charlie', 'char...@gmail.com'))
db.session.commit()
text = db.text('exec getuser')
users = db.session.query(User).from_statement(text).all()
The resulting User objects look reasonable, *but no AWOOOOGAs*, and the
strings are all bytestrings.
After looking at the docs more closely, this isn't very surprising: text()
does warn about a lack of type processing, and suggests using
text().columns() to provide a mapping (in lieu of the now-deprecated
typemap kwarg to text()). This creates a TextAsFrom object, which adds some
extra superpowers to text() including a .c attribute. Problem is,
from_statement() doesn't like it:
db.session.commit()
typemap = {'id': db.Integer, 'username': db.String, 'email': db.String,
'random_time': UTCDateTime}
taf = text.columns(**typemap)
users = db.session.query(User).from_statement(taf).all()
This results in a stack trace:
AttributeError Traceback (most recent call last)
<ipython-input-20-c694595d6ec1> in <module>()
----> 1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279 """
-> 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __iter__(self)
2386
2387 def __iter__(self):
-> 2388 context = self._compile_context()
2389 context.statement.use_labels = True
2390 if self._autoflush and not self._populate_existing:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in _compile_context(self, labels)
2732
2733 def _compile_context(self, labels=True):
-> 2734 context = QueryContext(self)
2735
2736 if context.statement is not None:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __init__(self, query)
3478 if query._statement is not None:
3479 if isinstance(query._statement, expression.SelectBase)
and \
-> 3480 not query._statement.use_labels:
3481 self.statement = query._statement.apply_labels()
3482 else:
AttributeError: 'TextAsFrom' object has no attribute 'use_labels'
Looks like TextAsFrom isn't quite select-like enough for from_statement().
I tried tacking on a taf.use_labels = True before running the query, but
just got another error:
NoSuchColumnError Traceback (most recent call last)
<ipython-input-23-c694595d6ec1> in <module>()
----> 1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279 """
-> 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in instances(query, cursor, context)
70 process[0](row, rows)
71 elif single_entity:
---> 72 rows = [process[0](row, None) for row in fetch]
73 else:
74 rows = [util.KeyedTuple([proc(row, None) for proc in
process],
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in _instance(row, result)
358 identitykey = (
359 identity_class,
--> 360 tuple([row[column] for column in
pk_cols])
361 )
362
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/engine/result.pyc
in _key_fallback(self, key, raiseerr)
315 raise exc.NoSuchColumnError(
316 "Could not locate column in row for column
'%s'" %
--> 317 expression._string_or_unprintable(key))
318 else:
319 return None
NoSuchColumnError: "Could not locate column in row for column 'user.id'"
Any ideas? Incidentally, we can use the taf object in a session.execute()
and get great results back, type processing and all. Problem is, they're
just tuples (or a ResultProxy before you fetchall or iterate over it). Any
way to convince SQLAlchemy to turn that result set into User objects, or at
that point should we just send those to User() ourselves?
Thanks,
Matt
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
