Menu

Out of range for column... with decimal type

Help
Anonymous
2011-01-19
2012-09-19
  • Anonymous

    Anonymous - 2011-01-19

    So I have run into a problem which I can't seem to find a solution for. I've
    tried debugging but when I get into the c code I start to get a little lost.

    It seems I can't insert decimal values with the MySQL-python client. Doing it
    from the mysql command line client works fine but not from python.

    I've tried using integer, float, Decimal, and string types and they all yield
    the same error. Has anyone else seen this before?

    mysql> desc test_table;
    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra          |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
    | coeficient | decimal(10,10)   | NO   |     | NULL    |                | 
    +------------+------------------+------+-----+---------+----------------+
    
    import MySQLdb
    
    conn = MySQLdb.connect(...)
    
    cursor = conn.cursor()
    cursor.execute("INSERT INTO `test_table` (`coeficient`) VALUES(%s)", ('10.0'))
    cursor.close()
    conn.close()
    

    Gives me:

    "Warning: Out of range value for column 'coeficient' at row 1"

    Python 2.7.1

    Mysql client 5.1.53_0

    Percona server 5.1.53-rel12.4-log

    MySQL-python 1.2.3

     
  • Andy Dustman

    Andy Dustman - 2011-01-19

    Your schema is wrong.

    http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-
    changes.html

    The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of
    values for the arguments in MySQL 5.1 are as follows:

    M is the maximum number of digits (the precision). It has a range of 1 to 65.
    (Older versions of MySQL permitted a range of 1 to 254.)

    D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

    10.0 can't fit in DECIMAL(10,10); only values <1.

     
  • Anonymous

    Anonymous - 2011-01-19

    Thanks, I read that page at least 3 times and still missed the point. Le sigh.

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.