How to pass table name in read_sql_query

January 14, 2022

i’m trying to create a function for this code:

db_path = dmttools.paths.payments_db

try:
    engine = dmttools.database.access_engine(db_path)
    access_zeroDollar = pd.read_sql_query("SELECT * FROM `Site Visit Fees-Zero Amount`", engine)
    logger.info('Read.')
except Exception as e:
    logger.error('Error reading database.')
    logger.error(e)
    raise e
finally:
    engine.dispose()

running this block works fine, problem is…I run multiple times and want to create a function to clean up my script. When I convert to this function:

def read_database(logger, db_path, table, df_name):
logger = dmttools.util.DefaultLogger(logger)
logger.info('Reading existing database...')

sq = "SELECT * FROM {}"
    
try:
    engine = dmttools.database.access_engine(db_path)
    df_name = pd.read_sql_query(sq.format(table), engine)
    logger.info('Read.')
except Exception as e:
    logger.error('Error reading database.')
    logger.error(e)
    raise e
finally:
    engine.dispose()

and call the function like this:

read_database('Zero Dollar Fee Aging Report', dmttools.paths.payments_db, 'Site Visit Fees-Zero Amount', 'access_test')

I receive this error:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. (-3506) (SQLExecDirectW)')
[SQL: SELECT * FROM Site Visit Fees-Zero Amount]
(Background on this error at: http://sqlalche.me/e/13/f405)

>Solution :

You need to change this:

sq = "SELECT * FROM {}"

to this:

sq = "SELECT * FROM `{}`"

If you don’t, the table name won’t be quoted, and it will only associate the first word of "Site Visit Fees-Zero Amount" with the table name, leaving it with extra words. That causes a syntax error.

As a side note, you may want to consider not using spaces inside your table names.