How to call a column name dynamically in a sql query, based on the select bar option!

Using Streamlit
1

I have a side bar, named project_bar when all values in the specific column is shown as selectbox.

project_bar = st.sidebar.selectbox('Select Project Name', study["ProjectName"])
               

query1= " select BusinessUnit from [TestTable].[dbo].[ProjectDetails](nolock) where ProjectName = +'project_bar '; 
#I need the value to be dynamically filled here,on whatever the user selects from the sidebar (project_bar)
study1 = pd.read_sql(query1, cnxn)
st.write(study1)

I am trying to execute a query where I have the “where” condition, which gets dynamically filled from the select bar option.

2

Hi @Hiba_Fatima,

You could try using f-strings :slightly_smiling_face:

project_bar = st.sidebar.selectbox('Select Project Name', study["ProjectName"])

query1= f"select BusinessUnit from [TestTable].[dbo].[ProjectDetails](nolock) where ProjectName = '{project_bar}';"

study1 = pd.read_sql(query1, cnxn)
st.write(study1)

Best, :balloon:
Snehan

2 Likes
3

Hi @Hiba_Fatima,

Be aware that constructing sql commands using string formatters opens up the possibility of SQL injection. The way @snehankekre is suggesting should be fine since the input in controlled by the selection box :slight_smile: But if you include anything like a textbox you need to watch out.

image

1 Like
4

When I am using the f-string, I am getting an error

DatabaseError: Execution failed on sql ‘select BusinessUnit from [TestTable].[dbo].ProjectDetails where ProjectName = Test;’: (‘42S22’, “[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘Test’. (207) (SQLExecDirectW)”)

5

when I am running this query, it gives the right output

query1= "select BusinessUnit from [TestTable].[dbo].ProjectDetails where ProjectName = ‘Test’; "

6

You need to add single quotes outside the f-string placeholder:

v = "test"
print(f"This is a '{v}'")

>>> This is a 'test'

2 Likes
8

How else would you properly format a SQL query to avoid something like this?