python pyodbc sql server native client 11.0 cannot return geometry column

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: //docs.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)

Suggestion : 2

could it be column type it cannot read, anycodings_pyodbc there is a point geometry in this table. ,I have SQL Server Native Client 11.0 and anycodings_pyodbc pyodbc installed using python 2.7. I am able anycodings_pyodbc to set the connection correctly within anycodings_pyodbc python ,Based on the GitHub issue here, the anycodings_pyodbc following output converter function anycodings_pyodbc seems to do the trick:,how do I return the query?

I have SQL Server Native Client 11.0 and anycodings_pyodbc pyodbc installed using python 2.7. I am able anycodings_pyodbc to set the connection correctly within anycodings_pyodbc 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

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

def unpack_geometry(raw_bytes):
   # adapted from SSCLRT information at
# https: //docs.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)

Suggestion : 3

This driver implements support for access to spatial tables in Microsoft SQL Server 2008+ which contains the geometry and geography data types to represent the geometry columns.,Tables=schema1.table1(geometry column1),schema2.table2(geometry column2): By using this parameter you can specify the subset of the layers to be used by the driver. If this parameter is not set, the layers are retrieved from the geometry_columns metadata table. You can omit specifying the schema and the geometry column portions of the syntax.,In addition to the standard parameters of the ODBC driver connection string format the following custom parameters can also be used in the following syntax:,This driver doesn’t support creating new databases, you might want to use the Microsoft SQL Server Client Tools for this purpose, but it does allow creation of new layers within an existing database.

MSSQL: server = .\MSSQLSERVER2008;
database = dbname;
trusted_connection = yes

ogr2ogr - overwrite - f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes"
"rivers.tab"

ogr2ogr - overwrite - f MSSQLSpatial "MSSQL:server=127.0.0.1;database=TestDB;UID=SA;PWD=DummyPassw0rd"
"rivers.gpkg"

ogrinfo - al "MSSQL:server=.\MSSQLSERVER2008;database=geodb;tables=rivers;trusted_connection=yes"

ogrinfo - al "MSSQL:server=127.0.0.1;database=TestDB;driver=ODBC Driver 17 for SQL Server;UID=SA;PWD=DummyPassw0rd"

ogrinfo - al MSSQL: server = .\MSSQLSERVER2008;
database = geodb;
trusted_connection = no;
UID = user;
PWD = pwd

Suggestion : 4

Last Updated : 22 Nov, 2021,GATE CS 2021 Syllabus

pypyodbc: It is a pure Python Cross-Platform ODBC interface module. To Install pypyodbc module to access the ODBC databases using this command in the terminal.

 pip install pypyodbc

Step 1: Create a Database

CREATE DATABASE GeeksforGeeks;

Step 2: Using the database

USE GeeksForGeeks

Suggestion : 5

Python: TypeError: Can't convert 'generator' object to str implicitly,The problem is the part where you iterate over the children and convert them to strings:,Before doing the string concatenation, you should probably join the strings that get created by the generator into a single string by calling join. Something like this will join the strings using a comma:,In the end, your code should probably be simplified to something like

(str(child) for child in node.children)