python mysql 分页

import pymysql
import pandas as pd
import gc
import time
import threading
 
class Sql_df(object):
    def __init__(self,input_db):
        self.host = 'ip_xxx'
        self.port = 3306
        self.username = 'root'
        self.password = 'xxx'
        self.input_db = input_db
        self.conn = pymysql.connect(host = self.host,port = self.port,user = self.username,passwd = self.password,db = self.input_db,charset = 'utf8')
    def sql_input_all(self,sql_state):
        cur_1 = self.conn.cursor(cursor = pymysql.cursors.DictCursor)
        cur_1.execute(sql_state+' limit 1')
        column_df = cur_1.fetchall()
        column_list = column_df[0].keys()
        cur_2 = self.conn.cursor()
        start_time = time.time()
        cur_2.execute(sql_state)
        tmp_list = cur_2.fetchall()
        result_df = pd.DataFrame(list(tmp_list),columns = column_list)
        end_time = time.time()
        during_time = round(end_time-start_time,0)/60
        print('input data has spend %f minutes'%during_time)
        return result_df
    def sql_input_batch(self,sql_state,nums_sql_state,batch_size):
        cur_1 = self.conn.cursor(cursor = pymysql.cursors.DictCursor)
        cur_1.execute(sql_state+' limit 1')
        column_df = cur_1.fetchall()
        column_list = column_df[0].keys()
        cur_2 = self.conn.cursor()
        start_time = time.time()
        cur_2.execute(nums_sql_state)
        nums_sample = cur_2.fetchall()[0][0]
        batches = nums_sample//batch_size
        cur_3 = self.conn.cursor()
        result_df = pd.DataFrame()
        for i in range(batches):
            cur_3.execute(sql_state+' limit '+str(i*batch_size)+','+str(batch_size))
            tmp_list = list(cur_3.fetchall())
            tmp_df = pd.DataFrame(tmp_list,columns = column_list)
            del tmp_list
            gc.collect()
            result_df = result_df.append(tmp_df)
            del tmp_df
            gc.collect()
        last_index = batches*batch_size
        cur_3.execute(sql_state+' limit '+str(last_index)+','+str(nums_sample-last_index))
        tmp_list = list(cur_3.fetchall())
        tmp_df = pd.DataFrame(tmp_list,columns = column_list)
        result_df = result_df.append(tmp_df)
        end_time = time.time()
        during_time = round(end_time-start_time,0)/60
        print('input data has spend %f minutes'%during_time)
        del tmp_df
        gc.collect()
        return result_df
 
if __name__ == '__main__':
    #input_db = 'aid-livelihood'
    data_input = Sql_df('aid-livelihood')
    pa_visit_hypertension_2014_2016 = data_input.sql_input_all('select * from pa_visit_cerebral_infarction_2014_2015')
    #pa_visit_hypertension_2017_2018 = data_input.sql_input_all('select * from pa_visit_hypertension_2017_2018')
    #pa_inhosp_info = data_input.sql_input_all('select * from pa_inhosp_info')
    #rr = data_input.sql_input_batch('select * from pa_empi','select count(1) from pa_empi',5000)   

https://www.cnblogs.com/franknihao/p/7326849.html
https://www.pythonheidong.com/blog/article/480254/b3599586d4e7a87888a6/

MYSQL 分页查询
一般情况下，客户端通过传递 pageNo（页码）、pageSize（每页条数）两个参数去分页查询数据库中的数据，在数据量较小（元组百/千级）时使用 MySQL自带的 limit 来解决这个问题：

收到客户端{pageNo:1,pagesize:10}
select * from table limit (pageNo-1)*pageSize, pageSize;

收到客户端{pageNo:5,pageSize:30}
select * from table limit (pageNo-1)*pageSize,pageSize;

https://www.cnblogs.com/nickup/p/9758691.html