search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.92k stars 561 forks source link

add_output_converter function called but un-converted value is returned #1285

Closed denck007 closed 12 months ago

denck007 commented 12 months ago

Environment

Issue

I am implementing a columns to store binary data, the values come from a float32 numpy array. When I add an output converter to the pyodbc connection for the column, the converter function is run but the binary values are returned (not the converted values).

Here is a stand alone example demonstrating this. It creates a table with VARBINARY column and table with VARIANT column. The same value is inserted to both. Output converters are added for each type. The converter for VARBINARY is called but un-converted data is returned, converter for VARIANT is called and the correctly converted values are returned. 

import sys
import numpy as np
import pyodbc
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.dialects.mssql import VARBINARY, SQL_VARIANT

connection_url = r"mssql+pyodbc://SA:****@localhost:1433/dev?TrustServerCertificate=yes&driver=ODBC+Driver+17+for+SQL+Server"
engine_mssql = sa.create_engine(connection_url)

print(f"{sys.version_info=}")
print(f"{pyodbc.version=}")
print(f"{np.version.version=}")
print(f"{sa.__version__=}")

# Define and create the tables
Base = declarative_base()

class BinaryDataTable_VARBINARY(Base):
    __tablename__ = "binary_data_table_varbinary"
    id = sa.Column(sa.Integer, primary_key=True)
    value = sa.Column(VARBINARY)

class BinaryDataTable_VARIANT(Base):
    __tablename__ = "binary_data_table_variant"
    id = sa.Column(sa.Integer, primary_key=True)
    value = sa.Column(SQL_VARIANT)

Base.metadata.drop_all(engine_mssql)
Base.metadata.create_all(engine_mssql)

# Insert same data to each table
with sessionmaker(engine_mssql)() as s:
    for idx in range(3):
        value = np.array([idx, idx**2], dtype=np.float32)
        s.add(BinaryDataTable_VARBINARY(value=value.tobytes()))
        s.add(BinaryDataTable_VARIANT(value=value.tobytes()))
        s.commit()

# Same output converter function used for each
def convert_to_np_float32(raw) -> np.ndarray:
    out = np.frombuffer(raw, np.float32)
    print(f"\tin conversion function: {raw=} -> {out=}")
    return out

# Query for the data
with sessionmaker(engine_mssql)() as s:
    print("VARBINARY")
    s.connection().connection.add_output_converter(pyodbc.SQL_VARBINARY, convert_to_np_float32)
    q = sa.select(BinaryDataTable_VARBINARY.id, BinaryDataTable_VARBINARY.value)
    for item in s.execute(q):
        print(f"\tResult: {item}")
    s.connection().connection.clear_output_converters()

    print("VARIANT")
    s.connection().connection.add_output_converter(-150, convert_to_np_float32)
    q = sa.select(BinaryDataTable_VARIANT.id, BinaryDataTable_VARIANT.value)
    for item in s.execute(q):
        print(f"\tResult: {item}")
    s.connection().connection.clear_output_converters()

Output:

