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

[graeme_perrow]

Whether the casting is done in your application or in the sqlanydb driver, it will take the same amount of time. The sqlanydb driver is written on top of our dbcapi library, which returns everything as a string, so the casting is necessary.

We are investigating ways that we could return native python types without the need for casting, but unfortunately I do not have a solution for you right now.

[Former Member]

Thanks a lot for the quick answer Graeme.

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