sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'NumericType'

[Former user]

Selecting a numeric type from Sybase and attempting to insert it into PostgreSQL does not work.

It can be worked around by casting to int first, but this neverless violates the principle of least surprise.

[Former user]

Example

[Former user]

Output displaying exception thrown

[Mike Bayer]

I can't reproduce this. My python-sybase environment does not have postgresql installed, however creating a table on Sybase with an "Integer", inserting, then selecting, returns an <int>. I don't know what NumericType is - I attempted using a NUMERIC column instead of INTEGER though these appear to return floats (which we coerce into Decimal), though its safe to assume this is some type specific to Python-sybase.

My advice would be to use the latest python-sybase, ensure the datatype of your column is in fact INTEGER and not NUMERIC or perhaps some other type that the dialect does not currently cover (in which case subclass UserDefinedType and handle it directly), or use pyodbc which overall has much stronger support and operation than Python-sybase which is almost unmaintained.

Helpful here would be to remove the usage of Postgresql, and turn on echo='debug' which would shed more light on the conversation at hand. Also full table schemas, sample data, and a fully working test would be helpful. Below is the test that I inferred from your partial example:

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from decimal import Decimal

syb_engine = create_engine('sybase+pysybase://scott:tiger7@SLICEHOST/', echo='debug')
syb_Session = sqlalchemy.orm.sessionmaker(bind=syb_engine)
syb_session = syb_Session()
SybBase = declarative_base()

class SybKontakt(SybBase):
    __tablename__ = 'kontakt'

    kontakt_id = Column(Integer, primary_key=True)
    y = Column(Numeric)

SybBase.metadata.drop_all(syb_engine)
SybBase.metadata.create_all(syb_engine)
syb_engine.execute(SybKontakt.__table__.insert(), {'kontakt_id':1, 'y':Decimal('10')})


syb_rows = syb_session.query(SybKontakt).order_by(SybKontakt.kontakt_id)
for row in syb_rows:
    print type(row.kontakt_id)
    print type(row.y)

[Former user]

Hi

The type in the Sybase database is id_type, e.g.:

CREATE TABLE kontakt ( kontakt_id ID_TYPE IDENTITY PRIMARY KEY);

which would be the normal type to give an identity column in Sybase.

But on a more general level, I made a script bug2.py (that does not even require any database) and I do not understand why it does not fail. If I declare a column to have a certain type, should the ORM not ensure that that is maintained at all times?

[Former user]

Why does this not throw an exception?

[Mike Bayer]

The ORM relies upon database interaction to handle typing as much as possible since that is a natural function of the DBAPI and the underlying database. We coerce and validate as needed to what the DBAPI needs at SQL execution time if necessary (and we don't if its not needed, much faster to rely upon native drivers to do it if available), but it would be wasteful and error-prone to double-assert all attributes.

[Mike Bayer]

Replying to guest:

Hi

The type in the Sybase database is id_type, e.g.:

CREATE TABLE kontakt ( kontakt_id ID_TYPE IDENTITY PRIMARY KEY);

which would be the normal type to give an identity column in Sybase.

This does not appear to be correct. The normal approach is "INTEGER IDENTITY". I tried your exact syntax, "ID_TYPE IDENTITY PRIMARY KEY", and received:

Can't find type 'ID_TYPE'.

I googled around for ID_TYPE and could not find any mention of such a builtin for Sybase - its mentioned here as an example column name in their manuals:

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/6616;hf=0

which strongly suggests this is not a builtin keyword for Sybase. I also did find it used as a type in this script:

http://svn.apache.org/repos/asf/jakarta/slide/trunk/src/conf/schema/SybaseSchema.sql

which is specific to Jakarta Slide and creates ID_TYPE as a particular custom type (and also as a NUMERIC, not INTEGER). So it appears ID_TYPE is something your DBAs have configured for you. That's where you'd have to determine the correct datatype.

[Former user]

Right, yes, sorry, ID_TYPE is local to us - I asked around and it turns out to be defined as

sp_addtype id_type, 'numeric(8,0)', 'not null'

As to my other question, is there at least an option to force automatic casting at all times, because for my present use case (comparing values from various different databases) it is a nightmare.

[Mike Bayer]

did you try putting "Numeric" on your Column instead of "Integer" ? that will call float() on the value. Custom types can be devised which will coerce in any way you'd like.

[Mike Bayer]

or rather it calls Decimal() by default, which is compatible with int.

[Mike Bayer]

assuming this worked out.

[Mike Bayer]

Removing milestone: 0.6.xx (automated comment)