list_news() done!

1 files changed, 15 insertions, 13 deletions

diff --git a/famille.py b/famille.py
index db06b94..8124b58 100644
--- a/famille.py
+++ b/famille.py
@@ -13,7 +13,7 @@ from email import utils
 from bs4 import BeautifulSoup
 from flask_mail import Mail, Message
 import locale
-from sqlalchemy import create_engine, MetaData, Table
+from sqlalchemy import create_engine, MetaData, Table, select, func
 from sqlalchemy.orm import scoped_session, sessionmaker
 
 locale.setlocale(locale.LC_ALL, 'fr_FR.UTF-8')
@@ -74,8 +74,8 @@ def pluralize(word, count, plural=None):
     else:
         return "{0} {1}s".format(count, word)
 
-def query_db(db, query, args=(), one=False):
-    cur = db.execute(query, args)
+def query_db(stmt, one=False):
+    cur = db_session.execute(stmt)
     rv = cur.fetchone() if one else cur.fetchall()
     cur.close()
     return rv
@@ -103,15 +103,17 @@ def list_news():
     users = Table('users', metadata, autoload=True)
     news = Table('news', metadata, autoload=True)
     comments = Table('comments', metadata, autoload=True)
-    news = query_db(db, "SELECT news.*, users.user_name, count(comments.id) as ncomments FROM news "
-    "LEFT JOIN users ON news.user_id = users.id "
-    "LEFT JOIN comments ON news.id=comments.news_id GROUP BY news.id "
-    "ORDER BY news.date DESC")
-    comments = query_db(db, "SELECT * from comments LEFT JOIN users "
-                        "ON comments.user_id = users.id ORDER BY date DESC LIMIT 5")
-    users = query_db(db, "SELECT * from users ORDER BY last_seen DESC")
-    return render_template("news/list.html", news=news, comments=comments,
-                           users=users)
+
+    stmt = select([news, users.c.user_name, func.count(comments.c.id).label('ncomments')]).\
+      select_from(news.outerjoin(users).outerjoin(comments, comments.c.news_id==news.c.id)).\
+      group_by(news.c.id).order_by(news.c.date.desc())
+    list_news = query_db(stmt)
+    stmt = select([comments]).select_from(comments.outerjoin(users)).order_by(comments.c.date.desc()).limit(5)
+    list_comments = query_db(stmt)
+    stmt = select([users]).order_by(users.c.last_seen.desc())
+    list_users = query_db(stmt)
+    return render_template("news/list.html", news=list_news, comments=list_comments,
+                           users=list_users)
 
 @app.route('/news/add/', methods=['GET', 'POST'])
 @login_required
@@ -243,7 +245,7 @@ def login():
         users = Table('users', metadata, autoload=True)
         username = request.form['username']
         password = hashlib.md5(request.form['password']).hexdigest()
-        user = users.select(users.c.user_name == username).execute().first()
+        user = users.select(users.c.user_name == username).execute().fetchone()
 
         if user:
             if user['password'] == password: