cursor.execute: How to insert dynamic number in a string?

[stoeberhai]

I'd like to make a SQL-request.
In the mooment SQL-approach and the request are working well.
In the cursor.execute command i coded a static string with an item-number '894204'.
Now I like to change this number dynamically.
How to to this? It's perhaps a silly question for you, but I'm a beginner.

import sys
from os import getenv
import pymssql
import time
import numpy as np
import array
from re import sub
import openpyxl
import subprocess

# Definition vo Variablen und Felder
...
conn = pymssql.connect(server="server" , user="mustermann", password="passwort")
cursor = conn.cursor()
cursor.execute("""SELECT convert(NVARCHAR,b.[Posting Date],104) as Datum
	   ,b.[Posting Date]
	   ,[No_] as Artikel#
        ,a.[DescriptionFUll]
	    ,b.[Document No_]
		,b.[Entry Type]
		,"Vorgang" =
		case
	      when [Entry Type]=0 then 'Einkauf'
		  when [Entry Type]=1 then 'Verkauf'
		  when [Entry Type]=2 then 'Zugang'
		  when [Entry Type]=3 then 'Abgang'
		  when [Entry Type]=4 then 'Umlagerung'
		  when [Entry Type]=5 then 'Verbrauch'
		  when [Entry Type]=6 then 'Istmeldung'
		  when [Entry Type]=7 then 'N.N'
		  when [Entry Type]=8 then 'Verbrauch für Montage'
		  when [Entry Type]=9 then 'Montage-Istmeldung'
		end
		,cast(round(b.[Quantity],2,2) as real) as Menge
      ,[Base Unit of Measure]
      ,cast(round([Unit Cost],2) as real) as 'EuroProEinheit'
      ,[Blocked]
      ,cast(round([Lot Size],2,2) as real) as Losgröße
      ,cast(round([Safety Stock Quantity],2,2)as real) as Sich_Bestand     
	  ,[Zeichnungsnummer]
      ,[Inventur]
      ,[Blocked Purchase]
FROM [Mustermann BC].[dbo].[Mustermann_GmbH$Item] as a
   LEFT JOIN [Mustermann BC].[dbo].[Mustermann_GmbH$Item Ledger Entry] AS b ON a.No_ = b.[Item No_]
where No_ like '894204'""")

[buran]

use parametrized query. look at the last example in the docs
https://pythonhosted.org/pymssql/pymssql...compliance

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

Also the docs https://pythonhosted.org/pymssql/ref/pym...or.execute

Cursor.execute(operation)
Cursor.execute(operation, params)

operation is a string and params, if specified, is a simple value, a tuple, a dict, or None.

Performs the operation against the database, possibly replacing parameter placeholders with provided values. This should be preferred method of creating SQL commands, instead of concatenating strings manually, what makes a potential of SQL Injection attacks. This method accepts formatting similar to Python’s builtin string interpolation operator. However, since formatting and type conversion is handled internally, only the %s and %d placeholders are supported. Both placeholders are functionally equivalent.

Keyed placeholders are supported if you provide a dict for params.

If you call execute() with one argument, the % sign loses its special meaning, so you can use it as usual in your query string, for example in LIKE operator. See the examples.

You must call Connection.commit() after execute() or your data will not be persisted in the database. You can also set connection.autocommit if you want it to be done automatically. This behaviour is required by DB-API, if you don’t like it, just use the _mssql module instead.

[stoeberhai]

Thank you @buran !
Your hint does the trick! Problem solved.
My altered source code for people who want to know the solution:

Artikelnummer='894204'	
# ^^^^^^^^^^^^^^^^^^^
# "Artikelnummer" as a string
conn = pymssql.connect(server="server" , user="mustermann", password="passwort")
cursor = conn.cursor()
cursor.execute("""SELECT convert(NVARCHAR,b.[Posting Date],104) as Datum
	   ,b.[Posting Date]
	   ,a.[No_] as Artikel#
        ,a.[DescriptionFUll]
	    ,b.[Document No_]
		,b.[Entry Type]
		,"Vorgang" =
		case
	      when [Entry Type]=0 then 'Einkauf'
		  when [Entry Type]=1 then 'Verkauf'
		  when [Entry Type]=2 then 'Zugang'
		  when [Entry Type]=3 then 'Abgang'
		  when [Entry Type]=4 then 'Umlagerung'
		  when [Entry Type]=5 then 'Verbrauch'
		  when [Entry Type]=6 then 'Istmeldung'
		  when [Entry Type]=7 then 'N.N'
		  when [Entry Type]=8 then 'Verbrauch für Montage'
		  when [Entry Type]=9 then 'Montage-Istmeldung'
		end
		,cast(round(b.[Quantity],2,2) as real) as Menge
      ,[Base Unit of Measure]
      ,cast(round([Unit Cost],2) as real) as 'EuroProEinheit'
      ,a.[Blocked]
      ,cast(round([Lot Size],2,2) as real) as Losgröße
      ,cast(round([Safety Stock Quantity],2,2)as real) as Sich_Bestand     
	  ,[Zeichnungsnummer]
      ,[Inventur]
      ,[Blocked Purchase]
	  ,Lieferant.No_
	  ,Lieferant.Name
	  ,Lieferant.City
FROM [Mustermann].[dbo].[Mustermann_GmbH$Item] as a
   LEFT JOIN [Mustermann].[dbo].[Mustermann_GmbH$Item Ledger Entry] AS b ON a.No_ = b.[Item No_]
   LEFT JOIN [Mustermann].[dbo].[Mustermann_GmbH$Vendor] as Lieferant on a.[Vendor No_] = Lieferant.No_
   where a.No_ like %s""", Artikelnummer)
#                   ^^     ^^^^^^^^^^^^^
#  format and the variable Artikelnummer again