[sqlalchemy] Re: Caching
merge is working rudimentally for objects with unloaded scalar/ instance/collection attributes in r3974. whats not yet happening is the merging of the various query.options() that may be present on the original deferred loader, which means the merged instance wont necessarily maintain the exact eager/lazy/deferred loading of the original, but this is not especially critical for the basic idea to work. example script using merge attached. Michael, thanks a lot for your support! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Caching
I rearranged instance-level deferred loaders to be serializable instances in r3968. you can now pickle an instance + its _state and restore, and all deferred/lazy loaders will be restored as well. I didnt yet test it specifically with merge() but give it a try, you shoudnt be getting that error anymore...the pickling issue from ticket #870 is also no longer present. Unfortunately it does not work (I am now at r3973). 1) I created an object with deferred property (not None). 2) Reloaded it in a new session (to erase deferred property) 3) Pickled/Unpickled 4) Removed everything but properties and _state. 5) obj = s.merge(obj, dont_load=True) (with a fresh session s) 6) obj.deferred_ppty = None merge worked without an exception this time. Thanks. PS. Special thanks for #871 (overheads in backref). It was blocking the full-featured use of SqlAlchemy while staying as efficient as raw SQL for me :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Caching
Mike, thanks for your reply.
what happens if you just leave _state alone ? there shouldnt be any
need to mess with _state (nor _entity_name). the only attribute
worth deleting for the cache operation is _sa_session_id so that the
instance isnt associated with any particular session when it gets
cached. Id also consider using session.merge(dont_load=True) which is
designed for use with caches (and also watch out for that log.debug(),
debug() calls using the standard logging module are notoriously slow).
The reason for deleting _state is to save some space in cache. I save
instances to cache on get operation, so they are unmodified. But, of
course, it is internal thing so the final decision is yours :)
I gave up trying merge(dont_load=True) after running this sample:
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100)),
Column('surname', String(100)))
mapper(User, users,
properties={
'surname': deferred(users.c.surname)
})
s = create_session()
u = User()
u.name = 'anton'
u.surname = 'belyaev'
s.save(u)
s.flush()
# now we need an instance with not loaded surname (because it is
deferred)
s = create_session()
u = s.query(User).get(1)
# cache it
cache = pickle.dumps(u)
# try to restore in a new session
s = create_session()
u = pickle.loads(cache)
u = s.merge(u, dont_load=True)
The latest statement fails with:
File /home/anton/eggs/lib/python2.5/site-packages/SQLAlchemy-0.4.1-
py2.5.egg/sqlalchemy/orm/session.py, line 1136, in object_session
if obj in sess:
TypeError: argument of type 'NoneType' is not iterable
Some notes on this test case:
1) If surname was a simple (not deferred) column property, merge
would work fine.
2) session.update(u) instead of merge would work fine even with
deferred column property, and the property itself would work fine (it
would load on first reference).
the only trac ticket for this is #490, which with our current
extension architecture is pretty easy to fix so its resolved in 3967 -
MapperExtensions are now fully inherited. If you apply the same
MapperExtension explicitly to a base mapper and a subclass mapper,
using the same ME instance will have the effect of it being applied
only once (and using two different ME instances will have the effect
of both being applied to the subclass separately).
I meant #870 (sorry, I should had provided reference in the first
message).
Back again to the testcase and note 2:
If, let say, I had some inheritance:
class Teacher(User):
pass
with polymorphic_fetch='deferred' (this is important), even
session.update(u) would not work. Because in this case deferred
attributes work through callables in _state, and callable does not
survive pickling.
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Caching
pickle isnt going to work with deferred columns unless you implement __getstate__ and __setstate__. so the issue with session.merge() is just an extension of that issue, correct ? i.e. without deferreds merge has no issue. is it not reasonable to ask that objects which are to be serialized and cached not have any deferred columns ? (or they are explicitly loaded before caching )? Sorry, I dont understand clearly. I do understand that pickle saves only __dict__ when no __getstate__ defined. So, to be cached, an object should fetch all its deferred columns (if any) and provide all of them at __getstate__. Right? And if an instance from cache has nothing for one of its deferred column values, then referencing these properties after merge wont load them from DB, but just fail? Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Caching
Hello, Several people already wrote something about memcached + SqlAlchemy. Remember, Mike Nelson wrote a mapper extention, it is available at: http://www.ajaxlive.com/repo/mcmapper.py http://www.ajaxlive.com/repo/mcache.py I've rewritten it a bit to fit 0.4 release of SA. Any response and comments are welcome, since I am not sure I am doing right things in the code :) I dont like that dirty tricks with deleting _state, etc. Maybe it could be done better? But it works somehow. It manages to cache query get operations. It has some problems with deferred fetch on inherited mapper because of some issues of SA (I've found them in Trac). import memcache as mc class MCachedMapper(MapperExtension): def get(self, query, ident, *args, **kwargs): key = query.mapper.identity_key_from_primary_key(ident) obj = query.session.identity_map.get(key) if not obj: mkey = gen_cache_key(key) log.debug(Checking cache for %s, mkey) obj = mc.get(mkey) if obj is not None: obj.__dict__[_state] = InstanceState(obj) obj.__dict__[_entity_name] = None log.debug(Found in cache for %s : %s, mkey, obj) query.session.update(obj) else: obj = query._get(key, ident, **kwargs) if obj is None: return None _state = obj._state del obj.__dict__[_state] del obj.__dict__[_entity_name] mc.set(mkey, obj) obj.__dict__[_state] = _state obj.__dict__[_entity_name] = None return obj def before_update(self, mapper, connection, instance): mkey = gen_cache_key(mapper.identity_key_from_instance(instance)) log.debug(Clearing cache for %s because of update, mkey) mc.delete(mkey) return EXT_PASS def before_delete(self, mapper, connection, instance): mkey = gen_cache_key(mapper.identity_key_from_instance(instance)) log.debug(Clearing cache for %s because of delete, mkey) mc.delete(mkey) return EXT_PASS The mapper can be used like this: mapper(User, users_table, extension=MCachedMapper()) session = create_session() user_1234 = session.query(User).get(1234) # this one loads from the DB session.clear() user_1234 = session.query(User).get(1234) # this one fetches from Memcached --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA and MySQL replication.
On Dec 6, 11:51 pm, Andrew Stromnov [EMAIL PROTECTED] wrote: I have DB with onemasterserver and several replicated slaves (MySQL). How to implement this functionality: read-only request can be passed to any DB (masterorslave), but any write request with following read requests must be sended tomasteronly (to avoid synchronization lag). This is an example of vertical partitioning. I am trying to find out how to implement this with SA too. Quite an offen-used scheme. Strange that no one has replied this thread yet. SA even has support for sharding (which is more complex than vertical partitioning IMHO) so there certainly should be the way for 1-master-N- slaves scheme. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unnecessary selects when cascade=all, delete-orphane and composite keys
I am building a grading system for students and got unexpected performance problems. I am using composite key for marks, which refer to students and subjects. When I am creating a mark (for student_1 and subject_1), unnecessary select operations are performed (select all marks for student_1 and select all marks for subject_1). Why these selects are generated and how to avoid them? The SQL issue looks like the marks collections on Student and Subject issuing a lazyload for their full collection of Mark items before the backref event appends the Mark object to each of them, i.e. the event that occurs when you issue mark.student = subject_1. Ordinary collections currently don't handle being present in a partial state, so in order for an append to occur, they load their contents. As a workaround, you can use lazy=dynamic relations for the collections, which is a special relation that can handle append operations without the full collection being available. lazy=noload would work as well but then you couldn't read from your collections. A future release may look into merging some of the dynamic relation behavior into an ordinary un-loaded collection so that this workaround would not be needed. Actually this might not be a bad idea for 0.4.2, so ive added ticket #871. Thanks a lot for your reply! Setting the relation to be lazy=dynamic really eliminated unnecessary selects when creating Mark. Making a default relation a bit dynamic is a great idea! There is problem when relation is both lazy=dynamic and cascade=all, delete-orphan: When parent (Subject or Student) object is deleted, its children (Marks) are not deleted. When relation is not dynamic, children are deleted correctly. Is this a proper behavior? Thanks for the attention. P.S. Congratulations to developers and users of SA with version 0.4.1! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Unnecessary selects when cascade=all, delete-orphane and composite keys
Hello all,
I am building a grading system for students and got unexpected
performance problems. I am using composite key for marks, which refer
to students and subjects.
When I am creating a mark (for student_1 and subject_1), unnecessary
select operations are performed (select all marks for student_1 and
select all marks for subject_1).
Why these selects are generated and how to avoid them?
Thanks for the attention.
Here is the sample code:
mapper(Student, table_students, properties={
'marks': relation(Mark, cascade='all, delete-orphan',
backref='student')
})
mapper(Subject, table_subjects, properties={
'marks': relation(Mark, cascade='all, delete-orphan',
backref='subject')
})
mapper(Mark, table_marks)
[... skip ...]
mark = Mark()
s.save(mark)
mark.student = student_1
mark.subject = subject_1
mark.value = 5
s.flush()
Here is SQL output (shortened a bit for ease of reading):
SELECT * FROM marks WHERE 1 = marks.student_id-
unnecessary select
SELECT * FROM marks WHERE 1 = marks.subject_id -
unnecessary select
BEGIN
INSERT INTO marks (subject_id, student_id, value) VALUES (1, 1, 5)
COMMIT
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SA does implicit cascading
Hello, here is a sample:
children_table = Table('children', metadata,
Column('id', Integer, primary_key=True))
child2group_table = Table('child2group', metadata,
Column('child_id', Integer,
ForeignKey('children.id'), nullable=False),
Column('group_id', Integer,
ForeignKey('groups.id'), nullable=False))
groups_table = Table('groups', metadata,
Column('id', Integer, primary_key=True))
mapper(Child, children_table,
properties={ 'groups':relation(Group,
secondary=child2group_table) })
mapper(Group, groups_table, properties={
'children':relation(Child, secondary=child2group_table) })
Speaking English, this means there are groups and children, and child
can belong to some groups.
When I issue this:
group = session.query(Group).get(2)
session.delete(group)
session.flush()
SA does this:
2007-09-03 11:40:25,915 INFO sqlalchemy.engine.base.Engine.0x..8c
BEGIN
2007-09-03 11:40:25,918 INFO sqlalchemy.engine.base.Engine.0x..8c
DELETE FROM child2group WHERE child2group.child_id = ? AND
child2group.group_id = ?
2007-09-03 11:40:25,918 INFO sqlalchemy.engine.base.Engine.0x..8c [[3,
2], [4, 2]]
2007-09-03 11:40:25,919 INFO sqlalchemy.engine.base.Engine.0x..8c
DELETE FROM groups WHERE groups.id = ?
2007-09-03 11:40:25,920 INFO sqlalchemy.engine.base.Engine.0x..8c [2]
2007-09-03 11:40:25,921 INFO sqlalchemy.engine.base.Engine.0x..8c
COMMIT
Well, I dont mind, because this is what I really wanted to have.
Please, explain, why does this happen? I thought only group item would
be deleted and I would get orphaned records in child2group table.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Consistency with DB while modifying metadata
t = Table('mytable', meta,
Column(...)
)
someothermeta = MetaData()
t2 = Table('mytable', someothermetadata, autoload=True,
autoload_with=connection)
assert t.compare(t2)
I believe this should be done somehow automatically. Because everyone
needs this. There should be two separate disjoint options:
1) Autoload, when working with previously created database for rapid
start. Columns arent specified at all in Python code (SQLAlchemy).
2) Tables are specified in the code. Database tables might already
exist and might not. And when issuing create_all(), all the situations
should be handled correctly:
2a) If tables exist in DB and match Python-defined, ok.
2b) If tables do not exist in DB they are created, ok.
2c) If tables exist in DB and there is mismatch with Python-defined,
an exception is raised.
If feel this is kind of natural. Though, I am not an expert in DB or
SQLAlchemy.
but why not just use autoload=True across the board in the first
place and eliminate the chance of any errors ?
1) I dont know if tables exist. I might need to create them.
2) When they exist, autoloading them might cause inconsistency with
SQLAlchemy-defined tables in sources. This is exactly what I am trying
to avoid.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Consistency with DB while modifying metadata
Hey, I believe there is a common approach to the situation, but I just dont know it. Let say, I have some tables created in the DB using SQLAlchemy. Then I modify Python code, which describes the table (add a column, remove another column,...). What is the common way to handle this situation? I guess it would be good to have an exception raised when there is a mismatch between DB tables and Python-defined (using SQLAlchemy). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Consistency with DB while modifying metadata
On 24 июл, 17:34, svilen [EMAIL PROTECTED] wrote: On Tuesday 24 July 2007 16:22:43 Anton V. Belyaev wrote: Hey, I believe there is a common approach to the situation, but I just dont know it. Let say, I have some tables created in the DB using SQLAlchemy. Then I modify Python code, which describes the table (add a column, remove another column,...). What is the common way to handle this situation? I guess it would be good to have an exception raised when there is a mismatch between DB tables and Python-defined (using SQLAlchemy). Very soon i'll be in your situation (with hundreds of tables), so i'm very interested if something comes up. it's in the todo list of dbcook. my idea so far is: - automaticaly reverse engineer i.e. autoload the available db-structure into some metadata. - create another metadata as of current code - compare the 2 metadatas, and based on some rules - ??? - alter/migrate the DB into the new shape. This has to be as automatic as possible, leaving only certain - if any - decisions to the user. Assuming that the main decision - to upgrade or not to upgrade - is taken positive, and any locks etc explicit access is obtained. svil Of course db modification is hard. It cant be done completely automatically. For now I would like SQLAlchemy just to signal somehow when its definitions are different from already existing db tables. When I do create_all() it checks anyway tables properties, but doesnt let me know when there is mismatch. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
