Re: [sqlalchemy] Slow entities pickling for caching purposes

[Alexander Egorov]

I've considered manual queries and building classes by myself. This option 
is good in terms of performance, but it requires a lot of code duplication 
and kills code reuse. So I want to use the same mapper classes for both 
read and modify operations. Also, I want to have the same query 
description. I've tried to build classes using Query.values and calling 
constructor and it gave ~3 times boost. What I want is to have the same 
functionality in generic form, without need to manually write these loops. 
Pseudocode to illustrate my probably crazy idea:
session.query(Entity).all()  # loads entities with modification tracking, 
with _sa_instance_state filled and added to the session.
session.query(Entity).readonly().all()  # loads entities without 
modification tracking, without _sa_instance_state and not added to the 
session.

# Probably it would be useful in cases when the Entity has a read-only link 
to another entity via relationship.
session.query(Entity).options(sa.orm.joinedload(Entity.another_entity, 
readonly_mapping=True)).get(123)

PyPy is an option I'm considering too. Its pickle module seems to be 
significantly slower, however. I need 3.6 version which is in beta now, so 
I have to wait.
Name (time in ms) PyPy 3.5   CPython 3.6.3
--
test_unpickle_dicts   12.2969 (1.0)  2.3764
test_load_from_database   26.4408 (2.15)91.3482
test_unpickle_classes 30.7339 (2.50) 9.4188
--



пятница, 11 октября 2019 г., 20:52:57 UTC+7 пользователь Mike Bayer написал:
>
>
>
> On Fri, Oct 11, 2019, at 5:23 AM, Александр Егоров wrote:
>
> Thank you very much for your answers!
>
> Some more questions
> 1) I see you use sa.inspect(self).dict in __getstate__. Is it safe to 
> replace it with self.__dict__? I didn't find any difference in the result, 
> but __dict__ works 30 times faster.
>
>
> there's no difference.   there's an old extension that allows objects to 
> persist in some form other than __dict__ but I'm sure you're not using it.
>
>
> 2) I'm going to implement __getstate__ and __setstate__ in a Base class. 
> Is it a risky idea from your point of view?
>
>
> not really, pickle is already flaky enough
>
>
>
> 3) We've greatly optimized the unpickling for read-only entities. Is it 
> possible to do the same during loading from the database? When I read data, 
> I don't need ORM state, because I'm not going to modify the data and don't 
> want lazy loads.
>
>
> I would recommend using a plain result object from Query.statement or 
> Core.statement and build up the objects yourself in that case. the "ORM 
> state" you refer towards is intrinsic to everything the ORM does including 
> all the relationship loading, etc.you'd need to re-implement all of 
> that yourself.
>
> Since your performance requirements appear to be well beyond the norm have 
> you considered using pypy ?   This would appear to be a much simpler way to 
> double or triple your performance without building your own ORM.
>
>
>
> On Friday, 11 October 2019 00:34:03 UTC+7, Mike Bayer wrote:
>
>
>
> On Thu, Oct 10, 2019, at 2:48 AM, Александр Егоров wrote:
>
> Hello!
>
> I want to cache query results in a distributed cache (Redis), so I need to 
> serialize/deserialize fetched entities very fast.
> However, it turned that SQLAlchemy entities are very heavy for pickle to 
> dump/load. Regular classes and dicts with the same structure takes 
> significantly less amount of time to reconstruct from bytes.
> It's even slower than fetching and mapping the entities from a database.
>
> Here are benchmark results, loading 1 simple entities:
>
> *class *Entity(Base):
> __tablename__ = *'test_entity'*
> id = sa.Column(sa.Integer, primary_key=*True*, autoincrement=*True*)
> field1 = sa.Column(sa.String)
> field2 = sa.Column(sa.Integer)
>
>
>
> Name (time in ms)  MedianStdDev   
> Dump bytes
> 
> test_unpickle_dicts2.3764 (1.0)  0.0797 (1.09)  
> 337806
> test_unpickle_dataclasses  9.2546 (3.89) 0.0734 (1.0)   
> 437861
> test_unpickle_classes  9.4188 (3.96) 0.1230 (1.68)  
> 437862
> test_load_from_database   91.3482 (38.44)0.6686 (9.10)
> test_unpickle_unsaved_entities   108.6726 (45.73)0.5154 (7.02) 
> 1448169
> test_unpickle_persisted_entities 166.4531 (70.04)0.3787 (5.16) 
> 1697849
> -
>
>
> *Environment*:
> OS: Ubuntu 16.04
> Python: CPython 3.6.3
> SQLAlchemy: 1.3.0
> Database: in-memory SQLite, PostgreSQL (all gives similar results)
> The full 

Re: [sqlalchemy] boud parameter to NCHAR column in Oracle

[Mike Bayer]

On Fri, Oct 11, 2019, at 3:56 PM, Victor Olex wrote:
> A bit of advocacy from my side on cx_Oracle: 
> https://github.com/oracle/python-cx_Oracle/issues/365
> 
> Mike, there's something you might want to look at. We have this monkey patch 
> on SQLAlchemy in our recent code, which was necessary to get the correct 
> behavior for NCHAR columns.
> 
> from sqlalchemy.dialects.oracle import cx_oracle
> from sqlalchemy.sql sqltypes
> 
> class _OracleNChar(sqltypes.NCHAR):
>  def get_dbapi_type(self, dbapi):
>  return dbapi.FIXED_NCHAR
> 
> cx_oracle._OracleNChar = _OracleNChar
> cx_oracle.dialect.colspecs[sqltypes.NCHAR] = _OracleNChar

it seems likely that should be in the codebase as there is already:

class _OracleChar(sqltypes.CHAR):
 def get_dbapi_type(self, dbapi):
 return dbapi.FIXED_CHAR



can you open a bug report ?




> 
> 
> On Thursday, October 3, 2019 at 12:26:40 PM UTC-4, Mike Bayer wrote:
>> With cx_Oracle, you really should likely be using typing for everything as 
>> cx_Oracle is pretty sensitive as well to the cursor.setinputsizes() 
>> settings, which SQLAlchemy will do for you if you send in typed bound 
>> parameters; additionally, you can set typing information for result columns 
>> also (use text().columns()) which SQLAlchemy uses in order to set up 
>> cursor.outputtypehandler. Both of these are cx_Oracle things that are 
>> unfortunately unique to this DBAPI and they are hugely important; even the 
>> django ORM has to use outputtypehandler. 
>> 
>> On Thu, Oct 3, 2019, at 12:20 PM, Mike Bayer wrote:
>>> hey there -
>>> 
>>> you should apply typing behavior which should be safe to apply to any CHAR 
>>> like this:
>>> 
>>>  class PaddedChar(TypeDecorator):
>>>  impl = NCHAR
>>> 
>>>  def process_bind_param(self, value, dialect):
>>>  if value is not None:
>>>  value = value + (" " * (self.impl.length - len(value)))
>>>  return value
>>> 
>>>  result = conn.execute(
>>>  text("select * from nchartable where id = :id").bindparams(
>>>  bindparam("id", type_=PaddedChar(4))
>>>  ),
>>>  {"id": "1"},
>>>  ).fetchall()
>>> 
>>> 
>>> 
>>> 
>>> On Thu, Oct 3, 2019, at 4:47 AM, mdob wrote:
 Hi everyone, 
 
 There's IMO unusual behavior in Oracle when using bound parameters on 
 NCHAR column. 
 
 from sqlalchemy import create_engine
 from sqlalchemy.sql import text
 
 
 e = create_engine('oracle://chinook:p4ssw0rd@localhost/xe')
 
 
 result = e.execute(
  text("select * from nchartable where id = '1'"),
 ).fetchall()
 
 
 print 'hardcoded:', list(result)
 
 
 result = e.execute(
  text('select * from nchartable where id = :id'),
 {'id': '1'}
 ).fetchall()
 
 
 print 'trimmed:', list(result)
 
 
 
 
 result = e.execute(
  text('select * from nchartable where id = :id'),
 {'id': '1 '} # padded with spaces
 ).fetchall()
 
 
 print 'padded:', list(result)
 
 
 output
 hardcoded: [(u'1 ',)]
 trimmed: []
 padded: [(u'1 ',)]
 
 When value is hardcoded or bound parameter is padded the statement works 
 as expected, but it doesn't when value is trimmed. Although the value in 
 where statement is just like in hardcoded case.
 
 As explained on cxOracle mailing list 
 https://sourceforge.net/p/cx-oracle/mailman/message/36775002/ it's a 
 deliberate decision to bind values str to VARCHAR2 and unicode to 
 NVARCHAR2. Unfortunately that doesn't work correctly for filtering on CHAR 
 and NCHAR. 
 
 I know it's more cxOracle matter but how do you deal with this in 
 SQLAlchemy? Expecially when working with text SQL statement which may be 
 complex e.g. use stored procedures. 
 
 Thanks for any clues, 
 Michal
 

 --
 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 sqlal...@googlegroups.com.
 To view this discussion on the web visit 
 https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com
  
 .
