Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Seems like exclude_tables not working in call to get_app #188

Open
thomascapote opened this issue Nov 24, 2020 · 0 comments
Open

Seems like exclude_tables not working in call to get_app #188

thomascapote opened this issue Nov 24, 2020 · 0 comments

Comments

@thomascapote
Copy link

thomascapote commented Nov 24, 2020 •
edited

My SQL Server database has a table name with special characters, including single-quotes and a space: ['Downstream Accounts$']

image

Note that the following query works in Microsoft SQL Server Management Studio.

SELECT * FROM ['Downstream Accounts$'];

Without any qualification, a call to sandman2ctl fails with the message "Incorrect syntax near 'Downstream'."

thomas@guru:~/sandman$ sandman2ctl mssql+pyodbc://user:pass@db_instance
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 "/usr/local/bin/sandman2ctl", line 8, in <module>
    sys.exit(main())
  File "/home/thomas/.local/lib/python3.8/site-packages/sandman2/__main__.py", line 57, in main
    app = get_app(args.URI, read_only=args.read_only, schema=args.schema)
  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)

The next-to-last line of the above stack trace shows the table name SQLAlchemy is choking on: @table_name = ''Downstream Accounts$''. (The link mentioned in the last line is useless and unrelated to escaping table names.)

My next step was to try to create a custom user model for this table, as described in #186. I have not been able to get that approach to work.

The next thing to try is using the exclude_tables parameter of get_app to have SQLAlchemy skip the attempted introspection of this table, but this fails with the identical error!

thomas@guru:~/sandman$ python3 mrm_live_sandman.py 
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 "mrm_live_sandman.py", line 46, in <module>
    app = get_app(conn_str, exclude_tables=exclude_tables)
  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 script (mrm_live_sandman.py) attempts to quote the table name every way I can think of, but it seems SQLAlchemy still attempts to use the same failing form of the table name.

from sandman2 import get_app

conn_str = 'mssql+pyodbc://user:pass@db_instance'
exclude_tables = [
        'Downstream Accounts$',      #
       '[Downstream Accounts$]',     # ]
       '"Downstream Accounts$"',     # "
      '"[Downstream Accounts$]"',    # ]"
      '\'Downstream Accounts$\'',    # '
     '[\'Downstream Accounts$\']',   # ']
     '"\'Downstream Accounts$\'"',   # '"
    '"[\'Downstream Accounts$\']"',  # ']"
]
app = get_app(conn_str, exclude_tables=exclude_tables)

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

I am at a loss for how to get sandman2 to ignore this table or, better yet (of course), process it without complaining about the name. Thank you very much for taking the time to read this far and trying to help!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@thomascapote