Re: [sqlalchemy] Columns not deferred when an object is merged into a session with load=False
On 7/16/15 11:47 AM, Tom Flannaghan wrote: We are using sqlalchemy and postgres extensively, and have come across a problem with deferred columns when an object is merged into a session with load=False. Under these conditions, we find that the deferred columns are not deferred - i.e. they loaded when any attribute is accessed, rather than being deferred until explicitly accessed. We are using sqlalchemy 0.9.7 and postgres 9.4.1. thanks for reporting this issue. https://bitbucket.org/zzzeek/sqlalchemy/issues/3488/columnpopertymerge-w-load-false-calls has been added to address a possible fix targeted at 1.1. For now, I'd recommend either not using expire() or specifying specific attribute names to expire(). Here's a simple example that illustrates the problem using the following table: class Port(Base): ''' Table port in schema comm. ''' __tablename__ = port name = Column(String, primary_key=True) port = deferred(Column(Integer, unique=True)) First we try querying Port on name: my_port = session.query(Port).filter_by(name='test_thing').one() my_port.name This code produces the following query, showing that the port column is correctly deferred: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(name_1)s Now I expire and expunge the my_port object, and then merge it back into the session with load=False, and access name like this: session.expire(my_port) session.expunge(my_port) new_port = session.merge(my_port, load=False) new_port.name Which produces the following query, showing that the port column has not been deferred, and has instead been included in the query that fetches name: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.port AS comm_port_port, comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(param_1)s If I do not set load=False, the column is correctly deferred. I've also tried creating a deferred object directly rather than expunging one from the session and the problem still occurs, so it seems to be caused by the load=False option. Thanks, Tom -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] using sqlalchemy to track changes in database
I think you can write a trigger in your DB for all tables and actions you want to analyze .. and your triggers you can put the changes in some log tables. Using sqlalchemy you can put this changes inside your db functions. My best regards, Ivan El 15/7/2015 21:37, Balaji Pattewar balaji.patte...@gmail.com escribió: Hi All, Can any body help me how Sqlalchemy can be used to identify changes in database? I mean how to identify that some rows got delete or added after particular time.? Thanks Balaji -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] strange behavior in count with custom mapper option
yeah, that's a little confusing for most people (including me), but a
simple session.query(func.count(Entity.id_)).filter(...).scalar() solved
the issue, since it produces a statement close to what i really want.
Mike, thanks for your time! :)
best regards,
richard.
On 07/15/2015 07:50 PM, Mike Bayer wrote:
there's your two rows:
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
and you can see they both are the same. therefore count of 2, .all()
gives you one object.
On 7/15/15 3:58 PM, Richard Gerd Kuesters wrote:
right! sorry, now here we go (again):
(Pdb) import logging
(Pdb) logging.basicConfig()
(Pdb) logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:56:44,565 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s
AND system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s
AND system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine
{'rm_timestamp_1': 0, 'system_unit_id_1': 24769797950537768,
'rm_timestamp_2': 0}
INFO:sqlalchemy.engine.base.Engine:{'rm_timestamp_1': 0,
'system_unit_id_1': 24769797950537768, 'rm_timestamp_2': 0}
2015-07-15 16:56:44,567 DEBUG sqlalchemy.engine.base.Engine Col
('count_1',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('count_1',)
2015-07-15 16:56:44,568 DEBUG sqlalchemy.engine.base.Engine Row (2L,)
DEBUG:sqlalchemy.engine.base.Engine:Row (2L,)
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:57:06,070 INFO sqlalchemy.engine.base.Engine
[sqlalchemy] Columns not deferred when an object is merged into a session with load=False
We are using sqlalchemy and postgres extensively, and have come across a problem with deferred columns when an object is merged into a session with load=False. Under these conditions, we find that the deferred columns are not deferred - i.e. they loaded when any attribute is accessed, rather than being deferred until explicitly accessed. We are using sqlalchemy 0.9.7 and postgres 9.4.1. Here's a simple example that illustrates the problem using the following table: class Port(Base): ''' Table port in schema comm. ''' __tablename__ = port name = Column(String, primary_key=True) port = deferred(Column(Integer, unique=True)) First we try querying Port on name: my_port = session.query(Port).filter_by(name='test_thing').one() my_port.name This code produces the following query, showing that the port column is correctly deferred: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(name_1)s Now I expire and expunge the my_port object, and then merge it back into the session with load=False, and access name like this: session.expire(my_port) session.expunge(my_port) new_port = session.merge(my_port, load=False) new_port.name Which produces the following query, showing that the port column has not been deferred, and has instead been included in the query that fetches name: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.port AS comm_port_port, comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(param_1)s If I do not set load=False, the column is correctly deferred. I've also tried creating a deferred object directly rather than expunging one from the session and the problem still occurs, so it seems to be caused by the load=False option. Thanks, Tom -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Columns not deferred when an object is merged into a session with load=False
Thanks for your reply. Our exact problem is that we are creating empty detached objects from the primary key alone, and then merging them in to a session, so we can't do this: On Thursday, 16 July 2015 18:44:26 UTC+1, Michael Bayer wrote: For now, I'd recommend either not using expire() or specifying specific attribute names to expire(). I just included the expire() in the example as it was a more succinct way to reproduce the same bug. Our code looks more like this: detached_port = Port(name='test') make_transient_to_detached(detached_port) new_port = session.merge(detached_port, load=False) ... In my example, Port only has two columns so this won't demonstrate the bug as the only non-deferred column is filled in already, but more complicated objects that are merged in this way will not defer columns. Do you think there a work around in this case? Thanks, Tom -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
