Bug: Backticks in sql formation of statements in awswrangler.data_api.rds.RdsDataApi cause issues with some Postgres engines

[Stewart-Holloway-Pivotal]

Describe the bug

Backticks are not part of the ANSI SQL standard and shouldn't be present in the SQL statements for the RdsDataApi.

This causes a failure with RDS Aurora Serverless Postgres V11.19. It likely causes issues with most Postgres versions. It may work with some MySQL engines, but SQL statements should conform.

Stacktrace:

[ERROR] 2023-07-07T21:39:41.739Z 9a6d6e82-093f-4d29-8129-39f9db16f6d0 Maximum BadRequestException retries reached for query DROP TABLE IF EXISTS {MY_TABLE_NAME}
NoneType: NoneFailed to connect to pg, error An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: syntax error at or near "`"
Position: 22; SQLState: 42601

How to Reproduce

*P.S. Please do not attach files as it's considered a security risk. Add code snippets directly in the message body as much as possible.*

Provision a Postgres Aurora DB with DataAPI enabled. Run wr.data_api.rds.to_sql with any dataframe payload.

Expected behavior

RdsDataApi should be

Your project

private

Screenshots

No response

OS

Linux

Python version

3.9

AWS SDK for pandas version

3.2.0

Additional context

No response

[kukushking]

Hi @Stewart-Holloway-Pivotal thank you for opening this - indeed backticks aren't supported by PostgreSQL. I've opened a PR with a fix.

[Stewart-Holloway-Pivotal]

Excellent, thank you for your prompt attention! Appreciate the work here.