0.5 + pyodbc + Custom Types

[fw]

Hi guys,

I have an application running under 0.4.7p1 It access a MSSQL server
using pymssql. Now I am trying to see if I can port it to 05 and use
pyodbc (2.1.4).

I am experiencing a strange problem.

I have defined some custom column types (see below) to deal with some
legacy database. Essentially I have to deal with columns defined as
text but that are essentially integer.

So I defined my table with those types, classes and created mappers

Now, when I run the code, I don't see the custom type conversion being
run and the type of attribute corresponding to the column is not what
I expect. In this case it is a string instead of the expected integer

Python 2.5.2 (r252, Sep 15 2008, 11:30:53)
[GCC 4.1.2 (Gentoo 4.1.2 p1.0.1)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import database as db
>>> sess=db.getDBSession()
>>> stock=sess.query(db.Stock).get(3)
>>> stock.id
'03'
>>>

If I run the same code under 0.4.7p1 and pymssql I get

Python 2.5.2 (r252, Sep 15 2008, 11:30:53)
[GCC 4.1.2 (Gentoo 4.1.2 p1.0.1)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import database as db
>>> sess=db.getDBSession()
>>> stock=sess.query(db.Stock).get(3)
Converting 3 to string
Returning 03 as int
Returning 03 as int
>>> stock.id
3
>>>

So the int is converted to padded string on the query (get) and the
string is converted (twice?) to int when the object is created. This
is very much what I expected.

Note that the query succeeds and return the right object in both
cases.

I looked at the doc but there seem to be nothing wrong with what I do.
Is it me or have I stumbled onto something?

Cheers,
Francois


Here is some code snippet

============ %< ============= %<==============
class IntString(sa.types.TypeDecorator):
"""A string type converted between string and integer"""

impl = sa.types.String
def convert_bind_param(self, value, engine):
"""Convert from int to string"""
if value is None:
return None
return str(value)
def convert_result_value(self, value, engine):
"""Convert from string to int"""
#return unicode(value,"utf8")
if value is None:
return None
return int(value.strip())

class PaddedIntString(IntString):
"""A string type converted between string and integer with some
padding"""

def __init__(self, length=None, convert_unicode=False,padding='0'):
if length is None:
raise Exception("Use IntString instead")

self.pat="%%%s%dd"%(padding,length)
self.padding=padding
IntString.__init__(self,length,convert_unicode)

def convert_bind_param(self, value, engine):
"""Convert from int to string"""
if value is None:
return None
print "Converting %d to string"%value
return self.pat%(value)

def convert_result_value(self, value, engine):
"""Convert from string to int"""
#return unicode(value,"utf8")
if value is None:
return None
print "Returning %s as int"%value
return int(value.strip())


tblStore=sa.Table("Store",SomeMetadata,
sa.Column("StoreNo",PaddedIntString(2), primary_key=True),
sa.Column("StoreEnglishName",StripString(50)),
sa.Column("StoreType",sa.types.String(2)),
sa.Column("StoreLocation",sa.types.String(2)),
sa.Column("StoreStatus",sa.types.String(1)),
sa.Column("Shop",sa.types.Integer),
sa.Column("LastDate",sa.types.DateTime),
sa.Column("LastUser",sa.types.String(8)))

class Stock(DBObject):
"""One of our Real or virtual shops"""
def __repr__(self):
if self.id in self.FGStocks.keys():
return self.FGStocks[self.id]
if self.id in self.VirtualStocks.keys():
return self.VirtualStocks[self.id]
if self.id in self.OtherStocks.keys():
return self.OtherStocks[self.id]
return "Unknown Stock %d"%(self.id)

def _Unposted(self):
return [ x for x in self.Inventory if x.Level!=x.Balance ]


Unposted=property(_Unposted)
Name=property(__repr__)

FGStocks={1:"Stock 1", 2:"Stock 2"}
VirtualStocks={3:"V Stock 1", 4:"V Stock 2"}
OtherStocks={5:"O Stock 1", 6:"O Stock 2"}


Stock.Pmapper=mapper(Stock, tblStore, properties={
'Inventory':relation(StockItem,lazy=True, cascade="all, delete-
orphan"),
'id' : tblStore.c.StoreNo,
}
)
============ %< ============= %<==============

[Michael Bayer]

you're using methods which have been removed (which were also
deprecated throughout 0.4). Check out the third bulletpoint here:

http://www.sqlalchemy.org/trac/wiki/05Migration#SchemaTypes

[fw]

Hi Michael,

Thanks for the reply.

And sorry for the dumb question

Sometimes I can't see what is just in front of my nose.

Regards,
François