[GH-ISSUE #186] Errors generated by table names containing spaces; unable to start sandman2ctl #67

New issue

Open

opened 2026-02-26 01:33:02 +03:00 by kerem · 1 comment

kerem commented

2026-02-26 01:33:02 +03:00

Owner

Originally created by @thomascapote on GitHub (Nov 19, 2020).
Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/186

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.

Originally created by @thomascapote on GitHub (Nov 19, 2020). Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/186 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.

kerem commented

2026-02-26 01:33:02 +03:00

Author

Owner

@thomascapote commented on GitHub (Nov 19, 2020):

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?

@thomascapote commented on GitHub (Nov 19, 2020): 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](https://sandman2.readthedocs.io/en/latest/admin.html)) like this: ```python # 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: ```python #!/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?