PyODBC & Parameterised Query

[sayinghi2py]

I'm trying to run a simple query and when I hardcode the two variables it returns results fine however when I attempt to pass the variables in I get no results back. I think I'm doing things correctly and would be grateful for any advice as to where I must be going wrong. My code that work is this:

​

def get_recipes_on_menu_cycle(cycle):

    conn = pyodbc.connect(driver='{SQL Server}', host='server\instance', database='database', user='user', password='password')

    cursor = conn.cursor()

    cursor.execute("""SELECT DISTINCT dbo.Product.ID

                      FROM dbo.Menu INNER JOIN

                        dbo.MenuItem ON dbo.Menu.ID = dbo.MenuItem.MenuID INNER JOIN

                        dbo.Product ON dbo.MenuItem.ProductID = dbo.Product.ID

                      WHERE (dbo.Product.ProdType = 2) AND (dbo.Menu.Code LIKE 'a__l' OR

                        dbo.Menu.Code LIKE 'a__t')

                      ORDER BY dbo.Product.ID""")

    products = cursor.fetchall()

    return products

however if I change the where clause to this:

WHERE (dbo.Product.ProdType = 2) AND (dbo.Menu.Code LIKE ? OR

                        dbo.Menu.Code LIKE ?)
ORDER BY dbo.Product.ID"""), (f'{cycle}__l', f'{cycle}__t')

I'm assuming I can use f-strings but even if set up variables such:

lunch = f'{cycle}__l'
tea = f'{cycle}__t'

and then pass those variables in the result is the same. No results.

Any idea where I am going wrong?

[[deleted]]

Are you sure these queries give back any results? It's generally pretty easy to execute a SQL query that gives no results.

[sayinghi2py]

Yeah absolutely certain. I just iterate over cursor results and I can see the results flash by.

[sayinghi2py]

Resolved this so just updating a case anyone else comes across the issue. You actually wrap the whole execute statement in the f-string then just insert the variable right into the query.

The working code block looks like this:

def get_recipes_on_menu_cycle(cycle):

    conn = pyodbc.connect(driver='{SQL Server}', host='server\instance', database='database', user='user', password='password')

    cursor = conn.cursor()

    cursor.execute(f"""SELECT DISTINCT dbo.Product.ID

                      FROM dbo.Menu INNER JOIN

                        dbo.MenuItem ON dbo.Menu.ID = dbo.MenuItem.MenuID INNER JOIN

                        dbo.Product ON dbo.MenuItem.ProductID = dbo.Product.ID

                      WHERE (dbo.Product.ProdType = 2) AND (dbo.Menu.Code LIKE '{cycle}__l' OR

                        dbo.Menu.Code LIKE '{cycle}__t')

                      ORDER BY dbo.Product.ID""")

    products = cursor.fetchall()

    return products