Any way to get access to interpolated SQL strings?

[ncryer]

Hi, thanks for an awesome library!

I'm writing some tests for a project which uses pydapper, and my idea was to hook into pydapper.connect and extract the final SQL strings which are sent over ODBC, such that tests could assert that the correct SQL is generated.

I'm using objects of this class:

class MockPydapperConnect:

    test_sql: str = ""
    test_param:  Union["ParamType", "ListParamType"] = None

    def __init__(self):
        return None

    def __enter__(self):
        return self

    def __call__(self, *args: Any, **kwds: Any) -> Self:
        return self
    
    def query(self, sql: str, param: Union["ParamType", "ListParamType"] = None) -> int:
        param_handler = DefaultSqlParamHandler(sql, param)
        
        self.test_sql = param_handler.prepared_sql 
        self.test_param = param_handler.ordered_param_values
        
        return 1

    def execute(self,  sql: str, param: Union["ParamType", "ListParamType"] = None) -> int:
        return self.query(sql, param)
    
    def get_test_parameters(self) -> Tuple[str, Union["ParamType", "ListParamType"]]:
        return (self.test_sql, self.test_param)

    def __exit__(self,  *args: Any, **kwds: Any):
        return None

Tests would then use pytest-mock to patch the connect object in the following way:

    mock_connect = MockPydapperConnect()
    mocker.patch('my_module.connect', mock_connect)

Usages of the connect context manager then ends up using this object, and that's all gravy. However, I can't figure out how to actually get my grubby hands on the interpolated SQL strings; the best I can do is a string in which values are substituted with %s.

Is there any way to do this? Or are there better approaches to this problem?

Thanks a lot!

[zschumacher]

the library intentionally doesn't give a way to do this to deter SQL injection. Most dbapis, however, have some kind of cursor.mogrify method that will allow you to view the prepared SQL.

Is there a certain dbapi you're trying to use this with?

[zschumacher]

psycopg2 - https://www.psycopg.org/docs/cursor.html#cursor.mogrify
pymysql - https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.mogrify
aiopg - https://aiopg.readthedocs.io/en/stable/core.html#aiopg.Cursor.mogrify

I also believe post execution, most cursor's implement cursor._executed which will give you the string that was sent to the db.

Hope this helps!

[ncryer]

Thanks a ton, super helpful :)

[zschumacher]

The tests I'm using in the pydapper suite for the param handler might be useful for you to look at though?