Using f-strings in Python query_table_rows API call

Community Discussions
, , ,
1

Hi Honeycode team,

We're loving the new Table row operations APIs (query_table_rows, batch_upsert_table_rows etc). Nice work!

We note that getting the right string formatting for the FILTER and FINDROW formulas can be a bit finicky. To make the APIs more accessible to a wider range of Python devs, in addition to the examples provided, developers using the Python API may find it helpful to see some examples using f-strings in the queries. For example, using the Stocks table from your documentation, to find the AMZN stock row:

ticker = 'AMNZ'
response = honeycode_client.query_table_rows(
    workbookId = <workbook_id>,
    tableId = <table_id>,
    filterFormula = {"formula": f'=FILTER(Stocks,"Stocks[Symbol]=%", "{ticker}")'}
)

And, if you have a better approach, we're keen to see it.

3

Hi @Doug,

Welcome to the community! :honeybee:

We're happy to hear that the Table row operations APIs have been helpful, thank you for that note :slight_smile:

Great feedback too on what you're experiencing with the formulas; we can see how it can be tricky at times when it comes to writing and updating queries.

I'll highlight though that the approach you provided works well, as that is also how we'd recommend to write the query.

I'm going to pass on your request to our team to add more examples to our API documentation. If you think of anything else in particular that would be helpful to see, let us know. :honey_pot: :honeybee:

6

Hi I really appreciate this example, however even following it bit by bit, I am getting a "InvalidParameterValueException" :frowning: , how can I debug that? I just put my Table name and column name and it gives me the error. Many thanks in advance!

7

Hi @alpiovs, welcome to the community! :wave: :smiley:

I'm happy to help you out. Rather than the table name and column name, you'll need to enter the workbook ID and table ID.

To get the workbook ID, right click on a screen and select "get ARN and IDs".

Screen Shot 2021-12-27 at 12.29.05 PM
Screen Shot 2021-12-27 at 12.29.05 PM1068×572 24.2 KB

To get table IDs from your workbooks for API calls, you can use ListTables. Here's an article with more info on this: ListTables - Amazon Honeycode

AWS CLI:

aws honeycode list-tables \
  --workbook-id "<workbook-id>"

Python SDK:

response = honeycode_client.list_tables(
    workbookId = '<workbook-id>')

I hope this helps! :slight_smile: :honeybee:

8

Thanks for the answer, I am putting already the WB and Table ID into the parameters.
Should I use them in the Filter Formula as well, like this, where "8146e4f-6748-4111-b80f-7d3f2dba604a" is my Table ID?

v_value = 'myValueforFiltering'
filterFormula = {"formula": f'=FILTER(8146e4f-6748-4111-b80f-7d3f2dba604a,"8146e4f-6748-4111-b80f-7d3f2dba604a[COLUMNID]=%*", "{v_value}")'}

Many thanks

9

Got it, thanks for that clarity, @alpiovs :slight_smile: :honeybee:

The FILTER formula requires the table name and column name (not the table ID), meaning your API request would look more like:

value = 'ValueToFind'
response = honeycode_client.query_table_rows(
    workbookId = <workbook_id>,
    tableId = <table_id>,
    filterFormula = {"formula": f'=FILTER(TableName,"TableName[ColumnName]=%", "{value}")'}
)

Keep in mind that the table name and column name need to be within the same table as the Table ID, and the value you're looking to find must be in that table as well. Looking at your formula, there may be a syntax error as well (remove the * after the %).

I hope that helps! :honey_pot:

10

Many thanks Alyssa, I did some test with the FILTER formula in honeyCode and then found the solution for my code.
My purpose was to create a CSV file on S3 for each month of my records, to limit the time of execution of my Python script on AWS Lambda.
To do so, I created a calculated column with the MONTH function in HoneyCode and then performed the filter with this formula in Python:
currmonth = time.strftime("%m")
filterFormula = {"formula": f'=FILTER(A_SPESE_MAIN,"A_SPESE_MAIN[Mese]=%", "{currmonth}")'}

"Mese" is Month in italian.

Now I will be able to archive my personal expenses in a monthly basis and analyze them efficiently through Athena :slight_smile:
Thanks again

1 Like
11

Great, I'm glad you got it working, @alpiovs :smiley: :star2:

Thanks for sharing your use case and formula too; that's a great solution to archive and analyze your monthly expenses.

Let us know if you need any other assistance in the future. :honey_pot: