Python Forum
cursor.execute: How to insert dynamic number in a string?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
cursor.execute: How to insert dynamic number in a string?
#1
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'""")
Reply
#2
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

Quote: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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
Thumbs Up 
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
 
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  TypeError: int() argument must be a string, a bytes-like object or a number, not 'Non Anldra12 2 1,489 May-02-2021, 03:45 PM
Last Post: Anldra12
  Regular expression: cannot find 1st number in a string Pavel_47 2 779 Jan-15-2021, 04:39 PM
Last Post: bowlofred
  change mouse cursor (in Windows) mgroen 0 400 Nov-11-2020, 11:26 AM
Last Post: mgroen
  Please support regex for version number (digits and dots) from a string Tecuma 4 1,033 Aug-17-2020, 09:59 AM
Last Post: Tecuma
  Make an array of string number in a List polantas 5 1,286 May-27-2020, 07:18 AM
Last Post: buran
  line number of first and second occurance of string in a file mdalireza 1 807 Nov-18-2019, 09:55 AM
Last Post: perfringo
  How can i judge 1st string position is correct number christing 3 1,033 Oct-30-2019, 03:32 AM
Last Post: newbieAuggie2019
  How to avoid slow cursor operations? sevensixtwo 0 846 Oct-11-2019, 02:23 PM
Last Post: sevensixtwo
  String + Number from List BollerwagenIng 5 1,327 Aug-25-2019, 07:47 PM
Last Post: perfringo
  TypeError: int() argument must be a string or a number, not 'NoneType' Hadad 2 4,137 Jul-30-2019, 07:22 PM
Last Post: Hadad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020