Retrieve data from SQL Server database using Python and PYODBC

Question

I have Python code that connects with SQL Server database using PYODBC and Streamlit to create a web app.

The problem is when I try to perform a select query with multiple conditions the result is empty where as the result it must return records.

If I try the SQL query direct on the database it return the below result:

SELECT        TOP (200) ID, first, last
FROM            t1
WHERE        (first LIKE '%tes%') AND (last LIKE '%tesn%')

where as the query from the python it return empty

sql="select * from testDB.dbo.t1 where ID = ? and first LIKE '%' + ? + '%' and last LIKE '%' + ? + '%' "
param0 = vals[0]
param1=f'{vals[1]}'
param2=f'{vals[2]}'
rows = cursor.execute(sql, param0,param1,param2).fetchall()

Code:

import pandas as pd
import streamlit as st

vals = []
expander_advanced_search = st.beta_expander('Advanced Search')
with expander_advanced_search:
for i, col in enumerate(df.columns):
      val = st_input_update("search for {}".format(col))
      expander_advanced_search.markdown(val, unsafe_allow_html=True)
      vals.append(val)
                                
      if st.form_submit_button("search"):
                            
         if len(vals)>0:
            sql='select * from testDB.dbo.t1 where ID = ? and first LIKE  ? and last LIKE ?  '
            param0 = vals[0]
            param1=f'%{vals[1]}%'
            param2=f'%{vals[2]}%'
            rows = cursor.execute(sql, param0,param1,param2).fetchall()
      df = pd.DataFrame.from_records(rows, columns = [column[0] for column in cursor.description])
      st.dataframe(df)

Based on suggestion of Dale k I use the OR operator in the select query:

sql="select * from testDB.dbo.t1 where ID = ? OR first LIKE  ? or last LIKE ? "
param0 = vals[0]   # empty
param1=f'%{vals[1]}%' # nabi
param2=f'%{vals[2]}%' # empty 
rows = cursor.execute(sql, param0,param1,param2).fetchall()

The displayed result:

all the records in the database

The expected result:

id first  last
 7 nabil jider

When you use parameters like that, which is correct, the values are quoted. You need to do as follows `first LIKE '%' + ? + '%'` and remove the `%` from your passed in parameter values. — Dale K, Jul 01 '21 at 20:23
@DaleK about the first comment i post images in order to make my question more understandable and more clear. and about the second comment the parameter values will becomes `param1=f'{vals[1]}'` ? — DevLeb2022, Jul 01 '21 at 20:27
@DaleK i tried your second comment it still return empty result. — DevLeb2022, Jul 01 '21 at 20:31

dww142 · Answer 1 · 2021-07-01T20:50:31.140

0

I think this is probably in your parameters - your form is only submitting first/last values, but your query says ID=? You're not providing an ID from the form so there are no results. Or it's putting the value from the 'first' input into vals[0] and the resulting query is looking for an ID = 'tes'.

Also, look into pd.read_sql() to pipe query results directly into a DataFrame/

OR statement might be what you're after if you want each clause treated separately:

where ID = ? or first LIKE  ? or last LIKE ?'

edited Jul 01 '21 at 20:50

answered Jul 01 '21 at 20:32

dww142

96
4

i am trying to make the query dynamic where the user will input the ONE OF THE REQUIRED INPUT or will put all of them... and about `Or it's putting the value from the 'first' input into vals[0] and the resulting query is looking for an ID = 'tes'.` My answer is no because in the debugging it shows that the `vals[0] is the ID vals[1] is the first vals[2] is the last` – DevLeb2022 Jul 01 '21 at 20:37
@khaledM_dev "ONE OF THE REQUIRED INPUT" implies you need **OR** instead of **AND**. AND means **ALL* inputs required. So actually the query you are using in code is not the same as the query you ran in SSMS. – Dale K Jul 01 '21 at 20:40
That's a more complicated where clause to account for a combination of three values that may or may not be present. What does your debugger show in vals[0] when you leave ID blank on the form? – dww142 Jul 01 '21 at 20:41
@DaleK so how write a query in order to allow the user to input 1 or 2 of the fields or if the user will input all the fields will narrow his search. – DevLeb2022 Jul 01 '21 at 20:45
@khaledM_dev as I said, use **OR**. – Dale K Jul 01 '21 at 20:46
https://stackoverflow.com/questions/333965/sql-search-query-for-multiple-optional-parameters – Dale K Jul 01 '21 at 20:47
@dww142 i tried your query but it still return empty – DevLeb2022 Jul 01 '21 at 20:48
@DaleK if i use `OR` it will return the result where ever the user put his search parameter – DevLeb2022 Jul 01 '21 at 20:52
@Dalek might be right, try it with OR's - this will evaluate each filter separately, if you put first like '%tes%' or last like '%tesn%' - you'll get 'tes tesn' as well as 'joe tesn' and 'tes smith' – dww142 Jul 01 '21 at 20:52
1

@khaledM_dev I guess the thing is you, said your query worked in SSMS but not in code. Therefore we assumed you knew how to write the logic, just not how to covert to calling from code. However it seems your real issue is you don't understand how to write the logic. The link I posted a few comments above explains this. – Dale K Jul 01 '21 at 20:55
@DaleK i will edit my question and display the result if i use select query with `OR` – DevLeb2022 Jul 01 '21 at 21:00
@khaledM_dev the link I gave you above shows the logic as `WHERE (@var1 IS NULL OR col1 = @var1) AND (@var2 IS NULL OR col2 = @var2)` so you only include the condition if it exits. – Dale K Jul 01 '21 at 22:43

Retrieve data from SQL Server database using Python and PYODBC

1 Answers1