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,
}
)
============ %< ============= %<==============
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
