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?
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)