2

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.

user1944673
  • 279
  • 1
  • 4
  • 13
  • Pls can you show your table structure ? `exec sp_columns yourTable;` – Loïc Oct 04 '17 at 21:35
  • 1
    @Loïc I could not run your query in my environment, but edited above issue description with the table structure. Thanks for looking! – user1944673 Oct 04 '17 at 22:06
  • that is weird, your query looks fine from my point of view. Are you sure this is the one query triggering the crash ? Could you print the SQL being generated for the insert ? – Loïc Oct 04 '17 at 22:20
  • Hmmm I'm unable to pull the query via debugging, but am certain there is something going wrong with the float values. I assume SQL Server is interpreting the values as shortdatetime instead of floats for some reason. – user1944673 Oct 04 '17 at 23:36
  • are you able to highlight the field.. – RoMEoMusTDiE Oct 04 '17 at 23:58
  • Can you [edit] your question with the versions of Python and pypyodbc that you are using? I am unable to reproduce your issue using the test code [here](https://pastebin.com/nmNFD2nr). – Gord Thompson Oct 05 '17 at 00:09
  • can you check this : https://stackoverflow.com/questions/5266430/how-to-see-the-real-sql-query-in-python-cursor-execute I think that being able to see the real sql insert would help lots. There are other ways like this : https://stackoverflow.com/questions/13638435/last-executed-queries-for-a-specific-database – Loïc Oct 05 '17 at 00:40
  • Unfortunately it looks like capturing the actual query is not possible using pyodbc: https://stackoverflow.com/questions/17591459/how-to-get-prepared-query-which-is-sent-to-db. I also don't have the access rights to perform the query in your second link, @Loïc. – user1944673 Oct 05 '17 at 16:40
  • @GordThompson I've updated the question with my versions of Python and pypyodbc. Thanks for helping troubleshoot...I'm still at a loss for what is going on here. – user1944673 Oct 05 '17 at 16:44
  • maybe you should use decimal instead of float with python for MinRate / MaxRate – Loïc Oct 05 '17 at 17:21
  • Also unable to reproduce the issue with Python 2.7.13 and pypyodbc 1.3.5 (PyCharm wouldn't install 1.3.3). My test code is [here](https://pastebin.com/jHRg1hUd). Can you reproduce with pypyodbc 1.3.5? – Gord Thompson Oct 05 '17 at 17:37
  • I upgraded my pypyodbc to 1.3.5 and still see the failure. I also changed the data type to Decimal, resulting in the same error message with "float" replaced by "decimal." – user1944673 Oct 05 '17 at 23:50
  • This leads me to believe the values "33.5" and "50.24" in the above example are being interpreted as smalldatetimes, not float/decimals. – user1944673 Oct 05 '17 at 23:53
  • Can you double-check that `type(requisition.MinRate)` returns `` ...? – Gord Thompson Oct 06 '17 at 15:59
  • Yes I have confirmed that it is of type float. – user1944673 Oct 06 '17 at 23:46
  • I've implemented a workaround for now, as I've still been unable to resolve the issue. Thank you everyone for the help! – user1944673 Oct 06 '17 at 23:46

0 Answers0