mysql分页查询

 

 

python 3.6

 

# -*- coding: utf-8 -*-

import pymysql
import smtplib
import datetime
import time


mysql_server="192.168.1.22"
user_name="root"
password= "yeemiao3040"
db_name="db_admin"

offset = 100



def query_indb(page_number):
    sqltext = "select auto_id,\
           ID,\
           USER,\
           HOST,\
           DB,\
           COMMAND,\
           TIME,\
           STATE,\
           INFO,\
           create_time,\
           update_time\
           from tb_run_long_sql limit %s,%s" %(page_number*offset,offset)
    print(sqltext)
    db = pymysql.connect(mysql_server,user_name, password, db_name,port=3306)
    cursor = db.cursor()
    try:
        cursor.execute(sqltext)
        results = cursor.fetchall()
        record_cnt = len(results)
    except Exception as e:
        print(e)
    db.close()
    return results,record_cnt

def data_to_file(page_number):

    sqltext = "select auto_id,\
           ID,\
           USER,\
           HOST,\
           DB,\
           COMMAND,\
           TIME,\
           STATE,\
           create_time,\
           update_time\
           from tb_run_long_sql_cp limit %s,%s" %(page_number*offset,offset)
    print(sqltext)
    db = pymysql.connect(mysql_server,user_name, password, db_name,port=3306)
    cursor = db.cursor()
    cursor.execute(sqltext)
    results = cursor.fetchall()
    ##print(rows[0][4])
    try:
        with open ("E:/aa.txt",'a+') as fout:
            for row in results:
                fout.write('%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n'%(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
    except Exception as e:
        print(e)
    finally:
        db.close()


def gente_total_cnt():
    sql = "select count(1) from tb_run_long_sql_cp"
    db = pymysql.connect(mysql_server,user_name, password, db_name,port=3306)
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        total_cnt = results[0][0]
    except Exception as e:
        print(e)
    db.close()
    return total_cnt

if __name__ == '__main__':
    print("开始时间:"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
    total_cnt = gente_total_cnt()
    print("表记录数:" + str(total_cnt))
    page_cnt = round(total_cnt/offset)
    print("页数:" + str(page_cnt))
    i = 0
    while i <= page_cnt:
        data_to_file(i)
        i = i + 1