CODEWITHSUNDEEP
pythonmysqlormsqlalchemyalchemy
Coati
Coati

Reputation: 21

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

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?

Upvotes: 0

Views: 1409

Answers (2)

Coati
Coati

Reputation: 21

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)

Upvotes: 0

Sam
Sam

Reputation: 4090

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...

Upvotes: 0

Related Questions