Python & MySQL - Numeric type limited? [duplicate]

Question

I'm trying to use MySQL to save some time-series boolean data, and then make a decision according to the saved data.

I am required to save the last 144 samples, and then check if more than X% are non-zero.

I try to use a Numeric field with a scale of 44. At some point the data is truncated to 9999999999.

That's how I created the column:

op.add_column('object', sa.Column('data_history', sa.Numeric(scale=44), nullable=False, default=0))

Here's my code that handles the calculation:

happens_now = data < DATA_THRESHOLD mask = 0 for i in xrange(144): mask <<= 1 mask |= 1 safe_logging.info('History: {} LongHistory: {}'.format(my_object.data_history, long(my_object.data_history))) my_object.data_history *= 2 safe_logging.info('History: {} LongHistory: {}'.format(my_object.data_history, long(my_object.data_history))) my_object.data_history += happens_now safe_logging.info('History: {} LongHistory: {}'.format(my_object.data_history, long(my_object.data_history))) history_to_consider = long(my_object.data_history) & mask safe_logging.info('Mask: {} Data: {} Happens now: {} History: {}'.format(mask, data, happens_now, history_to_consider)) pop_count = self._PopCount(history_to_consider) if (not my_object.is_total) and pop_count/144 > PERCENTAGE_THERSHOLD: my_object.is_total = True

Here's my log output for two consecutive data insertions:

INFO: History: 8589934591 LongHistory: 8589934591 INFO: History: 17179869182 LongHistory: 17179869182 INFO: History: 17179869183 LongHistory: 17179869183 INFO: Mask: 22300745198530623141535718272648361505980415 Data: 10 Happens now: True History: 17179869183 INFO: History: 9999999999 LongHistory: 9999999999 INFO: History: 19999999998 LongHistory: 19999999998 INFO: History: 19999999999 LongHistory: 19999999999 INFO: Mask: 22300745198530623141535718272648361505980415 Data: 10 Happens now: True History: 19999999999

I tried to find a limitation in MySQL docs and couldn't. Python seems to handle well long numbers (see mask).

Is it a SQLAlchemy limitation? Some MySQL limitation I missed?

I can see scale, but what is the precision (max allowed digits) for the numeric numbers? Also, your numbers seem to be integers. Why do you have a scale of 44? — Shadow
– Shadow, Commented Dec 21, 2015 at 6:45
@Shadow I need no precision. I's ok with zero precision. They are Integers, but really big ones. 2^144 ~= 2.2*10^43 So I took 43+1 — Omri Segal
– Omri Segal, Commented Dec 21, 2015 at 6:47
You may have mixed up precision and scale then. Precision is the number of digits in the entire number, scale is the max number decimal digits (digits to the right of the decimal point). — Shadow
– Shadow, Commented Dec 21, 2015 at 6:52
Thanks. Seems you're right, silly me. And now I also see max precision is 30 :( I'll concat two BIGINTs or something. Thanks again :) — Omri Segal
– Omri Segal, Commented Dec 21, 2015 at 6:57

Omri Segal · Accepted Answer · 2015-12-21 06:58:40Z

0

So, as @Shadow pointed out, I mixed up scale and precision.

Scale is the number of digits after the decimal point, precision is total number of digits.

In addition, max precision is 30, so I'll have to concat two BigInts or so, to have a bigger number.

answered Dec 21, 2015 at 6:58

Omri Segal

3974 silver badges16 bronze badges

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

2 Comments

Shadow Over a year ago

In mysql the limit for precision is 65, not 30.

Omri Segal Over a year ago

FML, I switched them in the add_column statement. I think the best thing I could do now is go grab a cup of tea and wake myself up

Collectives™ on Stack Overflow

Python & MySQL - Numeric type limited? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related