Fixing error in a SHOW TABLES IN DATABASE name query

Question

I am trying to list all the table in a database in Amazon AWS Athena via a Python script. Here is my script:

data = {'name':['database1', 'database-name', 'database2']}
# Create DataFrame
df = pd.DataFrame(data)

for index, schema in df.iterrows():
    tables_in_schema = pd.read_sql("SHOW TABLES IN "+schema[0],conn)

There is an error running this

When I run the same query in the Athena query editor, I get an error

SHOW TABLES IN database-name

Here is the error

DatabaseError: Execution failed on sql: SHOW TABLES IN database-name
An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 
1:19: mismatched input '-'. Expecting: '.', 'LIKE', <EOF>
unable to rollback

I think the issue is with the hypen "-" in the database name. How do I escape this in the query?

Answer 1

You can use the Glue client instead. It provides a function get_tables(), which returns a list of all the tables in a specific data base.

Answer 2

The database, table or columns names cannot have anything other than an underscore "_" in its name. Any other special character will cause an issue when querying. It does not stop you from creating an object with the special characters but will cause an issue when using those objects.

The only way around this is to re-create the database names without the special character, hyphen "-" in this case.

https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html