>>> 
>>> 

>>> --
>>> 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 

Re: [sqlalchemy] boud parameter to NCHAR column in Oracle

[Victor Olex]

A bit of advocacy from my side on cx_Oracle: 
https://github.com/oracle/python-cx_Oracle/issues/365

Mike, there's something you might want to look at. We have this monkey 
patch on SQLAlchemy in our recent code, which was necessary to get the 
correct behavior for NCHAR columns.

from sqlalchemy.dialects.oracle import cx_oracle
from sqlalchemy.sql sqltypes

class _OracleNChar(sqltypes.NCHAR):
def get_dbapi_type(self, dbapi):
return dbapi.FIXED_NCHAR

cx_oracle._OracleNChar = _OracleNChar
cx_oracle.dialect.colspecs[sqltypes.NCHAR] = _OracleNChar


On Thursday, October 3, 2019 at 12:26:40 PM UTC-4, Mike Bayer wrote:
>
> With cx_Oracle, you really should likely be using typing for everything as 
> cx_Oracle is pretty sensitive as well to the cursor.setinputsizes() 
> settings, which SQLAlchemy will do for you if you send in typed bound 
> parameters; additionally, you can set typing information for result columns 
> also (use text().columns())  which SQLAlchemy uses in order to set up 
> cursor.outputtypehandler.   Both of these are cx_Oracle things that are 
> unfortunately unique to this DBAPI and they are hugely important; even the 
> django ORM has to use outputtypehandler.   
>
> On Thu, Oct 3, 2019, at 12:20 PM, Mike Bayer wrote:
>
> hey there -
>
> you should apply typing behavior which should be safe to apply to any CHAR 
> like this:
>
> class PaddedChar(TypeDecorator):
> impl = NCHAR
>
> def process_bind_param(self, value, dialect):
> if value is not None:
> value = value + (" " * (self.impl.length - len(value)))
> return value
>
> result = conn.execute(
> text("select * from nchartable where id = :id").bindparams(
> bindparam("id", type_=PaddedChar(4))
> ),
> {"id": "1"},
> ).fetchall()
>
>
>
>
> On Thu, Oct 3, 2019, at 4:47 AM, mdob wrote:
>
> Hi everyone, 
>
> There's IMO unusual behavior in Oracle when using bound parameters on 
> NCHAR column. 
>
> from sqlalchemy import create_engine
> from sqlalchemy.sql import text
>
>
> e = create_engine('oracle://chinook:p4ssw0rd@localhost/xe')
>
>
> result = e.execute(
> text("select * from nchartable where id = '1'"),
> ).fetchall()
>
>
> print 'hardcoded:', list(result)
>
>
> result = e.execute(
> text('select * from nchartable where id = :id'),
> {'id': '1'}
> ).fetchall()
>
>
> print 'trimmed:', list(result)
>
>
>
>
> result = e.execute(
> text('select * from nchartable where id = :id'),
> {'id': '1   '}  # padded with spaces
> ).fetchall()
>
>
> print 'padded:', list(result)
>
>
> output
> hardcoded: [(u'1   ',)]
> trimmed: []
> padded: [(u'1   ',)]
>
> When value is hardcoded or bound parameter is padded the statement works 
> as expected, but it doesn't when value is trimmed. Although the value in 
> where statement is just like in hardcoded case.
>
> As explained on cxOracle mailing list 
> https://sourceforge.net/p/cx-oracle/mailman/message/36775002/ it's a 
> deliberate decision to bind values str to VARCHAR2 and unicode to 
> NVARCHAR2. Unfortunately that doesn't work correctly for filtering on CHAR 
> and NCHAR. 
>
> I know it's more cxOracle matter but how do you deal with this in 
> SQLAlchemy? Expecially when working with text SQL statement which may be 
> complex e.g. use stored procedures. 
>
> Thanks for any clues, 
> Michal
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com
>  
> 
> .
>
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/c313d214-d5a2-455c-b661-3a7cdfb379f2%40www.fastmail.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 

