On Mon, Mar 5, 2018 at 10:42 AM, Ruben Di Battista <[email protected]> wrote: > I have a table that is storing a huge amount of numerical details about my > application. I have huge INSERT queries (also millions of rows for each of > them) of float values that are made with core API, while the rest of > application logic is ORM. The precision I need on each float is not big, two > decimal digits is enough. I was thinking about reducing the volume of each > query trying to emit INSERT queries directly with floats with a limited > number of digits. > > To better explain, what I have now: > > 2018-02-27 15:42:42,253 - INFO - sqlalchemy.engine.base.Engine - base - > INSERT INTO passage_data (time, azimuth, elevation, doppler, slant, > passage_id) VALUES (%s, %s, %s, %s, %s, %s) > 2018-02-27 15:42:42,253 - INFO - sqlalchemy.engine.base.Engine - base - > ((datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), 36.496509331447605, > 1.2611702704468281e-08, 1.8684261690630526e-05, 2585.2088123511294, > 14L), (datetime.datetime(2018, 2, 28, 9, 15, 2, 63752), 36.60203082082902, > 0.05023170345696884, 1.8660941451945825e-05, 2579.610896504773, 14L), > (datetime.datetime(2018, 2, 28, 9, 15, 3, 63752), 36.70799537639 > 969, 0.10050903526080569, 1.8637443765193708e-05, 2574.019998391197, 14L), > (datetime.datetime(2018, 2, 28, 9, 15, 4, 63752), 36.81440550887081, > 0.15083186067307605, 1.8613767355120377e-05, 2568.4361714766696, 1 > 4L), (datetime.datetime(2018, 2, 28, 9, 15, 5, 63752), 36.921263739618595, > 0.20120002680977034, 1.8589910938324356e-05, 2562.8594695400034, 14L), > (datetime.datetime(2018, 2, 28, 9, 15, 6, 63752), 37.02857260192 > 68, 0.2516133749806172, 1.8565873222763086e-05, 2557.289946693618, 14L), > (datetime.datetime(2018, 2, 28, 9, 15, 7, 63752), 37.136334642317216, > 0.3020717396984603, 1.8541652907053198e-05, 2551.727657483191, 14L) > , (datetime.datetime(2018, 2, 28, 9, 15, 8, 63752), 37.244552421268985, > 0.3525749481349419, 1.85172486800893e-05, 2546.172656937015, 14L) ... > displaying 10 of 562 total bound parameter sets ... (datetime.date > time(2018, 2, 28, 9, 24, 21, 63752), 147.15663042736335, > 0.08665444173526915, -1.8705826550795816e-05, 2564.7906146486603, 14L), > (datetime.datetime(2018, 2, 28, 9, 24, 22, 63752), 147.26246413819342, > 0.03587018 > 554496605, -1.873029089372862e-05, 2570.402148180257, 14L)) > > > What I was thinking to reduce the volume, was to coerce the float values to > 2 decimal digits, and emit a SQL insert with values that are smaller in > terms of string bytes. E.g. > > datetime(datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), 36.50, 0.00, 0.00, > 2585.21, > 14L) > > # Instead of: > > datetime(datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), > 36.496509331447605, 1.2611702704468281e-08, 1.8684261690630526e-05, > 2585.2088123511294, > > 14L) > > > How should I attack this problem? I would like to keep the `Float` type for > the column, but to emit "smaller queries". Do I need a custom type?
a simple TypeDecorator to create the float values you'd like would be the most expedient approach, yes. > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
