SQL Alchemy ORM and Text of same query returns different results?

Question

I currently have a script in Python using SQL Alchemy’s text function to execute queries to gather stats about my Facebook posts. I am attempting to update it to use the ORM, however I am getting different results for what seem to me to be the same queries. For example one text query is as follows:

mysql> SELECT COUNT(facebook_posts.id) AS temphold
FROM facebook_posts
WHERE shares < 10  AND account_id=‘12345’ AND article_id IS NOT NULL
AND date(created_time) > '2016-10-01' AND date(created_time) < '2016-10-05';

+----------+
| temphold |
+----------+
|      104 |
+----------+

1 row in set (0.02 sec)

And then with the ORM:

under_10 = session.query(func.count(FacebookPost.id)).\
                         filter(FacebookPost.shares < 10,
                                FacebookPost.account_id == '12345',
                                FacebookPost.article_id != None).\
                        filter(FacebookPost.created_time > start,
                               FacebookPost.created_time < end)

under_10 = session.execute(under_10)

When I print the query appears to be the same:

SELECT count(facebook_posts.id) AS count_1 
FROM facebook_posts 
WHERE facebook_posts.shares < 10 
AND facebook_posts.account_id = '12345' 
AND facebook_posts.article_id IS NOT NULL 
AND facebook_posts.created_time > 2016-10-01 
AND facebook_posts.created_time < 2016-10-05

(150L,)

It appears to be the same, but the results are different - 104 vs 150. Why is this happening?

Well, that's because they are not the same query. `date(created_time) > '2016-10-01'` and `created_time > '2016-10-01'` are different criteria. The timestamp `2016-10-01 01:00:00` matches the latter but not the former. — univerio, Nov 07 '16 at 21:37
Yes! That was indeed the problem... and quite embarrassing that I missed that and was stuck on it for hours :-( Thanks so much!!! — Coati, Nov 07 '16 at 22:37

score 0 · Answer 1 · answered Nov 07 '16 at 20:30

0

Your SQL statement is bad syntax. The # is turning the line into a comment and AND article_id IS NOT NULL is not being implemented. Try changing it to

...WHERE shares < 10  AND account_id="account#"
 AND article_id IS NOT NULL...

answered Nov 07 '16 at 20:30

Sam

4,000
20
27

The # isn't actually in my code I just didn't want to but the number I'm using so it is actually: session.query(func.count(FacebookPost.id)).\ filter(FacebookPost.shares<10, FacebookPost.account_id=='12345', FacebookPost.article_id!=None).\ filter(FacebookPost.created_time>start, FacebookPost.created_time – Coati Nov 07 '16 at 20:46
did you try changing curly quotes to straight quotes? – Sam Nov 07 '16 at 21:25

score 0 · Accepted Answer · edited May 23 '17 at 12:13

Thanks to univerio's comment above I went looking for how to specify DATE() in SQL Alchemy and got it working by changing it to the following:

under_10 = session.query(func.count(FacebookPost.id)).\
                         filter(FacebookPost.shares < 10, 
                                FacebookPost.account_id == '1234',
                                FacebookPost.article_id != None).\
                        filter(func.date(FacebookPost.created_time) > start,
                               func.date(FacebookPost.created_time) < end)

SQL Alchemy ORM and Text of same query returns different results?

2 Answers2