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

asked 17 Sep '14, 08:40

Cris%20da%20Rocha's gravatar image

Cris da Rocha
45114
accept rate: 0%


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.

permanent link

answered 17 Sep '14, 10:34

Graeme%20Perrow's gravatar image

Graeme Perrow
8.7k374111
accept rate: 52%

Thanks a lot for the quick answer Graeme.

Regards,

Cris da Rocha

(17 Sep '14, 10:50) Cris da Rocha

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.

permanent link

answered 18 Sep '14, 10:43

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

edited 18 Sep '14, 10:50

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

(30 Sep '14, 07:21) Cris da Rocha
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×16
×12

question asked: 17 Sep '14, 08:40

question was seen: 24,619 times

last updated: 30 Sep '14, 07:21