How to pass table name in read_sql_query

Question

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)

Nick ODell · Accepted Answer · 2022-01-13 23:10:52Z

2

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.

answered Jan 13, 2022 at 23:10

Nick ODell

28.1k7 gold badges52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Mann Over a year ago

when I update to this I now get this error: DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in query. Incomplete query clause. (-3003) (SQLExecDirectW)') [SQL: SELECT * FROM 'Site Visit Fees-Zero Amount'] (Background on this error at: sqlalche.me/e/13/dbapi)

Nick ODell Over a year ago

@MikeMann Note that there is a difference between ` and '.

Sheikh_Sheharyar · Accepted Answer · 2022-01-14 00:06:59Z

0

One thing you can try by formatting the table outside the function and then access the table within the function

sq = "SELECT * FROM {}"
qry = sql.format(*dict(table=table_name))

And within the function call the formatted variable

df_name = pd.read_sql_query(qry, connection)

edited Jan 14, 2022 at 0:06

answered Jan 13, 2022 at 23:15

Sheikh_Sheharyar

36 bronze badges

2 Comments

Mike Mann Over a year ago

when I attempt this, the qry line give me this error: IndexError: Replacement index 0 out of range for positional args tuple

Sheikh_Sheharyar Over a year ago

@MikeMann remove one asterisk before dict then give a shot

Collectives™ on Stack Overflow

How to pass table name in read_sql_query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related