Error fetching data with Python from MS SQL server

Question

0.00/5 (No votes)

See more:

I need to get a table from MS SQL, but I get the error:
ProgrammingError: ('ODBC SQL type -151 is not yet supported. column-index=3 type=-151', 'HY106')

Any idea?

What I have tried:

import pyodbc

cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};"
                      "Server=localhost;"
                      "Database=AdventureWorks2012;"
                      "Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM HumanResources.Employee')

row = cursor.fetchone()
while row:
    print (row[0])
    row = cursor.fetchone()

Posted 17-Aug-17 21:37pm

acg99

Updated 17-Aug-17 21:49pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Daniels · Answer 1 · 2017-08-17T21:49:00

Solution 1

Your Python driver does not support the ODBC SQL type that's referred to as "-151" (I could not find what the actual type behind this is) so it can't just read the table, so you'll need to add an output converter for the type -151 so the driver knows what to do with it. Here's an example for an Output Converter, you can transform it into what you need for your type: Using an Output Converter function · mkleehammer/pyodbc Wiki · GitHub[^]

Posted 17-Aug-17 21:49pm

Thomas Daniels

Comments

Richard Deeming 18-Aug-17 10:06am

It's tricky to search for negative numbers on Google - it tends to think you want to exclude the number from the search results. :)

As far as I can see, -151 is SQL_SS_UDT - a user-defined type.

acg99 18-Aug-17 12:32pm

The column in the SQL table that is creating the error has data type hierarchyID. I have no clue what hat is. It looks like a string of text with an x somewhere in it: here is a link to see what it looks like: https://snag.gy/1aNCuj.jpg
This database is the sample database that Microsoft provides AdventureWorks2012.
How would that work converting this column to a string with an output converter?

Thomas Daniels 18-Aug-17 12:36pm

I don't exactly know what a hierarchyID is, but you could try starting with adjusting the example I linked to -151, and then debug to go into your converter method and figure out what dto_value (in the Python code) contains.

Error fetching data with Python from MS SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Error fetching data with Python from MS SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us