Re: Snowflake odities and ReplaceableObject

[Mike Bayer]

I think it would be simplest to use a regular expression to pull out the symbol 
names from the declared function in order to write out the DROP text.

Otherwise, just add additional arguments to ReplaceableObject. It's a recipe so 
you should change it directly, I wouldn't create new superclasses or anything 
like that. 




On Fri, Oct 11, 2019, at 2:58 AM, Scott wrote:
> I am using alembic with Snowflake.
> 
> In terms of DDL migrations I need to cater for SECURED VIEWS, it is pretty 
> trivial to extend ReplaceableObject to deal with this.
> 
> The other thing I am finding is that Snowflake insists that DROP FUNCTION be 
> provided with a list of the argument types along with the function name, so 
> with a function:
> 
>  CREATE FUNCTION myfunc(TABLENAME VARCHAR)
>  RETURNS VARCHAR
>  
> 
> this does not work:
> 
>  DROP FUNCTION myfunc
> 
> I need to go
> 
>  DROP FUNCTION myfunc(VARCHAR)
> 
> But I cannot go
> 
>  DROP FUNCTION myfunc(TABLENAME VARCHAR)
> 
> So in terms of using ReplaceableObject, to CREATE I need:
> 
> ReplaceableObject(
>  "myfunc(TABLENAME VARCHAR)",
>  """
>  RETURNS VARCHAR
> LANGUAGE javascript
> ...
> 
> But to DROP I need:
> 
> ReplaceableObject(
>  "myfunc(VARCHAR)",
>  """
>  RETURNS VARCHAR
> LANGUAGE javascript
> ...
> 
> I can hack something together with a superclass, but though I would mention 
> here in case someone has a cleaner solution.
> 
> Cheers, Scott
> 

> --
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy-alembic" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy-alembic/38d5e79a-2e50-4924-a77a-c2def597a132%40googlegroups.com
>  
> .

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/95987d77-6110-49ae-b5d0-f7c4a199c1dc%40www.fastmail.com.

Re: [sqlalchemy] Slow entities pickling for caching purposes

[Mike Bayer]

On Fri, Oct 11, 2019, at 5:23 AM, Александр Егоров wrote:
> Thank you very much for your answers!
> 
> Some more questions
> 1) I see you use sa.inspect(self).dict in __getstate__. Is it safe to replace 
> it with self.__dict__? I didn't find any difference in the result, but 
> __dict__ works 30 times faster.

there's no difference. there's an old extension that allows objects to persist 
in some form other than __dict__ but I'm sure you're not using it.


> 2) I'm going to implement __getstate__ and __setstate__ in a Base class. Is 
> it a risky idea from your point of view?

not really, pickle is already flaky enough



> 3) We've greatly optimized the unpickling for read-only entities. Is it 
> possible to do the same during loading from the database? When I read data, I 
> don't need ORM state, because I'm not going to modify the data and don't want 
> lazy loads.

