python mysql - unicode

Question

I have a column in mysql which is intended to take decimal values (e.g. 0.00585431)

In python, I have a function that gets this value from a webpage, then returns it into a variable. When I print this variable I get [u'0.00585431'] (which is strange)

I then try to insert this into the mysql column which is set to take a decimal(10,0) value. however, the database stores it as just a 0

the code to insert is nothing special and works for other things:

cur.execute("""INSERT INTO earnings VALUES (%s)""", (variable))

if I change the column to a string type then it stores the whole [u'0.00585431']. So i imagine that when I try to store it as a decimal its not actually taking a proper decimal value and stores a 0 instead?

any thoughts on how to fix this?

Mad Dog Tannen · Accepted Answer · 2014-03-12 13:48:53Z

1

DECIMAL(10,0) will give 0 to the right of the comma.

The declaration syntax for a DECIMAL column remains DECIMAL(M,D), although the range of values for the arguments has changed somewhat:

M is the maximum number of digits (the precision). It has a range of 1 to 65. This introduces a possible incompatibility for older applications, because previous versions of MySQL permit a range of 1 to 254. (The precision of 65 digits actually applies as of MySQL 5.0.6. From 5.0.3 to 5.0.5, the precision is 64 digits.)

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.

Try to change your column datatype to DECIMAL(10,8)

If your values will always be in same format as 0.00585431 then DECIMAL(9,8) would suffice.

https://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

edited Mar 12, 2014 at 13:48

answered Mar 12, 2014 at 13:23

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Simon Over a year ago

so I made that change but it still gives me 0, along with this error: "Warning: Incorrect decimal value: '[u'0.00585431']' for column"

Simon Over a year ago

I imagine it has more to do with the unicode aspect of the issue than the structure of the mysql table

Mad Dog Tannen Over a year ago

You cant have [u' as decimal value. Try only integers

Simon Over a year ago

thats my problem. the function parses a webpage, and the return from it is exactly [u'0.00585431'] and I cant seem to cast into another type, not even str() to strip the non number characters

Mad Dog Tannen Over a year ago

OK, this is a bit hacky but could work, you still have to change your decimal column type. cur.execute("""INSERT INTO earnings VALUES (REPLACE(REPLACE(%s,'[''u',''),''']',''))""", (variable))

|

Collectives™ on Stack Overflow

python mysql - unicode

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related