Retrieve df from spark.sql : [PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT'

Question

I'm using a databricks notebook and I'd like to retrieve a dataframe from an SQL execution in Spark. I have:

statement = f""" USER {db}; SELECT * FROM {table}
"""

df = spark.sql(statement)
display(df) 

However, unlike when I fire off the same statement in an SQL cell in the notebook, I get the following error:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT': extra input 'SELECT'(line 1...

Where am I going wrong?

Answer 1

I tried to reproduce the same in my environment and got below results:

This my sample demo table Persons.

Create dataframe by using this code as shown in the below image.

df = sqlContext.sql("select * from Persons")
display(df)