[sqlalchemy] Re: In a many to many relationship how to access some properties of that relationship
No, the association proxy would be used if you want to let users directly access movies or vice versa. To get the score, you can define a mapper for movie_vote table and define relationships between it and users and movies. Barry - Original Message From: Jason [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Tuesday, October 23, 2007 7:10:15 PM Subject: [sqlalchemy] In a many to many relationship how to access some properties of that relationship Greetings, for my model I have this: user_table=Table(users, metadata, Column(id, Integer, primary_key=True), Column(gender, Unicode), Column(age, Integer, ForeignKey(ages.id)), Column(occupation, Integer, ForeignKey(occupations.id)), Column(zipCode, Integer(5)) ) movie_table=Table(movies, metadata, Column(id, Integer, primary_key=True), Column(title, Unicode) ) movie_vote_table=Table(movie_votes, metadata, Column(id, Integer, primary_key=True), Column(user, Integer, ForeignKey(users.id)), Column(movie, Integer, ForeignKey(movies.id)), Column(score, Float) ) I've of course left out some of the other tables that weren't needed for this question so I of course a many to many relationship between the users and the movies they have rated. but the only problem is how do I access that score? It would be cool to have something like User.movies[0].score, User.movies[1].score .. etc. is this something I would want to use the association proxy for? -Jason __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --~--~-~--~~~---~--~~ 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] Read-only Sessions
Greetings Alchemists, I'm using Alchemy for a Pylons application and I just migrated to 0.4. My application has a read only mode and even though I do all the relevant checks all over the place, I know I'm going to forget a critical spot one of there days so I used to do: def abort_ro(): log.error(...) abort(403) if c.site_readonly: model.ctx.current.flush = abort_ro I'd like to do something like this with Alchemy 0.4 but I'm a bit lost. I use a scoped session like this: db_sess = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config['pylons.g'].sa_engine)) So the session object is reused all over the place and overwriting one of its methods is not a good idea. What would be my best option to implement readonly mode? -- Yannick Gingras --~--~-~--~~~---~--~~ 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] Two MSSQL databases on one engine?
Hi Alchemists, Do have to define two engines to access two databases on the same database server? I am using MSSQL and pylons with the following: sqlalchemy.default.url = mssql://login:[EMAIL PROTECTED]:1272/database1 I would like to autoload a table that is on that same MSSQL server, but in database2 not database1. Is there a way to do this without defining another engine? It looks like Table will take a schema in the table name argument but not another database name. Thank you, Scott --~--~-~--~~~---~--~~ 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: Read-only Sessions
On Oct 25, 2007, at 12:03 PM, Yannick Gingras wrote: Greetings Alchemists, I'm using Alchemy for a Pylons application and I just migrated to 0.4. My application has a read only mode and even though I do all the relevant checks all over the place, I know I'm going to forget a critical spot one of there days so I used to do: def abort_ro(): log.error(...) abort(403) if c.site_readonly: model.ctx.current.flush = abort_ro I'd like to do something like this with Alchemy 0.4 but I'm a bit lost. I use a scoped session like this: db_sess = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config ['pylons.g'].sa_engine)) So the session object is reused all over the place and overwriting one of its methods is not a good idea. What would be my best option to implement readonly mode? the design of scoped_session is not much different from SessionContext. if you were setting ctx.current.flush to something for every new session, the approach here would be the same (Session ().flush = abort_ro). if you were only setting ctx.current.flush to abort_ro() at the module level, then your 0.3 approach wasn't working either (since 'current' is a functional property) ;). --~--~-~--~~~---~--~~ 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: Read-only Sessions
Michael Bayer [EMAIL PROTECTED] writes: If you were setting ctx.current.flush to something for every new session, the approach here would be the same (Session ().flush = abort_ro). if you were only setting ctx.current.flush to abort_ro() at the module level, then your 0.3 approach wasn't working either (since 'current' is a functional property) ;). Oops! Yeah I was setting it on a per-session basis. There is a global read-only mode but there is also a per-request read-only mode. I use it to implement soft-bans: a banned user can read the site but he can't make changes. What do you recommend for a per-session read-only mode? -- Yannick Gingras --~--~-~--~~~---~--~~ 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: Two MSSQL databases on one engine?
Hi, Do have to define two engines to access two databases on the same database server? With MSSQL, at the moment you do. People have suggested changes to workaround this (using dbname.schema.table), but nothing has been implemented as yet. Paul --~--~-~--~~~---~--~~ 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: polymorphic question
hi, i'm back to the cane field... do your ABC tests all use select_mapper ? ticket 795 revealed that totally basic ABC loading was broken if you're using secondary loads of the remaining attributes (which is the default behavior when you dont specify select_mapper). u mean mapper's select_table=..? it's allways used because of polymorphism. today i tried those ABC things, found 2 issues: - r3449 introduces some memory leak - r3646 introduces some forever-recursion, goes like: File other/expression.py, line 355, in module p2 = session.query( Person).filter_by( name= 'pesho').first() File /home/az/src/hor-trunk/sqlalchemy/orm/query.py, line 595, in first ret = list(self[0:1]) File /home/az/src/hor-trunk/sqlalchemy/orm/query.py, line 620, in __iter__ context = self._compile_context() File /home/az/src/hor-trunk/sqlalchemy/orm/query.py, line 873, in _compile_context value.setup(context) File /home/az/src/hor-trunk/sqlalchemy/orm/interfaces.py, line 483, in setup self._get_context_strategy(querycontext).setup_query(querycontext, **kwargs) File /home/az/src/hor-trunk/sqlalchemy/orm/strategies.py, line 553, in setup_query value.setup(context, parentclauses=clauses, parentmapper=self.select_mapper) File /home/az/src/hor-trunk/sqlalchemy/orm/interfaces.py, line 483, in setup self._get_context_strategy(querycontext).setup_query(querycontext, **kwargs) File /home/az/src/hor-trunk/sqlalchemy/orm/strategies.py, line 553, in setup_query value.setup(context, parentclauses=clauses, parentmapper=self.select_mapper) ... last two repeated ... more details tomorrow ciao svilen --~--~-~--~~~---~--~~ 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: Problem when slicing a relation list
Here is a test case for the bug. The bug only manifests itself if the transaction that slices the list also modifies all the remaining objects in the list. Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Wednesday, October 24, 2007 5:01:43 PM Subject: [sqlalchemy] Re: Problem when slicing a relation list hey Barry - again, can you please attach a working test case for this one ? attached is mine, which tests this exact operation for four diferent kinds of relation()s - one-to-many and many to many, with and without delete cascade on the relation. passes for 0.3 (including 0.3.10) and 0.4. -Inline Attachment Follows- from sqlalchemy import * from sqlalchemy.orm import * def test(m2m=False, cascade=False, useclear=False): engine = create_engine('sqlite://', echo=True) meta = MetaData(engine) a = Table('a', meta, Column('id', Integer, primary_key=True), Column('foo', String(30))) if m2m: b = Table('b', meta, Column('id', Integer, primary_key=True), Column('foo', String(30))) else: b = Table('b', meta, Column('id', Integer, primary_key=True), Column('foo', String(30)), Column('a_id', Integer, ForeignKey('a.id'))) if m2m: atob = Table('atob', meta, Column('a_id', Integer, ForeignKey('a.id')), Column('b_id', Integer, ForeignKey('b.id')), ) else: atob = None class A(object): def __init__(self, foo): self.foo = foo class B(object): def __init__(self, foo): self.foo = foo if cascade: use_cascade = all, delete-orphan else: use_cascade = save-update mapper(A, a, properties={ 'bs':relation(B, secondary=atob, cascade=use_cascade) }) mapper(B, b) meta.create_all() a1 = A('a1') a1.bs.append(B('b1')) a1.bs.append(B('b2')) a1.bs.append(B('b3')) sess = create_session() sess.save(a1) sess.flush() if m2m: assert atob.count().scalar() == 3 else: assert b.count(b.c.a_id == None).scalar() == 0 assert b.count().scalar() == 3 if useclear: sess.clear() a1 = sess.query(A).get(a1.id) assert len(a1.bs) == 3 a1.bs = a1.bs[1:] sess.flush() if m2m: assert atob.count().scalar() == 2 else: assert b.count(b.c.a_id != None).scalar() == 2 if cascade: assert b.count().scalar() == 2 else: assert b.count().scalar() == 3 if useclear: sess.clear() a1 = sess.query(A).get(a1.id) assert len(a1.bs) == 2 for m2m in (True, False): for cascade in (True, False): for useclear in (True, False): test(m2m, cascade, useclear) On Oct 24, 2007, at 4:18 PM, Barry Hart wrote: I found a problem in SqlAlchemy 0.3.10 this week: slicing a list property caused the whole association list to be deleted. For example, suppose I want to remove the first 3 items from a list of related items: my_obj.related = my_obj.related[3:] When these changes were saved , my_obj.related was empty the next time it loaded. This code, however, worked correctly: for i in range(0, 3): del my_obj.related[0] Barry __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- from sqlalchemy import * from sqlalchemy.ext.assignmapper import assign_mapper import sqlalchemy from sqlalchemy.ext import activemapper, sessioncontext import datetime engine = None def create_engine(): global engine engine = sqlalchemy.create_engine('sqlite://') #engine = sqlalchemy.create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/testdb') metadata.connect(engine) def create_session(): return sqlalchemy.create_session(bind_to=engine) def fuzzy_search(column, value): Case insensitive search allowing partial string matches. return func.lower(column).like('%%%s%%' % value.lower()) metadata = activemapper.metadata create_engine() session = activemapper.Objectstore(create_session) activemapper.objectstore = session g_count = 10 ## # Classes
[sqlalchemy] Re: Problem when slicing a relation list
On Oct 25, 2007, at 3:58 PM, Barry Hart wrote: Here is a test case for the bug. The bug only manifests itself if the transaction that slices the list also modifies all the remaining objects in the list. hi barry - nice job again. we've narrowed down the specific mechanism in this in ticket #834. 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: Problem when slicing a relation list
Credit for this one goes to my colleague Greg Hunt. All I did was tell him to write a test case. :-) Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Thursday, October 25, 2007 6:16:24 PM Subject: [sqlalchemy] Re: Problem when slicing a relation list On Oct 25, 2007, at 3:58 PM, Barry Hart wrote: Here is a test case for the bug. The bug only manifests itself if the transaction that slices the list also modifies all the remaining objects in the list. hi barry - nice job again. we've narrowed down the specific mechanism in this in ticket #834. thanks ! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---