# -*- coding: utf-8 -*-
import psycopg2 as postgres
import datetime

if __name__ == '__main__':
    # connection
    conn = postgres.connect(host='localhost', port='5432',dbname='foo', user='user', password='password')
    cur = conn.cursor()
    # fecth records count
    now = datetime.datetime.now()
    cur.execute('SELECT * FROM KNOWLEDGES WHERE INSERT_DATETIME BETWEEN %s AND %s',\
                ((now - datetime.timedelta(days=7)).date(), now.date()))
    print(cur.rowcount)
    # 4

    # fecth favolite data
    fav_sql = '''
    SELECT
            KNOWLEDGES.TITLE
            ,USERS.USER_NAME AS INSERT_USER_NAME
            ,UP_USER.USER_NAME AS UPDATE_USER_NAME
            ,COUNT(LIKES.NO) AS LIKE_COUNT_ON_TERM
        FROM
            KNOWLEDGES
                LEFT OUTER JOIN LIKES
                    ON (
                        KNOWLEDGES.KNOWLEDGE_ID = LIKES.KNOWLEDGE_ID
                        AND LIKES.INSERT_DATETIME BETWEEN %s AND %s
)
                LEFT OUTER JOIN USERS
                    ON USERS.USER_ID = KNOWLEDGES.INSERT_USER
                LEFT OUTER JOIN USERS AS UP_USER
                    ON UP_USER.USER_ID = KNOWLEDGES.UPDATE_USER
        WHERE
            KNOWLEDGES.DELETE_FLAG = 0
        GROUP BY
            KNOWLEDGES.KNOWLEDGE_ID, USERS.USER_NAME, UP_USER.USER_NAME
        ORDER BY
            COUNT(LIKES.NO) DESC
            ,KNOWLEDGES.UPDATE_DATETIME DESC
        LIMIT 10 OFFSET 0;
    '''
    cur.execute(fav_sql, ((now - datetime.timedelta(days=7)).date(), now.date()))
    print(cur.fetchall())
    # [('sss', None, None, 0), ('テスト4', None, None, 0), ('テスト3', None, None, 0), ('テスト2', None, None, 0), ('テスト1', None, None, 0)]