CODEWITHSUNDEEP
pythonsqlstored-procedurespyodbc
Katelyn Clasper
Katelyn Clasper

Reputation: 23

How can I get pyodbc to use user inputs as parameters for a stored procedure?

I have created an executable app which takes three user inputs (material, quantity, cost). My code currently uses pyodbc to connect to a SQL server and run a stored procedure.

The problem I'm having is that python can't seem to convert quantity and cost to decimals and my stored procedure needs them as decimal(18,2).

I keep getting the error: pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type nvarchar to decimal. (8114)

I have tried specifying quantity and cost using the decimal package for python i.e. decimal.Decimal(). I have also tried using float() although I don't think this is good practice.

Relevant Code:

#Packages:
import os, sqlite3, xlrd, pandas as pd, win32com.client, wx, pyodbc
import wx.grid as grid, wx.aui as aui
from openpyxl import load_workbook
from tabulate import tabulate
from datetime import datetime

self.Material = wx.TextCtrl(top, value = "",pos = (40,60))
self.Quant = wx.TextCtrl(top, value = "",pos = (190,60))
self.Cost = wx.TextCtrl(top, value = "",pos = (340,60))

d1 = datetime.now()
person = os.getlogin()
material = self.Material.GetValue()
quantity = float(self.Quant.GetValue())
cost = float(self.Cost.GetValue())

conn= pyodbc.connect('Driver=SQL Server Native Client 11.0;'
                            'Server=J211;'
                            'Database=T3_Forecasting_TRS;'
                            'Trusted_Connection=yes;')
cursor = conn.cursor()
#Run Stored Procedure
param = (d1, person, material, quantity, cost)
cursor.execute("execute SP_KC_RunMOQRules d1, person, material, quantity, cost")
cursor.commit

Upvotes: 0

Views: 176

Answers (1)

Katelyn Clasper
Katelyn Clasper

Reputation: 23

As per Murphy's Law, the minute I ask for help I find a solution. For anyone interested or stick on a similar issue, I worked around it by changing how I called the stored procedure.

OLD CODE:

param = (d1, person, material, quantity, cost)
    cursor.execute("execute SP_KC_RunMOQRules d1, person, material, quantity, cost")
    cursor.commit

NEW CODE:

sql=""" EXEC SP_KC_RunMOQRules @Date=?, @User=?, @Material=?, @Quant=?, @Cost=?"""
    param = (d1, person, material, quantity, cost)
    cursor.execute(sql,param)
    cursor.commit

Upvotes: 1

Related Questions