首页 / PYTHON / python mysql 分页
python mysql 分页
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python mysql 分页,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2938字,纯文字阅读大概需要5分钟。
内容图文
![python mysql 分页](/upload/InfoBanner/zyjiaocheng/865/c76155c8723f4d339560a0105c25eaa6.jpg)
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
内容总结
以上是互联网集市为您收集整理的python mysql 分页全部内容,希望文章能够帮你解决python mysql 分页所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。