Fetching data from IQ with python (sqlanydb) as unicode

[Former Member]

Dear all,

we are using python 2.7.1 and sqlanydb 1.0.5 (now officially supported by SAP). When fetching data from our IQ 15 server, any data that is not an integer like (smallint, int ...) is returned as "unicode", loosing the data type mapping to Python data types.

When using the ASE python-sybase library (aka Sybase) 0.39, fetching data preserves data types.

We have contacted the customer support, but unfortunately they could not provide us any solution for the case.

Did anybody experienced a similar problem?

Unfortunately decoding and casting the data to the appropriated data types is a very slow options when you fetch a couple million rows.

Thanks in advance for the help.

Regards,

Cris da Rocha

[jack_schueler]

Re: any data that is not an integer like (smallint, int ...) is returned as "unicode".

The binary datatypes (BINARY, LONG BINARY, VARBINARY, IMAGE) are returned as "str" (the str class).

You haven't said what "native" datatypes you want to use. Have you considered using converters? These work well with specialized data types like datetime or decimal. The only built-in type we don't intrinsically support is boolean but a converter can be used here as well.

import decimal
import datetime

def convert_to_boolean(val):
    return val != 0

def convert_to_datetime(val):
    return datetime.strptime(val,'%Y-%m-%d %H:%M:%S.%f')

def convert_to_decimal(val):
    return decimal.Decimal(val)

sqlanydb.register_converter(sqlanydb.DT_BIT, convert_to_boolean)
sqlanydb.register_converter(sqlanydb.DT_DECIMAL, convert_to_decimal)
sqlanydb.register_converter(sqlanydb.DT_TIMESTAMP, convert_to_datetime)


If you are looking for high-performance data transfers, Python may not be a good choice. For example, a Python application fetching 100,000 rows each containing 100 integer columns is approximately 37x slower than an ODBC applicaton doing the same thing.

[Former Member]

Dear Jack,

thanks for your reply and suggestions. Sorry for not replying before.

I'll take a look at it and see how I can implement that to our needs. Thanks a lot.

Surely Python is not the best option for high-performance, unfortunately not my choice 🙂

Regards,

Cris da Rocha