0
votes

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!

3
so what type is the dropdown1_value? str or list? - Ferris

3 Answers

0
votes

As suggested by the error code, you are not referencing the string MTL and SQL Server defaults to understanding MTL as a column reference. Surround your value by single quotes and it should work.

      df = pd.read_sql('SELECT CONVERT(int,month) as month,CONVERT(int, revenue) as revenue FROM 
                        dbo.Sales_data where region = \'' +   dropdown1_value + '\'',
0
votes

Thanks for your reply. So now I get an error:

   File "C:\Users\007\PycharmProjects\pythonProject5\main.py", line 377, in update_fig1
   df = pd.read_sql('SELECT CONVERT(int,month) as month,CONVERT(int, revenue) as 
   revenue FROM dbo.Sales_data where region = \'' + dropdown1_value + '\'',
   

TypeError: can only concatenate str (not "list") to str

0
votes

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)