[sqlalchemy] Re: Serializing sqlalchemy declarative instances with yaml

[Sergii Nechuiviter]

This is Yaml bug: http://pyyaml.org/ticket/245

-- 
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: Row versioning not working with multi-table polymorphism

[Chris Wilson]

Dear Mike,

Thank you very much for the fast reply and workaround!

I think it might have been us that originally requested the server-side 
versioning. We would like to continue using it because we have some large 
tables that don't really need an additional ID column since the database 
already provides one for us. However it had occurred to me and we might use 
that approach if we have problems with the event listener approach.

Thanks, Chris.

-- 
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] Row versioning not working with multi-table polymorphism

[Mike Bayer]

On 09/20/2016 09:13 AM, Chris Wilson wrote:

Dear Michael,

I have been trying to add row versioning to our existing multi-table
polymorphism setup and I think I may have found an issue that I hope you
can help with.


hi Chris -

thanks for the clear test case, this is very helpful.

So you're doing the xmin thing which was a newer use case I got the 
library to support once someone asked that it be possible.   As you 
noticed, there are no values to actually UPDATE.  If we hit an attribute 
on the base table as well as the child table, then we see two UPDATEs 
like we expect:


UPDATE animal SET species=%(species)s WHERE animal.id = %(animal_id)s 
AND animal.xmin = %(animal_xmin)s RETURNING animal.xmin
2016-09-20 09:28:06,700 INFO sqlalchemy.engine.base.Engine 
{'animal_xmin': '547653', 'animal_id': 1, 'species': 'asdf'}
2016-09-20 09:28:06,701 INFO sqlalchemy.engine.base.Engine UPDATE dog 
SET toy=%(toy)s WHERE dog.id_animal = %(dog_id_animal)s
2016-09-20 09:28:06,701 INFO sqlalchemy.engine.base.Engine {'toy': 
'Bone', 'dog_id_animal': 1}



the problem is in the absence of any columns on the base table, we still 
need to bump "xmin" but there's no values to put into the UPDATE, except 
the set of a column to itself which is of course something the ORM never 
does normally.


The only thing I could find on google about this, which refers to 
setting a value to itself at 
http://dba.stackexchange.com/questions/40815/is-there-better-way-to-touch-a-row-than-performing-a-dummy-update, 
only concludes that it's better to use a distinct version counter, which 
is how I'd do it also.  I believe the XMIN thing here someone wanted so 
that a particular schema need not be changed.


To add a dummy "id=id", one way is to just intercept UPDATE statements 
that have no SET clause and to just add this in (using the 
before_execute() event).   To do this at the ORM is presenting more of a 
challenge, mostly because this column is a primary key column which 
doesn't support updating against a SQL expression at this level; issue 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3801/cant-use-sql-expression-for-pk-col 
is added.



We can set a non-PK column to a SQL expression that is the same column 
like this:


with session.begin():
dog.name = Animal.name.__clause_element__()
dog.toy = 'Bone'

Bugs here also include that I need to produce a ClauseElement directly 
for persistence to pick up on it, I shouldn't need to call upon 
__clause_element__() like that; issue 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3802/sql-expression-in-orm-update-only-look-for 
is added.


That's the most immediate workaround.   We can put that into an event:

from sqlalchemy import event
@event.listens_for(Dog, "before_update")
def upd(mapper, connection, obj):
# TODO: check for other columns already changed, check that "name"
# not already modified
obj.name = Animal.name.__clause_element__()

with session.begin():
dog.toy = 'Bone'

