Pandas read_sql inconsistent behaviour dependent on driver?

When I run a query from my local machine to a SQL server db, data is returned. If I run the same query from a JupyterHub server (using ssh), the following is returned:

TypeError: 'NoneType' object is not iterable

Implying it isn't getting any data.

The connection string is OK on both systems (albeit different), because running the same stored procedure works fine on both systems using the connection string -

Local= "Driver={SQL Server};Server=DNS-based-address;Database=name;uid=user;pwd=pwd"

Hub = "DRIVER=FreeTDS;SERVER=IP.add.re.ss;PORT=1433;DATABASE=name;UID=dbuser;PWD=pwd;TDS_Version=8.0"

Is there something in the FreeTDS driver that affects chunksize, or means a set nocount is required in the original query as per this NoneType object is not iterable error in pandas - I tried this fix by the way and got nowhere.

Solution

Are you using pymssql, which is build on top of FreeTDS?

For SQL-Server you could also try the Microsoft JDBC Driver with the python package jaydebeapi: https://github.com/microsoft/mssql-jdbc.

import pandas as pd
import pymssql

conn = pymssql.connect(
    host = r'192.168.254.254',
    port = '1433',
    user = r'user',
    password = r'password',
    database = 'DB_NAME'
)

query = """SELECT * FROM db_table"""

df = pd.read_sql(con=conn, sql=query)