python pyodbc SQL Server Native Client 11.0 cannot return geometry column

Question

I have SQL Server Native Client 11.0 and pyodbc installed using python 2.7. I am able to set the connection correctly within python

import pyodbc
conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=b;DATABASE=b;UID=b;PWD=b')
cur=conn.cursor()
query=cur.execute('''select top 1 * from table''')
for x in query.fetchall():
    print x

Traceback (most recent call last):
  File "<module1>", line 8, in <module>
ProgrammingError: ('ODBC SQL type -151 is not yet supported.  column-index=40  type=-151', 'HY106')

if i do, i get the same error

for x in query:
   print x

how do I return the query?

if I just print query I get pyodbc.Cursor object at 0x02B74AD8

could it be column type it cannot read, there is a point geometry in this table.

UPDATE

the problem is with the point geometry. when I take out the geometry I can return what the cursor contains. however when the geometry column is in there it always throughs this error. I am assuming the pyodbc does not have support for geometric objects... as a work around I can convert to WKT

Similar issue fixed: https://stackoverflow.com/questions/46405373/odbc-sql-type-155-is-not-yet-supported — Alexander Volok, Feb 25 '19 at 19:45
the problem does seem to be with pyodbc returning the geometry — ziggy, Mar 04 '19 at 21:44

Gord Thompson · Accepted Answer · 2019-03-06T16:56:43.327

Based on the GitHub issue here, the following output converter function seems to do the trick:

def unpack_geometry(raw_bytes):
    # adapted from SSCLRT information at
    #   https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-ssclrt/dc988cb6-4812-4ec6-91cd-cce329f6ecda
    tup = struct.unpack('<i2b3d', raw_bytes)
    # tup contains: (unknown, Version, Serialization_Properties, X, Y, SRID)
    return tup[3], tup[4], tup[5]

# ...

cnxn.add_output_converter(-151, unpack_geometry)
crsr.execute("SELECT CAST('POINT(-79.528874 43.648533 12345)' AS geometry)")
print(crsr.fetchval())  # (-79.528874, 43.648533, 12345.0)

python pyodbc SQL Server Native Client 11.0 cannot return geometry column

1 Answers1

Linked