def test_mssql_get_df(mocker): snock = mocker.patch('pyodbc.connect') reasq = mocker.patch('pandas.read_sql') mssql_connector = MSSQLConnector(name='mycon', host='localhost', user='******', password='******', port=22) datasource = MSSQLDataSource( name='mycon', domain='mydomain', database='mydb', query='SELECT Name, CountryCode, Population ' 'FROM City WHERE ID BETWEEN 1 AND 3', ) mssql_connector.get_df(datasource) snock.assert_called_once_with( driver='{ODBC Driver 17 for SQL Server}', as_dict=True, server='127.0.0.1,22', user='******', password='******', database='mydb', ) reasq.assert_called_once_with( 'SELECT Name, CountryCode, Population FROM City WHERE ID BETWEEN 1 AND 3', con=snock())
def test_query_variability_jinja(mocker): """It should interpolate safe (server side) parameters using jinja templating""" mock_pyodbc_connect = mocker.patch('pyodbc.connect') mock_pandas_read_sql = mocker.patch('pandas.read_sql') con = MSSQLConnector(name='mycon', host='localhost', user='******', password='******', port=22) ds = MSSQLDataSource( query= 'select * from {{user.attributes.table_name}} where id_nb in %(ids)s;', domain='test', name='test', database='db', parameters={ 'ids': [1, 2], 'user': { 'attributes': { 'table_name': 'blah' } } }, ) con.get_df(ds) mock_pandas_read_sql.assert_called_once_with( 'select * from blah where id_nb in (?,?);', con=mock_pyodbc_connect(), params=[1, 2], )
def test_connection_params(): connector = MSSQLConnector(name='my_mssql_con', host='myhost', user='******') assert connector.connection_params == {'server': 'myhost', 'user': '******', 'as_dict': True} connector = MSSQLConnector(name='my_mssql_con', host='myhost', user='******', password='******', port=123, connect_timeout=60, db='mydb') assert connector.connection_params == {'server': 'myhost', 'user': '******', 'as_dict': True, 'password': '******', 'port': 123, 'login_timeout': 60, 'database': 'mydb'}
def mssql_connector(mssql_server): return MSSQLConnector( name='mycon', host='localhost', user='******', password='******', port=mssql_server['port'], )
def test_connection_params(): connector = MSSQLConnector(name='my_mssql_con', host='myhost', user='******') assert connector.get_connection_params(None) == { 'driver': '{ODBC Driver 17 for SQL Server}', 'server': 'myhost', 'user': '******', 'as_dict': True, } connector = MSSQLConnector( name='my_mssql_con', host='myhost', user='******', password='******', port=123, connect_timeout=60, ) assert connector.get_connection_params('mydb') == { 'driver': '{ODBC Driver 17 for SQL Server}', 'server': 'myhost,123', 'user': '******', 'as_dict': True, 'password': '******', 'timeout': 60, 'database': 'mydb', }
def test_query_variability(mocker): """It should connect to the database and retrieve the response to the query""" mock_pyodbc_connect = mocker.patch('pyodbc.connect') mock_pandas_read_sql = mocker.patch('pandas.read_sql') con = MSSQLConnector(name='mycon', host='localhost', user='******', password='******', port=22) # Test with integer values ds = MSSQLDataSource( query= 'select * from test where id_nb > %(id_nb)s and price > %(price)s;', domain='test', name='test', parameters={ 'price': 10, 'id_nb': 1 }, database='db', ) con.get_df(ds) mock_pandas_read_sql.assert_called_once_with( 'select * from test where id_nb > ? and price > ?;', con=mock_pyodbc_connect(), params=[1, 10], ) # Test when the value is an array mock_pandas_read_sql = mocker.patch('pandas.read_sql') ds = MSSQLDataSource( query='select * from test where id_nb in %(ids)s;', domain='test', name='test', database='db', parameters={'ids': [1, 2]}, ) con.get_df(ds) mock_pandas_read_sql.assert_called_once_with( 'select * from test where id_nb in (?,?);', con=mock_pyodbc_connect(), params=[1, 2], )