Hi,
I have a rather complex query that I'd like to execute through SA on a
Postgres dababase.
This is the query:
SELECT questions.id, questions.question,
(SELECT count(attempts.id) AS count_1
FROM attempts
WHERE attempts.question_id = questions.id AND attempts.is_correct =
True) AS anon_1,
(SELECT count(attempts.id) AS count_2
FROM attempts
WHERE attempts.question_id = questions.id AND attempts.is_correct =
True) AS anon_2,
((select count(*)::float from attempts a where questions.id =
a.question_id and a.is_correct) /
(select count(*)::float from attempts a where questions.id =
a.question_id )) as ratio
FROM questions;
It is in most aspects created using SA, with the following code:
qt = model.Question.table
at = model.Attempt.table
q = select([qt.c.id,
qt.c.question,
select([func.count(at.c.id)],
and_(at.c.question_id == qt.c.id,
at.c.is_correct == False)).as_scalar(),
select([func.count(at.c.id)],
and_(at.c.question_id == qt.c.id,
at.c.is_correct == True)).as_scalar(),
text("""
((select count(*)::float from attempts a where questions.id =
a.question_id and a.is_correct) /
(select count(*)::float from attempts a where questions.id =
a.question_id )) as ratio
""")
])
But it fails with
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) unrecognized token: ":" ...
So - my problem is that the ::float-annotations aren't passed through.
Is that a bug?
Additonally, I'd of course prefer to formulate the subqueries using SA
as well - but that fails as I can't divide selects.
Diez
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
