A spike test on using turbodbc to bulk insert geometry data into SQL Server as Well known Text (WKT)

turbodbc_spike.py

from turbodbc import connect, make_options, Error

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

options = make_options(prefer_unicode=True)
connection = connect(connection_string='DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format('{SQL Server}', host,database,user,password), turbodbc_options=options)

cursor = connection.cursor()

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)
connection.commit()

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

# First row in params fails due to length while second line is inserted successfully
params = [
    (0.0, '30516', 1.0, -1, -1.0, 0, -1, 0, 0, 8, 'GW', -1, 18142898, 40, -1.0, -1.0, 3, 18163779, -1, 2383631, 846.21, 2, 1.0, 2383631, 0, 3.5, 18428652.0, -1, 23028122, 2, 6428467, -1, -1, None, 40, 'AT30516', 2, 407, 5, 'Thomasberg', -1, -1, 'LINESTRING (508433.3261117005022243 434364.0341346164350398, 508459.5245631239959039 434371.9956868125009350, 508475.6284347525215708 434375.6747556913178414, 508490.9875452077249065 434380.2236141046741977, 508504.8132094207685441 434381.7904485370963812, 508517.4603264195029624 434386.7501219468540512, 508539.4798043834744021 434393.2526225033798255, 508560.9915493988664821 434391.8819606795441359, 508582.3548260809038766 434391.9199096616357565, 508598.1429101738613099 434397.0949351059389301, 508609.2502805865369737 434405.2927266536280513, 508624.1251000810880214 434410.4401903540710919, 508640.9496285537024960 434412.4592354526976123, 508661.8782507456489839 434414.6450052090222016, 508686.5920971700688824 434419.9781158885452896, 508705.8453034865087830 434423.6541053796536289, 508730.7016713873599656 434430.5556342828203924, 508750.2826266027987003 434432.2281003281823359, 508765.4931387357646599 434428.7066459648776799, 508782.6964992322027683 434421.1442076104576699, 508802.9242734478903003 434417.8170347625273280, 508818.8001917939400300 434420.7214809894212522, 508833.6754226235207170 434425.8611591696972027, 508883.0315617772866972 434437.3685238940524869, 508903.3940192615846172 434442.2674678454641253, 508923.3646070389659144 434445.7836876179208048, 508938.3454951148014516 434448.1923580508446321, 508955.1700395019724965 434450.2114019812433980, 508974.3612813202198595 434450.0402065824018791, 508996.3166339196031913 434449.5228287585196085, 509015.0998984141624533 434449.5938005786738358, 509034.9297484184498899 434450.6898510238970630, 509116.9255233139847405 434445.8942052292404696, 509136.3073563852813095 434444.4331537397229113, 509157.8554684121045284 434442.5246493519516662, 509177.4364442109363154 434444.1972103290027007, 509199.8012042717309669 434444.6100153125007637, 509216.6786373357754201 434445.2635528836399317, 509237.2785357464454137 434444.2326639262028039, 509261.8204747704439797 434441.6976945521892048)')
    ,
    (0.0, '32525', 1.0, -1, -1.0, 0, -1, 0, 0, 8, 'GW', -1, 18179031, 40, -1.0, -1.0, 3, 18178575, -1, 2383631, 27.34, 2, 1.0, 2383631, 0, 4.0, 18365890.0, -1, 23028151, 2, 6428468, -1, -1, None, 40, 'AT32525', 2, 407, 5, 'Siebenlinden', -1, -1, 'LINESTRING (522263.1287272393237799 531124.9973289136542007, 522275.9491660660714842 531119.9391361826565117, 522288.3547260488267057 531114.4974003466777503)')
]
cursor.executemany(sql, params)
connection.commit()