pythonsql-serversqlgeographyjaydebeapi

How to insert geography data to SQL Server with Python jaydebiapi cursor.executemany()?

I did insert data into SQL Server using cursor.execute().

Here is my code snippet:

insert_str = """INSERT INTO [test].[DBO].[SPATIAL] VALUES({},{});"""

geography = "geography::STGeomFromText('LINESTRING(-95.323167 29.985500, -95.323333 29.985500)', 4326)"

cursor.execute(insert_str.format(123, geography))

But when I use cursor.executemany() with the code snippet below, it didn't work:

insert_str = """INSERT INTO [test].[DBO].[SPATIAL] VALUES(?,?);"""

geography = "geography::STGeomFromText('LINESTRING(-95.323167 29.985500, -95.323333 29.985500)', 4326)"

cursor.executemany(insert_str, [(122, geography)])

This is the Traceback log: 

Traceback (most recent call last):
  File "spatial_point.py", line 35, in <module>
    run()
  File "spatial_point.py", line 33, in run
    cursor.executemany(insert_str, [(122, geography)])
  File "/root/anaconda3/lib/python3.7/site-packages/jaydebeapi/__init__.py", line 518, in executemany
    update_counts = self._prep.executeBatch()
jpype._jexception.BatchUpdateExceptionPyRaisable: java.sql.BatchUpdateException: A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24114: The label geography::STGeomFro in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException:
   at Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

Does anyone know what is going wrong here? Thanks

Solution

  • The first approach simply formats the text INSERT INTO [test].[DBO].[SPATIAL] VALUES({},{}); and replaces the positional arguments with values 123 and "geography::STGeomFromText('LINESTRING(-95.323167 29.985500, -95.323333 29.985500)', 4326)".

    The second approach uses placeholders, so the statement should be different. You need to pass the WKT representation of the geography instance and the spatial reference ID as parameters for the geography::STGeomFromText call:

    insert_str = """INSERT INTO [test].[DBO].[SPATIAL] VALUES(?, geography::STGeomFromText(?, ?);"""
cursor.executemany(insert_str, [(122, 'LINESTRING(-95.323167 29.985500, -95.323333 29.985500)', 4326)])