# coding=utf-8

from django.core.paginator import Paginator


def paginator(data_list, per_page, page_no):
  """封裝Django分頁"""
  pages = Paginator(data_list, per_page)

  # 防止超出頁數(shù)
  if not page_no > 0:
    page_no = 1
  if page_no > pages.num_pages:
    page_no = pages.num_pages

  p = pages.page(page_no) # 獲取本頁數(shù)據(jù)

  data = dict() # 獲取分頁信息
  data['count'] = pages.count
  data['page_num'] = pages.num_pages
  data['per_page'] = per_page
  data['current'] = page_no
  data['start_index'] = p.start_index() - 1

  return p.object_list, page_no, data


class QueryWrapper(object):
  """查詢集包裝器。實(shí)現(xiàn)django Paginator需要的必要方法，實(shí)現(xiàn)和query一樣使用Paginator分頁"""

  def __init__(self, sql, params=None, db="default"):
    """
    :param sql: sql語句
    :param params: sql語句的params參數(shù)
    :param db: 數(shù)據(jù)庫名稱（Django配置）
    """
    self.db = db
    self.sql = sql
    self.params = params

  def count(self):
    """計(jì)算總頁數(shù)"""
    sql = """select count(*) from (%s) _count""" % self.sql
    # sql封裝方法請(qǐng)參考https://my.oschina.net/watcher/blog/1573503
    return fetchone_sql((sql, self.params), db=self.db, flat=True) # 返回總頁數(shù)

  def __getslice__(self, x, y):
    """ self.__getslice(x, y) = self[x:y]"""
    sql = self.sql + ' LIMIT {start}, {num}'.format(start=x, num=y - x)
    # sql封裝方法請(qǐng)參考https://my.oschina.net/watcher/blog/1573503
    return fetchall_to_dict((sql, self.params), db=self.db) # 字典列表形式返回


def demo_orm():
  """使用Django的ORM分頁"""
  # 示例：查詢status=1的用戶分頁，每頁10條，取第2頁數(shù)據(jù)（假設(shè)數(shù)據(jù)量足夠）
  status = 1
  per_page = 10
  page_no = 2

  # 使用Django的ORM
  from django.contrib.auth.models import User

  query = User.objects.filter(status=status).values("id", "username", "first_name")
  one_page_data_list, page_no, page_data = paginator(query, per_page, page_no)
  # one_page_data_list 即為第二頁數(shù)據(jù)，例如：[{"id": 1, "username": "111", "first_name": "aaa"}]
  print one_page_data_list


def demo_raw():
  """使用原生sql實(shí)現(xiàn)相同分頁"""
  # 示例：查詢status=1的用戶分頁，每頁10條，取第2頁數(shù)據(jù)（假設(shè)數(shù)據(jù)量足夠）
  status = 1
  per_page = 10
  page_no = 2

  sql = "select id, username, first_name from auth_user where status=%(status)s"
  params = {"status": status} # 使用params防止sql注入
  query = QueryWrapper(sql, params, "default")
  one_page_data_list, page_no, page_data = paginator(query, per_page, page_no)
  # one_page_data_list 同ORM獲取數(shù)據(jù)一樣
  print one_page_data_list


if __name__ == "__main__":
  demo_orm()
  demo_raw()