Python pandas pd.read_sql with parameter from dropdown text value

Question

I am using:

• import pandas as pd
• import dash
• import dash_core_components as dcc
• import dash_html_components as html

I want to execute SQL using pd.read_sql with parameter (the default value of dropdown1 is 'MTL')

   @app.callback(dash.dependencies.Output("graph1", "figure"),
          [dash.dependencies.Input("dropdown1","value")])

   def update_fig1(dropdown1_value):
          df = pd.read_sql('SELECT CONVERT(int,month) as month,CONVERT(int, revenue) as revenue FROM 
                            dbo.Sales_data where region = ' +   dropdown1_value +'',
          cnxn
          )

          df_month = df['month']
          df_revenue = df['revenue']
          data = [
                 {'x': df_month,
                  'y': df_revenue,
                  'type': 'line', 'name': 'SF'
                 }
                 ]

          return {'data': data}

And I get an Error:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT CONVERT(int,month) as month,CONVERT(int, revenue) as revenue FROM dbo.Sales_data where region = MTL': ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'MTL'. (207) (SQLExecDirectW)")

I am very grateful for any help that may come!

Answer 1

When dropdown1_value is a str value, then use:

sql = f'''
SELECT CONVERT(int,month) as month
    ,CONVERT(int, revenue) as revenue 
FROM dbo.Sales_data where region = '{dropdown1_value}'
'''
df = pd.read_sql(sql, cnxn)

while dropdown1_value is a list value, then use:

sql = f'''
SELECT CONVERT(int,month) as month
    ,CONVERT(int, revenue) as revenue 
FROM dbo.Sales_data where region in {tuple(dropdown1_value)}
'''
df = pd.read_sql(sql, cnxn)