实现功能
利用limit 与 offset进行数据库数据批量查询与处理
实现代码
def query_batch(self, engine, batch_step, end, sql):
session = make_session(engine)
cursor = session.execute(sql.format(batch_step, end))
fields = cursor._metadata.keys
df = pd.DataFrame([dict(zip(fields, item))for itemin cursor.fetchall()])
print(len(df))
cursor.close()
session.close()
return df
# 批量查询并做表连接处理
sql1 ='select count(*) from clusterAllReplicas (clickhouse, data_mgmt.gha_activity_2023_03)'
start =0
max_length = make_session(engine).execute(sql1).fetchall()[0][0]
batch_step =5000000
result_df = pd.DataFrame()
for iin range(start, max_length, batch_step):
print(i)
print(batch_step, i)
df2 = query_batch(engine, batch_step, i,
sql='select * from clusterAllReplicas (clickhouse, data_mgmt.gha_activity_2023_03) order by id limit {} offset {}')
# df2['repo_url'] = df2['repo.url'].map(lambda x: Object2.string_extraction(x))
temp_df = merge_data(df1, df2)
if len(temp_df):
result_df = result_df.append(temp_df)
print(len(result_df))
result_df.to_excel('D:\工作\ospp-report\\result\%d.xlsx' % i)
实现效果
喜欢记得点赞，在看，收藏，关注V订阅号：数据杂坛，获取数据集，完整代码和效果，将持续更新！