I would recommend using a plain result object from Query.statement or 
Core.statement and build up the objects yourself in that case. the "ORM state" 
you refer towards is intrinsic to everything the ORM does including all the 
relationship loading, etc. you'd need to re-implement all of that yourself.

Since your performance requirements appear to be well beyond the norm have you 
considered using pypy ? This would appear to be a much simpler way to double or 
triple your performance without building your own ORM.


> 
> On Friday, 11 October 2019 00:34:03 UTC+7, Mike Bayer wrote:
>> 
>> 
>> On Thu, Oct 10, 2019, at 2:48 AM, Александр Егоров wrote:
>>> Hello!
>>> 
>>> I want to cache query results in a distributed cache (Redis), so I need to 
>>> serialize/deserialize fetched entities very fast.
>>> However, it turned that SQLAlchemy entities are very heavy for pickle to 
>>> dump/load. Regular classes and dicts with the same structure takes 
>>> significantly less amount of time to reconstruct from bytes.
>>> It's even slower than fetching and mapping the entities from a database.
>>> 
>>> Here are benchmark results, loading 1 simple entities:
>>> 
>>> *class *Entity(Base):
>>>     __tablename__ = *'test_entity'**
**    *id = sa.Column(sa.Integer, primary_key=*True*, autoincrement=*True*)
>>>     field1 = sa.Column(sa.String)
>>>     field2 = sa.Column(sa.Integer)
>>> 
>>> 
>>> Name (time in ms)                      Median            StdDev           
>>> Dump bytes
>>> 
>>> test_unpickle_dicts                    2.3764 (1.0)      0.0797 (1.09)      
>>> 337806
>>> test_unpickle_dataclasses              9.2546 (3.89)     0.0734 (1.0)       
>>> 437861
>>> test_unpickle_classes                  9.4188 (3.96)     0.1230 (1.68)      
>>> 437862
>>> test_load_from_database               91.3482 (38.44)    0.6686 (9.10)
>>> test_unpickle_unsaved_entities       108.6726 (45.73)    0.5154 (7.02)     
>>> 1448169
>>> test_unpickle_persisted_entities     166.4531 (70.04)    0.3787 (5.16)     
>>> 1697849
>>> -
>>> 
>>> *Environment*:
>>> OS: Ubuntu 16.04
>>> Python: CPython 3.6.3
>>> SQLAlchemy: 1.3.0
>>> Database: in-memory SQLite, PostgreSQL (all gives similar results)
>>> The full benchmark code is in a single attachment file.
>>> To run: 
>>> pytest test_ser.py --benchmark-columns=median,stddev --benchmark-disable-gc
>>> Requirements:
>>> pip install sqlalchemy==1.3.0 pytest==3.6.0 pytest-benchmark==3.1.1 
>>> dataclasses==0.6
>>> 
>>> *Questions:*
>>> 1) What strategy for caching I should consider while using SQLAlchemy? 
>>> Currently, the only option I see is to have a duplicated declaration of 
>>> entities in a form of simple classes and use it when I don't need 
>>> modification. Needles to say, it's a lot of code duplication.
>>> 2) Is it possible to have a read-only loader in SQLAlchemy? I can imagine 
>>> mapping loaded data to an entity class but without instrumentation overhead 
>>> which is not needed to read the data.
>>> If continue the idea further, I can imagine a situation when I load the 
>>> entity for modification, and it has a read-only relationship to another 
>>> entity. So that I don't pay for things I don't use.
>>> 
>>> Probably I'm making some crazy things and all wrong, feel free to point me 
>>> out. My ultimate goal is to manage caching with SQLAlchemy and understand 
>>> SQLAlchemy more in this direction.
>>> Thank you in advance! 
>> 
>> Hi there -
>> 
>> there's no simple answer for this, have you considered writing your own 
>> serializer for your objects rather than using pickle's default strategy? 
>> that is, if you implement __getstate__ and __setstate__, you can allow the 
>> serialization to not include the "_sa_instance_state" that's associated with 
>> the objects which is where you'd get some of the slowness on dumping / 
>> loading, it also would save lots of memory / network overhead by 

Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects

