Python automatic operation and maintenance development: basic Mysql Database Paging and paging in Python code

Back end database paging is implemented according to limit

The first writing method: limit the number of pages offset start position

select id,name,sex,tel,addr from user limit 1 offset 1;
select * from student limit pageSize offset (pageNumber-1)*pageSize

The second writing method: limit starting position, number of pages

select id,name from user limit 1,2;
select * from studnet limit (pageNumber-1)*pageSize,pageSize

When the data volume is large, sub query is used for paging

select id,name from user where id >= (select id from user order by id limit 1,1) limit 2;
Select id,name from user where id > = (select id from user order by ID limit, 1) limit the number of pages;

Pagination of background data in Python code

views.py file

The Page() function is a method of the inner class of mypage.py

User? Count() is the method to get the total number of mysql data

def index(request):
    page_num = request.GET.get("page")
    login_user = request.session.get('user')
    count = user_count()
    per_page = 2
    page_obj = Page(page_num, count,url_prefix="/users/",per_page=2, max_page=4,)
    page_num = page_obj.num
    users = mysql_page(per_page,page_num)
    page_html = page_obj.page_html()
    return render(request,'static/index.html',{"users": users,"login_user": login_user,"page_html": page_html})

mypage.py

class Page():

    def __init__(self, page_num, total_count, url_prefix, per_page, max_page=4):
        """
        :param page_num: Current page number
        :param total_count: Total data
        :param url_prefix: a Label href Prefix
        :param per_page: How many pieces of data are displayed per page
        :param max_page: Maximum number of pages displayed on the page
        """
        self.url_prefix = url_prefix
        self.max_page = max_page
        #How many pieces of data are displayed on each page
        #How many pages are needed to show
        total_page, m = divmod(total_count, per_page)
        if m:
            total_page += 1
        self.total_page = total_page

        try:
            page_num = int(page_num)
            #If the number of pages entered exceeds the maximum number of pages, the last page will be returned by default
            if page_num > total_page:
                page_num = total_page
        except Exception as e:
            #When the entered page number is not a positive number, the data of the first page will be returned by default
            page_num = 1
        self.page_num = page_num


        #How many pages are displayed on the page
        if total_page < self.max_page:
            self.max_page = total_page

    @property
    def num(self):
        return self.page_num

    def page_html(self):
        #Make your own HTML code
        html_str_list = []
        #Add the first page
        html_str_list.append('<li><a href="{}?page=1">home page</a></li>'.format( self.url_prefix))

        #Judge if it is the first page, there is no previous page
        if self.page_num <= 1:
            html_str_list.append('<li class="disabled"><a href="#"><span aria-hidden="true">&laquo;</span></a></li>'.format(self.page_num-1))
        else:
            #Add a label from the previous page
            html_str_list.append('<li><a href="{}?page={}"><span aria-hidden="true">&laquo;</span></a></li>'.format( self.url_prefix, self.page_num-1))

        for i in range(1,self.total_page+1):
            #Add an active style class to the current page
            if i == self.page_num:
                tmp = '<li class="active"><a href="{0}?page={1}">{1}</a></li>'.format(self.url_prefix, i)
            else:
                tmp = '<li><a href="{0}?page={1}">{1}</a></li>'.format( self.url_prefix, i)

            html_str_list.append(tmp)

        #Add a button for the next page
        #Judge, if it is the last page, there is no next page
        if self.page_num >= self.total_page:
            html_str_list.append('<li class="disabled"><a href="#"><span aria-hidden="true">&raquo;</span></a></li>')
        else:
            html_str_list.append('<li><a href="{}?page={}"><span aria-hidden="true">&raquo;</span></a></li>'.format( self.url_prefix, self.page_num+1))
        #Add last page
        html_str_list.append('<li><a href="{}?page={}">Tail page</a></li>'.format( self.url_prefix, self.total_page))

        page_html = "".join(html_str_list)
        return page_html

models,py 

def mysql_page(per_page,page):
    start = (page - 1 ) * per_page
    sql = 'select id,name,sex,age,tel,addr,role from user where id >= \
                          ( select id from user order by id limit %s,1)limit %s'
    args = (start,per_page)
    conn = MySQLdb.connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER, passwd=MYSQL_PASSWORD, db=MYSQL_DB,
                           charset='utf8mb4')
    cursor = conn.cursor()
    cursor.execute(sql, args)
    lines = cursor.fetchall()
    users = []
    for line in lines:
        users.append({
            'id': line[0],
            'name': line[1],
            'sex': 'male' if line[2] else 'female',
            'age': line[3],
            'tel': line[4],
            'addr': line[5],
            'role': line[6]
        }
        )
    cursor.close()
    conn.close()
    return users


Tags: Python SQL Database MySQL

Posted on Mon, 02 Dec 2019 19:23:45 -0500 by kh411dz