Re: [sqlalchemy] Emit INSERT query with float values reducing number of decimal digits

2018-03-05 Thread Mike Bayer 
On Mon, Mar 5, 2018 at 10:42 AM, Ruben Di Battista
 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Emit INSERT query with float values reducing number of decimal digits

2018-03-05 Thread Ruben Di Battista 
Thanks,

I believe that precision keyword sets the Float type precision *on the DB. *The
SQL query values still have lots of decimal digits. I believe the
modification has to be done within Python/SQLAlchemy: the query string must
be generated with a "less-precise" float number string. Basically I need
smaller query strings, since the values to be inserted are a lot, and
saving digits in the VALUES elements can help us when communicating with a
remote DB in terms of latency.

What comes into my mind is generating a custom Float type that coerces the
float values (for example rounding it) to just two digits, and then emits
the related "smaller" string.

On Mon, Mar 5, 2018 at 5:04 PM, Антонио Антуан  wrote:

> You can specify column precision
> 
>
> пн, 5 мар. 2018 г. в 18:42, Ruben Di Battista :
>
>> 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?
>>
>>
>>
>> --
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> 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 u

Re: [sqlalchemy] Emit INSERT query with float values reducing number of decimal digits

2018-03-05 Thread Антонио Антуан 
You can specify column precision


пн, 5 мар. 2018 г. в 18:42, Ruben Di Battista :

> 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?
>
>
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Emit INSERT query with float values reducing number of decimal digits

2018-03-05 Thread Ruben Di Battista 
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?



-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.