[natsjoo sodillepa]

Hi Simon,
Thank you for your reply. 
I've tried all the variant I could think of, also eg for item in items: 
session.delete(it),
with and without remove on the list of items, all known mapper and relation 
params but nothing.

However, I've tried a similar structure from examples from the web and 
those seems to work,
so the problem must lie somewhere in our code. Or could it have anything to 
do with scoped sessions?

Anyway: I'm working on the isolation of the problem, which is not that 
easy. I'll come back here if I've
got something more MCVE or with a solution.

Kind regards,
Nacho

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/32d41bf0-11a2-4552-9ae3-450808074080%40googlegroups.com.

Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects

[Simon King]

You haven't given a complete script to reproduce the problem, so I'm
not certain what happened. The following is just a guess

You are using query(...).delete() with joined-table inheritance, which
has a lot of caveats in the docs:


https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.delete

As far as I can tell, SA will issue a query to delete rows from the
item table. You've got ON DELETE CASCADE on the item_meta.id foreign
key, so I guess the db will delete the corresponding rows from
item_meta.

The default value for synchronize_session in Query.delete is
'evaluate', which means SA will try to find objects in the in-memory
session which match your deletion criteria and remove them from the
session. I'm guessing that this step is missing at least one ItemMeta
object for some reason.

When you query for Item objects, SA will first autoflush any pending
changes in the session. It looks like you have some pending changes on
an ItemMeta instance, so it tries to flush those, but the
corresponding row has already been deleted, hence the error.

If you provide a runnable test script, we may be able to give more answers.

Hope that helps,

Simon

On Fri, Oct 11, 2019 at 10:08 AM natsjoo sodillepa  wrote:
>
> Hi all,
>
> We have a list of polymorphic objects from which delete object does not work, 
> not matter what we try.
> The situation:
>
> class ItemGroup(Base):
> __tablename__ = 'item_group'
> __table_args__ = (
> UniqueConstraint('model_id', 'item_group_color_id', 
> name='unique_model_id_item_group_color_id_uc'),
> )
>
> id = Column(Integer, primary_key=True)
> items = relationship("Item", back_populates="item_group")
>
> class Item(Base):
> __tablename__ = 'item'
>
> id = Column(Integer, primary_key=True)
>
> item_group_id = Column(ForeignKey('item_group.id'), nullable=False, 
> index=True)
> item_group = relationship('ItemGroup', back_populates="items", 
> uselist=False)
>
> __mapper_args__ = {
> 'polymorphic_identity': __tablename__,
> 'polymorphic_on': item_type
> }
>
> class ItemMeta(Item):
> __tablename__ = 'item_meta'
>
> id = Column(Integer, ForeignKey('item.id', ondelete="CASCADE"), 
> primary_key=True)
>
> meta_name = Column(String(255, collation), nullable=False)
>
> __mapper_args__ = {
> 'polymorphic_identity': __tablename__,
> }
>
>
> The problem occurs after a delete:
>
>   session.query(ItemMeta).filter_by(item_group=ig).delete()
>
> Now, querying the child works fine:
>   session.query(ItemMeta).filter_by(item_group=ig).all()
>   []
>
> But querying the parent:
>   test_fixtures.session.query(Item).filter_by(item_group=ig).all()
>
>
>   Give: Instance '' has been deleted, or its row 
> is otherwise not present.
>
> In the database I can see the lingering parent objects. I guess that I have 
> to use something like
> delete-orphan, but as I dont have a relation from child to father, so now I'm 
> stuck.
>
> Any ideas are welcome.
>
> Kind regards,
> Nacho
>
> --
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/7dea2eaa-6390-4a54-abd5-fae925727c17%40googlegroups.com.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdiKj4vPGW%2BSB-HC6ZU%3DwABe6Vz98pSjZBNp6wLZ3DH1w%40mail.gmail.com.

