How do I paginate my mysql query using AWS rds-data using boto3 and AWS-aurora?

Question

I am querying AWS Aurora (MySQL) serverless from AWS Lambda using Boto3. I want to query a table that has more than 10k records. My query can fetch records more than 7k. Now how can I paginate this data, on database level or Lambda level, so that I can get fewer data per page and send that small dataset to the user interface to display? I cannot send the entire dataset of 7k records.

Here is my Python code

rdsDataClient.execute_statement(resourceArn=cluster_arn,
                                secretArn=secret_arn,
                                database='myTestDB',
                                sql=sqlQuery,
                                parameters = paramSet
                               )

I am aware of the SQL query LIMIT and OFFSET but want to know if there is any other better way of doing this. Please help.

Thanks.

Did you find a solution for this? I have the same issue. – DrkStr Jan 09 '21 at 23:44 — DrkStr, Jan 09 '21 at 23:44
Yes @DrkStr Please check my answer below – Shashikant Kulkarni Jan 25 '21 at 07:23 — Shashikant Kulkarni, Jan 25 '21 at 07:23

score 1 · Accepted Answer · answered Jan 25 '21 at 07:21

Yes I used the approach of passing limit and offset to sqlQuery. Here is what I did

def lambda_handler(event, context):
    queryParameters = event["queryStringParameters"]
    search = getQueryStringElement("search", queryParameters, '')
    pageSize = int(getQueryStringElement("pageSize", queryParameters, 5))
    page = int(getQueryStringElement("page", queryParameters, 1))
    
    limit = pageSize
    offset = (pageSize * page) - pageSize
    
    sqlQuery = sqlQuery + ' LIMIT {} OFFSET {} ;'.format(limit, offset)

    rdsDataClient.execute_statement(resourceArn=cluster_arn,
                                secretArn=secret_arn,
                                database='myTestDB',
                                sql=sqlQuery,
                                parameters = paramSet
                               )

This solution worked perfect for my use case where I used to show the paginated list of records in UI

Thanks

score 0 · Answer 2 · answered Jan 24 '21 at 15:28

I believe the best way is to let the database handle the pagination for several reasons:

The query is quite simple to write, just add a LIMIT and OFFSET as you suggested.
The data transferred between the database and the service / lambda will be far less if paginated, thus saving time and cost.
The memory needed to store the data in the service will be less, thus you won't need a beefier lambda to do the computation.
Responsiveness of the service / lambda will be better as a result of the above.

Hello and welcome to SO! Please read the [tour](https://stackoverflow.com/tour), and [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) Adding code snippet might be useful. — Tomer Shetah, Jan 24 '21 at 15:57

How do I paginate my mysql query using AWS rds-data using boto3 and AWS-aurora?

2 Answers2