10

I follow this link to query Azure database. 

import pyodbc
server = 'your_server.database.windows.net'
database = 'your_database'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 13 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM FinancialRecord where deleted=0")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

When I run the code above, it show the error.

Traceback (most recent call last): File "sqltest.py", line 10, in row = cursor.fetchone() pyodbc.ProgrammingError: ('ODBC SQL type -155 is not yet supported. column-index=2 type=-155', 'HY106')

I am new to Azure. Anyone can help?

hokkaidi
  • 858
  • 6
  • 19
Sam
  • 1,252
  • 5
  • 20
  • 43

2 Answers2

15

pyodbc supports Output Converter functions that we can use when a database returns an SQL type that pyodbc does not support natively. The example on the Wiki page linked above will perform a client-side conversion similar to what would be achieved by a CAST to [N]VARCHAR on the server:

import struct
import pyodbc
conn = pyodbc.connect("DSN=myDb")


def handle_datetimeoffset(dto_value):
    # ref: https://github.com/mkleehammer/pyodbc/issues/134#issuecomment-281739794
    tup = struct.unpack("<6hI2h", dto_value)  # e.g., (2017, 3, 16, 10, 35, 18, 0, -6, 0)
    tweaked = [tup[i] // 100 if i == 6 else tup[i] for i in range(len(tup))]
    return "{:04d}-{:02d}-{:02d} {:02d}:{:02d}:{:02d}.{:07d} {:+03d}:{:02d}".format(*tweaked)


crsr = conn.cursor()
# create test data
crsr.execute("CREATE TABLE #dto_test (id INT PRIMARY KEY, dto_col DATETIMEOFFSET)")
crsr.execute("INSERT INTO #dto_test (id, dto_col) VALUES (1, '2017-03-16 10:35:18 -06:00')")

conn.add_output_converter(-155, handle_datetimeoffset)
value = crsr.execute("SELECT dto_col FROM #dto_test WHERE id=1").fetchval()
print(value)

crsr.close()
conn.close()

which prints

2017-03-16 10:35:18.0000000 -06:00
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    slightly more concise: ```year, month, day, hour, minute, second, nanoseconds, offset_hours, offset_minutes = struct.unpack("<6hI2h", value) dt = datetime(year, month, day, hour, minute, second, nanoseconds // 1000, tzinfo=timezone(timedelta(hours=offset_hours, minutes=offset_minutes))) ``` – amohr Feb 18 '21 at 22:21
12

ODBC SQL type -155 corresponds to the SQL Server type DatetimeOFFSET and the ODBC type SQL_SS_TIMESTAMPOFFSET. The mapping between ODBC types and SQL Server types is described in this documentation page. The error message says that this SQL Server datatype is currently unsupported by the Python ODBC API.

To work around the issue, you will need to change your query to avoid querying columns with DatetimeOFFSET datatype. One way to proceed is to identify the columns in your FinancialRecord table that have a DatetimeOFFSET datatype and convert them to a nvarchar(100) type.

SELECT CAST(MyColumn as AS nvarchar(100)) AS MyColumnAsNVarChar', ...
FROM FinancialRecord where deleted=0
hokkaidi
  • 858
  • 6
  • 19
  • 1
    Yes, it works. For time and size optimization, I would take only the char I need. Example for me : I need only the date, not the time, then I take " nvarchar(10) " – el Josso Jul 23 '21 at 07:28
  • Note: the cast in this answer has the `as` duplicated - one has to be removed to make it work. – Dave Rael Jun 08 '23 at 17:22