bug: Default where clause generation for incremental replication_key fails with TypeError

[BuzzCutNorman]

Singer SDK Version

0.24.0

Is this a regression?

• Yes

Python Version

3.9

Bug scope

Taps (catalog, state, etc.)

Operating System

Windows

Description

The portion of SQLStream.get_records that adds a where clause to the query for the given replication-key fails with the following:

TypeError: object of type 'Column' has no len()

The TypeError is happening because replication_key_col which is of type SQLAlchemy Columns is being used in sqlalchemy.text().bindparams() . Columns doesn't have a default way to convert to a string so we get the has no len() type error.

There are three ways this issue can be resolved.

1. use replication_key_col.name instead
2. use self.replication_key instead
3. switch to .where(replication_key_col >= start_val)

My vote would be for option 3. It is what I used to close the below issue.

BuzzCutNorman/tap-mssql#24

Code

if start_val:
	query = query.where(
		sqlalchemy.text(":replication_key >= :start_val").bindparams(
			replication_key=replication_key_col,
			start_val=start_val,
		),
	)

PS C:\development\projects\my-test-stuff> meltano invoke tap-mssql > output.jsonl
2023-05-05T21:57:35.365433Z [info     ] Environment 'dev' is active
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-Employee'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-Error_Log'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-MoneyTrouble'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-SmallMoneyTrouble'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-badges'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-comments'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-postlinks'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-posts'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-tags'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-users'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Skipping deselected stream 'dbo-votes'.
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Beginning incremental sync of 'raw-tags'...
2023-05-05 14:57:39,569 | INFO     | tap-mssql            | Tap has custom mapper. Using 1 provided map(s).
2023-05-05 14:57:39,616 | INFO     | singer_sdk.metrics   | METRIC: {"type": "timer", "metric": "sync_duration", "value": 0.04687690734863281, "tags": {"stream": "raw-tags", "context": {}, "status": "failed"}}
2023-05-05 14:57:39,616 | INFO     | singer_sdk.metrics   | METRIC: {"type": "counter", "metric": "record_count", "value": 0, "tags": {"stream": "raw-tags", "context": {}}}
TypeError: object of type 'Column' has no len()

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

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Program Files\Python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\Scripts\tap-mssql.exe\__main__.py", line 7, in <module>
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\click\core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\click\core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\click\core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\click\core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\singer_sdk\tap_base.py", line 542, in cli
    tap.sync_all()
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\singer_sdk\tap_base.py", line 413, in sync_all
    stream.sync()
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\singer_sdk\streams\core.py", line 1194, in sync
    for _ in self._sync_records(context=context):
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\singer_sdk\streams\core.py", line 1093, in _sync_records
    for record_result in self.get_records(current_context):
  File "C:\development\buzzcutnorman\tap-mssql\tap_mssql\client.py", line 542, in get_records
    for record in conn.execute(query):
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\future\engine.py", line 280, in execute
    return self._execute_20(
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1948, in _execute_context
    self._handle_dbapi_exception(
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2133, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1905, in _execute_context
    self.dialect.do_execute(
  File "C:\development\projects\my-test-stuff\.meltano\extractors\tap-mssql\venv\lib\site-packages\sqlalchemy\engine\default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
SystemError: <class 'pyodbc.Error'> returned a result with an error set