Python with sql server and cursor.execute with parms

Python Help
1

Greetings,

New to Python, using pyodbc and Sql Server ODBC Driver v17 against a local SQL 2019 instance.

Given the following test3 table;

/****** Object:  Table [dbo].[test3]    Script Date: 11/12/2023 9:30:17 AM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test3]') AND type in (N'U'))
DROP TABLE [dbo].[test3]
GO
/****** Object:  Table [dbo].[test3]    Script Date: 11/12/2023 9:30:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[column1] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test3] ON 
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (1, N'aaa')
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (2, N'bbb')
GO
SET IDENTITY_INSERT [dbo].[test3] OFF
GO

Problem:

The sqlstatement1 returns all two rows of the table
The sqlstatement2 returns zero rows of the table

import pyodbc

connectionString = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=7D3QJR3;DATABASE=mint2;Trusted_Connection=yes'
currentConnection = pyodbc.connect(connectionString) 


sqlStatement1 = '''
SELECT 
	id,
	column1
FROM test3
WHERE
	ISNULL(?, id) = id
ORDER BY 
	ID
    '''

sqlStatement2 = '''
SELECT 
	id,
	column1
FROM test3
WHERE
	ISNULL(?, column1) = column1
ORDER BY 
	ID
    '''

#Process sqlStatement1
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement1,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH ID=NULL:' + str(len(rows)))
cursor.close()

#Process sqlStatement2
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement2,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH COLUMN1=NULL:' + str(len(rows)))
cursor.close()

So why does it work with an int data type but not a string data type?

2

This is not a python problem.
I think the problem is your sql is not doing what you are hoping it will do.

What do you expect isnull to do?
I see docs here SQL Server ISNULL() Function

3

The SQL statement works fine;

SELECT
id,
column1
FROM test3
WHERE
ISNULL(null, column1) = column1
ORDER BY
ID

Returns 2 rows and of course

SELECT
id,
column1
FROM test3
WHERE
ISNULL(‘aaa’, column1) = column1
ORDER BY
ID

Returns 1 row

Now substitute the hardcoded value with the ? positional parameter

5

My gut feeling is the issue has to do with how the sp_prepexec is created for each statement;

If the P1 parameter is compared to an int field it works

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N’@P1 int’,N’
SELECT
id,
column1
FROM test3
WHERE
ISNULL(@P1, id) = id
ORDER BY
ID
',NULL
select @p1

If the P1 parameter is compared to an varchar column it does not work

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N’@P1 varchar(1)',N’
SELECT
id,
column1
FROM test3
WHERE
ISNULL(@P1, column1) = column1
ORDER BY
ID
',NULL
select @p1

because P1 is define varchar(1) for some strange reason, although the column is varchar(10)

6

This means return arg2 if arg1 is null according to the docs.
In other words its the same as column1 = column1 e.g. always true.

This is the same as ‘aaa’ = column1.

That is assuming that I understood the docs.

7

Well answered at: python - Unexpected behaviour when passing None as a parameter value to SQL Server - Stack Overflow

@barry-scott

The where clause is basically the same as saying

WHERE @P1 = id OR @P1 IS NULL

But I did not want to pass the same parameter twice.

Yes, negates the index use.

I ended up casting the value like;

WHERE
    ISNULL(CAST(? AS varchar(10)), column1) = column1