using pypyodbc to access a table with spaces in its name

Question

This command works fine..

count = conn.cursor().execute("select COUNT(*) FROM Orders;").fetchall()

However this command kicks back an error

count = conn.cursor().execute("select COUNT(*) FROM "Summary of Sales"; ").fetchall()

The error is..

Traceback (most recent call last):
  File "python-database.py", line 39, in <module>
    print(row_count())
  File "python-database.py", line 29, in row_count
    count = conn.cursor().execute(query).fetchall()
  File "/usr/local/lib/python2.7/dist-packages/pypyodbc.py", line 1595, in execute
    self.execdirect(query_string)
  File "/usr/local/lib/python2.7/dist-packages/pypyodbc.py", line 1621, in execdirect
    check_success(self, ret)
  File "/usr/local/lib/python2.7/dist-packages/pypyodbc.py", line 985, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "/usr/local/lib/python2.7/dist-packages/pypyodbc.py", line 953, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: (u'42000', u"[42000] [FreeTDS][SQL Server]Incorrect syntax near 'Summary of Sales'.")

I've used a SQL profiler to see the exact sql query and it's perfect, I've even coped and pasted it into a console session with the database to test, and it returns the row count without issue. I've tried all different combinations of single and double quotes. It just seems that if a put a table name that is a single word without any quotes it works fine. If I try to put a table name that consists of more than one word I have to use quotes and it fails.

Hope you guys can shed some light on this, it's stopped my projects in it's tracks, :(

Answer 1

You are using double quotes inside of the string, and also to delimit the string. Either escape the quotes, or use single quotes to delimit the string like this:

count = conn.cursor().execute('select COUNT(*) FROM "Summary of Sales"; ').fetchall()

Hope that helps.

Answer 2

When the names of databases, tables, stored procedures, or variable names contain spaces or special characters you can surround the names with square brackets.

count = conn.cursor().execute("select COUNT(*) FROM [Summary of Sales];").fetchall()