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],
)
