SHARE
TWEET

Untitled

a guest May 22nd, 2017 49 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # -*- coding: utf-8 -*-
  2. import psycopg2 as postgres
  3. import datetime
  4.  
  5. if __name__ == '__main__':
  6.     # connection
  7.     conn = postgres.connect(host='localhost', port='5432',dbname='foo', user='user', password='password')
  8.     cur = conn.cursor()
  9.     # fecth records count
  10.     now = datetime.datetime.now()
  11.     cur.execute('SELECT * FROM KNOWLEDGES WHERE INSERT_DATETIME BETWEEN %s AND %s',\
  12.                 ((now - datetime.timedelta(days=7)).date(), now.date()))
  13.     print(cur.rowcount)
  14.     # 4
  15.  
  16.     # fecth favolite data
  17.     fav_sql = '''
  18.     SELECT
  19.             KNOWLEDGES.TITLE
  20.             ,USERS.USER_NAME AS INSERT_USER_NAME
  21.             ,UP_USER.USER_NAME AS UPDATE_USER_NAME
  22.             ,COUNT(LIKES.NO) AS LIKE_COUNT_ON_TERM
  23.         FROM
  24.             KNOWLEDGES
  25.                 LEFT OUTER JOIN LIKES
  26.                     ON (
  27.                         KNOWLEDGES.KNOWLEDGE_ID = LIKES.KNOWLEDGE_ID
  28.                         AND LIKES.INSERT_DATETIME BETWEEN %s AND %s
  29. )
  30.                 LEFT OUTER JOIN USERS
  31.                     ON USERS.USER_ID = KNOWLEDGES.INSERT_USER
  32.                 LEFT OUTER JOIN USERS AS UP_USER
  33.                     ON UP_USER.USER_ID = KNOWLEDGES.UPDATE_USER
  34.         WHERE
  35.             KNOWLEDGES.DELETE_FLAG = 0
  36.         GROUP BY
  37.             KNOWLEDGES.KNOWLEDGE_ID, USERS.USER_NAME, UP_USER.USER_NAME
  38.         ORDER BY
  39.             COUNT(LIKES.NO) DESC
  40.             ,KNOWLEDGES.UPDATE_DATETIME DESC
  41.         LIMIT 10 OFFSET 0;
  42.     '''
  43.     cur.execute(fav_sql, ((now - datetime.timedelta(days=7)).date(), now.date()))
  44.     print(cur.fetchall())
  45.     # [('sss', None, None, 0), ('テスト4', None, None, 0), ('テスト3', None, None, 0), ('テスト2', None, None, 0), ('テスト1', None, None, 0)]
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top