MSSQL 'Numeric Value Out of Range' Error for 20 digit python Long Int into Numeric(24,0) Column

I am attempting to insert a 20 digit, 64 bit python long integer into a MSSQL Numeric(24,0) column. This results in a 'Numeric Value Out of Range' Error from MSSQL. I am using the pypyodbc module and ODBC Driver 13 for SQL Server to INSERT the data from my python application.

I've tried using a Numeric(38,0) column in SQL just to test the limit, but receive the same error. In the insert statement, I've also attempted explicitly casting the id as a Numeric(24,0) data type. All attempts resulted in the same error.

#Python SQL Insert Code
id = 'ADD7A9FA-E77B-4BBB-92AA-3D9C7BBB44D0'
idlist =  id.split('-')

val = [int(idlist[0] + idlist[1] + idlist[2], 16), 
int(idlist[3] + idlist[4], 16)]

cmd = "INSERT INTO jssuser.dbo.API_VIMSPost (\
[id_int1], \
[id_int2]) \
VALUES (?, ?)"

#function to simplify the use of pypyodbc
sqlcmd.sqlCmd(cmd, values = val)

#SQL Table Code
USE [jssuser]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[API_VIMSPost](
    [id_int1] [numeric](24, 0) NOT NULL,
    [id_int2] [numeric](24, 0) NOT NULL,
 CONSTRAINT [PK_PostID] PRIMARY KEY CLUSTERED 
(
    [id_int1] ASC,
    [id_int2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I would expect that a python long int of 20 digits and 64 bits would fit into a MSSQL column of type Numeric(20,0) or greater. However, an Insert results in a 'Numeric Value Out of Range' error.

Solution

The size of Python integer values is only limited by available memory; they are not restricted to an arbitrary number of bits. However, the largest integer value that the SQL Server ODBC driver(s) can handle is a (64-bit signed) bigint whose maximum positive value is math.pow(2, 63) - 1, or 9223372036854775807 which is nineteen (19) digits long.

When pypyodbc tries to pass a 20-digit integer the ODBC driver chokes on it, so this fails

x = 13807673592980537824
crsr.execute("CREATE TABLE ##tmp (id INT PRIMARY KEY, id_int1 NUMERIC(24, 0))")
sql = "INSERT INTO ##tmp (id, id_int1) VALUES (?, ?)"
params = (1, x)
crsr.execute(sql, params)

However, the following works because pypyodbc doesn't tell the ODBC driver to expect an integer

x = 13807673592980537824
crsr.execute("CREATE TABLE ##tmp (id INT PRIMARY KEY, id_int1 NUMERIC(24, 0))")
sql = "INSERT INTO ##tmp (id, id_int1) VALUES (?, ?)"
params = (1, Decimal(x))  # convert Python `int` to Python `decimal.Decimal`
crsr.execute(sql, params)