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
Question Extracting Version Number from a String britesc 2 1,030 May-31-2023, 10:20 AM
Last Post: britesc
Question Sqlite3 how to know when cursor.execute didn't return anything ? SpongeB0B 2 810 Dec-18-2022, 06:13 PM
Last Post: deanhystad
  TypeError: float() argument must be a string or a number, not 'list' Anldra12 2 4,757 Jul-01-2022, 01:23 PM
Last Post: deanhystad
  Cursor Variable inside Another Cursor . CX_ORacle paulo79 1 1,477 Apr-09-2022, 10:24 AM
Last Post: ibreeden
  loop for dynamic cut string - cleaner way? korenron 4 1,876 Nov-22-2021, 02:30 PM
Last Post: korenron
  TypeError: int() argument must be a string, a bytes-like object or a number, not 'Non Anldra12 2 5,103 May-02-2021, 03:45 PM
Last Post: Anldra12
  Regular expression: cannot find 1st number in a string Pavel_47 2 2,364 Jan-15-2021, 04:39 PM
Last Post: bowlofred
  Please support regex for version number (digits and dots) from a string Tecuma 4 3,102 Aug-17-2020, 09:59 AM
Last Post: Tecuma
  Make an array of string number in a List polantas 5 3,023 May-27-2020, 07:18 AM
Last Post: buran
  line number of first and second occurance of string in a file mdalireza 1 1,797 Nov-18-2019, 09:55 AM
Last Post: perfringo

Forum Jump:

User Panel Messages

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