Assign python Decimal objects to MySQL DECIMAL columns
Question: I have a table "City" defined as:

    
    
    CREATE TABLE `City` (
      `id` int(11) NOT NULL,
      `lat` decimal(9,6) default NULL,
      `long` decimal(9,6) default NULL,
      `lm_index` int(11) default NULL,
      `num_business` int(11) default NULL,
      `postal_code` varchar(16) default NULL,
    PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

I use python with SQLAlchemy to map this table:

    
    
    Base = declarative_base(engine)
    
    class City(Base):
        ''''''
        __tablename__ = 'City'
        __table_args__ = {'autoload':True}
        def __init__(self, id, lat, long, lm_index, numbiz, postal):
            self.id = id
            self.lat = lat
            self.long = long
            self.lm_index = lm_index
            self.num_business = numbiz
            self.postal_code = postal
    

If I understand the python decimal package correctly, this should work:

    
    
    from decimal import *
    
    getcontext().prec = 6
    lat = 12.11111111
    long = 46.2222222
    
    city = City(1, Decimal(str(lat)), Decimal(str(long)), 0, 0, 0)
    

However, I still get the same errors as I do when I don't call Decimal():

    
    
    Warning: Data truncated for column 'lat' at row 1
    

What am I doing wrong here?


Answer: My guess is, because you're sending an instance of `Decimal`, the `lat` or
`long` fields are being turned to something like a string representation of
the instance. Again, I'm not sure. You can use `pdb` to debug that.

Anyway, there are better ways to deal with `DECIMAL` fields in `MySQL` via
SQLAlchemy. Instead of handling the decimal processing yourself, you could
just let sqlalchemy take care of it -

from sqlalchemy.ext.declarative import declarative_base Base =
declarative_base()

    
    
    class City(Base):
        __tablename__ = 'City'
        __table_args__ = {'autoload':True}
    
        id = Column(Integer, primary_key=True)
        lat =  Column(Numeric(precision=9, scale=6))
        long =  Column(Numeric(precision=9, scale=6))
        ...
    

`Numeric` field returns `decimal.Decimal` objects by default, applying
conversion as needed.

Then you can initiate it as -

    
    
    city = City(lat=12.11111111, long=46.2222222, ...)
    

If you override `__init__()` then you can initiate it like `City(12.1111,
46.2222)`, but I prefer keeping the column name, that way you know at a glance
which field is getting what, instead of counting the number of values each
time.

I haven't tested the code, but it should work. Try.

