使用Pyodbc包从sql数据库获取数据

Question

我有一个流式应用程序，从sql server数据库中检索数据，我使用pyodbc。

我有一个选择查询，我尝试根据用户输入使用通配符在sql查询中选择数据。

问题是当用户搜索ID旁边的任何字段时它不返回任何东西。 <标题>数据库:

CREATE TABLE [dbo].[t1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[first] [nvarchar](50) NULL,
[last] [nchar](50) NULL,
[Rating] [int] NULL,
CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

<标题>Python代码:

import pandas as pd 
import streamlit as st
advanced_search_term_list = []
if len(advanced_search_term_list)>0:
sql="select * from testDB.dbo.t1 where (ID = ? OR ID is null) and (first LIKE  ? OR first is null) and (last LIKE ? or last is null) and (Rating = ? or Rating is null) "   
param0=advanced_search_term_list[0]
param1=f'%{advanced_search_term_list[1]}%'
param2=f'%{advanced_search_term_list[2]}%'
param3=advanced_search_term_list[3]
rows = cursor.execute(sql,param0,param1,param2,param3).fetchall()

查询只返回param0的数据

我的代码哪里出错了?

Answer 1

如果我理解正确的话，你需要解决这些问题:

• 在WHERE条件的IS NULL部分使用参数，而不是列名(如@Charlieface在评论中解释的)
• 当你使用cursor.execute()时，使用LIKE CONCAT('%', ?, '%')来正确处理Python的None值。

下面的工作示例是您的问题的解决方案:

表:

CREATE TABLE [dbo].[t1] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[first] [nvarchar](50) NULL,
[last] [nchar](50) NULL,
[Rating] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[t1] ([first], [last], [Rating])
VALUES 
('AB', 'CD', 100),
('EF', 'GH', 101),
('IJ', 'KL', 100)

Python:

sql = (
r"select ID, first, last, Rating "
r"from dbo.t1 "
r"where "
r"(ID = ? OR ? IS NULL) AND "
r"(first LIKE CONCAT('%', ?, '%') OR ? IS NULL) AND "
r"(last LIKE CONCAT('%', ?, '%') OR ? IS NULL) AND "
r"(Rating = ? OR ? IS NULL)"   
)
# Only for test. The values of the advanced_search_term_list
# come from the user input.     
advanced_search_term_list = [1, 'AB', None, 100]
param0 = advanced_search_term_list[0]
param1 = advanced_search_term_list[1]
param2 = advanced_search_term_list[2]
param3 = advanced_search_term_list[3]
for row in cursor.execute(sql, [param0, param0, param1, param1, param2, param2, param3, param3]) :
print(row.ID)
print(row.first)
print(row.last)
print(row.Rating)
print("<br>")