Re: [sqlalchemy] Error with polymorphic selectin and adding item to session.info: 'expanding' parameters can't be used with an empty list

2018-03-05 Thread Mike Bayer 
hi Damon -

this week preferably tomorrow

- mike


On Mon, Mar 5, 2018 at 1:50 PM,   wrote:
> Hey Mike,
>
> Noticed 1.2.5 doesn't have a release date yet on
> http://docs.sqlalchemy.org/en/latest/changelog/changelog_12.html#change-1.2.2.
> Was wondering if that could be released sometime soon - we're blocked from
> switching over until it's released.
>
>
> Damon
>
>
> On Friday, February 23, 2018 at 12:47:13 PM UTC-8, da...@benchling.com
> wrote:
>>
>> Awesome, thanks Mike! Looking forward to the release.
>>
>>
>> Damon
>>
>> On Friday, February 23, 2018 at 11:20:33 AM UTC-8, Mike Bayer wrote:
>>>
>>> On Fri, Feb 23, 2018 at 1:38 PM, Mike Bayer 
>>> wrote:
>>> > *perfect* test case, I'll get a bug report up and can fix this quickly,
>>> > thanks!
>>>
>>> here's the issue:
>>>
>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4199/selectin-polymorphic-hitting-expanding-in
>>>
>>> here's the patch:
>>> https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/678
>>>
>>> thanks!
>>>
>>>
>>> >
>>> > On Fri, Feb 23, 2018 at 1:26 PM, Damon Doucet 
>>> > wrote:
>>> >> Hey all,
>>> >>
>>> >> Loving the new selectin stuff. I think we've hit a bug with
>>> >> polymorphic_load=selectin. I've posted a small repro at the bottom.
>>> >>
>>> >> The crash we're seeing is:
>>> >>
>>> >> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError)
>>> >> 'expanding' parameters can't be used with an empty list [SQL: u'SELECT
>>> >> a1.id
>>> >> AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id =
>>> >> a1.id
>>> >> \nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id']
>>> >> [parameters:
>>> >> [{'primary_keys': []}]]
>>> >>
>>> >> A few interesting points:
>>> >>
>>> >> - Uncommenting L2 => no crash
>>> >> - Commenting L1 or L3 => no crash
>>> >>
>>> >> Let me know if there's anything more I can do to clarify/help.
>>> >>
>>> >>
>>> >> Thanks!
>>> >> Damon
>>> >>
>>> >>
>>> >>
>>> >> from sqlalchemy import *
>>> >> from sqlalchemy.orm import *
>>> >> from sqlalchemy.ext.declarative import declarative_base
>>> >> from sqlalchemy import event
>>> >>
>>> >> Base = declarative_base()
>>> >>
>>> >>
>>> >> class A(Base):
>>> >> __tablename__ = 'a'
>>> >> id = Column(Integer, primary_key=True)
>>> >> type = Column(String)
>>> >> b_id = Column(ForeignKey('b.id'))
>>> >>
>>> >> __mapper_args__ = {
>>> >> 'polymorphic_on': type,
>>> >> }
>>> >>
>>> >>
>>> >> class A1(A):
>>> >> __tablename__ = 'a1'
>>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
>>> >> __mapper_args__ = {
>>> >> 'polymorphic_identity': 'a1',
>>> >> 'polymorphic_load': 'selectin',
>>> >> }
>>> >>
>>> >>
>>> >> class A2(A):
>>> >> __tablename__ = 'a2'
>>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
>>> >> __mapper_args__ = {
>>> >> 'polymorphic_identity': 'a2',
>>> >> 'polymorphic_load': 'selectin',
>>> >> }
>>> >>
>>> >>
>>> >> class B(Base):
>>> >> __tablename__ = 'b'
>>> >> id = Column(Integer, primary_key=True)
>>> >> a_list = relationship('A')
>>> >>
>>> >>
>>> >> e = create_engine("sqlite://", echo=True)
>>> >> Base.metadata.create_all(e)
>>> >>
>>> >> s = Session(e)
>>> >>
>>> >> b = B(a_list=[A1(), A2()])
>>> >> s.add(b)
>>> >> s.info['foo'] = b.a_list[0]  # L1
>>> >> # s.info['bar'] = b.a_list[1]  # L2
>>> >> s.commit()  # L3
>>> >>
>>> >> print b.a_list  # crashes
>>> >>
>>> >> --
>>> >> 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+...@googlegroups.com.
>>> >> To post to this group, send email to sqlal...@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 - 
The Python SQL Toolkit and Object Relational Mapper

h

Re: [sqlalchemy] Error with polymorphic selectin and adding item to session.info: 'expanding' parameters can't be used with an empty list

2018-03-05 Thread damon 
Hey Mike,

Noticed 1.2.5 doesn't have a release date yet 
on 
http://docs.sqlalchemy.org/en/latest/changelog/changelog_12.html#change-1.2.2. 
Was wondering if that could be released sometime soon - we're blocked from 
switching over until it's released.


Damon

On Friday, February 23, 2018 at 12:47:13 PM UTC-8, da...@benchling.com 
wrote:
>
> Awesome, thanks Mike! Looking forward to the release.
>
>
> Damon
>
> On Friday, February 23, 2018 at 11:20:33 AM UTC-8, Mike Bayer wrote:
>>
>> On Fri, Feb 23, 2018 at 1:38 PM, Mike Bayer  
>> wrote: 
>> > *perfect* test case, I'll get a bug report up and can fix this quickly, 
>> thanks! 
>>
>> here's the issue: 
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4199/selectin-polymorphic-hitting-expanding-in
>>  
>>
>> here's the patch:  
>> https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/678 
>>
>> thanks! 
>>
>>
>> > 
>> > On Fri, Feb 23, 2018 at 1:26 PM, Damon Doucet  
>> wrote: 
>> >> Hey all, 
>> >> 
>> >> Loving the new selectin stuff. I think we've hit a bug with 
>> >> polymorphic_load=selectin. I've posted a small repro at the bottom. 
>> >> 
>> >> The crash we're seeing is: 
>> >> 
>> >> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) 
>> >> 'expanding' parameters can't be used with an empty list [SQL: u'SELECT 
>> a1.id 
>> >> AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id = 
>> a1.id 
>> >> \nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id'] 
>> [parameters: 
>> >> [{'primary_keys': []}]] 
>> >> 
>> >> A few interesting points: 
>> >> 
>> >> - Uncommenting L2 => no crash 
>> >> - Commenting L1 or L3 => no crash 
>> >> 
>> >> Let me know if there's anything more I can do to clarify/help. 
>> >> 
>> >> 
>> >> Thanks! 
>> >> Damon 
>> >> 
>> >> 
>> >> 
>> >> from sqlalchemy import * 
>> >> from sqlalchemy.orm import * 
>> >> from sqlalchemy.ext.declarative import declarative_base 
>> >> from sqlalchemy import event 
>> >> 
>> >> Base = declarative_base() 
>> >> 
>> >> 
>> >> class A(Base): 
>> >> __tablename__ = 'a' 
>> >> id = Column(Integer, primary_key=True) 
>> >> type = Column(String) 
>> >> b_id = Column(ForeignKey('b.id')) 
>> >> 
>> >> __mapper_args__ = { 
>> >> 'polymorphic_on': type, 
>> >> } 
>> >> 
>> >> 
>> >> class A1(A): 
>> >> __tablename__ = 'a1' 
>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True) 
>> >> __mapper_args__ = { 
>> >> 'polymorphic_identity': 'a1', 
>> >> 'polymorphic_load': 'selectin', 
>> >> } 
>> >> 
>> >> 
>> >> class A2(A): 
>> >> __tablename__ = 'a2' 
>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True) 
>> >> __mapper_args__ = { 
>> >> 'polymorphic_identity': 'a2', 
>> >> 'polymorphic_load': 'selectin', 
>> >> } 
>> >> 
>> >> 
>> >> class B(Base): 
>> >> __tablename__ = 'b' 
>> >> id = Column(Integer, primary_key=True) 
>> >> a_list = relationship('A') 
>> >> 
>> >> 
>> >> e = create_engine("sqlite://", echo=True) 
>> >> Base.metadata.create_all(e) 
>> >> 
>> >> s = Session(e) 
>> >> 
>> >> b = B(a_list=[A1(), A2()]) 
>> >> s.add(b) 
>> >> s.info['foo'] = b.a_list[0]  # L1 
>> >> # s.info['bar'] = b.a_list[1]  # L2 
>> >> s.commit()  # L3 
>> >> 
>> >> print b.a_list  # crashes 
>> >> 
>> >> -- 
>> >> 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+...@googlegroups.com. 
>> >> To post to this group, send email to sqlal...@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] Reload mapper column definition

2018-03-05 Thread Mike Bayer 
On Mon, Mar 5, 2018 at 11:40 AM, Mike Bayer  wrote:
> On Mon, Mar 5, 2018 at 1:35 AM, Tolstov Sergey  wrote:
>> On my project, i use __getattr__ for adding column deifinition to object
>> class
>> Such as:
>>
>> def __getattr__(self,attr):
>> ...
>>   my_load_function(...)
>>   session.refresh(self)
>>...
>> return getattr(self,attr)
>>
>
> I realized you are actually adding a new mapped attribute as the
> __getattr__ is called.   This is a very strange pattern.

note this pattern is entirely non-threadsafe.   if you have multiple
threads you need to use mutexing and double-checks for the attribute
you are looking for.


>
>
>> It works, but refresh loses changes on this object. I cannot flush, because
>> it used for another function.
>
> do session.expire() on the attribute instead.  the getattr() will load it.
>
>
>
>>
>> I found solution
>> session.refresh(self, attribute_names=['attr_name'])
>>
>> But it will raise exception
>> sqlalchemy.exc.InvalidRequestError: No column-based properties specified for
>> refresh operation. Use session.expire() to reload collections and related
>> items.]
>>
>> I already tried use session.expire(self) and session.expire(self,
>> attribute_names=['attr_name'])) before and after refresh, but nothing
>> changed
>>
>> Can someone help me with this eror?
>>
>> --
>> 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] Reload mapper column definition

2018-03-05 Thread Mike Bayer 
On Mon, Mar 5, 2018 at 1:35 AM, Tolstov Sergey  wrote:
> On my project, i use __getattr__ for adding column deifinition to object
> class
> Such as:
>
> def __getattr__(self,attr):
> ...
>   my_load_function(...)
>   session.refresh(self)
>...
> return getattr(self,attr)
>

I realized you are actually adding a new mapped attribute as the
__getattr__ is called.   This is a very strange pattern.


> It works, but refresh loses changes on this object. I cannot flush, because
> it used for another function.

do session.expire() on the attribute instead.  the getattr() will load it.



>
> I found solution
> session.refresh(self, attribute_names=['attr_name'])
>
> But it will raise exception
> sqlalchemy.exc.InvalidRequestError: No column-based properties specified for
> refresh operation. Use session.expire() to reload collections and related
> items.]
>
> I already tried use session.expire(self) and session.expire(self,
> attribute_names=['attr_name'])) before and after refresh, but nothing
> changed
>
> Can someone help me with this eror?
>
> --
> 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 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.