CODEWITHSUNDEEP
pythonsql-serverpandassqlalchemypyodbc
dcrowley01
dcrowley01

Reputation: 151

Python Pyodbc SQL Server Returns 'NoneType'

Pyodbc is returning TypeError: 'NoneType' object is not iterable. Although, when I copy and paste the sql f string into sql server, I get millions of records.

cnxn = pyodbc.connect('DRIVER={SQL Server};'
                           'SERVER=server;'
                           'DATABASE=db;')

sql=f"exec dbo.storedproc '{AdmitDate_Start}','{AdmitDate_End}','{RelativeStartDate}','{RelativeEndDate}'"
df=pd.read_sql(sql,cnxn)

I was able to successfully execute another stored procedure from the same location (server and db). However, that stored proc doesn't require that I pass the four variables above. I also tried creating the sql statement with Declare and passing in the variables. RelativeStartDate and RelativeEndDate can be null.

sql='''declare @AdmitDate_Start datetime = '''+"'"+ads+"'"+''';declare @AdmitDate_End datetime = '''+"'"+ade+"'"+''';declare @RelativeStartDate VARCHAR(20);declare @RelativeEndDate VARCHAR(20);exec dbo.storedproc @AdmitDate_Start, @AdmitDate_End,@RelativeStartDate,@RelativeEndDate;'''

This also returned 0 records in python, but ran fine in SQL Server.

This led me to trying a few different drivers, but these wouldn't connect:

['SQL Server Native Client 11.0', 'ODBC Driver 13 for SQL Server', 'ODBC Driver 17 for SQL Server']

Any ideas as to what may be the cause of returning None records and how to fix?

Upvotes: 0

Views: 785

Answers (2)

dcrowley01
dcrowley01

Reputation: 151

the sql script did not contain "set no count on;" When adding this line to before execute, it worked.

sql=f"set no count on;exec dbo.storedproc '{AdmitDate_Start}','{AdmitDate_End}','{RelativeStartDate}','{RelativeEndDate}'"

Upvotes: 1

user5386938
user5386938

Reputation:

The following works here at my end. (Note the use of params.)

import pyodbc
import pandas as pd

MSSQL = {
        "database": "***",
        "password": "***",
        "server": "***",
        "user": "***"
    }
    
SQL = 'EXEC dbo.delete_me @lat = ?, @lon = ?, @InvoiceNumber = ?'

def get_connection(server, database, user, password):
    cs = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={user};PWD={password}'

    return pyodbc.connect(cs)

if __name__ == '__main__':
    with get_connection(**MSSQL) as conn:
        df = pd.read_sql(SQL, conn, params=(43.6010067, -79.6838868, 123456))
        
    print(df)

where dbo.delete_me is defined as

create procedure [dbo].[delete_me]

    @lat decimal(18,12)
    ,
    @lon decimal(18,12)
    ,
    @InvoiceNumber nvarchar(15)

as

begin

    set nocount on;
    select @lat as latitude, @lon as longitude, @InvoiceNumber as invoice

end

The set nocount on; does not affect the python code in anyway.

Upvotes: 0

Related Questions