Above would be your solution for now (short of "use a normal version 
column" which I would recommend :) ).   I don't know that I want the ORM 
to do this automatically just yet.   I would prefer to just add more 
docs to the "server versioned column" examples noting that you need to 
do something like this for joined inheritance (once the bugs are fixed).


Note the point of setting to a SQL expression is to force the ORM to 
actually include the column; if we set it to itself, it sees no net 
change and it wouldn't work, unless you used a special value that didn't 
naturally compare against itself.












The following example appears to generate invalid SQL when it tries to
update the base class table, but there are no columns to fetch; it just
wants to get the current version number:

from sqlalchemy import Column, FetchedValue, ForeignKey, Integer,
Text, create_engine
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute

Base = declarative_base()

class Animal(Base):
__tablename__ = 'animal'

id = Column(Integer, primary_key=True)
name = Column(Text)
species = Column(Text)
_version = Column("xmin", Integer,
server_default=FetchedValue(), server_onupdate=FetchedValue(),
system=True)
# _version = Column("xmin", Integer)

@declared_attr
def __mapper_args__(cls):
if isinstance(cls._version, InstrumentedAttribute):
version_col = cls._version.property.columns[0]
else:
version_col = cls._version

return {
'polymorphic_on':   cls.species,
'polymorphic_identity': cls.__name__,
'version_id_col':   version_col,
'version_id_generator': False,
}

class Dog(Animal):
__tablename__ = 'dog'
id_animal = 

[sqlalchemy] Row versioning not working with multi-table polymorphism

[Chris Wilson]

Dear Michael,

I have been trying to add row versioning to our existing multi-table 
polymorphism setup and I think I may have found an issue that I hope you 
can help with.

The following example appears to generate invalid SQL when it tries to 
update the base class table, but there are no columns to fetch; it just 
wants to get the current version number:

from sqlalchemy import Column, FetchedValue, ForeignKey, Integer, Text, 
create_engine
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute

Base = declarative_base()

class Animal(Base):
__tablename__ = 'animal'

id = Column(Integer, primary_key=True)
name = Column(Text)
species = Column(Text)
_version = Column("xmin", Integer, server_default=FetchedValue(), 
server_onupdate=FetchedValue(), system=True)
# _version = Column("xmin", Integer)

@declared_attr
def __mapper_args__(cls):
if isinstance(cls._version, InstrumentedAttribute):
version_col = cls._version.property.columns[0]
else:
version_col = cls._version

return {
'polymorphic_on':   cls.species,
'polymorphic_identity': cls.__name__,
'version_id_col':   version_col,
'version_id_generator': False,
}

class Dog(Animal):
__tablename__ = 'dog'
id_animal = Column(Integer, ForeignKey(Animal.id), primary_key=True)
toy = Column(Text)

engine = create_engine('postgresql://user@host/database')
engine.echo = True
Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)
session = DBSession(autocommit=True)

with session.begin():
dog = Dog(name="fido")
session.add(dog)

with session.begin():
dog.toy = 'Bone'


Which raises the following exception:

File sqlalchemy_issue_repro_2.py, line 59, in : dog.toy = 'Bone' 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
490, in __exit__ : self.rollback() 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py, 
line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb) 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
487, in __exit__ : self.commit() 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
392, in commit : self._prepare_impl() 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
372, in _prepare_impl : self.session.flush() 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
2015, in flush : self._flush(objects) 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
2133, in _flush : transaction.rollback(_capture_exception=True) 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py, 
line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb) 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line 
2097, in _flush : flush_context.execute() 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py, 
line 373, in execute : rec.execute(self) 

File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py, 
line 532, in execute : uow 

File 

Re: [sqlalchemy] AutomapBase without binding engine/tables individually?

[Simon King]

On Tue, Sep 20, 2016 at 5:36 AM, Rahul Ahuja  wrote:
> Thanks for the swift reply, Mike! Unfortunately, I had some trouble running
> your code. I'm getting
> base = automap_base(MetaData())
>   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py",
> line 848, in automap_base
> Base.__name__,
> AttributeError: 'MetaData' object has no attribute '__name__'
>
> For some reason the class name doesn't seem to be parsing?

I think it should have been:

base = automap_base(metadata=MetaData())

...although I haven't followed the rest of the thread and I don't know
why it was necessary to specify the metadata specifically in the first
place.

Simon

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