Created
January 31, 2018 15:26
-
-
Save pinkerltm/9cb8a85a5df5c2f0ba2a6f66b8306815 to your computer and use it in GitHub Desktop.
This is a testcase to show how to insert a couple of rows with spatial geometry content over pyodbc executemany to sqlserver. Unfortunately this is failing if you pass more than one tuple at a time.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment