Re: [sqlalchemy] Increase max query length!
Hi Jonhatan, That query inserts around 1 million of rows nowadays (in more or less 8 minutes on remote DB — while the profiling data in this thread are on localhost — ) 2/3 times a day. This is expected to increase of a factor around 10x in next months/year. I'm personally not targeting any particular performance. For me 8 minutes looks reasonable but possibly in the future with scaling that number could scale up soon. I was asked to furtherly optimise that query and the solution that was found was to build up the query textually. I really hate it for a multitude of reasons, but well… I’m not the one making the decisions! :/ Unfortunately postgresql is not an option either. The stack has been based on MySQL + PHP. Because with postgresql and the Array native datatype I could have saved everything in an Array (currently the data are related to another table with a one-to-many relationship through the passageID foreign key) dropping the relationship, since it’s useless being these data just numerical parameters that do not need to be queried but “just” ingested by some workers. Maybe we could use JSON column type instead of a table in a one-to-many relationship. However thank you for your suggestion… The CLI version does not work for us since the resulting data come from a fairly complex optimisation procedure. _ -. .´ | ', ;|∞∞ ˜˜ |∞ RdB ,.,|∞∞ .' '. | -' `’ https://rdb.is On 2 novembre 2018 a 22:05:08, Jonathan Vanasco (jvana...@gmail.com) scritto: can you elaborate on how much data is being loaded and what performance you're targeting ? if you're concerned with loading many MB of data as periodic batches, the best performance by far is going to be generating a text file in one of the formats your database server natively supports, and using a commandline client to load it into the server. i'm not familiar with how mysql handles foreign key checks, but in postgresql deferring the constraint check OR dropping and rebuilding the constraint will make things run even faster. -- 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. signature.asc Description: Message signed with OpenPGP using AMPGpg
Re: [sqlalchemy] Increase max query length!
Thank you Mike.
I actually tried with the limit raised up, and the performances became actually
slightly worse even.
So the problem resides in the prepared statements logic of `mysqlclient`, that
does some additional escaping on parameters.
I will try to see if there’s a workaround on that… Thank you very much for your
support!
On 2 novembre 2018 a 18:41:32, Mike Bayer (mike...@zzzcomputing.com) scritto:
On Fri, Nov 2, 2018 at 1:08 PM Ruben Di Battista
wrote:
>
> Thanks Mike as always,
>
> I'm diving a bit more in the problem. The solution they decided to apply is
> to modify the code from above, like this:
>
> header = 'INSERT INTO `passageData` (`time`, `azimuth`, ' \
> '`elevation`, `doppler`, `slant`, `passageID`) VALUES '
>
> with profiled():
> for ix in range(n):
> time_i = times[ix].strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
> az_i = round(azimuth[ix], 2)
> el_i = round(elevation[ix], 2)
> dopp_i = round(doppler[ix], 10)
> slant_i = round(slant[ix], 2)
>
> header += '(\'{0}\', {1}, {2}, {3}, {4}, {5})'.\
> format(time_i, az_i, el_i, dopp_i, slant_i, self.id)
>
> if ix != n-1:
> header += ','
>
> header += ';'
> session.execute(header)
>
> And I'm profiling the previous one with:
>
> with profiled():
> session.execute(
> insert_query,
> [
> {
> 'time': times[i],
> 'elevation': elevation[i],
> 'azimuth': azimuth[i],
> 'doppler': doppler[i],
> 'slant': slant[i],
> 'passageID': passage_id
> }
> for i in six.moves.range(0, n)
> ]
> )
>
> And these are the lines I get from the profiling in the first case (with ugly
> string interpolation):
> 420358 function calls in 1.591 seconds
>
> Ordered by: cumulative time
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 1 0.000 0.000 1.114 1.114
> /lib/python2.7/site-packages/sqlalchemy/orm/session.py:1047(execute)
> 1 0.000 0.000 0.971 0.971
> /lib/python2.7/site-packages/sqlalchemy/engine/base.py:882(execute)
> 1 0.000 0.000 0.971 0.971
> /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:267(_execute_on_connection)
>
> 1 0.000 0.000 0.971 0.971
> /lib/python2.7/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement)
>
> 1 0.000 0.000 0.834 0.834
> /lib/python2.7/site-packages/sqlalchemy/engine/base.py:(_execute_context)
>
> 1 0.000 0.000 0.789 0.789
> /lib/python2.7/site-packages/sqlalchemy/engine/default.py:508(do_execute)
> 1 0.005 0.005 0.789 0.789
> /lib/python2.7/site-packages/MySQLdb/cursors.py:204(execute)
> 1 0.000 0.000 0.780 0.780
> /lib/python2.7/site-packages/MySQLdb/cursors.py:411(_query)
> 1 0.000 0.000 0.780 0.780
> /lib/python2.7/site-packages/MySQLdb/cursors.py:372(_do_query)
> 1 0.780 0.780 0.780 0.780
> /lib/python2.7/site-packages/MySQLdb/connections.py:267(query)
> 3 0.267 0.089 0.267 0.089 {method 'sub' of '_sre.SRE_Pattern' objects}
> 6 0.205 0.000 0.205 0.000 {method 'strftime' of 'datetime.date' objects}
> 1 0.011 0.011 0.143 0.143
> /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:4287(_literal_as_text)
>
> 1 0.000 0.000 0.137 0.137 :1()
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:379(compile)
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:444(_compiler)
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:393(__init__)
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:180(__init__)
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:244(process)
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/visitors.py:86(_compiler_dispatch)
>
> 1 0.000 0.000 0.137 0.137
> /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:759(visit_textclause)
>
> 1 0.000 0.000 0.132 0.132
> /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:1259(__init__)
> 24 0.120 0.000 0.120 0.000 {round}
> 6 0.104 0.000 0.104 0.000 {method 'format' of 'str' objects}
> 6 0.047 0.000 0.047 0.000
> /lib/python2.7/site-packages/sqlalchemy/orm/attributes.py:234(__get__)
>
> While in the second case I get:
>
> 5103248 function calls (5103183 primitive calls) in 3.829 seconds
>
> Ordered by: cumulative time
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 1 0.000 0.000 3.829 3.829
> lib/python2.7/site-packages/sqlalchemy/orm/session.py:1047(execute)
> 1 0.000 0.000 3.829 3.829
> lib/python2.7/site-packages/sqlalchemy/engin
Re: [sqlalchemy] Increase max query length!
03.8293.829
lib/python2.7/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement)
10.0010.0013.8293.829
lib/python2.7/site-packages/sqlalchemy/engine/base.py:(_execute_context)
10.0000.0002.8292.829
lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py:104(do_executemany)
10.0000.0002.8292.829
lib/python2.7/site-packages/MySQLdb/cursors.py:256(executemany)
10.1300.1302.8292.829
lib/python2.7/site-packages/MySQLdb/cursors.py:286(_do_execute_many)
60.1980.0001.9700.000
lib/python2.7/site-packages/MySQLdb/cursors.py:105(_escape_args)
420.1690.0001.2450.000
lib/python2.7/site-packages/MySQLdb/cursors.py:111()
360.3780.0001.0760.000
lib/python2.7/site-packages/MySQLdb/connections.py:302(literal)
10.3680.3680.9980.998
lib/python2.7/site-packages/sqlalchemy/engine/default.py:595(_init_compiled)
610.0010.0000.7030.012
lib/python2.7/site-packages/MySQLdb/cursors.py:204(execute)
610.0000.0000.7020.012
lib/python2.7/site-packages/MySQLdb/cursors.py:411(_query)
610.0010.0000.7010.011
lib/python2.7/site-packages/MySQLdb/cursors.py:372(_do_query)
610.6990.0110.6990.011
lib/python2.7/site-packages/MySQLdb/connections.py:267(query)
360.1600.0000.5430.000 {method 'escape' of
'_mysql.connection' objects}
60.1810.0000.5210.000 {map}
360.1910.0000.3390.000
lib/python2.7/site-packages/MySQLdb/cursors.py:98(_ensure_bytes)
15603250.3230.0000.3230.000 {isinstance}
60.3130.0000.3130.000
lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:526(construct_params)
240.0760.0000.2840.000
lib/python2.7/site-packages/sqlalchemy/sql/type_api.py:1181(process)
240.0850.0000.2080.000
/Users/rubendibattista/git/Leaf/pyggdrasill/pyggdrasill/sql/types/small_float.py:10(process_bind_param)
60.0340.0000.2060.000
lib/python2.7/site-packages/MySQLdb/times.py:125(DateTime2literal)
240.1550.0000.1550.000
lib/python2.7/site-packages/MySQLdb/converters.py:68(Float2Str)
60.0280.0000.1540.000
lib/python2.7/site-packages/MySQLdb/times.py:39(format_TIMESTAMP)
60.1260.0000.1260.000 {method 'format' of 'str'
objects}
240.1230.0000.1230.000 {round}
4200150.0330.0000.0330.000 {method 'append' of 'list'
objects}
60.0220.0000.0220.000
lib/python2.7/site-packages/MySQLdb/converters.py:58(Thing2Str)
60.0180.0000.0180.000 {_mysql.string_literal}
1802060.0140.0000.0140.000 {len}
610.0010.0000.0020.000
lib/python2.7/site-packages/MySQLdb/cursors.py:182(_do_get_result)
So I get a way bigger number of function calls. To me this seems related to
the args escaping from MySQLdb cursors.py. Is there any better way to
optimize that INSERT query?
On Friday, November 2, 2018 at 5:23:51 PM UTC+1, Mike Bayer wrote:
>
> On Fri, Nov 2, 2018 at 11:17 AM Ruben Di Battista
> > wrote:
> >
> > Hello,
> >
> > I have a huge insert of the type:
> >
> > ```
> > session.execute(
> > insert_query,
> > [
> > {
> > 'time': times[i],
> > 'elevation': elevation[i],
> > 'azimuth': azimuth[i],
> > 'doppler': doppler[i],
> > 'slant': slant[i],
> > 'passageID': passage_id
> > }
> > for i in six.moves.range(0, n)
> > ]
> > )
> >
> > ```
> >
> > where n is huge.
> >
> > I was told by a colleague that SQLALchemy limits query length to 65536
> chars while MySQL can cappet 33MB big queries. Can I tailor this in
> SQLAlchemy?
>
> your colleague is mistaken. SQLAlchemy has no limit on the sizes of
> query strings or any other string.
>
>
> >
> > --
> > 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 r
[sqlalchemy] Re: Increase max query length!
So just to expand on what I said before, If I use the code I just posted,
SQLAlchemy caps the query string limit and so it emits 10 - 12 queries
instead of just one big query (taking more time). If I pre-build the big
INSERT string with Python raw string interpolation, and then I
session.execute it, I obtain performance benefits.
On Friday, November 2, 2018 at 4:17:43 PM UTC+1, Ruben Di Battista wrote:
>
> Hello,
>
> I have a huge insert of the type:
>
> ```
> session.execute(
> insert_query,
> [
> {
> 'time': times[i],
> 'elevation': elevation[i],
> 'azimuth': azimuth[i],
> 'doppler': doppler[i],
> 'slant': slant[i],
> 'passageID': passage_id
> }
> for i in six.moves.range(0, n)
> ]
> )
>
> ```
>
> where n is huge.
>
> I was told by a colleague that SQLALchemy limits query length to 65536
> chars while MySQL can cappet 33MB big queries. Can I tailor this in
> SQLAlchemy?
>
--
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] Increase max query length!
Hello,
I have a huge insert of the type:
```
session.execute(
insert_query,
[
{
'time': times[i],
'elevation': elevation[i],
'azimuth': azimuth[i],
'doppler': doppler[i],
'slant': slant[i],
'passageID': passage_id
}
for i in six.moves.range(0, n)
]
)
```
where n is huge.
I was told by a colleague that SQLALchemy limits query length to 65536
chars while MySQL can cappet 33MB big queries. Can I tailor this in
SQLAlchemy?
--
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] Custom JSON type that acts as a numpy array on Python side
Ok, thank you for the fast answer as always.
Moreover the specs for the JSON datatype for MySQL say:
A JSON column cannot have a default value.
So I think I will just skip the default value.
Thank you by the way again!
_
-. .´ |
', ;|∞∞
˜˜ |∞ RdB
,.,|∞∞
.' '. |
-' `’
https://rdb.is
On 30 agosto 2018 a 17:09:33, Mike Bayer (mike...@zzzcomputing.com) scritto:
On Thu, Aug 30, 2018 at 6:32 AM, Ruben Di Battista
wrote:
> Ehi Mike, thank you :).
>
> I just went ahead in implementing a custom type for a Python object that
is
> a (subclass of) numpy array and that is stored as JSON in the DB.
>
> This is the Python class:
>
> class ElevationMask(np.ndarray):
> r""" A class to represent an elevation mask.
>
> This class proxies the functionality of a numpy array encoding the
> elevation mask as a functional dependency of the type:
>
> .. math::
>
> \epsilon_\text{mask} = \epsilon_\text{mask}(\theta)
>
> So the elevation mask is described as the mapping
> :math:`\epsilon_\text{mask}` that for each value of azimuth angle
> :math:`\theta` maps the corresponding elevation angle value
>
>
> Args:
> elevation_mask(numpy.ndarray): The elevation mask as numpy array.
> It's
> an 2xN array. First row is the azimuth values. Second row is
> elevation values
> Attributes:
> azimuth(numpy.ndarray): The values of the azimuth angles that are
> part
> of the elevation mask
> elevation(numpy.ndarray): The corrisponding elevation values to the
> azimuth values
> """
>
> def __new__(cls, elevation_mask):
> obj = np.asarray(elevation_mask).view(cls)
> rows, cols = obj.shape
>
> if not(rows == 2):
> raise IndexError("The elevation mask array given has not the "
> "right shape. It needs to be a 2xN array.")
>
> return obj
>
> @property
> def azimuth(self):
> return self[0, :]
>
> @property
> def elevation(self):
> return self[1, :]
>
> def __call__(self, azimuth_value):
>
> # Interpolation bounds checking
> if azimuth_value < self.azimuth.min() or \
> azimuth_value > self.azimuth.max():
> raise ValueError("The required azimuth "
> "value is out of interpolation bounds ")
>
> return np.interp(azimuth_value,
> self.azimuth,
> self.elevation)
>
> @staticmethod
> def _to_json(obj):
> """ This method returns the dict representation for JSON encoding
> """
>
> return {
> '__type__': type(obj).__name__,
> 'entries': {
> 'azimuth': obj.azimuth.tolist(),
> 'elevation': obj.elevation.tolist()
> }
> }
>
> def to_json(self):
> """ This actually dumps the instance into the JSON string """
>
> return json.dumps(self, default=self._to_json)
>
> @staticmethod
> def _from_json(json_dict):
> """ This reconstitutes the Python object from the JSON serialization
> """
> if '__type__' in json_dict:
> if json_dict['__type__'] == ElevationMask.__name__:
> mask_entries = json_dict['entries']
> azimuth = mask_entries['azimuth']
> elevation = mask_entries['elevation']
>
> el_mask_array = np.array([
> azimuth,
> elevation
> ])
>
> return ElevationMask(el_mask_array)
>
> return json_dict
>
> @classmethod
> def from_json(self, json_dict):
> return json.loads(json_dict, object_hook=self._from_json)
>
>
> So it's basically a 2xN array.
>
> Then I have the actual custom type
>
> from sqlalchemy.dialects.mysql import JSON
>
> class JSONElevationMask(TypeDecorator):
> """ This type emits a JSON object into the Database. When instead
> loading
> the JSON from the database, it transforms the JSON in a ElevationMask
> object (i.e. a numpy-like object with few additional features """
>
> impl = JSON
>
> def process_bind_param(self, value, dialect):
> if value is not None:
> return value.to_json()
>
> def process_load_param(self, value, dialect):
> if value is not None:
> return ElevationMask.from_json(value)
>
> It seems to work as expected. The only problem I'm experiencing is when I
> setup a default value for a Column of this type:
>
> elevation_mask = Column('elevationMask', JSONElevationMask,
> default=ElevationMask([
> [0, 2*PI],
> [0, 0]])
> )
it looks like that object you have has many special behaviors,
including __new__, __call__, and is also a descendant of a C object,
so some combination of those things which I can't exactly reproduce
here is causing SQLAlchemy's check for passing of a "callable" to
fail. The "default" parameter of a Column can accept a Python
calla
Re: [sqlalchemy] Custom JSON type that acts as a numpy array on Python side
/Users/rubendibattista/.envs/pyggdrasill/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 330, in get_callable_argspec return get_callable_argspec(fn.__call__, no_self=no_self) File "/Users/rubendibattista/.envs/pyggdrasill/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 317, in get_callable_argspec if no_self and (_is_init or fn.__self__): ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all() That is because a `numpy` array cannot be checked for truth. Is there a way to monkey patch that `if no_self` in order to use the right method for the array to be true (so .any() or .all())? On Monday, July 23, 2018 at 7:51:15 PM UTC+2, Mike Bayer wrote: > > On Mon, Jul 23, 2018 at 1:28 PM, Ruben Di Battista > > wrote: > > Hello, > > I need to store a matrix into the database and I was evaluating the > > possibility to have a column of JSON type in MySQL to store it. What I > would > > like to achieve is the possibility of operating on a numpy array when > > manipulating that column on Python while keeping a "meaningful" data > type on > > the DB, if possible. > > > > I was reading the Custom Types section of the documentation and, even > though > > the application I envisage looks like just an extension to the JSON > > datatype, I believe to have understood that the `TypeDecorator` approach > > can't store a state (i.e. the numpy array). Is that correct? So do I > need to > > use the `UserDefined` subclassing method? > > when you want to use a special kind of datatype on the Python side, > and there is already a SQLAlchemy type on the database side that does > what you want, you use TypeDecorator. Types don't "store" states, > database columns do - you'd need to define how to marshal your numpy > array into JSON and back out again. you don't need to use > UserDefinedType. If you can create numpy->json and json->numpy > functions, I can show you how to stick that onto TypeDecorator. > > > > > > > > > > > Thanks in advance, > > > > -- > > 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] how to create timestamp without time zone column with postgresql
What about using DateTime type?
_
-. .´ |
', ;|∞∞
˜˜ |∞ RdB
,.,|∞∞
.' '. |
-' `’
http://rdb.is
On 23 luglio 2018 a 11:01:10, Yingchen Zhang (cevin.che...@gmail.com)
scritto:
data type TIMESTAMP just have one param is timezone and it's use server
timezone setting.
how to create a `timestamp without time zone` column with postgresql ?
code:
Column('created_at', TIMESTAMP(False), nullable=False, server_default=text(
'now()'))
--
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] Custom JSON type that acts as a numpy array on Python side
Hello, I need to store a matrix into the database and I was evaluating the possibility to have a column of JSON type in MySQL to store it. What I would like to achieve is the possibility of operating on a numpy array when manipulating that column on Python while keeping a "meaningful" data type on the DB, if possible. I was reading the Custom Types section of the documentation and, even though the application I envisage looks like just an extension to the JSON datatype, I believe to have understood that the `TypeDecorator` approach can't store a state (i.e. the numpy array). Is that correct? So do I need to use the `UserDefined` subclassing method? Thanks in advance, -- 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] Merge instances of objects without dropping attributes that are not persisted
Hello, I have some SQLAlchemy-persisted instances of objects that have some relationships coming from a parallel execution with multiprocessing. When I reduce the results coming from the several processes, I need to merge some relationships (`satellite` and `ground_station` objects in the code below) since the associated object got another instance id during the multiprocessing execution and if I don't merge I get an error like this: sqlalchemy.exc.InvalidRequestError: Can't attach instance ; another instance with key <...> is already present in this session Below the code responsible of this (where results is the reduced list coming from the multiprocessing execution) for passage in results: # I need to merge since if coming from multiprocessing the instance # IDs change. passage.satellite = session.merge(passage.satellite) passage.ground_station = session.merge(passage.ground_station) session.add(passage) This was working as expected when an attribute `satellite.tle` was persisted in the database. I was asked now to remove that persistence from the DB. So that attribute is present at runtime, but it's not in the columns of the DB. `session.merge` now returns an objects where that `tle` attribute is not present anymore. So in the call: passage.satellite = session.merge(passage.satellite) the `satellite` object within `session.merge` correctly has the `tle` object, while the returned object from `session.merge` does not. Is there a way to avoid that merge strips off the attributes that are not persisted in the database? -- 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
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, Антонио Антуан <a.ch@gmail.com> wrote: > You can specify column precision > <http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Float.__init__> > > пн, 5 мар. 2018 г. в 18:42, Ruben Di Battista <rubendibatti...@gmail.com>: > >> 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 gr
[sqlalchemy] Emit INSERT query with float values reducing number of decimal digits
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.
Re: [sqlalchemy] Re: Temporarily disable DB persistence for optimization routine
I confirm what I said. The run in multiprocessing was regenerating instances because after deserialization they were getting new IDs. I tried to implement a custom __hash__ but it seems that SQLAlchemy does not get it. What I did was disabling the backref cascade for `Satellite` and `GroundStation` objects and then, after optimization, doing: for passage in results: # I need to merge since if coming from multiprocessing the instance # IDs change. passage.satellite = session.merge(passage.satellite) passage.ground_station = session.merge(passage.ground_station) session.add(passage) This looks working as expected. Thanks to Mike and Simon pointing me on the right track! -- 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] Re: Temporarily disable DB persistence for optimization routine
I was not able to find anything generating new instances. In facts the culprit was that with the `prop.schedule()` method I'm using multiprocessing. So the instances of the `Passage` objects that are in different processes, gain a different `id` (and moreover they're referenced instances of related objects as GroundStation or Satellite) and when I "join" the result from all the processes, the Passage objects contain references to the objects that became "new" since they were run in a separate process. In facts I removed the parallel multiprocessing part, and everything is working as expected. How should I cope with multiprocessing (I'm already using `scoped session`)? Merging objects when joining the results in the different processes (tried on the fly, so far getting other errors... did not investigate enough)? Are there best practices? On Mon, Jan 15, 2018 at 11:53 AM, Simon King <si...@simonking.org.uk> wrote: > Yes, if you can't find where you are creating new Satellite instances, > I'd probably stick an assert statement in Satellite.__init__ and see > where it gets triggered. > > Simon > > On Mon, Jan 15, 2018 at 10:34 AM, Ruben Di Battista > <rubendibatti...@gmail.com> wrote: > > Dear Simon, > > > > thanks again for your kind help. > > > > Actually the creation of new instances is not intended. But I'm not > getting > > where they are created... > > > > I give you more insight: > > > > This is the scheduler object with the associated propagate() method > > > > class Scheduler(six.with_metaclass(abc.ABCMeta)): > > """ This class gets a list of GroundStation objects and a list of > > Satellites > > objects and compute all the passages of the Satellites over the > > GroundStations > > > > Args: > > sat_list(list): List of Satellites objects > > gs_list(list): List of GroundStation objects > > start_day(datetime): The datetime object representing the day > from > > which to start the propagation > > time_of_propagation(int): Number of hours to propagate > > [default:24] > > deltaT(float): Time step to use for angles retrieval, in seconds > > """ > > > > def __init__(self, sat_list, gs_list, start_day, > time_of_propagation=24, > > deltaT=0.05): > > # Monkey patch the Satellite class with the cost_function > specific > > # of the scheduler algorithm. > > sat_class = type(sat_list[0]) > > sat_class.cost_function = self.cost_function > > > > self.sat_list = sat_list > > self.gs_list = gs_list > > self.start_day = start_day > > self.time_of_propagation = time_of_propagation > > self.deltaT = deltaT > > > > def propagate(self): > > """ This method computes all the passages of the Satellites over > the > > GroundStations > > > > Args: > > > > Returns: > > all_passages(PassageList): A list ordered from the earliest > > passage\ > > of all passages > > > > Raises: > > ModelNotAvailable: When a satellite is too far from Earth and > > the > > models available in Orbital are not good, a > > ModelNotAvailable is > > raised > > """ > > > > all_passages = PassageList() > > > > # Loop Over the gs list > > for gs in self.gs_list: > > # Loop over the satellites list > > for sat in self.sat_list: > > # Compute all the passages in the specified period > > passages = \ > > sat.get_next_passes(gs, self.start_day, > > self.time_of_propagation, > > deltaT=self.deltaT) > > > > # Unfolding the list of passages in a flat list > > all_passages = all_passages + passages > > > > return all_passages > > > > > > It just basically loops over all the ground station and satellites and > > generates all the passages. Maybe the fact that I monkey patch the > satellite > > class induces the creation of a new instance of `Satellite`? > > > > The details of the `get_next_passes` method of the `Satellite` class for > > what concerns the `Passage` instance creation, skipping the algorithmic &
Re: [sqlalchemy] Re: Temporarily disable DB persistence for optimization routine
Dear Simon, thanks again for your kind help. Actually the creation of new instances is not intended. But I'm not getting where they are created... I give you more insight: This is the scheduler object with the associated propagate() method class Scheduler(six.with_metaclass(abc.ABCMeta)): """ This class gets a list of GroundStation objects and a list of Satellites objects and compute all the passages of the Satellites over the GroundStations Args: sat_list(list): List of Satellites objects gs_list(list): List of GroundStation objects start_day(datetime): The datetime object representing the day from which to start the propagation time_of_propagation(int): Number of hours to propagate [default:24] deltaT(float): Time step to use for angles retrieval, in seconds """ def __init__(self, sat_list, gs_list, start_day, time_of_propagation=24, deltaT=0.05): # Monkey patch the Satellite class with the cost_function specific # of the scheduler algorithm. sat_class = type(sat_list[0]) sat_class.cost_function = self.cost_function self.sat_list = sat_list self.gs_list = gs_list self.start_day = start_day self.time_of_propagation = time_of_propagation self.deltaT = deltaT def propagate(self): """ This method computes all the passages of the Satellites over the GroundStations Args: Returns: all_passages(PassageList): A list ordered from the earliest passage\ of all passages Raises: ModelNotAvailable: When a satellite is too far from Earth and the models available in Orbital are not good, a ModelNotAvailable is raised """ all_passages = PassageList() # Loop Over the gs list for gs in self.gs_list: # Loop over the satellites list for sat in self.sat_list: # Compute all the passages in the specified period passages = \ sat.get_next_passes(gs, self.start_day, self.time_of_propagation, deltaT=self.deltaT) # Unfolding the list of passages in a flat list all_passages = all_passages + passages return all_passages It just basically loops over all the ground station and satellites and generates all the passages. Maybe the fact that I monkey patch the satellite class induces the creation of a new instance of `Satellite`? The details of the `get_next_passes` method of the `Satellite` class for what concerns the `Passage` instance creation, skipping the algorithmic part, are: def _generate_passage(self, next_pass, ground_station, deltaT): """ This method returns a Passage Object from the data returned from the original Orbital.get_next_passes method. """ aos, los, tca = next_pass return Passage(satellite=self, ground_station=ground_station, aos=aos, los=los, tca=tca, deltaT=deltaT) `self` should be a reference to the instance of `Satellite` already loaded from DB. I will try to dive more into the code... Thanks a lot for the kind help of all of you, On Monday, January 15, 2018 at 10:06:24 AM UTC+1, Simon King wrote: > > On Sat, Jan 13, 2018 at 3:31 PM, Ruben Di Battista > <rubendi...@gmail.com > wrote: > > > > > > On Friday, January 12, 2018 at 10:54:49 AM UTC+1, Simon King wrote: > >> > >> If I understand your code correctly, scheduler.propagate() creates a > >> large number of Passage instances, and you only want a small subset of > >> them to be added to the database. Is that correct? > > > > > > Correct! > > > >> > >> I would guess that the passages are getting added to the session > >> because you are setting their 'satellite' property to point to a > >> Satellite which is already in the database. This then causes the > >> passages to be added to the session due to the default cascade rules > >> on the relationship > >> (http://docs.sqlalchemy.org/en/latest/orm/cascades.html). > >> > >> If that really is the case, you can change the cascade rules for that > >> relationship, and then you'll probably need to explicitly add the > >> passages you want to *keep* to the session instead. > >> > >> Hope that helps, > >> > >> Simon > > > > > > Dear Simon, > > thank you. That was the case. Modif
Re: [sqlalchemy] Re: Temporarily disable DB persistence for optimization routine
On Friday, January 12, 2018 at 10:54:49 AM UTC+1, Simon King wrote: > > If I understand your code correctly, scheduler.propagate() creates a > large number of Passage instances, and you only want a small subset of > them to be added to the database. Is that correct? > Correct! > I would guess that the passages are getting added to the session > because you are setting their 'satellite' property to point to a > Satellite which is already in the database. This then causes the > passages to be added to the session due to the default cascade rules > on the relationship > (http://docs.sqlalchemy.org/en/latest/orm/cascades.html). > > If that really is the case, you can change the cascade rules for that > relationship, and then you'll probably need to explicitly add the > passages you want to *keep* to the session instead. > > Hope that helps, > > Simon > Dear Simon, thank you. That was the case. Modifying the cascade disabling the backref cascade does not load in the DB the passages at propagation time. But now, when I manually add the subset of passages after the optimization, I get a: InvalidRequestError: Can't attach instance ; another instance with key [...] is already present in this section. So, I suppose that disabling the backref cascade now SQLAlchemy is not capable anymore to recognize the already loaded Satellite objects... Should I maybe merge somewhere? > > On Fri, Jan 12, 2018 at 2:10 AM, Mike Bayer <mik...@zzzcomputing.com > > wrote: > > I can't give you much detail except to say the unique object recipe is > > doing an .add() when it finds an identity that isn't taken, if you > > don't want those persisted then take out the part of the recipe doing > > add(). However, you'd need to alter the recipe further such that if > > the program asks for that same identity again which you didn't want to > > flush to the DB, and you'd like to use the same object, you need to > > pull that from some kind of local dictionary of "pending" objects with > > those identities, if that makes sense. > > > > the second email with the after_attach thing implies you are already > > adding an object to the Session. > > > > Neither of these code examples show example of use, where you are > > doing things that make objects and you'd like them to not be > > persisted. If you need to create unique objects in memory without > > persisting, you just need to store them in some dictionary that sets > > up the in-memory uniqueness you are looking for. > > > > > > > > On Thu, Jan 11, 2018 at 11:37 AM, Ruben Di Battista > > <rubendi...@gmail.com > wrote: > >> Last copy paste went wrong. > >> > >> The uniqueness is ensured by: > >> > >> @event.listens_for(orm.session.Session, "after_attach") > >> def after_attach(session, instance): > >> # when ConstrainedSatellite objects are attached to a Session, > >> # figure out if in the database there's already the Constraint, > >> # requested, if yes return that object, if not create a new one. > >> # This is an adaptation of the UniqueObject pattern > >> # suggested by SQLAlchemy documentation > >> # > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject > >> if isinstance(instance, UniqueAssociationProxy): > >> instance = instance.ensure_unicity(session) > >> > >> > >> > >> > >> On Thursday, January 4, 2018 at 6:05:38 PM UTC+1, Ruben Di Battista > wrote: > >>> > >>> Hello, > >>> I'm writing a satellite passage scheduler that has a database > persistence > >>> layer to store the scheduled passages. > >>> > >>> The DB schema is organized as follows: > >>> - A table storing the satellites (using NORAD No as Primary Key) > >>> - A table storing the ground stations where to compute the passages of > the > >>> satellites > >>> - A table storing the passages of these satellites, with two foreign > keys > >>> linking each passage to a Ground Station and a Satellite > >>> - A table storing all the types of constraints a satellite can have > >>> - A table storing all the types of weights (used to perform the > >>> scheduling) that can be assigned to each satellite > >>> > >>> Than I configured some association proxies (with the related `Unique > >>> Object` pattern) in order to assign the weights and the satellites
[sqlalchemy] Re: Temporarily disable DB persistence for optimization routine
Last copy paste went wrong.
The uniqueness is ensured by:
@event.listens_for(orm.session.Session, "after_attach")
def after_attach(session, instance):
# when ConstrainedSatellite objects are attached to a Session,
# figure out if in the database there's already the Constraint,
# requested, if yes return that object, if not create a new one.
# This is an adaptation of the UniqueObject pattern
# suggested by SQLAlchemy documentation
# https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject
if isinstance(instance, UniqueAssociationProxy):
instance = instance.ensure_unicity(session)
On Thursday, January 4, 2018 at 6:05:38 PM UTC+1, Ruben Di Battista wrote:
>
> Hello,
> I'm writing a satellite passage scheduler that has a database persistence
> layer to store the scheduled passages.
>
> The DB schema is organized as follows:
> - A table storing the satellites (using NORAD No as Primary Key)
> - A table storing the ground stations where to compute the passages of the
> satellites
> - A table storing the passages of these satellites, with two foreign keys
> linking each passage to a Ground Station and a Satellite
> - A table storing all the types of constraints a satellite can have
> - A table storing all the types of weights (used to perform the
> scheduling) that can be assigned to each satellite
>
> Than I configured some association proxies (with the related `Unique
> Object` pattern) in order to assign the weights and the satellites as a
> dictionary
>
> sat.constraints['min_elevation']= 10
>
> The details of the relationships here below:
>
> # Relationship definitions
> orm.mapper(Satellite, satellite, properties={
> 'passages': orm.relationship(Passage,
> backref='satellite',
> order_by=passage.c.aos,
> cascade='all, delete-orphan'),
>
>
> '_constraints': orm.relationship(
> ConstrainedSatellite, backref='satellite',
> collection_class=orm.collections.attribute_mapped_collection(
> 'name'),
> cascade='all, delete-orphan'),
>
>
> '_weights': orm.relationship(
> WeightedSatellite, backref='satellite',
> collection_class=orm.collections.attribute_mapped_collection(
> 'name'),
> lazy='joined',
> cascade='all, delete-orphan'),
>
>
> '_tle': satellite.c.tle
>
>
> })
>
>
> orm.mapper(Constraint, constraint, properties={
> 'satellites': orm.relationship(ConstrainedSatellite, backref=
> 'constraint')
>
>
> })
>
>
> orm.mapper(Weight, weight, properties={
> 'satellites': orm.relationship(WeightedSatellite, backref='weight')
> })
>
>
>
>
> orm.mapper(ConstrainedSatellite, constraint_satellite)
>
>
> orm.mapper(WeightedSatellite, weight_satellite)
>
>
> orm.mapper(PassageData, passage_data)
>
>
> orm.mapper(Passage, passage, properties={
> 'angles': orm.relationship(PassageData, backref='passage',
>order_by=passage_data.c.time,
>cascade='all, delete-orphan')
>
>
> },
> confirm_deleted_rows=False
> )
>
>
> orm.mapper(GroundStation, ground_station, properties={
> 'passages': orm.relationship(Passage, backref='ground_station',
> order_by=passage.c.aos,
> cascade='all, delete-orphan')
> })
>
>
>
>
> # Association Proxies
> Satellite.constraints = association_proxy(
> '_constraints', 'value', creator=constrained_sat_creator
> )
>
>
> Satellite.weights = association_proxy(
> '_weights', 'value', creator=weighted_sat_creator
> )
>
>
>
>
> ConstrainedSatellite.constraint_name = association_proxy('constraint',
> 'name')
> WeightedSatellite.weight_name = association_proxy('weight', 'name')
>
>
>
>
> From the tests everything is working as expected. The problem is that I'm
> getting some performance issues while performing the scheduling procedure:
> this procedure needs to read the `constraints` and `weights` associated to
> each satellite to select the best passages among all the ones possible on
> each ground station. While reading the values of the `constraints` and
> `weights`, SQLAlchemy needs to store the Passage object in the Database. So
> all the passages are stored in the database during the propagation of all
> the possible passages, and then I need to manually expunge or delete the
> passages that haven't been scheduled from the DB.
>
> What I would like to achieve is to "shut down", after selecting the ground
> stati
[sqlalchemy] Re: Temporarily disable DB persistence for optimization routine
Dear Mike,
thank you for the fast response as usual.
Your comment made me think. Actually I was not adding things in the session
directly. I revised my code and I believe the behaviour I'm describing is
related to the application of the UniqueObject patter described in the
documentation.
What I'm doing is, having this mixin:
""" https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject
"""
import abc
def _unique(session, instance, hash_key, query_func):
cache = getattr(session, '_unique_cache', None)
if cache is None:
session._unique_cache = cache = {}
key = (type(instance), hash_key())
if key in cache:
return cache[key]
else:
with session.no_autoflush:
q = query_func(session)
obj = q.first()
if not obj:
obj = instance
if(isinstance(instance, UniqueAssociationProxy)):
session.add(obj)
cache[key] = obj
return obj
class UniqueAssociationProxy(object):
@abc.abstractmethod
def unique_hash(self, *arg, **kw):
raise NotImplementedError()
@abc.abstractmethod
def unique_filter(self, query, *arg, **kw):
raise NotImplementedError()
@abc.abstractmethod
def ensure_unicity(self, session):
return NotImplementedError()
Applied to the many by many mappings
class ConstrainedSatellite(UniqueAssociationProxy):
""" This class is used to be mapped with SQLALchemy in the association
object """
def __init__(self, constraint_name, value):
# Temporarily assigning the name to a string
# in order to ensure uniqueness
# https://goo.gl/LbJ7wf
# self._constraint_name = name
self._constraint_name = constraint_name
self.value = value
@property
def name(self):
if self.constraint is not None:
return self.constraint.name
else:
return self._constraint_name
def unique_hash(self):
return self._constraint_name
def unique_filter(self, session):
return
session.query(Constraint).filter_by(name=self._constraint_name)
def ensure_unicity(self, session):
instance = Constraint(name=self._constraint_name)
self.constraint = _unique(session, instance, self.unique_hash,
self.unique_filter)
return self
class WeightedSatellite(UniqueAssociationProxy):
""" This class is uded to be mapped with SQLAlchemy in the association
proxy with the Weights """
def __init__(self, weight_name, value):
# Temporarily assigning the name to a string
# in order to ensure uniqueness
# https://goo.gl/LbJ7wf
self._weight_name = weight_name
self.value = value
@property
def name(self):
if self.weight is not None:
return self.weight.name
else:
return self._weight_name
def unique_hash(self):
return self._weight_name
def unique_filter(self, session):
return session.query(Weight).filter_by(name=self._weight_name)
def ensure_unicity(self, session):
instance = Weight(name=self._weight_name)
self.weight = _unique(session, instance, self.unique_hash,
self.unique_filter)
return self
(In the previous message the mappings are reported).
Then the uniqueness is ensured by:
""" https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject
"""
import abc
def _unique(session, instance, hash_key, query_func):
cache = getattr(session, '_unique_cache', None)
if cache is None:
session._unique_cache = cache = {}
key = (type(instance), hash_key())
if key in cache:
return cache[key]
else:
with session.no_autoflush:
q = query_func(session)
obj = q.first()
if not obj:
obj = instance
if(isinstance(instance, UniqueAssociationProxy)):
session.add(obj)
cache[key] = obj
return obj
class UniqueAssociationProxy(object):
@abc.abstractmethod
def unique_hash(self, *arg, **kw):
raise NotImplementedError()
@abc.abstractmethod
def unique_filter(self, query, *arg, **kw):
raise NotImplementedError()
@abc.abstractmethod
def ensure_unicity(self, session):
return NotImplementedError()
In this way I'm making SQLA to store everything at instance init time, if I
well understand.
Could you please help me understand how to improve the situation?
Thanks in advance.
On Thursday, January 4, 2018 at 6:05:38 PM UTC+1, Ruben Di Battista wrote:
>
> Hello,
> I'm writing a satellite passage scheduler that has a database persistence
> layer to store the scheduled passages.
[sqlalchemy] Temporarily disable DB persistence for optimization routine
Hello,
I'm writing a satellite passage scheduler that has a database persistence
layer to store the scheduled passages.
The DB schema is organized as follows:
- A table storing the satellites (using NORAD No as Primary Key)
- A table storing the ground stations where to compute the passages of the
satellites
- A table storing the passages of these satellites, with two foreign keys
linking each passage to a Ground Station and a Satellite
- A table storing all the types of constraints a satellite can have
- A table storing all the types of weights (used to perform the scheduling)
that can be assigned to each satellite
Than I configured some association proxies (with the related `Unique
Object` pattern) in order to assign the weights and the satellites as a
dictionary
sat.constraints['min_elevation']= 10
The details of the relationships here below:
# Relationship definitions
orm.mapper(Satellite, satellite, properties={
'passages': orm.relationship(Passage,
backref='satellite',
order_by=passage.c.aos,
cascade='all, delete-orphan'),
'_constraints': orm.relationship(
ConstrainedSatellite, backref='satellite',
collection_class=orm.collections.attribute_mapped_collection('name'
),
cascade='all, delete-orphan'),
'_weights': orm.relationship(
WeightedSatellite, backref='satellite',
collection_class=orm.collections.attribute_mapped_collection('name'
),
lazy='joined',
cascade='all, delete-orphan'),
'_tle': satellite.c.tle
})
orm.mapper(Constraint, constraint, properties={
'satellites': orm.relationship(ConstrainedSatellite, backref=
'constraint')
})
orm.mapper(Weight, weight, properties={
'satellites': orm.relationship(WeightedSatellite, backref='weight')
})
orm.mapper(ConstrainedSatellite, constraint_satellite)
orm.mapper(WeightedSatellite, weight_satellite)
orm.mapper(PassageData, passage_data)
orm.mapper(Passage, passage, properties={
'angles': orm.relationship(PassageData, backref='passage',
order_by=passage_data.c.time,
cascade='all, delete-orphan')
},
confirm_deleted_rows=False
)
orm.mapper(GroundStation, ground_station, properties={
'passages': orm.relationship(Passage, backref='ground_station',
order_by=passage.c.aos,
cascade='all, delete-orphan')
})
# Association Proxies
Satellite.constraints = association_proxy(
'_constraints', 'value', creator=constrained_sat_creator
)
Satellite.weights = association_proxy(
'_weights', 'value', creator=weighted_sat_creator
)
ConstrainedSatellite.constraint_name = association_proxy('constraint',
'name')
WeightedSatellite.weight_name = association_proxy('weight', 'name')
>From the tests everything is working as expected. The problem is that I'm
getting some performance issues while performing the scheduling procedure:
this procedure needs to read the `constraints` and `weights` associated to
each satellite to select the best passages among all the ones possible on
each ground station. While reading the values of the `constraints` and
`weights`, SQLAlchemy needs to store the Passage object in the Database. So
all the passages are stored in the database during the propagation of all
the possible passages, and then I need to manually expunge or delete the
passages that haven't been scheduled from the DB.
What I would like to achieve is to "shut down", after selecting the ground
stations and the satellites for which to perform the optimization, the
SQLAlchemy persistence in order to perform the optimization procedure only
on Python objects, without having SQLAlchemy to store them in the DB, and
then just storing efficiently the optimized, smaller, list of them that are
computed by the scheduling algorithm.
Currently the scheduling script is like this (schematically):
# Create a DB session
session_factory = orm.sessionmaker(db)
session = orm.scoped_session(session_factory)
# Retrieve satellites from DB
sats = session.query(Satellite).all()
# Retrieve gss
ground_stations = session.query(GroundStation).all()
# Init Scheduler instance
scheduler = Scheduler(sats, ground_stations, start_day)
# This methods generates all the possible passages of all satellites on
all ground stations
# it needs to read `constraints` and `weights` for each satellite to
perform what it needs.
# currently all the passages get stored in the DB
all_passages = scheduler.propagate()
# This method selects from the totality of all_passages, a subset that
fulfills all
# the constraints.
scheduled_passages = prop.schedule(all_passages, iterations, pool)
# Remove from session passages not scheduled
# === This is the thing I would
[sqlalchemy] Multiprocessing with SQLAlchemy and pickling errors
Hello,
I have a heavy method of a class (not mapped with SQLA) that takes as
argument some SQLA instances (with relationships), does some operation on
them (without using any SQLA feature), and returns a subset of them. The
logic was working before I added the SQLAlchemy persistence layer using
pathos.multiprocessing.
In my full code I get a recursion depth exceeded error, and I was not able
to reproduce the problem with a MWE. By the way with a MWE I get a "Can't
picke : it's not found as
sqlalchemy.ext.declarative.api.Base". I suppose that I should define
__getstate__ and __setstate__ magic methods stripping out all the SQLA
"stuff" inside the instance. Is this right? Or there is something I'm not
considering? Will I be able to reconstitute the instance after the
pickling/unpickling? How does it work with the persistence with the
underlying session? Are there any examples for this?
Thanks in advance for the great support and help in this mailing list,
from sqlalchemy import Column, Integer, String, DateTime, Float,\
ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from pyggdrasill.sql.schema import connect_db
from datetime import datetime
import pathos.multiprocessing as mpp
from functools import partial
Base = declarative_base()
class Sensor(Base):
__tablename__ = 'sensor'
id = Column(Integer, primary_key=True)
name = Column(String(150))
readings = relationship("Reading", backref='sensor',
cascade='all, delete-orphan')
def __init__(self, name):
self.name = name
def read(self, room):
return [
Reading(
self,
room,
datetime.now(),
10.0),
Reading(
self,
room,
datetime.now(),
20.0),
Reading(
self,
room,
datetime.now(),
30.0)
]
class Room(Base):
__tablename__ = 'room'
id = Column(Integer, primary_key=True)
name = Column(String(150))
readings = relationship("Reading", backref='room',
cascade='all, delete-orphan')
def __init__(self, name):
self.name = name
class Reading(Base):
__tablename__ = 'reading'
id = Column(Integer, primary_key=True)
date = Column(DateTime)
voltage = Column(Float)
sensor_id = Column(Integer, ForeignKey('sensor.id'), nullable=False)
room_id = Column(Integer, ForeignKey('room.id'), nullable=False)
def __init__(self, sensor, room, date, voltage):
self.sensor = sensor
self.room = room
self.date = date
self.voltage = voltage
def __repr__(self):
return ''.format(self.date, self.voltage)
if __name__ == '__main__':
db = connect_db(
username='pygg',
password='albero della vita',
db_name='pyggdrasill',
echo=False)
Base.metadata.create_all(db)
S = sessionmaker(db)
session = S()
def mp_read(room, useless):
sensor = Sensor('Pressure Sensor')
return Room(sensor,
room,
datetime.now(),
20.0)
#readings = sensor.read(room)
pool = mpp.Pool()
partial_read = partial(mp_read, useless=None)
rooms = [Room('bedroom'), Room('Living'), Room('kitchen')]
readings = pool.map(partial_read, rooms)
# session.add(sensor)
# session.commit()
--
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] 'orphan-delete' when one ForeignKey is null on table with multiple ForeignKeys
Hello,
I have a tables with two ForeignKeys. When I remove the relation on one
side, SQLAlchemy sets to 'NULL' the related ForeignKey, but the related row
is not considered orphaned since it hase still the other ForeignKey. Is
there a way to make SQLAlchemy fulfill the `orphan-delete' cascade when
only one of the multiple ForeignKeys are removed? If I set them as NOT NULL
it will cause an Error.
MWE:
from sqlalchemy import Column, Integer, String, DateTime, Float,\
ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from pyggdrasill.sql.schema import connect_db
from datetime import datetime
Base = declarative_base()
class Sensor(Base):
__tablename__ = 'sensor'
id = Column(Integer, primary_key=True)
name = Column(String(150))
readings = relationship("Reading", backref='sensor',
cascade='all, delete-orphan')
def __init__(self, name):
self.name = name
def read(self, room):
return [
Reading(
self,
room,
datetime.now(),
10.0),
Reading(
self,
room,
datetime.now(),
20.0),
Reading(
self,
room,
datetime.now(),
30.0)
]
class Room(Base):
__tablename__ = 'room'
id = Column(Integer, primary_key=True)
name = Column(String(150))
readings = relationship("Reading", backref='room',
cascade='all, delete-orphan')
def __init__(self, name):
self.name = name
class Reading(Base):
__tablename__ = 'reading'
id = Column(Integer, primary_key=True)
date = Column(DateTime)
voltage = Column(Float)
sensor_id = Column(Integer, ForeignKey('sensor.id'))
room_id = Column(Integer, ForeignKey('room.id'))
def __init__(self, sensor, room, date, voltage):
self.sensor = sensor
self.room = room
self.date = date
self.voltage = voltage
def __repr__(self):
return ''.format(self.date, self.voltage)
if __name__ == '__main__':
db = connect_db(
username='pygg',
password='albero della vita',
db_name='pyggdrasill',
echo=False)
Base.metadata.create_all(db)
S = sessionmaker(db)
session = S()
sensor = Sensor('Pressure Sensor')
room = Room('bedroom')
readings = sensor.read(room)
accepted_readings = []
for r in readings:
if r.voltage > 10:
accepted_readings.append(r)
sensor.readings = accepted_readings
session.add(sensor)
session.commit()
print(len(accepted_readings))
print(len(session.query(Reading).all()))
--
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] Re: Streamlined dictionary numpy arrays storage in a one-to-many relationship
Thanks, this is in fact what I implemented now as a method in the Sensor
class, exploiting also the bulk_insert_mappings since the number of
readings are quite a lot (400k each time):
def store_readings(self, session):
if not(self.id):
session.add(self)
session.flush()
sensor_id = self.id
times, voltages = self.get_values_from_somewhere()
n = len(times)
chunk_size = 10
for i in six.moves.range(0, n, chunk_size):
begin = i
end = i+chunk_size
time_chunk = times[begin:end]
voltage_chunk = voltages[begin:end]
session.bulk_insert_mappings(PassageData, [
{
'time': t,
'voltage': v,
'sensor_id': sensor_id
} for t, v in
zip(time_chunk, voltave_chunk)]
)
session.commit()
The problem with the custom collection is that maybe we lose performances
that are gained while using the numpy powerful indexing. I need, still, to
study a bit the suggestion given by Mike.
Thanks,
On Friday, July 28, 2017 at 9:28:19 PM UTC+2, Jonathan Vanasco wrote:
>
> Unless you need to use all the readings immediately, have you considered
> just making a custom def under the Sensor model, and then inserting all the
> readings via sqlalchemy core? That would allow you to insert them without
> creating ORM objects, which people using numpy and a lot of data often like
> to avoid.
>
> Then you could do...
>
> s = Sensor()
> session.add(s)
> session.add_readings(dates, voltages, values)
>
> it would look something like this...
>
> class Sensor(Base):
> def add_readings(self, dates, voltages, values):
> if not self.id:
> # flush this to the session
> session = object_session(self)
> session.flush(objects=[self])
># insert via core
>
>
--
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] Re: Streamlined dictionary numpy arrays storage in a one-to-many relationship
sensor.values['value'] = values
This is a typo. Should be this:
sensor.readings['value'] = values
On Thursday, July 27, 2017 at 4:50:23 PM UTC+2, Ruben Di Battista wrote:
>
> Hello, I'm trying to figure out a streamlined way to store some children
> values that are stored in numpy arrays in Python. As example let's assume I
> have a parent object that is a sensor that has some readings associated to
> it:
>
> class Sensor(object):
> __tablename__ = 'sensor'
> id = Column(Integer, primary_key=True),
> name = Column(String)
> readings = relationship("Reading", backref="sensor")
>
>
> class Reading(object):
> __tablename__ = 'reading'
> id = Column(Integer, primary_key=True),
> date = Column(DateTime),
> voltage = Column(Float),
> value = Column(Float),
>
> sensor_id = Column(Integer, ForeignKey('sensor.id'))
>
>
> What I would like to achieve is something like:
> sensor = Sensor(name='Bedroom Sensor')
> dates, voltages, values = get_sensor_data_from_somewhere() #<-- This
> returns three numpy arrays respectively of datetime, float, float types,
> same len!
>
> sensor.readings['date'] = dates
> sensor.readings['voltage'] = voltages
> sensor.values['value'] = values
>
> session.add(sensor)
>
> Is this possible somehow? It's similar to the attribute_mapped_collection,
> but I need to map three different keys to the three attributes of the
> Reading object.
>
--
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] Streamlined dictionary numpy arrays storage in a one-to-many relationship
Hello, I'm trying to figure out a streamlined way to store some children
values that are stored in numpy arrays in Python. As example let's assume I
have a parent object that is a sensor that has some readings associated to
it:
class Sensor(object):
__tablename__ = 'sensor'
id = Column(Integer, primary_key=True),
name = Column(String)
readings = relationship("Reading", backref="sensor")
class Reading(object):
__tablename__ = 'reading'
id = Column(Integer, primary_key=True),
date = Column(DateTime),
voltage = Column(Float),
value = Column(Float),
sensor_id = Column(Integer, ForeignKey('sensor.id'))
What I would like to achieve is something like:
sensor = Sensor(name='Bedroom Sensor')
dates, voltages, values = get_sensor_data_from_somewhere() #<-- This
returns three numpy arrays respectively of datetime, float, float types,
same len!
sensor.readings['date'] = dates
sensor.readings['voltage'] = voltages
sensor.values['value'] = values
session.add(sensor)
Is this possible somehow? It's similar to the attribute_mapped_collection,
but I need to map three different keys to the three attributes of the
Reading object.
--
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] Inverse the mapping in a composite association proxy
I'm playing a bit with composite Association Proxies and stumbled upon this
discussion. Just wanted to point out that here:
@event.listens_for(Session, "after_attach")
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by(
title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
when we add objects in this way, for example:
students = # ... list of User objects
courses = {
'math': 10,
'italian': 9
}
for student in students:
student.courses= courses
session.add(student)
session.commit()
Would cause again an IntegrityError. So I modified the recipe like this:
@event.listens_for(Session, "after_attach")
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
if isinstance(instance, UserCourse):
cache = getattr(session, _unique_cache)
if cache is None:
session._unique_cache = {}
with session.no_autoflush:
key = instance._course_title
course = session.query(Course).filter_by(
title=key).first()
if course is None:
# Here we check that the object is in the session (but
not committed yet
# in the db
if key in cache:
course = cache[key]
else:
course = Course(title=instance._course_title)
cache[key] = course
instance.course = course
Don't know if there's a better way of achieving this.
On Thursday, January 9, 2014 at 1:44:00 AM UTC+1, Michael Bayer wrote:
>
> OK well to do it exactly the way the example does it, each time we create
> a UserCourse, it will also create a Course. That’s pretty simple, we use
> two association proxies, one for User.courses and the other for
> UserCourse.course, mappings are like this:
>
> class User(Base):
> __tablename__ = 'users'
>
> # Columns
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
> # Relations
> courses = association_proxy('user_courses', 'grade',
> creator=lambda k, v: UserCourse(course_title=k, grade=v))
>
> def __init__(self, name):
> self.name = name
>
> class Course(Base):
> __tablename__ = 'courses'
>
> # Columns
> id = Column(Integer, primary_key=True)
> title = Column(Text, unique=True)
>
> def __init__(self, title):
> self.title = title
>
>
> # Composite association proxies linking users and preferences
> class UserCourse(Base):
> __tablename__ = 'user_courses'
>
> # Columns
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
> grade = Column(Integer)
>
> # Relations
> user = relationship(
> User,
> backref=backref(
> 'user_courses',
> collection_class=attribute_mapped_collection('course_title'),
> cascade='all, delete-orphan'
> )
> )
> course = relationship(Course)
>
> course_title = association_proxy("course", "title”) # will create a
> new Course object when course_title is set
>
> def __init__(self, course_title, grade):
> self.course_title = course_title
> self.grade = grade
>
> the other way that’s maybe a little more “real world” is that if two
> different UserCourse objects are for “math”, we’d want only one Course
> object with “math”. There’s a few ways to go about making those unique
> Course objects - one common one is the “unique object” recipe at
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject .
>
> A variant on that which I’ve been using lately doesn’t rely upon any kind
> of global session and instead uses events.In this approach, we modify
> the above so that UserCourse.course_title temporarily points to a plain
> string, then when attached to a Session looks up and/or creates the unique
> Course object, looks like this:
>
> from sqlalchemy import event
>
> # same User and Course...
>
> # Composite association proxies linking users and preferences
> class UserCourse(Base):
> __tablename__ = 'user_courses'
>
> # Columns
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
> grade = Column(Integer)
>
> # Relations
>
Re: [sqlalchemy] Association proxy in classical mapping keeping the business logic and the db schema separated
Thanks for taking the time to answer.
Doing what you say means that then I have to import the models classes from
the *sql.py* module instead of the *models.py*. Am I right? Or the
association_proxy attribute is then available to the original class (as it
happens when using mapper)? I'm not super familiar with the automagic of
SQLAlchemy...
On Mon, Jul 24, 2017 at 1:50 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> On Sun, Jul 23, 2017 at 11:26 AM, Ruben Di Battista
> <rubendibatti...@gmail.com> wrote:
> > Hello,
> >
> > I'm trying to introduce database persistence into an already existent
> class
> > hierarchy. That mean I have in a separate module all the class
> representing
> > the models of my application (without SQL interaction methods), then I
> have
> > in another module the SQL schema and the mapping (so I'm using the
> classical
> > mapping):
> >
> > sql.py
> >
> > import sqlalchemy.orm as orm
> > from sqlalchemy import Metadata, Integer, String, DateTime
> > from myproject.models import Item, Order
> >
> > metadata = MetaData()
> >
> > item = Table('item', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('name', String),
> > Column('quantity', Integer)
> > )
> >
> > order = Order('order', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('date', DateTime),
> > )
> >
> > orm.mapper(Item, item)
> > orm.mapper(Order, order)
> >
> >
> >
> >
> > models.py
> > class Item(object):
> > def __init__(self, name, quantity):
> > self.name = name
> > self.quantity = quantity
> >
> > class Order(object):
> > def __init__(self, date):
> > self.date = date
> >
> > Now I would like to introduce a M2M relationship with additional column
> in
> > the association table:
> >
> > order_item = Table('order_item', metadata,
> >Column('order_id', Integer, ForeignKey('order.id')),
> >Column('item_id', Integer, ForeignKey('item.id')),
> >Column('price', Float)
> > )
> >
> >
> > In the documentation, when they present the Association proxy, they do
> that
> > in a declarative way. So they add an attribute to the class:
> >
> > # ... Class definition ...
> > items = association_proxy('order_items', 'items')
> >
> >
> > Is there a way to achieve this without touching the classes in models.py?
> > Maybe using the orm.mapper? Or the only way is to modify the class adding
> > the association proxy like in the documentation? Do you have any
> reference
> > to point me out on the right direction to use association proxies with
> > classical mapping?
>
> Basically, just do this:
>
> orm.mapper(Order, order)
> Order.items = association_proxy('order_items', 'items')
>
> The association proxy is independent of the declarative system and if
> you look in early documentation you'll see it described in terms of
> classical mapping.
>
> As far as the term "without touching the classes", there's not much
> point trying to be purist about this as the call to mapper(Order,
> order) highly alters the Order class in any case.
>
>
> >
> > Thanks,
> >
> > PS: You find this same question on SO, if you want you can answer also
> > there.
> >
> >
> >
> > --
> > 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.
> --
[sqlalchemy] Association proxy in classical mapping keeping the business logic and the db schema separated
Hello,
I'm trying to introduce database persistence into an already existent class
hierarchy. That mean I have in a separate module all the class representing
the models of my application (without SQL interaction methods), then I have
in another module the SQL schema and the mapping (so I'm using the
classical mapping):
*sql.py*
*import sqlalchemy.orm as ormfrom sqlalchemy import Metadata, Integer,
String, DateTimefrom myproject.models import Item, Ordermetadata =
MetaData()item = Table('item', metadata, Column('id', Integer,
primary_key=True), Column('name', String),
Column('quantity', Integer) )order = Order('order', metadata,
Column('id', Integer, primary_key=True),
Column('date', DateTime), )orm.mapper(Item,
item)orm.mapper(Order, order)*
*models.py*
class Item(object):
def __init__(self, name, quantity):
self.name = name
self.quantity = quantity
class Order(object):
def __init__(self, date):
self.date = date
Now I would like to introduce a M2M relationship with additional column in
the association table:
order_item = Table('order_item', metadata,
Column('order_id', Integer, ForeignKey('order.id')),
Column('item_id', Integer, ForeignKey('item.id')),
Column('price', Float)
)
In the documentation, when they present the Association proxy, they do that
in a declarative way. So they add an attribute to the class:
# ... Class definition ...
items = association_proxy('order_items', 'items')
Is there a way to achieve this without touching the classes in *models.py?
*Maybe
using the *orm.mapper? *Or the only way is to modify the class adding the
association proxy like in the documentation? Do you have any reference to
point me out on the right direction to use association proxies with
classical mapping?
Thanks,
PS: You find this same question on SO, if you want you can answer also there
.
--
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.
