I am using pypyodbc to insert a new record into a MS SQL Server table with the parameterized query below.
I've included table structure below. For the execute query, I've added inline comments of debug values at runtime.
sql_statement = """ INSERT INTO [hr].[VMS.Requisitions] (VMSRequisitionID, JobTitle, HiringManagerEEID, GroupID, DepartmentID, LocationID, MinRate, MaxRate, LaborTypeID, RequisitionStatusID, Openings, OpeningsRemaining, RequisitionCreateDate, RequisitionApproveDate, RequisitionOpenDate, RequisitionCloseDate, CreateDate, ModifyDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""" cursor.execute(sql_statement,(requisition.VMSRequisitionID, # {unicode} 'xxx' requisition.JobTitle, # {unicode} 'xxx' requisition.HiringManagerEEID, # {int} 3 requisition.GroupID, # {int} 36 requisition.DepartmentID, # {int} 189 requisition.LocationID, # {int} 44 requisition.MinRate, # {float} 33.5 requisition.MaxRate, # {float} 50.24 requisition.LaborTypeID, # {int} 5 requisition.RequisitionStatusID, # {int} 9 requisition.Openings, # {int} 11 requisition.OpeningsRemaining, # {int} 11 requisition.RequisitionCreateDate, # {datetime} 2017-07-10 11:41:36 requisition.RequisitionApproveDate, # {NoneType} None requisition.RequisitionOpenDate, # {NoneType} None requisition.RequisitionCloseDate, # {NoneType} None now, # {str} '2017-10-04T12:37:14' now, # {str} '2017-10-04T12:37:14' )) cursor.commit() I'm receiving the following error:
ProgrammingError: (u'42000', u'[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.')
I've been debugging this for hours and don't understand what's going wrong. I believe the issue arose when I added in the MinRate and MaxRate columns to the table, so assume there is an issue with the floats not converting properly using a parameterized query.
Table structure is as follows:
RequisitionID int Primary Key, Identity Column / Auto-increment VMSRequisitionID varchar(255) Required JobTitle varchar(255) Required HiringManagerEEID int NULLs allowed GroupID int NULLs allowed DepartmentID int NULLs allowed LocationID int NULLs allowed MinRate float NULLs allowed MaxRate float NULLs allowed LaborTypeID int NULLs allowed RequisitionStatusID int NULLs allowed Openings int NULLs allowed OpeningsRemaining int NULLs allowed RequisitionCreateDate datetime NULLs allowed RequisitionApproveDate datetime NULLs allowed RequisitionOpenDate datetime NULLs allowed RequisitionCloseDate datetime NULLs allowed CreateDate datetime Required ModifyDate datetime Required Any thoughts? I am using Python 2.7 and pypyodbc 1.3.3.
exec sp_columns yourTable;