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 1

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.