python - Using python and pandas I need to paginate the results from a sql query in sets of 24 rows into a plotly table . How do I do this?

Question

I can create the dataframe from the sql query df=pd.read_sql(sql_query, myconn) and it works in creating the plotly table but I don't know how to read only 24 rows of the dataframe - create the plotly table write it out to an image and then restart on the 25th row. The plotly table is using cells=dict(values=df.transpose().values and that works. I have tried pd function concat, append, etc into temp dataframes. I have tried for loops, while loops I always get an error. The question is how do you subset a set of rows in pandas dataframe from a sql query, keeping the column values for each subset group?

标签： pythonsqlpandasdataframepagination

Answer 1

temp_cursor.execute(sql_query1)
table_rows=temp_cursor.fetchall()

rowcnt=len(table_rows)
table_output=table_rows[0]

row = 1
while row < rowcnt:
    table_output.update(table_rows[row])
    if row % 24 == 0:
        ### call picture slide builder
       prs=table_slide_builder(awsconn, prs, pres_slide,table_output, orgid, png_file_path,function_name)
       row=row+1
       if row < rowcnt:
          table_output=table_rows[row]
    row=row+1

    #### this is in case there is any remaining rows count < 24 but will not print a dup slide if table_rows is multiple of 24
if rowcnt % 24 != 0 and rowcnt != 0:
    ### call  picture slide builder
    prs=table_slide_builder(awsconn, prs, pres_slide,table_output, orgid, png_file_path,function_name)

#### in table_slide_builder

cost_ops_24_table_trace = go.Table(
    columnwidth=[15]+[15], 
    header=dict(values=['<b>Account</b>',
    '<b>Region</b>','<b>Resource</b>', '<b>Salary</b>', '<b>Salary Amount</b>','<b>Description</b>'],
    fill_color ='#021F5F',
    font_family ='Arial',
    font_color ='#FFFFFF',
    font_size =28,
    height =80,
    align =['center']
    ),
    cells = dict(values=table_output,
        fill_color='rgb(255,255,255)',
        font_color='rgb(0,0,0)',
        font_family='Arial',
        font_size=24,
        align=['right']*5,
        line = dict(color='#000000'),
        height = 40,
        line_width=1
    )
)
cost_ops_24_layout = go.Layout(width=2450, height=1067, autosize=False)
cost_ops_24_fig = go.Figure(data=[cost_ops_24_table_trace], layout=cost_ops_24_layout)
               mytablefile=png_file_path+name_png_file(orgid,function_name,slide.slide_id, placeholderidx)

cost_ops_24_fig.write_image(mytablefile, format='png', scale=1.2, width=2450, height=1067)