import pyodbc

## Provide connection details here
host = ''
database = ''
user = ''
password = ''

connection = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format('{SQL Server}', host,database,user,password))
cursor = connection.cursor()
cursor.fast_executemany = True

ddl = '''
IF OBJECT_ID('[dbo].[emtest]') IS NOT NULL
  DROP TABLE [dbo].[emtest]

CREATE TABLE [dbo].[emtest](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL,
	[Shape] [geometry] NULL,
	[LINK_ID] [numeric](10, 0) NULL,
	[NAME1] [nvarchar](254) NULL,
	[NAME2] [nvarchar](254) NULL,
	[FROM_NODE] [numeric](10, 0) NULL,
	[TO_NODE] [numeric](10, 0) NULL,
	[SPEEDCAR_T] [int] NULL,
	[SPEEDCAR_B] [int] NULL,
	[SPEEDTRU_T] [int] NULL,
	[SPEEDTRU_B] [int] NULL,
	[VMAX_CAR_T] [int] NULL,
	[VMAX_CAR_B] [int] NULL,
	[VMAX_TRU_T] [int] NULL,
	[VMAX_TRU_B] [int] NULL,
	[ACCESS_TOW] [numeric](10, 0) NULL,
	[ACCESS_BKW] [numeric](10, 0) NULL,
	[LENGTH] [numeric](7, 2) NULL,
	[FRC] [int] NULL,
	[CAP_TOW] [numeric](10, 0) NULL,
	[CAP_BKW] [numeric](10, 0) NULL,
	[LANES_TOW] [numeric](2, 1) NULL,
	[LANES_BKW] [numeric](2, 1) NULL,
	[FORMOFWAY] [int] NULL,
	[BRUNNEL] [int] NULL,
	[MAXHEIGHT] [numeric](4, 1) NULL,
	[MAXWIDTH] [numeric](4, 1) NULL,
	[MAXPRESS] [numeric](4, 1) NULL,
	[ABUTTERCAR] [int] NULL,
	[ABUTTERTRU] [int] NULL,
	[URBAN] [numeric](10, 0) NULL,
	[WIDTH] [numeric](4, 1) NULL,
	[INT_LEVEL] [numeric](3, 1) NULL,
	[TOLL] [int] NULL,
	[BAUSTATUS] [int] NULL,
	[SUBNET_ID] [numeric](10, 0) NULL,
	[ONEWAY_CAR] [int] NULL,
	[ONEWAY_BK] [int] NULL,
	[ONEWAY_BUS] [int] NULL,
	[EDGE_ID] [numeric](19, 0) NULL,
	[EDGECAT] [nvarchar](3) NULL,
	[REGCODE] [nvarchar](31) NULL,
	[SUSTAINER] [nvarchar](19) NULL,
	[SDE_ID] [numeric](10, 0) NULL,
	[Shape_STLe] [numeric](38, 8) NULL
)'''

cursor.execute(ddl)
cursor.commit()


sql = 'INSERT INTO emtest (SDE_ID,WIDTH,TO_NODE,CAP_TOW,URBAN,VMAX_CAR_T,LENGTH,LANES_BKW,SHAPE,SPEEDTRU_T,SUSTAINER,REGCODE,MAXPRESS,INT_LEVEL,SPEEDCAR_T,FROM_NODE,SPEEDTRU_B,ONEWAY_BUS,ABUTTERCAR,TOLL,MAXWIDTH,MAXHEIGHT,NAME1,NAME2,CAP_BKW,SPEEDCAR_B,ACCESS_TOW,EDGECAT,EDGE_ID,LANES_TOW,FRC,ONEWAY_CAR,LINK_ID,VMAX_TRU_B,VMAX_TRU_T,ACCESS_BKW,FORMOFWAY,SUBNET_ID,VMAX_CAR_B,ABUTTERTRU,ONEWAY_BK,BAUSTATUS,BRUNNEL) Values (?,?,?,?,?,?,?,?,geometry::STGeomFromWKB(?, 31287),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

## this results in an error "pyodbc.DataError: ('String data, right truncation: length 89 buffer 73', '22001')"
params = [
    (6429256, 5.0, 18128899, -1, 1, -1, 50.17, 1.0, b"\x01\x02\x00\x00\x00\x04\x00\x00\x00\x86\x8d_\xa93O A\x98\xe9\xfa\xd4\xd0w\x1dA\xa9\x17#\x8bBO A\x12\xdb\xdb1\xb6w\x1dA\x8e\x9b \xc0vO A\xeeb\xa5'pw\x1dA\xeaWd;\x87O A\xc8'\xbf\x8ddw\x1dA", 0, '31523', 'AT31523', -1.0, 0.0, 15, 18017250, 0, 2, 0, -1, -1.0, -1.0, 'Untererla', None, -1, 15, 2383631, 'G', 18435988.0, 1.0, 12, 2, 23033181, -1, -1, 2383631, 3, 407, -1, 0, 2, 5, -1),
    (6428457, 5.0, 1579262, -1, 1, -1, 122.96, 1.0, b'\x01\x02\x00\x00\x00\x05\x00\x00\x00\xb8i\x84\x80>\t"A\xe9\xa8+-\xa7W\x1eA\xd5\xad~\x83D\t"A\xcc\xf1\xa1\xb8>Y\x1eA\x08w\x82\x8eG\t"Aw\xcb|\xb3rY\x1eA\x98R\xa3\nI\t"AQT\xb6r\x80Y\x1eA\xc6g\xec\xb3K\t"A\x9b\x1a\x8c9\x91Y\x1eA', 0, '32141', 'AT32141', -1.0, 0.0, 34, 18062246, 0, 2, 0, -1, -1.0, -1.0, 'Hauptstraße', 'Zwentendorf a.d.Donau', -1, 34, 2383631, 'GW', 18318305.0, 1.0, 5, 2, 23028031, -1, -1, 2383631, 3, 407, -1, 0, 2, 5, -1)
]

## this works
#params = [
#    (6428457, 5.0, 1579262, -1, 1, -1, 122.96, 1.0, b'\x01\x02\x00\x00\x00\x05\x00\x00\x00\xb8i\x84\x80>\t"A\xe9\xa8+-\xa7W\x1eA\xd5\xad~\x83D\t"A\xcc\xf1\xa1\xb8>Y\x1eA\x08w\x82\x8eG\t"Aw\xcb|\xb3rY\x1eA\x98R\xa3\nI\t"AQT\xb6r\x80Y\x1eA\xc6g\xec\xb3K\t"A\x9b\x1a\x8c9\x91Y\x1eA', 0, '32141', 'AT32141', -1.0, 0.0, 34, 18062246, 0, 2, 0, -1, -1.0, -1.0, 'Hauptstraße', 'Zwentendorf a.d.Donau', -1, 34, 2383631, 'GW', 18318305.0, 1.0, 5, 2, 23028031, -1, -1, 2383631, 3, 407, -1, 0, 2, 5, -1)
#]

cursor.executemany(sql, params)
cursor.commit()