Re: [sqlalchemy] Slow entities pickling for caching purposes

[Александр Егоров]

Thank you very much for your answers!

Some more questions
1) I see you use sa.inspect(self).dict in __getstate__. Is it safe to 
replace it with self.__dict__? I didn't find any difference in the result, 
but __dict__ works 30 times faster.
2) I'm going to implement __getstate__ and __setstate__ in a Base class. Is 
it a risky idea from your point of view?
3) We've greatly optimized the unpickling for read-only entities. Is it 
possible to do the same during loading from the database? When I read data, 
I don't need ORM state, because I'm not going to modify the data and don't 
want lazy loads.

On Friday, 11 October 2019 00:34:03 UTC+7, Mike Bayer wrote:
>
>
>
> On Thu, Oct 10, 2019, at 2:48 AM, Александр Егоров wrote:
>
> Hello!
>
> I want to cache query results in a distributed cache (Redis), so I need to 
> serialize/deserialize fetched entities very fast.
> However, it turned that SQLAlchemy entities are very heavy for pickle to 
> dump/load. Regular classes and dicts with the same structure takes 
> significantly less amount of time to reconstruct from bytes.
> It's even slower than fetching and mapping the entities from a database.
>
> Here are benchmark results, loading 1 simple entities:
>
> *class *Entity(Base):
> __tablename__ = *'test_entity'*
> id = sa.Column(sa.Integer, primary_key=*True*, autoincrement=*True*)
> field1 = sa.Column(sa.String)
> field2 = sa.Column(sa.Integer)
>
>
>
> Name (time in ms)  MedianStdDev   
> Dump bytes
> 
> test_unpickle_dicts2.3764 (1.0)  0.0797 (1.09)  
> 337806
> test_unpickle_dataclasses  9.2546 (3.89) 0.0734 (1.0)   
> 437861
> test_unpickle_classes  9.4188 (3.96) 0.1230 (1.68)  
> 437862
> test_load_from_database   91.3482 (38.44)0.6686 (9.10)
> test_unpickle_unsaved_entities   108.6726 (45.73)0.5154 (7.02) 
> 1448169
> test_unpickle_persisted_entities 166.4531 (70.04)0.3787 (5.16) 
> 1697849
> -
>
>
> *Environment*:
> OS: Ubuntu 16.04
> Python: CPython 3.6.3
> SQLAlchemy: 1.3.0
> Database: in-memory SQLite, PostgreSQL (all gives similar results)
> The full benchmark code is in a single attachment file.
> To run: 
> pytest test_ser.py --benchmark-columns=median,stddev --benchmark-disable-
> gc
> Requirements:
> pip install sqlalchemy==1.3.0 pytest==3.6.0 pytest-benchmark==3.1.1 
> dataclasses==0.6
>
> *Questions:*
> 1) What strategy for caching I should consider while using SQLAlchemy? 
> Currently, the only option I see is to have a duplicated declaration of 
> entities in a form of simple classes and use it when I don't need 
> modification. Needles to say, it's a lot of code duplication.
> 2) Is it possible to have a read-only loader in SQLAlchemy? I can imagine 
> mapping loaded data to an entity class but without instrumentation overhead 
> which is not needed to read the data.
> If continue the idea further, I can imagine a situation when I load the 
> entity for modification, and it has a read-only relationship to another 
> entity. So that I don't pay for things I don't use.
>
> Probably I'm making some crazy things and all wrong, feel free to point me 
> out. My ultimate goal is to manage caching with SQLAlchemy and understand 
> SQLAlchemy more in this direction.
> Thank you in advance! 
>
>
> Hi there -
>
> there's no simple answer for this, have you considered writing your own 
> serializer for your objects rather than using pickle's default strategy?  
> that is, if you implement __getstate__ and __setstate__,  you can allow the 
> serialization to not include the "_sa_instance_state" that's associated 
> with the objects which is where you'd get some of the slowness on dumping / 
> loading, it also would save lots of memory / network overhead by not 
> storing lots of data that you can probably forego for storing in your cache.
>
> For bringing the classes back,  you can implement a setstate that returns 
> them as non-ORM classes, or patches them in as ORM classes.
>
> I've altered your test to include only ORM mapped classes, comparing your 
> baseline to one that uses __getstate__, and then several variants which 
> will grant more or less ORM functionality once you have unpickled them.
> the very fastest one which omits any ORM functionality at all, and looks 
> like what you propose, has a mean of 26 ms compared to 380 ms for the most 
> expensive.  
>
> there's one ORM-level guarantee you would want me to add for this most 
> optimized version, that is if you have an ORM object that has no 
> _sa_instance_state at all, you can still access attributes on it.  This 
> works but I would want to add test support so that I keep this as a 
> supported use case.
>
>
>