sys.version_info=sys.version_info(major=3, minor=10, micro=12, releaselevel='final', serial=0)
pyodbc.version='4.0.39'
np.version.version='1.24.2'
sa.__version__='2.0.8'
VARBINARY
    in conversion function: raw=b'\x00\x00\x00\x00\x00\x00\x00\x00' -> out=array([0., 0.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x80?\x00\x00\x80?' -> out=array([1., 1.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x00@\x00\x00\x80@' -> out=array([2., 4.], dtype=float32)
    Result: (1, b'\x00\x00\x00\x00\x00\x00\x00\x00')
    Result: (2, b'\x00\x00\x80?\x00\x00\x80?')
    Result: (3, b'\x00\x00\x00@\x00\x00\x80@')
VARIANT
    in conversion function: raw=b'\x00\x00\x00\x00\x00\x00\x00\x00' -> out=array([0., 0.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x80?\x00\x00\x80?' -> out=array([1., 1.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x00@\x00\x00\x80@' -> out=array([2., 4.], dtype=float32)
    Result: (1, array([0., 0.], dtype=float32))
    Result: (2, array([1., 1.], dtype=float32))
    Result: (3, array([2., 4.], dtype=float32))
v-chojas commented 12 months ago

Could you post an ODBC trace?

denck007 commented 12 months ago

This is a trace selecting of selecting 1 item from binary_data_table_varbinary (the incorrectly parsed one)


kernel-v2-18808 79b8-3724   ENTER SQLAllocHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLHANDLE *         0x0000027CDB812748

kernel-v2-18808 79b8-3724   EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLHANDLE *         0x0000027CDB812748 ( 0x0000027CD37AA540)

kernel-v2-18808 79b8-3724   ENTER SQLFreeStmt 
        HSTMT               0x0000027CD37AA540
        UWORD                        0 <SQL_CLOSE>

kernel-v2-18808 79b8-3724   EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        0 <SQL_CLOSE>

kernel-v2-18808 79b8-3724   ENTER SQLExecDirectW 
        HSTMT               0x0000027CD37AA540
        WCHAR *             0x0000027CDB477DF0 [     112] "SELECT TOP 1 binary_data_table_varbinary.id, binary_data_table_varbinary.value \ aFROM binary_data_table_varbinary"
        SDWORD                   112

kernel-v2-18808 79b8-3724   EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        WCHAR *             0x0000027CDB477DF0 [     112] "SELECT TOP 1 binary_data_table_varbinary.id, binary_data_table_varbinary.value \ aFROM binary_data_table_varbinary"
        SDWORD                   112

kernel-v2-18808 79b8-3724   ENTER SQLRowCount 
        HSTMT               0x0000027CD37AA540
        SQLLEN *            0x000000AAD67E9DD8

kernel-v2-18808 79b8-3724   EXIT  SQLRowCount  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        SQLLEN *            0x000000AAD67E9DD8 (-1)

kernel-v2-18808 79b8-3724   ENTER SQLNumResultCols 
        HSTMT               0x0000027CD37AA540
        SWORD *             0x000000AAD67E9DC0

kernel-v2-18808 79b8-3724   EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        SWORD *             0x000000AAD67E9DC0 (2)

kernel-v2-18808 79b8-3724   ENTER SQLDescribeCol 
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        UCHAR *             0x000000AAD67E9C70 
        SWORD                      200 
        SWORD *             0x000000AAD67E9C5C
        SWORD *             0x000000AAD67E9C50
        SQLULEN *           0x000000AAD67E9C60
        SWORD *             0x000000AAD67E9C58
        SWORD *             0x000000AAD67E9C54

kernel-v2-18808 79b8-3724   EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        UCHAR *             0x000000AAD67E9C70 [       2] "id"
        SWORD                      200 
        SWORD *             0x000000AAD67E9C5C (2)
        SWORD *             0x000000AAD67E9C50 (4)
        SQLULEN *           0x000000AAD67E9C60 (10)
        SWORD *             0x000000AAD67E9C58 (0)
        SWORD *             0x000000AAD67E9C54 (0)

kernel-v2-18808 79b8-3724   ENTER SQLColAttribute 
        SQLHSTMT            0x0000027CD37AA540
        SQLSMALLINT                  1 
        SQLSMALLINT                  8 <SQL_DESC_UNSIGNED>
        SQLPOINTER         0x0000000000000000
        SQLSMALLINT                  0 
        SQLSMALLINT *       0x0000000000000000
        SQLPOINTER          0x000000AAD67E9C68

kernel-v2-18808 79b8-3724   EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
        SQLHSTMT            0x0000027CD37AA540
        SQLSMALLINT                  1 
        SQLSMALLINT                  8 <SQL_DESC_UNSIGNED>
        SQLPOINTER         0x0000000000000000
        SQLSMALLINT                  0 
        SQLSMALLINT *       0x0000000000000000
        SQLPOINTER          0x000000AAD67E9C68 (0) <FALSE>

kernel-v2-18808 79b8-3724   ENTER SQLDescribeCol 
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        UCHAR *             0x000000AAD67E9C70 
        SWORD                      200 
        SWORD *             0x000000AAD67E9C5C
        SWORD *             0x000000AAD67E9C50
        SQLULEN *           0x000000AAD67E9C60
        SWORD *             0x000000AAD67E9C58
        SWORD *             0x000000AAD67E9C54

kernel-v2-18808 79b8-3724   EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        UCHAR *             0x000000AAD67E9C70 [       5] "value"
        SWORD                      200 
        SWORD *             0x000000AAD67E9C5C (5)
        SWORD *             0x000000AAD67E9C50 (-3)
        SQLULEN *           0x000000AAD67E9C60 (0)
        SWORD *             0x000000AAD67E9C58 (0)
        SWORD *             0x000000AAD67E9C54 (1)

kernel-v2-18808 79b8-3724   ENTER SQLDescribeColW 
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        WCHAR *             0x0000027CDA00E890 
        SWORD                      300 
        SWORD *             0x000000AAD67E9DA8
        SWORD *             0x000000AAD67E9D90
        SQLULEN *           0x000000AAD67E9D40
        SWORD *             0x000000AAD67E9D34
        SWORD *             0x000000AAD67E9D30

kernel-v2-18808 79b8-3724   EXIT  SQLDescribeColW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        WCHAR *             0x0000027CDA00E890 [       2] "id"
        SWORD                      300 
        SWORD *             0x000000AAD67E9DA8 (2)
        SWORD *             0x000000AAD67E9D90 (4)
        SQLULEN *           0x000000AAD67E9D40 (10)
        SWORD *             0x000000AAD67E9D34 (0)
        SWORD *             0x000000AAD67E9D30 (0)

kernel-v2-18808 79b8-3724   ENTER SQLDescribeColW 
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        WCHAR *             0x0000027CDA00E890 
        SWORD                      300 
        SWORD *             0x000000AAD67E9DA8
        SWORD *             0x000000AAD67E9D90
        SQLULEN *           0x000000AAD67E9D40
        SWORD *             0x000000AAD67E9D34
        SWORD *             0x000000AAD67E9D30

kernel-v2-18808 79b8-3724   EXIT  SQLDescribeColW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        WCHAR *             0x0000027CDA00E890 [       5] "value"
        SWORD                      300 
        SWORD *             0x000000AAD67E9DA8 (5)
        SWORD *             0x000000AAD67E9D90 (-3)
        SQLULEN *           0x000000AAD67E9D40 (0)
        SWORD *             0x000000AAD67E9D34 (0)
        SWORD *             0x000000AAD67E9D30 (1)

kernel-v2-18808 79b8-3724   ENTER SQLFetch 
        HSTMT               0x0000027CD37AA540

kernel-v2-18808 79b8-3724   EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540

kernel-v2-18808 79b8-3724   ENTER SQLGetData 
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        SWORD                        4 <SQL_C_LONG>
        PTR                 <unknown type>
        SQLLEN                     4
        SQLLEN *            0x000000AAD67EAC58

kernel-v2-18808 79b8-3724   EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        1 
        SWORD                        4 <SQL_C_LONG>
        PTR                 <unknown type>
        SQLLEN                     4
        SQLLEN *            0x000000AAD67EAC58 (4)

kernel-v2-18808 79b8-3724   ENTER SQLGetData 
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        SWORD                       -2 <SQL_C_BINARY>
        PTR                 <unknown type>
        SQLLEN                  4096
        SQLLEN *            0x000000AAD67EAC38

kernel-v2-18808 79b8-3724   EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        2 
        SWORD                       -2 <SQL_C_BINARY>
        PTR                 <unknown type>
        SQLLEN                  4096
        SQLLEN *            0x000000AAD67EAC38 (8)

kernel-v2-18808 79b8-3724   ENTER SQLFetch 
        HSTMT               0x0000027CD37AA540

kernel-v2-18808 79b8-3724   EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
        HSTMT               0x0000027CD37AA540

kernel-v2-18808 79b8-3724   ENTER SQLFreeStmt 
        HSTMT               0x0000027CD37AA540
        UWORD                        0 <SQL_CLOSE>

kernel-v2-18808 79b8-3724   EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000027CD37AA540
        UWORD                        0 <SQL_CLOSE>

kernel-v2-18808 79b8-3724   ENTER SQLFreeHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0000027CD37AA540

kernel-v2-18808 79b8-3724   EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0000027CD37AA540

kernel-v2-18808 79b8-3724   ENTER SQLEndTran 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLSMALLINT                  1 

kernel-v2-18808 79b8-3724   EXIT  SQLEndTran  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLSMALLINT                  1 

kernel-v2-18808 79b8-3724   ENTER SQLEndTran 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLSMALLINT                  1 

kernel-v2-18808 79b8-3724   EXIT  SQLEndTran  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x0000027CAE88E3E0
        SQLSMALLINT                  1
gordthompson commented 12 months ago

I am unable to reproduce your issue. When I run your test code against …

Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
    Mar  4 2020 00:59:26 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>

… I get

sys.version_info=sys.version_info(major=3, minor=11, micro=5, releaselevel='final', serial=0)
pyodbc.version='4.0.39'
np.version.version='1.26.0'
sa.__version__='2.0.21'
VARBINARY
    in conversion function: raw=b'\x00\x00\x00\x00\x00\x00\x00\x00' -> out=array([0., 0.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x80?\x00\x00\x80?' -> out=array([1., 1.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x00@\x00\x00\x80@' -> out=array([2., 4.], dtype=float32)
    Result: (1, array([0., 0.], dtype=float32))
    Result: (2, array([1., 1.], dtype=float32))
    Result: (3, array([2., 4.], dtype=float32))
VARIANT
    in conversion function: raw=b'\x00\x00\x00\x00\x00\x00\x00\x00' -> out=array([0., 0.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x80?\x00\x00\x80?' -> out=array([1., 1.], dtype=float32)
    in conversion function: raw=b'\x00\x00\x00@\x00\x00\x80@' -> out=array([2., 4.], dtype=float32)
    Result: (1, array([0., 0.], dtype=float32))
    Result: (2, array([1., 1.], dtype=float32))
    Result: (3, array([2., 4.], dtype=float32))
denck007 commented 12 months ago

I tracked it down, it is related to SQLAlchemy (you used 2.0.21, I had 2.0.8). I searched through versions and found it starts working as expected starting with sqlalchemy==2.0.11. Their change log makes reference to binary data types now being handled on a per driver basis.

Upgrading to sqlalchemy>=2.0.11 fixes the problem.