[sqlalchemy] Designing a permission system
Hi, I'm designing a permission system to our messaging platform. The platform has different channels which contains messages. Every different user can have different permissions on a channel. The permissions are create, modify, delete. With these permissions, we can define that some users can create messages in a specific channel, and others can create, modify and delete them, etc. How would you design a database for this, and how would you implement it with SA? I'm thinking about making a many-to-many table which includes the permissions, but i have no idea on how to implement this in SA. Database schema: Channel = - Id (integer) - Title (unicode) - Messages (many-to-many) Channel_Permissions = - User Id (integer foreignkey) - Channel Id (integer foreignkey) - create (boolean) - modify (boolean) - delete (boolean) With simple many-to-many table, SA makes a Python list of the links. Is there a way to get these extra boolean variables somehow and still use the automatic list? Thanks and regards, K --~--~-~--~~~---~--~~ 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: Designing a permission system
look at http://code.google.com/p/spiff/ - SpiffGuard On Apr 10, 11:36 am, Kaali [EMAIL PROTECTED] wrote: Hi, I'm designing a permission system to our messaging platform. The platform has different channels which contains messages. Every different user can have different permissions on a channel. The permissions are create, modify, delete. With these permissions, we can define that some users can create messages in a specific channel, and others can create, modify and delete them, etc. How would you design a database for this, and how would you implement it with SA? I'm thinking about making a many-to-many table which includes the permissions, but i have no idea on how to implement this in SA. Database schema: Channel = - Id (integer) - Title (unicode) - Messages (many-to-many) Channel_Permissions = - User Id (integer foreignkey) - Channel Id (integer foreignkey) - create (boolean) - modify (boolean) - delete (boolean) With simple many-to-many table, SA makes a Python list of the links. Is there a way to get these extra boolean variables somehow and still use the automatic list? Thanks and regards, K --~--~-~--~~~---~--~~ 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: Putting Session.flush in a seperate thread
the elegant way would be to not worry about the thread scope of a connection and just use connection pooling normally. pull out a connection when needed, close it when complete (which is really just a return to the connection pool). keep the scope of those two operations local to a single thread. This is exactly what I'd like to do. But I have no clue to assign a new connection to a session in a new thread. This is what I'm doing. class FlushThread(Thread): def __init__(self, session): super(FlushThread, self).__init__() self.session = session self.start() def run(self): print self.session #engine.connect() #self.session.bind_to(engine) self.session.flush() --~--~-~--~~~---~--~~ 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: SQLite and decimal.Decimal
It would be great. Thank you. André On 7 abr, 12:57, Michael Bayer [EMAIL PROTECTED] wrote: the thing is, we have support for 6 different databases and postgres is the *only* one where its DBAPI implementation decides to use Decimal for numeric types. the rest return just floats. that means, people who have worked with databases other than postgres will be totally surprised to plug in SQLAlchemy one day and all the sudden they arent getting their expected float types back. So i dont think one DBAPI should dictate the behavior for all DBAPIs, and its definitely not a bug. its a feature request, asking for a generic numeric type that is guaranteed to return decimal.Decimal objects regardless of underlying DBAPI. So, I would rather add a new type called DecimalType that creates columns using NUMERIC semantics but explicitly returns decimal.Decimal objects. On Apr 7, 2007, at 9:16 AM, [EMAIL PROTECTED] wrote: Hi, I'm using SQLite in tests and there is a problem when using decimal.Decimal with sqlalchemy's Numeric type: SQLError: (InterfaceError) Error binding parameter 5 - probably unsupported type. This is not a new issue, a similar one was posted in http://groups.google.com/group/sqlalchemy/browse_thread/thread/ 300b757014c7d375/ad024f5365ab2eea It looks like a bug in sqlalchemy, but I'd rather discuss it here before creating a ticket. What I'd really like is that the Numeric field could work with decimal.Decimal in SQLite as it does with postgres, without any other external hack. Regards, André --~--~-~--~~~---~--~~ 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: (solved) Automatic generation of changelog
Hi all,
I have this working to my liking and as promised, here's the code:
It's not complete since I'm omitting some of my model and just showing
you the relevant parts.
This is my model:
verkefni = Table(verkefni, metadata,
Column(verkefni, Integer, primary_key=True),
Column(skrad, DateTime, nullable=False, default=func.now()),
Column(sidast_breytt, DateTime, nullable=False,
default=func.current_timestamp(), onupdate=func.current_timestamp()),
Column(skrad_af, Unicode(20), ForeignKey(notendur.notandi),
nullable=False),
Column(deadline, Date),
Column(titill, Unicode, nullable=False),
Column(lysing, Unicode, nullable=False),
Column(mikilvaegi, Integer, ForeignKey(mikilvaegi.mikilvaegi),
nullable=False, default=40),
Column(forgangur, Integer, nullable=False, default=0),
Column(framvinda, Integer, nullable=False, default=0),
Column(fasi, Integer, ForeignKey(fasar.fasi), nullable=False,
default=0),
Column(abyrgdarmadur, Unicode(20),
ForeignKey(notendur.notandi), nullable=False),
Column(cc, Unicode),
Column(verknumer, Unicode(20)),
Column(tengilidur, Unicode(60)),
Column(bidur_eftir, Unicode)
)
class Verkefni(base_model):
def changelog_entry(self, notandi, skyring):
entry = Atburdur(self, notandi, skyring)
def changelog(fld, old, new):
entry.create_item(fld, old, new)
instance = self
# Scalar fields
for fld in ('deadline', 'titill', 'lysing', 'forgangur',
'framvinda', 'mikilvaegi',
'fasi', 'abyrgdarmadur', 'cc', 'verknumer',
'tengilidur', 'bidur_eftir'):
history = getattr(Verkefni, fld).get_history(instance, passive=True)
if history.is_modified():
old, new = history.deleted_items()[0], history.added_items()[0]
# Need to check this cause SA considers 10 (int) -
10L (long) a change, we don't
if old != new:
changelog(fld, old, new)
# Fyrirtaeki
history = Verkefni.fyrirtaeki_tengsl.get_history(instance, passive=True)
for i in history.added_items():
changelog('fyrirtaeki_add', None, i.fyrirtaeki.fyrirtaeki)
for i in history.deleted_items():
changelog('fyrirtaeki_remove', i.fyrirtaeki.fyrirtaeki, None)
# Flokkar
history = Verkefni.flokkar.get_history(instance, passive=True)
for i in history.added_items():
changelog('flokkur_add', None, i.flokkur)
for i in history.deleted_items():
changelog('flokkur_remove', i.flokkur, None)
# Framkvaemd
history = Verkefni.framkvaemd.get_history(instance, passive=True)
for i in history.added_items():
changelog('framkvaemd_add', None, i.notandi.notandi)
for i in history.deleted_items():
changelog('framkvaemd_remove', i.notandi.notandi, None)
# Dependencies
history = Verkefni.depends_on.get_history(instance, passive=True)
for i in history.added_items():
changelog('dep_first_add', None, i.verkefni)
for i in history.deleted_items():
changelog('dep_first_remove', i.verkefni, None)
history = Verkefni.depend_on_me.get_history(instance, passive=True)
for i in history.added_items():
changelog('dep_then_add', None, i.verkefni)
for i in history.deleted_items():
changelog('dep_then_remove', i.verkefni, None)
# Related
history = Verkefni._relatives_a.get_history(instance, passive=False)
for i in history.added_items():
changelog('rel_add', None, i.verkefni)
for i in history.deleted_items():
changelog('rel_remove', i.verkefni, None)
history = Verkefni._relatives_b.get_history(instance, passive=False)
for i in history.added_items():
changelog('rel_add', None, i.verkefni)
for i in history.deleted_items():
changelog('rel_remove', i.verkefni, None)
atburdaskra = Table(atburdaskra, metadata,
Column(atburdur, Integer, primary_key=True),
Column(verkefni, Integer, ForeignKey(verkefni.verkefni),
nullable=False),
Column(notandi, Unicode(20), ForeignKey(notendur.notandi),
nullable=False),
Column(dags, DateTime, nullable=False, default=func.now()),
Column(skyring, Unicode)
)
class Atburdur(base_model):
def __init__(self, verkefni, notandi, skyring=None):
self.verkefni = verkefni
self.notandi = notandi
self.skyring = skyring
def create_item(self, svid, gamalt=None, nytt=None, texti=None):
nextid = max([0] + [item.item for item in self.items])+1
return AtburdurItem(self, nextid, svid, gamalt, nytt, texti)
assign_mapper(ctx, Atburdur, atburdaskra, properties={
'_verkefni': atburdaskra.c.verkefni,
'verkefni': relation(Verkefni, backref=backref(atburdir,
cascade=all, delete-orphan, order_by=atburdaskra.c.dags)),
[sqlalchemy] Re: Opinion on correct use of Sqlalchemy
Michael Bayer wrote: On Apr 9, 2007, at 10:06 PM, Huy Do wrote: Michael Bayer wrote: Particularly for your query you are doing an eager load between asset and location yet a lot of your query criterion depends upon location, so in that sense yes you have to use custom SQL, since query() will never involve eager loaded joins in the query criterion. Hi Michael, Everything you say makes perfect sense for 1:N relationships, but in my case, and with alot of other cases where I need the order by or the criteria/filter on the joined table, it's a 1:1. In these cases I'm not sure why SA can't generate the same type of SQL statement that I am above. It would make perfect sense for it to. I understand the eagerload problem with a list of child objects but with 1:1 relations I think the query interface should be querying in the same way that my manual SQL is. sorry, i just dont think that the loader strategy should *ever* have any effect on the primary results...otherwise loader strategies become intertwined with querying, and mappers become brittle since you can no longer change eager/lazy loads without breaking the results of your query. additionally for the eager loaders to guess when they should create non-aliased criterion and when they should create aliased criterion would be magical and complicated, and render improvements to eager loading impossible since people would be structutring their queries off of a now expected behavior which could then never change. it also implies that a lazy-loading mapper setup would break when eager loading is enabled via options, since the eager loader expects that its going to be used in query criterion, doesnt apply its aliases, and then changes the results of the query. so it definitely breaks a lot of widely accepted behavior. I don't think I understand the internals of SA enough to appreciate this response; i.e it went way over my head :-) I'm going to stick to the select-instances way for all my query ORM needs. I like it. (I guess I was after some DRY with contains_eager() and relation() having to be specified twice). I'll use the mapper/relation for CRUD stuff only. Thanks for your responses Michael, its very much appreciated. It's getting harder to keep up with SA these days. It's moving forward so quickly. Regards, Huy there are tools to join eager loads with query criterion which are contains_eager() and instances() or explcit select() objects passed to query.select(). id favor adding new mapper options to generate criterion that is simlar to that of the eager loader (such as, using contains_eager() with a Query-compiled query will add the columns in and the join for you) but loader strategies by default will never purposefully inject themselves into application query criterion. --~--~-~--~~~---~--~~ 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: About threadlocal strategy
Koen Bok wrote: I'm trying to wrap my head around the threadlocal plugin to decide if we need it for our app. We are building a GUI point of sale system. Until now I have done everything in sessions, basically in one global shared session. I have read all of the documentation about the threadlocal strategy but I still cannot decide if we should use it. Basically we hoped that someone could compare the threadlocal strategy to the default strategy and some examples in what kind of apps you would use it. Hi Koen, I was in your position with the global shared session thing. Then I realised that's what threadlocal is, so I switched and just used the threadlocal plugin. Its all behind a facade module, so it's transparent to my application whether or not I'm using threadlocal or specific sessions. Huy --~--~-~--~~~---~--~~ 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: Putting Session.flush in a seperate thread
just deal with the session, and bind it to an engine. saying engine.connect() does nothing down there since you arent holding onto anything. the engine itself is stateless with regards to connections (unless you use the threadlocal strategywhich you shouldnt). On Apr 10, 2007, at 5:16 AM, Koen Bok wrote: the elegant way would be to not worry about the thread scope of a connection and just use connection pooling normally. pull out a connection when needed, close it when complete (which is really just a return to the connection pool). keep the scope of those two operations local to a single thread. This is exactly what I'd like to do. But I have no clue to assign a new connection to a session in a new thread. This is what I'm doing. class FlushThread(Thread): def __init__(self, session): super(FlushThread, self).__init__() self.session = session self.start() def run(self): print self.session #engine.connect() #self.session.bind_to(engine) self.session.flush() --~--~-~--~~~---~--~~ 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: About threadlocal strategy
On Apr 10, 2007, at 5:33 AM, Koen Bok wrote: I'm trying to wrap my head around the threadlocal plugin to decide if we need it for our app. you dont. dont use it. if it requires any kind of head wrapping, then thats why i ripped it out of the core...youre better off designing your own system, which then youll understand perfectly. We are building a GUI point of sale system. Until now I have done everything in sessions, basically in one global shared session. use one session per thread. Basically we hoped that someone could compare the threadlocal strategy to the default strategy and some examples in what kind of apps you would use it. the threadlocal strategy is only remotely useful if you are working with explicit Connection and possibly Transaction objects, and its to support replacing this pattern: def do_function(conn): conn.execute(foo) def do_another_function(conn): conn.execute(bar) conn = engine.connect() do_function(conn) do_another_function(conn) conn.close() with this: def do_function(): engine.contextual_connect().execute(foo) def do_another_function(): engine.contextual_connect().execute(bar) conn = engine.contextual_connect() do_function() do_another_function() conn.close() --~--~-~--~~~---~--~~ 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: problems with threadlocal strategy and ResultProxy
On Apr 10, 2007, at 7:44 AM, Manlio Perillo wrote: Yes. From the documentation it is not clear if this is allowed. the word threadlocal means that a resource is tied to the current thread. therefore, if you take resources which expect this behavior and then send them off to three different threads during their lifecycle, they will not have consistent access to the resources they require. threadlocal strategies are not compatible with sharing those objects between threads - they are designed to allow global resources that are automatically held within the scope of a thread. with twisted, nothing except your initial engine (and metadata, Tables) should be held in a global scope, everything else (sessions, connections, transactions) should be declared and torn down within a local scope. --~--~-~--~~~---~--~~ 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: [PATCH] Filtered one_to_many relationships (Experimental)
On 3/31/07, Michael Bayer [EMAIL PROTECTED] wrote:
On Mar 31, 2007, at 1:17 PM, Gaetan de Menten wrote:
That's approximately what I did in my patch with the new params
keyword argument, except I only implemented the set operation, not
the add operation on the params. Anyway, what can/should I do to get
this included? Do you have any advice/pointers on how to do the same
for eager attributes? (or will you implement it yourself?)
im totally into a series of engine/execution patches/refactorings
right now, so for properties that have lazy=False, there is still a
LazyLoader strategy there...you should just call
property._get_strategy(LazyLoader) in all cases to get at it.
In case anybody is interested, here is my patch slightly modified with
what you suggest above. Now it works wonders for both lazy and eager
relationships. There is something ugly about it though: imports. I
have to import the LazyLoader class from the orm.strategies module,
but that module imports query, so what I did is import the LazyLoader
class inside the from_attr method to avoid a circular import
problem.
By the way, should I create a ticket for this?
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Index: orm/query.py
===
--- orm/query.py(revision 2493)
+++ orm/query.py(working copy)
@@ -8,6 +8,7 @@
from sqlalchemy.orm import mapper, class_mapper
from sqlalchemy.orm.interfaces import OperationContext, SynonymProperty
+
__all__ = ['Query', 'QueryContext', 'SelectionContext']
class Query(object):
@@ -42,12 +43,36 @@
self._distinct = kwargs.pop('distinct', False)
self._offset = kwargs.pop('offset', None)
self._limit = kwargs.pop('limit', None)
-self._criterion = None
+self._criterion = kwargs.pop('criterion', None)
+self._params = kwargs.pop('params', {})
self._joinpoint = self.mapper
self._from_obj = [self.table]
for opt in util.flatten_iterator(self.with_options):
opt.process_query(self)
+
+def from_attr(cls, instance, attr_name):
+from sqlalchemy.orm.strategies import LazyLoader
+
+prop = instance.mapper.props[attr_name]
+loader = prop._get_strategy(LazyLoader)
+
+# the following code is taken from strategies.py
+# this gets the values of the columns referenced by the property
+# for this specific instance
+params = {}
+allparams = True
+for col, bind in loader.lazybinds.iteritems():
+params[bind.key] = loader.parent.get_attr_by_column(instance, col)
+if params[bind.key] is None:
+allparams = False
+break
+
+if not allparams:
+return None
+
+return Query(prop.mapper, criterion=loader.lazywhere, params=params)
+from_attr = classmethod(from_attr)
def _clone(self):
q = Query.__new__(Query)
@@ -71,6 +96,7 @@
q._from_obj = list(self._from_obj)
q._joinpoint = self._joinpoint
q._criterion = self._criterion
+q._params = self._params
return q
def _get_session(self):
@@ -694,8 +720,10 @@
method, which takes the executed statement's ResultProxy
directly.
-
-result = self.session.execute(self.mapper, clauseelement,
params=params)
+final_params = self._params.copy()
+if params is not None:
+final_params.update(params)
+result = self.session.execute(self.mapper, clauseelement,
params=final_params)
try:
return self.instances(result, **kwargs)
finally:
[sqlalchemy] Aliasing within functions for eager loads
I've noticed that if you specify a primaryjoin criteria for a mapper relationship, SA automatically aliases the table/column names when doing an eager load of that relationship. But this doesn't seem to work if within that primary join, a column is referenced inside of a .func; SA leaves the naked name during the eager load, which then of course errors. Is this supposed to work? Thanks, Rick --~--~-~--~~~---~--~~ 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: [PATCH] Filtered one_to_many relationships (Experimental)
can u give some exampe, how this is supposed to be used (finaly)? as relation and/or directly as Query() On Tuesday 10 April 2007 17:22:45 Gaetan de Menten wrote: On 3/31/07, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 31, 2007, at 1:17 PM, Gaetan de Menten wrote: That's approximately what I did in my patch with the new params keyword argument, except I only implemented the set operation, not the add operation on the params. Anyway, what can/should I do to get this included? Do you have any advice/pointers on how to do the same for eager attributes? (or will you implement it yourself?) im totally into a series of engine/execution patches/refactorings right now, so for properties that have lazy=False, there is still a LazyLoader strategy there...you should just call property._get_strategy(LazyLoader) in all cases to get at it. In case anybody is interested, here is my patch slightly modified with what you suggest above. Now it works wonders for both lazy and eager relationships. There is something ugly about it though: imports. I have to import the LazyLoader class from the orm.strategies module, but that module imports query, so what I did is import the LazyLoader class inside the from_attr method to avoid a circular import problem. By the way, should I create a ticket for this? --~--~-~--~~~---~--~~ 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: [PATCH] Filtered one_to_many relationships (Experimental)
On 4/10/07, svilen [EMAIL PROTECTED] wrote:
can u give some exampe, how this is supposed to be used (finaly)?
as relation and/or directly as Query()
Attached is a simple example. The setup is done using Elixir but the
actual Query.from_attr usage would be the same with plain SA. If you
really need a plain SA example, just ask.
On Tuesday 10 April 2007 17:22:45 Gaetan de Menten wrote:
On 3/31/07, Michael Bayer [EMAIL PROTECTED] wrote:
On Mar 31, 2007, at 1:17 PM, Gaetan de Menten wrote:
That's approximately what I did in my patch with the new
params keyword argument, except I only implemented the set
operation, not the add operation on the params. Anyway, what
can/should I do to get this included? Do you have any
advice/pointers on how to do the same for eager attributes? (or
will you implement it yourself?)
im totally into a series of engine/execution patches/refactorings
right now, so for properties that have lazy=False, there is
still a LazyLoader strategy there...you should just call
property._get_strategy(LazyLoader) in all cases to get at it.
In case anybody is interested, here is my patch slightly modified
with what you suggest above. Now it works wonders for both lazy and
eager relationships. There is something ugly about it though:
imports. I have to import the LazyLoader class from the
orm.strategies module, but that module imports query, so what I did
is import the LazyLoader class inside the from_attr method to avoid
a circular import problem.
By the way, should I create a ticket for this?
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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 elixir import *
class A(Entity):
has_field('name', String(30))
has_many('b', of_kind='B', lazy=False)
class B(Entity):
has_field('name', String(30))
has_field('extra', Integer)
belongs_to('a', of_kind='A')
metadata.connect('sqlite:///')
create_all()
a1 = A(name='a1')
b1 = B(name='b1', a=a1, extra=10)
b2 = B(name='b2', a=a1)
b3 = B(name='b3', a=a1, extra=5)
b4 = B(name='b4', a=a1, extra=4)
b5 = B(name='b5', a=a1, extra=7)
objectstore.flush()
objectstore.clear()
#metadata.engine.echo = True
a = A.get_by(name='a1')
print normal
q = Query.from_attr(a, 'b')
for b in q:
print b.name, b.extra
print filtered extra 8
for b in q.filter(B.c.extra 8):
print b.name, b.extra
print ordered by extra
for b in q.order_by(B.c.extra):
print b.name, b.extra
[sqlalchemy] just what does 'delete-orphan' bring us ?
Hello List,
I have two (related) questions hiding below:
The enclosed code makes mappers with (edit + re-run please)
. default cascade rules
. cascade='all'
. cascade='all, delete-orphan'
It tries to test
u = User(); e = Email(); u.append(e) ; sess.fluhs() # works always
session.delete(u) #
works if any cascade specified
# and *always* deletes Emails
# even *without* delete-orphan
Q1: delete-orphan doesn't change the behaviour here.
*but*
u = User() ; u.name = 'giorgos' ; sess.save(u) ; session.flush() # ok
e = Email()
e.user_id = u.user_id # uid = 1
sess.save(e) ; sess.flush()# fails with cascade=all, delete-
orphan
sess.delete(u) # deletes orphans even with only
cascade='all'
# tries (and fails, which
is ok) with no cascade rules (because
# nullalble = False)
Q2: only cascade = 'all' works. if we have delete-orphan, we cannot
create an object
and forcibly stuff the parent's ID in the relevant column.
if we have no cascade, it tries to set email.user_id = Null, which
fails.
The Code...
--
from sqlalchemy import *
db = create_engine('sqlite:///temp.db')
meta = BoundMetaData(db)
meta.engine.echo = True
user_table = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('name', String, nullable=False)
)
email_table = Table('emails', meta,
Column('email_id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey('users.user_id'),
nullable=False)
)
meta.drop_all()
user_table.create()
email_table.create()
class User(object):
pass
class Email(object):
pass
mapper(User, user_table
,properties={
'emails' : relation(Email
#,cascade='all'# WORKS
,cascade='all, delete-orphan' # FAILS
)
}
)
mapper(Email, email_table)
session = create_session()
if 0:
# works always
u = User() ;u.name = 'giorgos'
e = Email()
u.emails.append(e)
session.save(u)
# note: no need to save e !
session.flush()
# let's check deletions get cascaded
session.delete(u)
# deletes email
session.flush()
if 1:
# works with cascade = 'all', fails with cascade = 'all, delete-
orphan'
u = User() ; u.name = 'giorgos'
session.save(u)
session.flush()
print 'uid:',u.user_id
e = Email()
e.user_id = u.user_id
session.save(e)
session.flush() # fails if cascade='all, delete-orphan'
# let's check deletions get cascaded
session.delete(u)
# deletes email
session.flush() # fails if no cascade options set.
--~--~-~--~~~---~--~~
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: [PATCH] Filtered one_to_many relationships (Experimental)
On Apr 10, 2007, at 10:22 AM, Gaetan de Menten wrote: In case anybody is interested, here is my patch slightly modified with what you suggest above. Now it works wonders for both lazy and eager relationships. There is something ugly about it though: imports. I have to import the LazyLoader class from the orm.strategies module, but that module imports query, so what I did is import the LazyLoader class inside the from_attr method to avoid a circular import problem. importing inside a method is perfectly fine, ill try to see if theres a workaround but sometimes there really isnt... By the way, should I create a ticket for this? yes. otherwise ill completely forget about it. i might have to rush out some extra features this weekend to get 0.3.7 out, so this one might not be in until 0.3.8. --~--~-~--~~~---~--~~ 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: Aliasing within functions for eager loads
On Apr 10, 2007, at 11:36 AM, Rick Morrison wrote: I've noticed that if you specify a primaryjoin criteria for a mapper relationship, SA automatically aliases the table/column names when doing an eager load of that relationship. But this doesn't seem to work if within that primary join, a column is referenced inside of a .func; SA leaves the naked name during the eager load, which then of course errors. Is this supposed to work? funcs in primary joins eh ? nasty. ive no idea what would occur there or even how that would work, somake up a little test script for me and we'll see. if you want to try looking into the aliasing methodology, its probably looking hard for real columns to alias but nothing else. --~--~-~--~~~---~--~~ 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: [PATCH] Filtered one_to_many relationships (Experimental)
hm, why is from_attr a classmethod ? not very consistent with all the other generative methods ? can we have a regular generative method as well ? --~--~-~--~~~---~--~~ 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] pymssql and encoding - I can not get \x92 to be an '
Hello all - Unfortunately, we have a ms sql server at work. When I get tuples from the server they look like this: .. (55, 26, 'Small Business and Individual Chapter 11s - The NewCode \x92s Effect on Strategies', 'AUDIO'... with \x92 for apostrophe etc. I've tried putting every encoding in the create_engine statement, including ISO-8859-1 used by MS SQL, but the print statements always come out like: .. The NewCode?s Effect on .. I also tried passing the string to unicode(string, 'ISO-8859-1'), but this gives me: .. UnicodeEncodeError: 'ascii' codec can't encode character u'\x96' in position 48: ordinal not in range(128) .. Does anyone know about MSSQL or this encoding, or how to get apostrophes where \x92 is? Any help would be greatly appreciated. -Steve --~--~-~--~~~---~--~~ 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: pymssql and encoding - I can not get \x92 to be an '
You've got hi-bit characters in your data. MSSQL with pymssql will store
this, but not understand it.
You most likely are looking for a normal ASCII apostrophe (i.e. ord(39))
instead of the hi-bit version you've got.
to get it, try ${str}.replace(ord(146), ')-- that last bit is a
single quote (apostrophe) surrounded by double-quotes
Rick
On 4/10/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hello all -
Unfortunately, we have a ms sql server at work. When I get tuples
from the server they look like this:
.. (55, 26, 'Small Business and Individual Chapter 11s - The NewCode
\x92s Effect on Strategies', 'AUDIO'...
with \x92 for apostrophe etc. I've tried putting every encoding in
the create_engine statement, including ISO-8859-1 used by MS SQL, but
the print statements always come out like:
.. The NewCode?s Effect on ..
I also tried passing the string to unicode(string, 'ISO-8859-1'), but
this gives me:
.. UnicodeEncodeError: 'ascii' codec can't encode character u'\x96' in
position 48: ordinal not in range(128) ..
Does anyone know about MSSQL or this encoding, or how to get
apostrophes where \x92 is?
Any help would be greatly appreciated.
-Steve
--~--~-~--~~~---~--~~
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: pymssql and encoding - I can not get \x92 to be an '
Arghh, that last bit should be chr(146), not ord(146)
On 4/10/07, Rick Morrison [EMAIL PROTECTED] wrote:
You've got hi-bit characters in your data. MSSQL with pymssql will store
this, but not understand it.
You most likely are looking for a normal ASCII apostrophe (i.e. ord(39))
instead of the hi-bit version you've got.
to get it, try ${str}.replace(ord(146), ')-- that last bit is a
single quote (apostrophe) surrounded by double-quotes
Rick
On 4/10/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hello all -
Unfortunately, we have a ms sql server at work. When I get tuples
from the server they look like this:
.. (55, 26, 'Small Business and Individual Chapter 11s - The NewCode
\x92s Effect on Strategies', 'AUDIO'...
with \x92 for apostrophe etc. I've tried putting every encoding in
the create_engine statement, including ISO-8859-1 used by MS SQL, but
the print statements always come out like:
.. The NewCode?s Effect on ..
I also tried passing the string to unicode(string, 'ISO-8859-1'), but
this gives me:
.. UnicodeEncodeError: 'ascii' codec can't encode character u'\x96' in
position 48: ordinal not in range(128) ..
Does anyone know about MSSQL or this encoding, or how to get
apostrophes where \x92 is?
Any help would be greatly appreciated.
-Steve
--~--~-~--~~~---~--~~
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: 'PropertyLoader' object has no attribute 'strategy'
Some details I forgot to mention: I'm using: * SQLAlchemy 0.3.6 * Postgresql 7.3.4 * Linux RedHat kernel 2.4.20-8 Other important detail: looking at my log files, I noticed that the message: global name 'anxnews_urllocal' is not defined appears several hours after the message: 'PropertyLoader' object has no attribute 'strategy' Thanks Roger On 4/11/07, Roger Demetrescu [EMAIL PROTECTED] wrote: Hi all, I have a daemon with 2 threads to control upload / download of some files (they use SQLAlchemy to find out which files must be worked). Once a week, my daemon's logging system sends me an email with this message: 'PropertyLoader' object has no attribute 'strategy' After that, I receive another email with this message: global name 'anxnews_urllocal' is not defined where 'anxnews_urllocal' is a field from a table. I usually don't need to touch this daemon... it still works fine even after this alert. Any hints about what could be causing this exception ? Please feel free to ask for more details... TIA Roger --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