[sqlalchemy] delete of polymorphic objects leaves orphaned parent objects

[natsjoo sodillepa]

Hi all,

We have a list of polymorphic objects from which delete object does not 
work, not matter what we try.
The situation:

class ItemGroup(Base):
__tablename__ = 'item_group'
__table_args__ = (
UniqueConstraint('model_id', 'item_group_color_id', 
name='unique_model_id_item_group_color_id_uc'),
)

id = Column(Integer, primary_key=True)
items = relationship("Item", back_populates="item_group")

class Item(Base):
__tablename__ = 'item'

id = Column(Integer, primary_key=True)

item_group_id = Column(ForeignKey('item_group.id'), nullable=False, 
index=True)
item_group = relationship('ItemGroup', back_populates="items", 
uselist=False)

__mapper_args__ = {
'polymorphic_identity': __tablename__,
'polymorphic_on': item_type
}

class ItemMeta(Item):
__tablename__ = 'item_meta'

id = Column(Integer, ForeignKey('item.id', ondelete="CASCADE"), 
primary_key=True)

meta_name = Column(String(255, collation), nullable=False)

__mapper_args__ = {
'polymorphic_identity': __tablename__,
}


The problem occurs after a delete:

  session.query(ItemMeta).filter_by(item_group=ig).delete()

Now, querying the child works fine: 
  session.query(ItemMeta).filter_by(item_group=ig).all()
  []

But querying the parent:
  test_fixtures.session.query(Item).filter_by(item_group=ig).all()
  

  Give: Instance '' has been deleted, or its row is 
otherwise not present.

In the database I can see the lingering parent objects. I guess that I have to 
use something like
delete-orphan, but as I dont have a relation from child to father, so now I'm 
stuck.

Any ideas are welcome.

Kind regards,
Nacho

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/7dea2eaa-6390-4a54-abd5-fae925727c17%40googlegroups.com.

Snowflake odities and ReplaceableObject

[Scott]

I am using alembic with Snowflake.

In terms of DDL migrations I need to cater for SECURED VIEWS, it is pretty 
trivial to extend ReplaceableObject to deal with this.

The other thing I am finding is that Snowflake insists that DROP FUNCTION 
be provided with a list of the argument types along with the function name, 
so with a function:

CREATE FUNCTION myfunc(TABLENAME VARCHAR)
RETURNS VARCHAR


this does not work:

DROP FUNCTION myfunc

I need to go

DROP FUNCTION myfunc(VARCHAR)

But I cannot go

DROP FUNCTION myfunc(TABLENAME VARCHAR)

So in terms of using ReplaceableObject, to CREATE I need:

ReplaceableObject(
"myfunc(TABLENAME VARCHAR)",
"""
 RETURNS VARCHAR
LANGUAGE javascript
...

But to DROP I need:

ReplaceableObject(
"myfunc(VARCHAR)",
"""
 RETURNS VARCHAR
LANGUAGE javascript
...

I can hack something together with a superclass, but though I would mention 
here in case someone has a cleaner solution.

Cheers, Scott

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/38d5e79a-2e50-4924-a77a-c2def597a132%40googlegroups.com.