Errors generated by table names containing spaces; unable to start sandman2ctl

[thomascapote]

Jeff:

First, kudos and thanks for this project. Beautiful stuff!

I'm working with a SQL Server database that is not "beautiful stuff". ;-) Specifically, the database contains the following table names.

• 'Downstream Accounts$'
• 'Downstream Contacts$'
• 'Downstream Locations$'
• PartNumberCosts$
• Sheet1$
• 'Upstream Accounts$'
• 'Upstream Contacts$'
• 'Upstream Locations$'

Strangely, the single quotes are also part of the table names. Querying these tables in the SQL Server toolset looks like this: SELECT * FROM ['Upstream Contracts$'].

Running sandman2ctl "$db_uri" takes a while, then throws an error: sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'Downstream'. (102) (SQLExecDirectW)").

The same error is thrown by python3 when running the following interactively in the REPL (not using sandman2ctl).

thomas@guru:~/sandman$ python3
Python 3.8.5 (default, Jul 28 2020, 12:59:40) 
[GCC 9.3.0] on linux
>>> import sandman2
>>> db_uri = 'mssql+pyodbc://user:pass@db_instance'
>>> exclude_tables = [ '\'Downstream Accounts$\'' ]
>>> app = sandman2.get_app(db_uri, exclude_tables=exclude_tables)

The same error is thrown with-or-without the exclude_tables parameter. Here's the full traceback:

>>> app = sandman2.get_app(db_uri)
Traceback (most recent call last):
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1243, in _execute_context
    self.dialect.do_execute(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'Downstream'. (102) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/thomas/.local/lib/python3.8/site-packages/sandman2/app.py", line 60, in get_app
    _reflect_all(exclude_tables, admin, read_only, schema=schema)
  File "/home/thomas/.local/lib/python3.8/site-packages/sandman2/app.py", line 132, in _reflect_all
    AutomapModel.prepare(  # pylint:disable=maybe-no-member
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/automap.py", line 758, in prepare
    cls.metadata.reflect(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4242, in reflect
    Table(name, self, **reflect_opts)
  File "<string>", line 2, in __new__
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 130, in warned
    return fn(*args, **kwargs)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 496, in __new__
    metadata._remove_table(name, schema)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 491, in __new__
    table._init(name, metadata, *args, **kw)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 580, in _init
    self._autoload(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 603, in _autoload
    autoload_with.run_callable(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1604, in run_callable
    return callable_(self, *args, **kwargs)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 430, in reflecttable
    return insp.reflecttable(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 632, in reflecttable
    for col_d in self.get_columns(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 371, in get_columns
    col_defs = self.dialect.get_columns(
  File "<string>", line 2, in get_columns
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
    ret = fn(self, con, *args, **kw)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 2082, in wrap
    return _switch_db(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 2103, in _switch_db
    return fn(*arg, **kw)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 2556, in get_columns
    cursor = connection.execute(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return self._execute_text(object_, multiparams, params)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1149, in _execute_text
    ret = self._execute_context(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self._handle_dbapi_exception(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1243, in _execute_context
    self.dialect.do_execute(
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'Downstream'. (102) (SQLExecDirectW)")
[SQL: sp_columns @table_name = ''Downstream Accounts$'', @table_owner = 'dbo']
(Background on this error at: http://sqlalche.me/e/f405)

My understanding was that SQLAlchemy's introspection routine handled all necessary quoting for special characters, but it seems to be failing here.

[thomascapote]

Reporting possible progress, but also a new error: ... could not assemble any primary key columns for mapped table...

I got past the original error by extending the reflected class (as mentioned in the docs) like this:

# user_models.py
from sandman2.model import db, Model

class DownstreamAccounts(db.Model, Model):
    __tablename__ = '\'Downstream Accounts$\''

# ... and so on for each of the 8 tables...

class UpstreamLocations(db.Model, Model):
    __tablename__ = '\'Upstream Locations$\''

The main script looks like this:

#!/usr/bin/python3
from sandman2 import get_app
from user_models import *

user_models = [
    DownstreamAccounts,
    DownstreamContacts,
    DownstreamLocations,
    PartNumberCosts,
    Sheet1,
    UpstreamAccounts,
    UpstreamContacts,
    UpstreamLocations,
]

conn_str = 'mssql+pyodbc://user:pass@db_instance'
app = get_app(conn_str, user_models=user_models)

if __name__ == '__main__':
    app.run(debug=True)

Running this generates the following error.

thomas@guru:~/sandman$ python3 mrm_live_sandman.py 
...
sqlalchemy.exc.ArgumentError: Mapper mapped class DownstreamAccounts->'Downstream Accounts$' could not assemble any primary key columns for mapped table ''Downstream Accounts$''

Here's the full stack trace.

thomas@guru:~/sandman$ python3 mrm_live_sandman.py 
Traceback (most recent call last):
  File "mrm_live_sandman.py", line 4, in <module>
    from user_models import (
  File "/home/thomas/sandman/user_models.py", line 3, in <module>
    class DownstreamAccounts(db.Model, Model):
  File "/home/thomas/.local/lib/python3.8/site-packages/flask_sqlalchemy/model.py", line 67, in __init__
    super(NameMetaMixin, cls).__init__(name, bases, d)
  File "/home/thomas/.local/lib/python3.8/site-packages/flask_sqlalchemy/model.py", line 121, in __init__
    super(BindMetaMixin, cls).__init__(name, bases, d)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/api.py", line 75, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 130, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 158, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 190, in __init__
    self._early_mapping()
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 193, in _early_mapping
    self.map()
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 689, in map
    self.cls.__mapper__ = mp_ = mapper_cls(
  File "<string>", line 2, in mapper
  File "<string>", line 2, in __init__
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 130, in warned
    return fn(*args, **kwargs)
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 716, in __init__
    self._configure_pks()
  File "/home/thomas/.local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 1394, in _configure_pks
    raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Mapper mapped class DownstreamAccounts->'Downstream Accounts$' could not assemble any primary key columns for mapped table ''Downstream Accounts$''

Can I manually define the primary key column for SQLAlchemy in a similar fashion as I did the table name?