Re: [sqlalchemy] avoid setting attribute on init and or.reconstructor
Thanks Mike, I oversimplified a little bit (the factory function takes more arguments than self.stops, not part of the Planning class), but if i create the factory with partial I think it will work. On Monday, November 16, 2020 at 6:16:24 PM UTC+1 Mike Bayer wrote: > I would use a memoized descriptor for that and have it evaluate when > called. > > Amazingly, i can't find public examples of Python memoize decorators that > aren't overwrought.we use one that is completely efficient and simple: > > class memoized_property(object): > """A read-only @property that is only evaluated once.""" > > def __init__(self, fget, doc=None): > self.fget = fget > self.__doc__ = doc or fget.__doc__ > self.__name__ = fget.__name__ > > def __get__(self, obj, cls): > if obj is None: > return self > obj.__dict__[self.__name__] = result = self.fget(obj) > return result > > > then you can just set it up as: > > class Planning(...): > @memoized_property > def matrix(self): > return self.matrix_factor(self.stops) > > > > > On Mon, Nov 16, 2020, at 11:43 AM, lars van gemerden wrote: > > Hi Mike, > > What if during reconstruction you need a one-to-many attribute, like: > > class Planning(SqlaBase): > stops = relationship(*"Stop"*, back_populates=*"planning"*, lazy= > *"joined"*) > > matrix_factory = Matrix > > def __init__(self, **kwargs): > super().__init__(**kwargs) > self.matrix = self.matrix_factory(self.stops) > > @reconstructor > def init_on_load(self): > self.matrix = self.matrix_factory(self.stops) > The docs say self.stops will not be completely loaded in init_on_load, how > could i make this work? > > Cheers, Lars > On Monday, August 7, 2017 at 6:09:08 AM UTC+2 Mike Bayer wrote: > > > > On Aug 6, 2017 1:33 PM, "Shane Carey" wrote: > > Hey Mike, > > I can expand my example. I have an orm mapped attribute like this > > class Obj(Base): > _evaluator = Column(String) > > def __init__(self, **kwargs): > super().__init__(**kwargs) > self._eval_func = eval(self._evaluator) > > @orm.reconstructor > def init_on_load(self): > self._eval_func = eval(self._evaluator) > > @property > def evaluator(self): > return self._eval_func > > @evaluator.setter > def set_evaluator(ev): > self._evaluator = ev > self._eval_func = eval(self._evaluator) > > You can see that I have to explicitly set self._eval_func in three > different places, when really I just want to set it every time > self._evaluator is set. > > It looks to me like the orm events are just a different way of placing the > different settings of this class attribute > > Also, I would like to not call eval in the getter of the property for the > sake of performance (I know that would simplify the issue). > > Is there a way to intercept the setting of self._evaluator for all cases? > > > > Use the init and load event listeners from my previous email on top of one > function. It will be called for init and load. > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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+...@googlegroups.com. > To post to this group, send email to sqlal...@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e9b2b9ff-0979-43be-94f4-fdb3ceb48ae9n%40googlegroups.com > >
Re: [sqlalchemy] avoid setting attribute on init and or.reconstructor
Hi Mike,
What if during reconstruction you need a one-to-many attribute, like:
class Planning(SqlaBase):
stops = relationship("Stop", back_populates="planning", lazy="joined")
matrix_factory = Matrix
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.matrix = self.matrix_factory(self.stops)
@reconstructor
def init_on_load(self):
self.matrix = self.matrix_factory(self.stops)
The docs say self.stops will not be completely loaded in init_on_load, how
could i make this work?
Cheers, Lars
On Monday, August 7, 2017 at 6:09:08 AM UTC+2 Mike Bayer wrote:
>
>
> On Aug 6, 2017 1:33 PM, "Shane Carey" wrote:
>
> Hey Mike,
>
> I can expand my example. I have an orm mapped attribute like this
>
> class Obj(Base):
> _evaluator = Column(String)
>
> def __init__(self, **kwargs):
> super().__init__(**kwargs)
> self._eval_func = eval(self._evaluator)
>
> @orm.reconstructor
> def init_on_load(self):
> self._eval_func = eval(self._evaluator)
>
> @property
> def evaluator(self):
> return self._eval_func
>
> @evaluator.setter
> def set_evaluator(ev):
> self._evaluator = ev
> self._eval_func = eval(self._evaluator)
>
> You can see that I have to explicitly set self._eval_func in three
> different places, when really I just want to set it every time
> self._evaluator is set.
>
> It looks to me like the orm events are just a different way of placing the
> different settings of this class attribute
>
> Also, I would like to not call eval in the getter of the property for the
> sake of performance (I know that would simplify the issue).
>
> Is there a way to intercept the setting of self._evaluator for all cases?
>
>
>
> Use the init and load event listeners from my previous email on top of one
> function. It will be called for init and load.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e9b2b9ff-0979-43be-94f4-fdb3ceb48ae9n%40googlegroups.com.
Re: [sqlalchemy] Possible Exceptions
Am 29.11.2017 um 17:16 schrieb Mike Bayer: > On Wed, Nov 29, 2017 at 11:12 AM, Mike Bayer <mike...@zzzcomputing.com> wrote: >> On Wed, Nov 29, 2017 at 3:37 AM, <lars.lied...@kit.edu> wrote: >>> Hello everybody, >>> >>> I am writing some piece of code, which should not exit unforseen in the best >>> case, at least it should write to a log file what went wrong (e.g. Database >>> not reachable, etc). So I tried figuring out, which Exceptions would be >>> possibly thrown by SQLAlchemy: > let me also add that if "unforseen exit" is the issue, you're mostly > going to look for the SQL-related errors thrown by the driver which on > the SQLAlchemy side are the DBAPIError classes, however even in that > case, some of those errors mean "couldn't connect to the database", > which could be mis-configuration OR the database is down, others can > mean "SQL is wrong". So even then it's hard to know ahead of time > what conditions can be caught and handled vs. which ones mean the > program needs to be re-configured and restarted. Of course, In my case it will be a call from the crontab starting my process regularly, I just wanted my process not to exit without writing it to the logfile what the problem was with a "nicely" formatted message, so the logfiles can be checked for certain messages in ELK. So I think I can limit them down to if there is an error connecting to the database because the queries are pretty much fixed and only differ in which element(s) I query and of course I have to make sure that the configuration is correct. > > > > >> the exception classes are documented at: >> >> http://docs.sqlalchemy.org/en/latest/core/exceptions.html >> http://docs.sqlalchemy.org/en/latest/orm/exceptions.html >> >> i will note that while the ORM exceptions are linked from the front >> page of the docs, the Core ones are not, you need to go into the full >> table of contents to see it >> (http://docs.sqlalchemy.org/en/latest/contents.html). >> >> as to the exact codepath that can raise these, it depends on the >> exception. The docstrings for the exceptions themselves can of >> course have some additional detail as to what kinds of operations >> might raise them.Though generic ones like "InvalidRequestError" or >> "ArgumentError" are thrown in dozens of places wherever the library is >> asked to do something that doesn't make sense or function arguments >> that don't make sense are detected. Thank you very much, I must have not seen the Link. >> >>> The first thing I did was googling for how to find out which Exceptions >>> could be thrown in Python. The answers I found on Stack Overflow etc. were >>> like "You simply don't", "This is Python, dumbass, you can't predict which >>> are thrown so don't even try" or "Just write tests until you found them all" >>> and "Just catch the general Exception class." >>> So I tried looking at the SQLAlchemy Documentation to see if there is >>> something written about when something goes wrong, but still no luck. >>> Before I started digging into the code I thought I'd ask here first >>> >>> So is there any hint to know which Exceptions could be thrown by SQLAlchemy? >>> The error cases I could think of were mostly wrrors while connecting to the >>> database or having errors in queries. I would totally be willing to help >>> with documenting at a certain point but even for this I need to know if I >>> just did't find any documentation for this and if you consider this as >>> neccessary. I feel that it is neccessary for me not just to kill the process >>> with maybe a stack trace on stdout. >>> >>> >>> Cheers >>> >>> Lars Liedtke >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> 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, vis
[sqlalchemy] Possible Exceptions
Hello everybody, I am writing some piece of code, which should not exit unforseen in the best case, at least it should write to a log file what went wrong (e.g. Database not reachable, etc). So I tried figuring out, which Exceptions would be possibly thrown by SQLAlchemy: - The first thing I did was googling for how to find out which Exceptions could be thrown in Python. The answers I found on Stack Overflow etc. were like "You simply don't", "This is Python, dumbass, you can't predict which are thrown so don't even try" or "Just write tests until you found them all" and "Just catch the general Exception class." - So I tried looking at the SQLAlchemy Documentation to see if there is something written about when something goes wrong, but still no luck. - Before I started digging into the code I thought I'd ask here first So is there any hint to know which Exceptions could be thrown by SQLAlchemy? The error cases I could think of were mostly wrrors while connecting to the database or having errors in queries. I would totally be willing to help with documenting at a certain point but even for this I need to know if I just did't find any documentation for this and if you consider this as neccessary. I feel that it is neccessary for me not just to kill the process with maybe a stack trace on stdout. Cheers Lars Liedtke -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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] join aliased
OK, thank you On Saturday, March 22, 2014 9:03:01 PM UTC+1, Michael Bayer wrote: On Mar 22, 2014, at 9:16 AM, lars van gemerden la...@rational-it.comjavascript: wrote: query = session.query(Email) query = query.join(user) #or query = query.join(user, aliased = True) query = query.add_columns(Email.email, User.name) the add_columns() method does not have the clause adaptation behavior of filter(), so it does not take into account the fact that “aliased=True” was called when the User entity was first pulled in. so this pulls in the User entity twice, once from User.name, and another because of the aliased(User) brought in by the join. solution is not to use aliased=True (a feature I would never have added if it were today), use ua = aliased(User); q.join(ua, “user”); query.add_columns(ua.name). -- 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.
[sqlalchemy] a relationship question
Hi all,
Simple question, but couldn't find it in the docs:
- How can i retrieve the target class in a relationship in the ORM? Say i
have a sqla class with a relationship(User, primaryjoin = ...), how can i
retrieve the User class from the relationship ('relationship' is a
descriptor, right?)
Cheers, Lars
--
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.
[sqlalchemy] join aliased
Hi all,
Maybe (hopefully;-) i am missing something simple, but i have the following
test code:
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
class User(Base):
__tablename__ = 'user_table'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Email, back_populates=user)
class Email(Base):
__tablename__ = 'email_table'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user_table.id'))
user = relationship(User, back_populates=addresses)
Base.metadata.create_all(engine)
u1 = User(name = bob)
u2 = User(name = jan)
a1 = Email(email = b...@school.nl, user = u1)
a2 = Email(email = j...@school.nl, user = u2)
a3 = Email(email = j...@home.nl, user = u2)
session = Session()
session.add_all([u1, u2, a1, a2, a3])
session.commit()
query = session.query(Email)
query = query.join(user) #or query = query.join(user, aliased =
True)
query = query.add_columns(Email.email, User.name)
for r in query.all():
print r[1:],
print
and if i use the line:
query = query.join(user)
this results in:
(u'b...@school.nl', u'bob') (u'j...@school.nl', u'jan')
(u'j...@home.nl', u'jan')
which i would expect, but if i exchange the line with:
query = query.join(user, aliased = True)
i get the full cross product between the tables:
(u'b...@school.nl', u'bob') (u'j...@school.nl', u'bob') (u'j...@home.nl',
u'bob') (u'b...@school.nl', u'jan') (u'j...@school.nl', u'jan')
(u'j...@home.nl', u'jan')
Can anyone explain why this difference occurs?
Cheers, Lars
--
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.
[sqlalchemy] Re: label column_descriptions
I solved it (again): for future reference: say you have the expression Person.name (Person is SQLA class) to use in a query, to have a label show up under that name in the resulting KeyedTuple's: to do: Person.name.label(something) is not enough, you have to do: expr = Person.name.label(something) and something like query.add_column(expr) after. Also Person.name.label(Person.name) and Person.name.label(Person_name)give exceptions! (I am auto generating labels to use as column names on gui tables in a web page and guess which 2 i tried first :-) Cheers, Lars -- 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.
[sqlalchemy] Re: label column_descriptions
thanks, Michael, we crossed responses; i thought it was something like that, but there are some gotchas (at least for me; see above). CL On Thursday, March 20, 2014 2:22:24 PM UTC+1, lars van gemerden wrote: I solved it (again): for future reference: say you have the expression Person.name (Person is SQLA class) to use in a query, to have a label show up under that name in the resulting KeyedTuple's: to do: Person.name.label(something) is not enough, you have to do: expr = Person.name.label(something) and something like query.add_column(expr) after. Also Person.name.label(Person.name) and Person.name.label(Person_name)give exceptions! (I am auto generating labels to use as column names on gui tables in a web page and guess which 2 i tried first :-) Cheers, Lars -- 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] label column_descriptions
Hi Michael, OK, that's why. Below are the stack traces. They are not the same for A.b and A_b. If i use other labels this error does not happen. cheers, lars with expr.label( %s_%s % (cls.__name__, attr_name)): File C:\python27\lib\site-packages\bottle.py, line 781, in _handle return route.call(**args) File C:\python27\lib\site-packages\bottle.py, line 1592, in wrapper rv = callback(*a, **ka) File D:\Documents\Code\python\floware\server\webserver.py, line 174, in trigger return manager.get_gui(session).get_json(session) File D:\Documents\Code\python\floware\models\role\roles.py, line 275, in get_json activities:[a.get_json(websession) for a in self.itermodel()]} File D:\Documents\Code\python\floware\models\role\roles.py, line 317, in get_json json = self.process.get_json(websession) File D:\Documents\Code\python\floware\models\flow\libraries\json.py, line 41, in get_json peeks: [task.peek() for task in tasks]} File D:\Documents\Code\python\floware\models\flow\tasks.py, line 127, in peek content = peek(self)[0:60] File D:\Documents\Code\python\floware\models\flow\tasks.py, line 26, in peek return | .join([peek(v) for v in value.itervalues()]) File D:\Documents\Code\python\floware\models\flow\tasks.py, line 21, in peek return value._peek_() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 46, in _peek_ return | .join(obj[0]._peek_() for obj in self) File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 46, in genexpr return | .join(obj[0]._peek_() for obj in self) File C:\python27\lib\_abcoll.py, line 581, in __iter__ v = self[i] File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 65, in __getitem__ return self._results_()[index] File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 41, in _results_ self.refresh() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 51, in refresh self.__results = self.all() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 70, in all return self._query_.with_session(session).all() File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2241, in all return list(self) File build\bdist.win32\egg\sqlalchemy\orm\loading.py, line 75, in instances labels) for row in fetch] File build\bdist.win32\egg\sqlalchemy\orm\loading.py, line 447, in _instance populate_state(state, dict_, row, isnew, only_load_props) File build\bdist.win32\egg\sqlalchemy\orm\loading.py, line 301, in populate_state populator(state, dict_, row) File build\bdist.win32\egg\sqlalchemy\orm\strategies.py, line 151, in fetch_col dict_[key] = row[col] InvalidRequestError: Ambiguous column name 'Toetsing.cijfer' in result set! try 'use_labels' option on select statement. with expr.label(%s.%s % (cls.__name__, attr_name)): Traceback (most recent call last): File C:\python27\lib\site-packages\bottle.py, line 781, in _handle return route.call(**args) File C:\python27\lib\site-packages\bottle.py, line 1592, in wrapper rv = callback(*a, **ka) File D:\Documents\Code\python\floware\server\webserver.py, line 174, in trigger return manager.get_gui(session).get_json(session) File D:\Documents\Code\python\floware\models\role\roles.py, line 275, in get_json activities:[a.get_json(websession) for a in self.itermodel()]} File D:\Documents\Code\python\floware\models\role\roles.py, line 317, in get_json json = self.process.get_json(websession) File D:\Documents\Code\python\floware\models\flow\libraries\json.py, line 41, in get_json peeks: [task.peek() for task in tasks]} File D:\Documents\Code\python\floware\models\flow\tasks.py, line 127, in peek content = peek(self)[0:60] File D:\Documents\Code\python\floware\models\flow\tasks.py, line 26, in peek return | .join([peek(v) for v in value.itervalues()]) File D:\Documents\Code\python\floware\models\flow\tasks.py, line 21, in peek return value._peek_() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 46, in _peek_ return | .join(obj[0]._peek_() for obj in self) File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 46, in genexpr return | .join(obj[0]._peek_() for obj in self) File C:\python27\lib\_abcoll.py, line 581, in __iter__ v = self[i] File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 65, in __getitem__ return self._results_()[index] File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 41, in _results_ self.refresh() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 51, in refresh self.__results = self.all() File D:\Documents\Code\python\floware\models\flow\libraries\queries.py, line 70, in all return self._query_.with_session(session).all() File build\bdist.win32\egg
Re: [sqlalchemy] AssertionError
clear and bug found .. thanks On Tuesday, March 18, 2014 4:51:36 PM UTC+1, Michael Bayer wrote: it means this: a1 = A(id=1) session.add(a1) session.commit() a2 = A(id=1) session.add(a2) # — error On Mar 18, 2014, at 9:59 AM, lars van gemerden la...@rational-it.comjavascript: wrote: Hi all, Does anyone know hoe to interpret the following error? AssertionError: A conflicting state is already present in the identity map for key (class 'models.data.database.Keuze', (1,)) Cheers, Lars -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . 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.
[sqlalchemy] AssertionError
Hi all, Does anyone know hoe to interpret the following error? AssertionError: A conflicting state is already present in the identity map for key (class 'models.data.database.Keuze', (1,)) Cheers, Lars -- 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] StaleDataError/ObjectDeletedError
Thanks, this helps some to narrow it down. Trying to zoom in: - why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before? - when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data? - if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? - Is there (in principle) a problem with: + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed) + setting an attribute of that object with another object that was queried from the database + committing the first object to the database? Cheers, Lars On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote: On Jan 31, 2014, at 8:11 PM, lars van gemerden la...@rational-it.comjavascript: wrote: this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing. Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case). - could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)? sure - this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible? absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is relevant here -- 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/groups/opt_out.
Re: [sqlalchemy] StaleDataError/ObjectDeletedError
Oh, on more question: might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above? CL On Saturday, February 1, 2014 1:25:27 PM UTC+1, lars van gemerden wrote: Thanks, this helps some to narrow it down. Trying to zoom in: - why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before? - when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data? - if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? - Is there (in principle) a problem with: + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed) + setting an attribute of that object with another object that was queried from the database + committing the first object to the database? Cheers, Lars On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote: On Jan 31, 2014, at 8:11 PM, lars van gemerden la...@rational-it.com wrote: this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing. Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case). - could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)? sure - this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible? absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is relevant here -- 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/groups/opt_out.
Re: [sqlalchemy] StaleDataError/ObjectDeletedError
Ok, Michael, That helped a lot, what i have done is (for future reference/others); 1) turned of autoflush on all sessions, 2) shortened the lifespan of session to a minimum, 3) removed the if object_session(obj): session.merge(obj) option in the Session() function, This seems to have solved the problem for now (needs more testing)! Thanks for the link to the talk as well. Cheerio, Lars On Saturday, February 1, 2014 4:34:10 PM UTC+1, Michael Bayer wrote: On Feb 1, 2014, at 9:01 AM, lars van gemerden la...@rational-it.comjavascript: wrote: Oh, on more question: might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above? the code which illustrates the @contextmanager and the “def Session()” looks error-prone and entirely awkward, and the rationale for such a context manager isn’t apparent. It appears to be mixing the intent of wishing to share random rows between multiple sessions (a bad idea) while at the same time trying to conceal the details of how a delicate operation like that is performed (it guesses whether add() or merge() should be used, etc). It also seems to mix the concerns of dealing with object mechanics and session creation at the same time which are typically two different concerns, not to mention that it has a complex system of committing or not committing using flags which makes it unsurprising that you’re seeing non-existent rows show up in other transactions. So yeah, if it were me, I’d definitely try to approach whatever the problem is it’s trying to solve in a different way, one which preferably sticks to the patterns outlined in the ORM tutorial as much as possible (e.g. one session at a time, load/manipulate objects, commit(), throw everything away). Those points at which an application actually uses two sessions at once, or transfers objects between them, should be very isolated cases with very explicit mechanics and clear rationale why this operation is needed in this specific case (where typical cases are: sending objects into worker threads or processes, moving objects in and out of caching layers, or running two transactions simultaneously so that one can commit and the other roll back, such as a transaction writing to an application history table).The app wouldn’t have a generic “here’s one of those places we need to use two sessions with a specific target object to pull out of one of them” use case such that a context manager is needed, there should be extremely few places where that kind of thing goes on. The kind of issue you’re hitting is exactly the one I talk about in detail in my talk, “The SQLAlchemy Session in Depth”: http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/ Page 18 of the PDF illustrates a slide “Lack of Behavioral Constraints Creates Confusion” illustrating an anti-pattern similar to the one I think we’re seeing here. -- 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/groups/opt_out.
Re: [sqlalchemy] StaleDataError/ObjectDeletedError
Hi Michael, With some more detail: What i do is: 1 make a new object (mapped)- obj1 2 create a scoped session (with context manager)- session1 3 do session1.add(obj) 4 create another scoped session - session2 5 do session2.query(someclass).get(some_id)-obj2 6 close session2, no commit, no flush - obj2 is detached (right?) 7 do obj1.someattr.append(obj2) 8 do session1.commit() 9 get the first ERROR above basically i use def Session( objs): session = session_maker() for obj in objs: if object_session(obj) is None: session.add(obj) else: session.merge(obj) return session @contextmanager def scoped_session(objs = [], commit = True): session = Session(objs) try: yield session if commit: session.commit() except: session.rollback() raise finally: session.close() and essentially code description (1-8) above comes down to: obj1 = cls1() with scoped_session([obj1]) as session1: obj1.somefield = somevalue with scoped_session(commit = False) as session2: obj2 = session2.query(cls2).get(some_id) obj1.someattr.append(obj2) if i just do: with scoped_session([obj1]) as session1: obj1.somefield = somevalue there is no problem. Also: this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing. Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case). - could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)? - this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible? As far as i know in the code that causes the problem, i do not do any deletes and i do not call flush myself. Doing some more testing, now i get more of the second error in: def __str__(self): #in mapped class print object_session(self) is not None, has_identity(self) # True, True, = OK print self.id #= ERROR .. with trace: File d:\Documents\Code\python\floware\models\flow\processes.py, line 333, in run self.execute(input, output) File d:\Documents\Code\python\floware\toolshed\logs.py, line 55, in wrapper f_result = func(*v, **k) File d:\Documents\Code\python\floware\models\flow\libraries\basic.py, line 159, in execute print %s %s % (self.cursor, str(i.item)) File d:\Documents\Code\python\floware\models\data\database.py, line 281, in __str__ print object_session(self), has_identity(self), self.id File C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py, line 316, in __get__ File C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py, line 611, in get File C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\state.py, line 380, in __call__ File C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py, line 606, in load_scalar_attributes sqlalchemy.orm.exc.ObjectDeletedError: Instance 'Company at 0x5e4a3f0' has been deleted, or its row is otherwise not present. CL -- 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/groups/opt_out.
[sqlalchemy] keyword search
Hi all, I want to implement a keyword search over multiple fields and/or even related tables. Tables/orm classes are not designed by me but by users of my software, so i will probably let them indicate whether a field/relationship should be included (or I'll base it on type, e.g. String and Text fields only). I was thinking of using a hybrid property, but they seem a bit to magical for this purpose (might be really slow). Is there a best practice/standard way to do this in SQLA? Cheers, Lars -- 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/groups/opt_out.
[sqlalchemy] Re: keyword search
PS: a search over all fields would be great too! On Tuesday, December 17, 2013 11:01:51 AM UTC+1, lars van gemerden wrote: Hi all, I want to implement a keyword search over multiple fields and/or even related tables. Tables/orm classes are not designed by me but by users of my software, so i will probably let them indicate whether a field/relationship should be included (or I'll base it on type, e.g. String and Text fields only). I was thinking of using a hybrid property, but they seem a bit to magical for this purpose (might be really slow). Is there a best practice/standard way to do this in SQLA? Cheers, Lars -- 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/groups/opt_out.
Re: [sqlalchemy] nullable/association question
Hi Michael, since i might also start with a Role instance and add User instances to them i need a symmetrical solution with regard ref and backref (and i think casade_backrefs=False would not work in that case?). So i tested two solutions which both seem to work in this specific case: - create a separate session for the query on Role (session.query(Role).all()); not the same session the User instance is in (as i did before) - set autoflush to false on the query as in (session.query(Role).autoflush(False).all()) Since you did not mention the second solution, i wondered whether there are possible disadvantages to the second approach; the first one seems slower but cleaner. Cheers, Lars On Wed, Nov 20, 2013 at 4:51 PM, Michael Bayer mike...@zzzcomputing.comwrote: you either want to prevent User from being cascaded and/or added into the Session ahead of time, which if this is occurring via backref you might want to look at casade_backrefs=False ( http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=cascade_backrefs#controlling-cascade-on-backrefs). If not that, then use “with session.no_autoflush” for the period of time that you query for Role. On Nov 20, 2013, at 8:02 AM, lars van gemerden l...@rational-it.com wrote: Hi all, I have 2 sqla classes with mapped tables: User and Role with a many to many relationship (User.roles/Role.users) through an association table. User has columns email and password, both non nullable. I create an instance of User but do not commit it yet, next i do a query on Role: session.query(Role).all() (to be able to let an end-user choose one or more roles for the for the newly created User). This gives the error: IntegrityError: (IntegrityError) User.email may not be NULL u'INSERT INTO User (_created_, discriminator, email, password) VALUES (CURRENT_TIMESTAMP, ?, ?, ?)' (None, None, None) I think the query tries to flush the instance of User, which causes the error. My questions is: Why and how do i avoid it? I just want a list of all roles currently in the Role table to be able to assign one or moe to the User instance. Cheers, Lars -- 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/groups/opt_out. -- Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 -- 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/groups/opt_out.
Re: [sqlalchemy] just a picture
The tool Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 21 nov. 2013, at 15:02, Stefane Fermigier s...@fermigier.com wrote: Hi Lars, Are you working on the tool, or on the application whose schema is depicted on the tool ? Regards, S. On Nov 21, 2013, at 1:38 PM, lars van gemerden wrote: something i have been working on, running on sqlalchemy . Cheers, Lars -- 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/groups/opt_out. Stefane Fermigier - http://fermigier.com/ - http://twitter.com/sfermigier - http://linkedin.com/in/sfermigier Founder CEO, Abilian - Enterprise Social Software - http://www.abilian.com/ Co-Founder and Chairman, Systematic FreeOSS Special Interest Group - http://www.gt-logiciel-libre.org/ Co-Founder Vice-President, National Council for FreeOSS - http://cnll.fr/ Vice President, Open World Forum 2013 - http://openworldforum.org/ Well done is better than well said. - Benjamin Franklin There's no such thing as can't. You always have a choice. - Ken Gor Le vrai courage, c'est de faire ce qui est juste. - Dr Benjamin Justice -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/01K5mYOcFSE/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out.
Re: [sqlalchemy] just a picture
No, sorry, to be able to do this, a lot of of settings are predetermined (the modeller can set nullable, unique, validation, etc, but not settings like cascades). The goal is to make a modelling tool that can run on existing db engines and persist python objects representing 'business' data, not to be a modelling tool with the reach of sqla itself. Cheers, Lars Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 21 nov. 2013, at 15:19, Werner werner...@gmx.ch wrote: On 21/11/2013 15:02, Stefane Fermigier wrote: Hi Lars, Are you working on the tool, or on the application whose schema is depicted on the tool ? Assuming it is a tool this would be very nice. Can it read an existing SA model? Werner -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/01K5mYOcFSE/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out.
[sqlalchemy] just a picture
something i have been working on, running on sqlalchemy . https://lh4.googleusercontent.com/-xwar8HEk2iU/Uo39bYo9x_I/AD4/AoTurWbGJ30/s1600/dbpic.png Cheers, Lars -- 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/groups/opt_out.
Re: [sqlalchemy] nullable/association question
in my application the query for roles is practically impossible to get into the block (close to) where the user instance is created, i could not attach the User instance to a session at all (before being ready to commit), but would i still be able to set the roles on the User? CL On Thursday, November 21, 2013 3:57:44 PM UTC+1, Michael Bayer wrote: I’d stick with judicious use of “with session.no_autoflush: around the block where the User is being constructed and isn’t ready to flush yet. On Nov 21, 2013, at 6:29 AM, lars van Gemerden la...@rational-it.comjavascript: wrote: Hi Michael, since i might also start with a Role instance and add User instances to them i need a symmetrical solution with regard ref and backref (and i think casade_backrefs=False would not work in that case?). So i tested two solutions which both seem to work in this specific case: - create a separate session for the query on Role (session.query(Role).all()); not the same session the User instance is in (as i did before) - set autoflush to false on the query as in (session.query(Role).autoflush(False).all()) Since you did not mention the second solution, i wondered whether there are possible disadvantages to the second approach; the first one seems slower but cleaner. Cheers, Lars On Wed, Nov 20, 2013 at 4:51 PM, Michael Bayer mik...@zzzcomputing.comjavascript: wrote: you either want to prevent User from being cascaded and/or added into the Session ahead of time, which if this is occurring via backref you might want to look at casade_backrefs=False ( http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=cascade_backrefs#controlling-cascade-on-backrefs). If not that, then use “with session.no_autoflush” for the period of time that you query for Role. On Nov 20, 2013, at 8:02 AM, lars van gemerden la...@rational-it.comjavascript: wrote: Hi all, I have 2 sqla classes with mapped tables: User and Role with a many to many relationship (User.roles/Role.users) through an association table. User has columns email and password, both non nullable. I create an instance of User but do not commit it yet, next i do a query on Role: session.query(Role).all() (to be able to let an end-user choose one or more roles for the for the newly created User). This gives the error: IntegrityError: (IntegrityError) User.email may not be NULL u'INSERT INTO User (_created_, discriminator, email, password) VALUES (CURRENT_TIMESTAMP, ?, ?, ?)' (None, None, None) I think the query tries to flush the instance of User, which causes the error. My questions is: Why and how do i avoid it? I just want a list of all roles currently in the Role table to be able to assign one or moe to the User instance. Cheers, Lars -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- Lars van Gemerden la...@rational-it.com javascript: +31 6 26 88 55 39 -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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/groups/opt_out.
[sqlalchemy] nullable/association question
Hi all, I have 2 sqla classes with mapped tables: User and Role with a many to many relationship (User.roles/Role.users) through an association table. User has columns email and password, both non nullable. I create an instance of User but do not commit it yet, next i do a query on Role: session.query(Role).all() (to be able to let an end-user choose one or more roles for the for the newly created User). This gives the error: IntegrityError: (IntegrityError) User.email may not be NULL u'INSERT INTO User (_created_, discriminator, email, password) VALUES (CURRENT_TIMESTAMP, ?, ?, ?)' (None, None, None) I think the query tries to flush the instance of User, which causes the error. My questions is: Why and how do i avoid it? I just want a list of all roles currently in the Role table to be able to assign one or moe to the User instance. Cheers, Lars -- 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/groups/opt_out.
[sqlalchemy] quick query question
Hi, Short question: when does e.g. query.all() returns a list of objects and when it returns a list of NamedTuple's? is it only after you call add_columns() on the query that all() starts returning NamedTuple's? Cheers, Lars -- 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/groups/opt_out.
Re: [sqlalchemy] quick query question
Thanks again .. Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 28 okt. 2013, at 18:12, Michael Bayer mike...@zzzcomputing.com wrote: the list of objects are for when the query is set to return exactly one mapped class. query.column_descriptions will show this. On Oct 28, 2013, at 12:44 PM, lars van gemerden l...@rational-it.com wrote: Hi, Short question: when does e.g. query.all() returns a list of objects and when it returns a list of NamedTuple's? is it only after you call add_columns() on the query that all() starts returning NamedTuple's? Cheers, Lars -- 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/groups/opt_out. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/If3XeRoBh6w/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out.
Re: [sqlalchemy] quelified/disambiguated column names in query
Thank you, i must have blundered over that in the docs ... Lars On Mon, Oct 21, 2013 at 11:36 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Oct 21, 2013, at 5:20 PM, lars van gemerden l...@rational-it.com wrote: Hello, Say that i have a table with reports with a column 'title' and a one to many relationship 'chapters' to chapters also with a column 'title', if i join these tables in a query, like: q = self.session.query(Report).join(Report.chapters).add_columns(Report.title, Chapter.title, Chapter.text) for p in q.all(): print p.title print p.title prints the title of the chapter. Is there a way to let the query.all() return NamedTuples with qualified/disambiguated names (either 'title' and 'chapters.title' or 'Report.title' and 'Chapter.title' or perhaps with underscores), because now i see no way to distinguish the columns (apart maybe from the order). p.keys() returns 'title' twice. use labels: add_columns(Report.title.label(report_title), Chapter.title.label(chapter_title)) Cheers, Lars PS: i am using sqla 0.7.5 -- 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/groups/opt_out. -- Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 -- 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/groups/opt_out.
[sqlalchemy] quelified/disambiguated column names in query
Hello, Say that i have a table with reports with a column 'title' and a one to many relationship 'chapters' to chapters also with a column 'title', if i join these tables in a query, like: q = self.session.query(Report).join(Report.chapters).add_columns(Report.title, Chapter.title, Chapter.text) for p in q.all(): print p.title print p.title prints the title of the chapter. Is there a way to let the query.all() return NamedTuples with qualified/disambiguated names (either 'title' and 'chapters.title' or 'Report.title' and 'Chapter.title' or perhaps with underscores), because now i see no way to distinguish the columns (apart maybe from the order). p.keys() returns 'title' twice. Cheers, Lars PS: i am using sqla 0.7.5 -- 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/groups/opt_out.
Re: [sqlalchemy] query question
Ok, i've tried both (and each separately), as in: r1 = Report(number = 1, title = The Bird) r2 = Report(number = 2, title = The Bucket) session.add_all([r1, r2]) session.commit() q = self.session.query(Report.title) q.with_entities(Report.number) q.add_columns(Report.number) print q.all() printing: [(u'The Bird',), (u'The Bucket',)] what i would like to see is: [(u'The Bird', 1), (u'The Bucket', 2)] or something similar. Cheers, Lars On Wednesday, September 4, 2013 1:24:28 PM UTC+2, Simon King wrote: On Wed, Sep 4, 2013 at 12:05 PM, lars van gemerden la...@rational-it.comjavascript: wrote: I think i must be reading over something, but: is there a way to delay the selection of attributes in a query; something like session.query(Person).filter(Person.age 100).select(Person.name).first() (uAncient Bob,) I'm not sure quite what you mean, but the with_entities method of Query would seem to be the closest to your example: http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.with_entities I assume you know you can also do this: session.query(Person.name).filter(Person.age 100).first() You can also defer certain columns so that they will be loaded lazily: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#deferred-column-loading Hope that helps, 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] query question
OK, never mind |-) should be: q = q.with_entities(Report.number) q = q.add_columns(Report.number) sry On Mon, Sep 9, 2013 at 2:43 PM, lars van gemerden l...@rational-it.comwrote: Ok, i've tried both (and each separately), as in: r1 = Report(number = 1, title = The Bird) r2 = Report(number = 2, title = The Bucket) session.add_all([r1, r2]) session.commit() q = self.session.query(Report.title) q.with_entities(Report.number) q.add_columns(Report.number) print q.all() printing: [(u'The Bird',), (u'The Bucket',)] what i would like to see is: [(u'The Bird', 1), (u'The Bucket', 2)] or something similar. Cheers, Lars On Wednesday, September 4, 2013 1:24:28 PM UTC+2, Simon King wrote: On Wed, Sep 4, 2013 at 12:05 PM, lars van gemerden la...@rational-it.com wrote: I think i must be reading over something, but: is there a way to delay the selection of attributes in a query; something like session.query(Person).filter(**Person.age 100).select(Person.name).**first() (uAncient Bob,) I'm not sure quite what you mean, but the with_entities method of Query would seem to be the closest to your example: http://docs.sqlalchemy.org/en/**rel_0_8/orm/query.html#** sqlalchemy.orm.query.Query.**with_entitieshttp://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.with_entities I assume you know you can also do this: session.query(Person.name).**filter(Person.age 100).first() You can also defer certain columns so that they will be loaded lazily: http://docs.sqlalchemy.org/en/**rel_0_8/orm/mapper_config.** html#deferred-column-loadinghttp://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#deferred-column-loading Hope that helps, Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/7hA4UZV8uaA/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 -- 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/groups/opt_out.
[sqlalchemy] query question
I think i must be reading over something, but: is there a way to delay the selection of attributes in a query; something like session.query(Person).filter(Person.age 100).select(Person.name).first() (uAncient Bob,) Cheers, Lars -- 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/groups/opt_out.
Re: [sqlalchemy] error related to setting attribute to same value twice
I am getting that same error (not the original one). For the moment i've
solved the problem in a different way, but it might pop up again. I'll keep
merge in mind (i've run into the is already attached to session before).
Thank you, Lars
On Monday, September 2, 2013 9:18:11 PM UTC+2, Simon King wrote:
Dunno, let's try it:
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Person(Base):
__tablename__ = 'people'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
name = sa.Column(sa.String(20))
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = saorm.sessionmaker(bind=engine)
# Create our first session and use it to add a Person to the database
sess1 = Session()
sess1.add(Person(name='lars'))
sess1.commit()
sess1.close()
# Retrieve our Person from the database again.
person = sess1.query(Person).first()
# Now try to add that instance to a second session
sess2 = Session()
sess2.add(person)
sess2.commit()
Output:
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: Object 'Person at 0x10c9f5790' is
already attached to session '1' (this is '2')
Looks very similar to your error message. I'm using SA 0.8.0 - it looks
like the message is slightly different in your version.
The correct thing to do in this case is to use session.merge to create a
copy of the object, attached to the new session. If you change the last few
lines of the script above to:
sess2 = Session()
merged = sess2.merge(person)
sess2.add(merged)
...it works fine.
Hope that helps,
Simon
On 2 Sep 2013, at 19:58, lars van Gemerden
la...@rational-it.comjavascript:
wrote:
Yes I know the question was a bit vague, but i couldn't think of a
simple way to show some code. A test case would probably require some major
surgery; I'll consider it though.
It is possible that the second instance is associated with a different
session, i'd have to check. What actually happens if you add the same
instance to a second session?
CL
On Mon, Sep 2, 2013 at 8:23 PM, Simon King
si...@simonking.org.ukjavascript:
wrote:
Without knowing your application it's very difficult to know...
Is there any chance that the second object is already associated with a
different session? Perhaps from a different thread, or retrieved from a
cache or something similar?
Producing a standalone test script would help in diagnosing the problem.
Simon
On 2 Sep 2013, at 18:56, Lars van Gemerden
la...@rational-it.comjavascript:
wrote:
Well, from the message yes, but i am not setting any primary keys
manually, so where could the second instance come from?
CL
Lars van Gemerden
la...@rational-it.com javascript:
+31 6 26 88 55 39
On 2 sep. 2013, at 17:39, Simon King
si...@simonking.org.ukjavascript:
wrote:
On Mon, Sep 2, 2013 at 4:01 PM, lars van gemerden
la...@rational-it.com javascript: wrote:
Hi all,
I have a one-to-one and many-to-one relationship (with an
association table
in the middle; Report.author-assoctable-Person.reports; why the
table in the
middle, you might ask, well it's a long story) and i am setting the
author
on a specific Report twice to the same value. This gives the
following
error:
InvalidRequestError: Can't attach instance person at
0x36a9350;
another instance with key (class 'models.data.classes.person',
(2,)) is
already present in this session.
The first time i set the author, it works and if i change the author
it
works as well.
This seems i little strange to me, why not be able to set the
attribute to
the same value twice?
Otherwise i have to check whether the new and old values are equal
(which
through the peculiarties of my code is somewhat wastefull)
I think the message is actually telling you that you have 2 different
instances of your person class with the same primary key.
SQLAlchemy
uses an identity map to ensure that a particular row in the database
is only identified by a single instance within a particular session.
If you violate that assumption (eg. by loading one instance of
person from the database, then creating another instance and
setting
its primary key), you will get errors like this.
Does that sound plausible?
Simon
--
You received this message because you are subscribed to a topic in
the Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d
Re: [sqlalchemy] error related to setting attribute to same value twice
My version is 0.7.5 (thought i was on 8 already), i will look at updating!
The error sounds similar, I'll get back on this after i do (after some
research to find out if updating is a good idea right now)
Cheers, Lars
On Tue, Sep 3, 2013 at 11:38 AM, Simon King si...@simonking.org.uk wrote:
I realised after I sent that script that I wasn't reproducing quite
the same situation. I should have loaded the person from the database
in sess2 as well, before trying to add the instance from sess1. In
other words, change the end of the test script to say:
sess2 = Session()
dupe = sess2.query(Person).first()
sess2.add(person)
sess2.commit()
So sess2 now contains an instance representing that database row. The
error message is now:
AssertionError: A conflicting state is already present in the identity
map for key (class '__main__.Person', (1,))
...which is still not quite the same as your error message. What
version of SQLAlchemy are you using? (The above was from 0.8.2)
Simon
On Tue, Sep 3, 2013 at 10:15 AM, lars van gemerden l...@rational-it.com
wrote:
I am getting that same error (not the original one). For the moment i've
solved the problem in a different way, but it might pop up again. I'll
keep
merge in mind (i've run into the is already attached to session
before).
Thank you, Lars
On Monday, September 2, 2013 9:18:11 PM UTC+2, Simon King wrote:
Dunno, let's try it:
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Person(Base):
__tablename__ = 'people'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
name = sa.Column(sa.String(20))
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = saorm.sessionmaker(bind=engine)
# Create our first session and use it to add a Person to the
database
sess1 = Session()
sess1.add(Person(name='lars'))
sess1.commit()
sess1.close()
# Retrieve our Person from the database again.
person = sess1.query(Person).first()
# Now try to add that instance to a second session
sess2 = Session()
sess2.add(person)
sess2.commit()
Output:
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: Object 'Person at 0x10c9f5790' is
already attached to session '1' (this is '2')
Looks very similar to your error message. I'm using SA 0.8.0 - it looks
like the message is slightly different in your version.
The correct thing to do in this case is to use session.merge to
create a
copy of the object, attached to the new session. If you change the last
few
lines of the script above to:
sess2 = Session()
merged = sess2.merge(person)
sess2.add(merged)
...it works fine.
Hope that helps,
Simon
On 2 Sep 2013, at 19:58, lars van Gemerden la...@rational-it.com
wrote:
Yes I know the question was a bit vague, but i couldn't think of a
simple way to show some code. A test case would probably require some
major
surgery; I'll consider it though.
It is possible that the second instance is associated with a different
session, i'd have to check. What actually happens if you add the same
instance to a second session?
CL
On Mon, Sep 2, 2013 at 8:23 PM, Simon King si...@simonking.org.uk
wrote:
Without knowing your application it's very difficult to know...
Is there any chance that the second object is already associated with
a
different session? Perhaps from a different thread, or retrieved from
a
cache or something similar?
Producing a standalone test script would help in diagnosing the
problem.
Simon
On 2 Sep 2013, at 18:56, Lars van Gemerden la...@rational-it.com
wrote:
Well, from the message yes, but i am not setting any primary keys
manually, so where could the second instance come from?
CL
Lars van Gemerden
la...@rational-it.com
+31 6 26 88 55 39
On 2 sep. 2013, at 17:39, Simon King si...@simonking.org.uk
wrote:
On Mon, Sep 2, 2013 at 4:01 PM, lars van gemerden
la...@rational-it.com wrote:
Hi all,
I have a one-to-one and many-to-one relationship (with an
association table
in the middle; Report.author-assoctable-Person.reports; why the
table in the
middle, you might ask, well it's a long story) and i am setting
the
author
on a specific Report twice to the same value. This gives the
following
error:
InvalidRequestError: Can't attach instance person at
0x36a9350;
another instance with key (class 'models.data.classes.person',
(2,)) is
already present in this session
[sqlalchemy] error related to setting attribute to same value twice
Hi all, I have a one-to-one and many-to-one relationship (with an association table in the middle; Report.author-assoctable-Person.reports; why the table in the middle, you might ask, well it's a long story) and i am setting the author on a specific Report twice to the same value. This gives the following error: InvalidRequestError: Can't attach instance person at 0x36a9350; another instance with key (class 'models.data.classes.person', (2,)) is already present in this session. The first time i set the author, it works and if i change the author it works as well. This seems i little strange to me, why not be able to set the attribute to the same value twice? Otherwise i have to check whether the new and old values are equal (which through the peculiarties of my code is somewhat wastefull) Cheers, Lars -- 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/groups/opt_out.
Re: [sqlalchemy] error related to setting attribute to same value twice
Well, from the message yes, but i am not setting any primary keys manually, so where could the second instance come from? CL Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 2 sep. 2013, at 17:39, Simon King si...@simonking.org.uk wrote: On Mon, Sep 2, 2013 at 4:01 PM, lars van gemerden l...@rational-it.com wrote: Hi all, I have a one-to-one and many-to-one relationship (with an association table in the middle; Report.author-assoctable-Person.reports; why the table in the middle, you might ask, well it's a long story) and i am setting the author on a specific Report twice to the same value. This gives the following error: InvalidRequestError: Can't attach instance person at 0x36a9350; another instance with key (class 'models.data.classes.person', (2,)) is already present in this session. The first time i set the author, it works and if i change the author it works as well. This seems i little strange to me, why not be able to set the attribute to the same value twice? Otherwise i have to check whether the new and old values are equal (which through the peculiarties of my code is somewhat wastefull) I think the message is actually telling you that you have 2 different instances of your person class with the same primary key. SQLAlchemy uses an identity map to ensure that a particular row in the database is only identified by a single instance within a particular session. If you violate that assumption (eg. by loading one instance of person from the database, then creating another instance and setting its primary key), you will get errors like this. Does that sound plausible? Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/bkm3-nB6d1o/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out.
Re: [sqlalchemy] error related to setting attribute to same value twice
Yes I know the question was a bit vague, but i couldn't think of a simple way to show some code. A test case would probably require some major surgery; I'll consider it though. It is possible that the second instance is associated with a different session, i'd have to check. What actually happens if you add the same instance to a second session? CL On Mon, Sep 2, 2013 at 8:23 PM, Simon King si...@simonking.org.uk wrote: Without knowing your application it's very difficult to know... Is there any chance that the second object is already associated with a different session? Perhaps from a different thread, or retrieved from a cache or something similar? Producing a standalone test script would help in diagnosing the problem. Simon On 2 Sep 2013, at 18:56, Lars van Gemerden l...@rational-it.com wrote: Well, from the message yes, but i am not setting any primary keys manually, so where could the second instance come from? CL Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 2 sep. 2013, at 17:39, Simon King si...@simonking.org.uk wrote: On Mon, Sep 2, 2013 at 4:01 PM, lars van gemerden l...@rational-it.com wrote: Hi all, I have a one-to-one and many-to-one relationship (with an association table in the middle; Report.author-assoctable-Person.reports; why the table in the middle, you might ask, well it's a long story) and i am setting the author on a specific Report twice to the same value. This gives the following error: InvalidRequestError: Can't attach instance person at 0x36a9350; another instance with key (class 'models.data.classes.person', (2,)) is already present in this session. The first time i set the author, it works and if i change the author it works as well. This seems i little strange to me, why not be able to set the attribute to the same value twice? Otherwise i have to check whether the new and old values are equal (which through the peculiarties of my code is somewhat wastefull) I think the message is actually telling you that you have 2 different instances of your person class with the same primary key. SQLAlchemy uses an identity map to ensure that a particular row in the database is only identified by a single instance within a particular session. If you violate that assumption (eg. by loading one instance of person from the database, then creating another instance and setting its primary key), you will get errors like this. Does that sound plausible? Simon -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/bkm3-nB6d1o/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/bkm3-nB6d1o/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 -- 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/groups/opt_out.
[sqlalchemy] alternate way to filter queries
Hi all, This might be a bit of a stretch but here it goes: Say that i have a lambda function that takes a mapped object and teturns whether it is valid; e.g: lambda person: person.age 17 or person.length 1.75 is it possible to use this method to perform an sqla query on the database? Perhaps by using hybrid_method? Cheers, Lars PS: this might seem strange question, but it would save me a lot of work and/or would make my internal and user interfaces a lot more consistent. PPS: i do also have a string representation of the lambda functions, if that helps -- 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/groups/opt_out.
Re: [sqlalchemy] alternate way to filter queries
Hi Michael, So just to be sure, if i understand correctly and i have: func = lambda person: person.age 17 or person.length 1.75 I can do: class Person(Base): # @hybrid_method def run_filter(self, fn): return fn(self) session.query(MyClass).filter(MyClass.run_compare(func)) to get all objects of class Person where obj.age17 or obj.length1.75 Is that without loading all records and filtering afterwards?? That's pretty impressive i think (and really helpful to me personally) Cheers, Lars On Monday, August 26, 2013 5:23:07 PM UTC+2, Michael Bayer wrote: On Aug 26, 2013, at 11:14 AM, lars van gemerden la...@rational-it.comjavascript: wrote: Hi all, This might be a bit of a stretch but here it goes: Say that i have a lambda function that takes a mapped object and teturns whether it is valid; e.g: lambda person: person.age 17 or person.length 1.75 is it possible to use this method to perform an sqla query on the database? Perhaps by using hybrid_method? Cheers, Lars PS: this might seem strange question, but it would save me a lot of work and/or would make my internal and user interfaces a lot more consistent. PPS: i do also have a string representation of the lambda functions, if that helps I'm not sure if you need these to remain as lambdas, sure you can just pass it to a hybrid_method: class MyClass(Base): # @hybrid_method def run_compare(self, fn, *args): return fn(*args) query(MyClass).filter(MyClass.run_compare(some_lambda, some_other_object)) -- 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/groups/opt_out.
Re: [sqlalchemy] alternate way to filter queries
Hmm, too bad. I do have the lambda methods in string form so i'll probably have to write a parser and construct the query with unions and intersects (or filter(A.b 1, A.c0) like calls instead of intersects). No how did writing a parser tree go |-)? CL On Monday, August 26, 2013 8:27:16 PM UTC+2, Simon King wrote: On 26 Aug 2013, at 19:15, lars van gemerden la...@rational-it.comjavascript: wrote: On Monday, August 26, 2013 5:23:07 PM UTC+2, Michael Bayer wrote: On Aug 26, 2013, at 11:14 AM, lars van gemerden la...@rational-it.com wrote: Hi all, This might be a bit of a stretch but here it goes: Say that i have a lambda function that takes a mapped object and teturns whether it is valid; e.g: lambda person: person.age 17 or person.length 1.75 is it possible to use this method to perform an sqla query on the database? Perhaps by using hybrid_method? Cheers, Lars PS: this might seem strange question, but it would save me a lot of work and/or would make my internal and user interfaces a lot more consistent. PPS: i do also have a string representation of the lambda functions, if that helps I'm not sure if you need these to remain as lambdas, sure you can just pass it to a hybrid_method: class MyClass(Base): # @hybrid_method def run_compare(self, fn, *args): return fn(*args) query(MyClass).filter(MyClass.run_compare(some_lambda, some_other_object)) Hi Michael, So just to be sure, if i understand correctly and i have: func = lambda person: person.age 17 or person.length 1.75 I can do: class Person(Base): # @hybrid_method def run_filter(self, fn): return fn(self) session.query(MyClass).filter(MyClass.run_compare(func)) to get all objects of class Person where obj.age17 or obj.length1.75 Is that without loading all records and filtering afterwards?? That's pretty impressive i think (and really helpful to me personally) Cheers, Lars As long as your function returns something meaningful when called with the class (Person) rather than an instance, it should be fine. Unfortunately I'm not sure your example will work, because expr or expr isn't something that SQLAlchemy can convert into an SQL statement. If you were able to use the bitwise or operator | instead, that would do the right thing. See the contains and intersects methods in the first example on http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/hybrid.html. Hope that helps, 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
Now i was doing both, but you're right, i can remove the default from the column definition, and just initialise the object with the default in __init__ (from metadata i created elsewhere, but will remain available). Setting the default ASAP (on object creation) seems like the way of least surprise and it's not hard to do, as Wichert mentioned. Thanks again, Michael, my code improves ... Lars On Saturday, July 13, 2013 10:59:08 PM UTC+2, Michael Bayer wrote: On Jul 13, 2013, at 3:21 PM, Lars van Gemerden la...@rational-it.comjavascript: wrote: Just trying to help; i am not sure, but what you might also consider is to initialise attributes (or on first access) with a default if a default value as Column argument is given (i am already doing this in my own code) and also reset to this default in case of del, but maybe there are disadvantages. I can see why that might be reasonable, but at the same time the default for Column is at the level of Core; the default could be a Python callable, and this callable could be relying upon the fact that it is invoked within the context of executing the INSERT statement.By pulling out the Core-level default upon attribute access and just populating at the ORM level means we bypass the Core in doing its normal job with default, which violates . there's a term for this I can't recall, but the idea is, A does job X using mechanism P therefore if, based on some very non-deterministic system, once in awhile B does job X using mechanism Q instead, it makes the code much harder to understand and debug. So to really do it this way, the contract of default in terms of the ORM would need to be modified completely, such that *all* Column default args are applied at the ORM level. But even then, your Column() is still behaving in two different ways based on if you use Session.add() or engine.execute(insert()). The original intent of default for Column is that it acts exactly like server_default, except it runs on the Python side for the case that your schema wasn't created with this default, or the functionality of your default is not supported by the database. But otherwise, just like a server_default, the value is None on the Python side until the INSERT occurs. Really, a better solution here would be an ORM-level default, that's what you've implemented anyway. -- 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/groups/opt_out.
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
I think i didn't explain the error enough; just calling del user.name in the
example below causes an exception (except when i already accessed (say print
user.name) the attribute, which seemed odd to me). This is independent of
whether the attr becomes None.
Otherwise it isn't a real problem in my code, just something i ran into while
cleaning up some internal api, as you say i can easily work around it.
Cheers, Lars
Lars van Gemerden
l...@rational-it.com
+31 6 26 88 55 39
On 13 jul. 2013, at 01:52, Michael Bayer mike...@zzzcomputing.com wrote:
There's all kinds of things that __delete__ should possibly do, most likely
just do what you expect, i.e. set the value to NULL, but this would be
changing its behavior in a very backwards-incompatible way; such a change
could only be considered for 0.9.
Whats not clear is how exactly an entire application was built around 0.7,
relying on del in some way such that this can no longer be changed, and
suddenly it's an issue? doesn't make sense, I don't see why you can't just
get replace those del statements on your end. It's not supported, and if
it were, it would not be in 0.7 or 0.8.
On Jul 12, 2013, at 6:11 PM, Lars van Gemerden l...@rational-it.com wrote:
After thinking on it some more, should InstrumentedAttribute.__delete__ even
exist then, or maybe raise a NotImplementedError?
CL
Lars van Gemerden
l...@rational-it.com
+31 6 26 88 55 39
On 12 jul. 2013, at 18:28, Michael Bayer mike...@zzzcomputing.com wrote:
We've never supported del obj.attrname as a means of setting an attribute
to None (which translates to NULL in SQL). Setting the value to None
explicitly is preferred.
On Jul 12, 2013, at 12:16 PM, lars van gemerden l...@rational-it.com
wrote:
Hi all,
I had an arror in my code and i think i have reconstructed it as follows:
---
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
session = Session()
user = User(name = 'bob')
session.add(user)
session.commit()
#user.name
del user.name #error in sqlalchemy file attributes.py line 529
#user.name
session.commit()
assert user.name == None #error: user.name is still 'bob'
These two errors do not occur if i access the attributes before the delete
or the commit (i.e. uncomment the #user.name lines).
I am using version 7.5; are these errors solved by the latest version?
I would like to avoid upgrading at this point, but if i could be
reasonably sure that upgrading solves the problem, then no problem ..
Cheers, Lars
--
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/groups/opt_out.
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/TRUmFTaDCF8/unsubscribe.
To unsubscribe from this group and all its topics, 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/groups/opt_out.
--
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/groups/opt_out.
--
You received this message because you are subscribed
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
Just trying to help; i am not sure, but what you might also consider is to initialise attributes (or on first access) with a default if a default value as Column argument is given (i am already doing this in my own code) and also reset to this default in case of del, but maybe there are disadvantages. Cheers, Lars Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 On 13 jul. 2013, at 20:02, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 13, 2013, at 10:24 AM, Lars van Gemerden l...@rational-it.com wrote: I think i didn't explain the error enough; just calling del user.name in the example below causes an exception (except when i already accessed (say print user.name) the attribute, which seemed odd to me). This is independent of whether the attr becomes None. Otherwise it isn't a real problem in my code, just something i ran into while cleaning up some internal api, as you say i can easily work around it. Great then, yes we don't do a good job with del but there is a logic to it which I can't be 100% sure some apps aren't relying upon.I can possibly make it behave intuitively for 0.9, I guess it should mean, set the attribute to None - this because as you might have noticed an instrumented column attribute defaults itself to None when first accessed. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/TRUmFTaDCF8/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out.
[sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
Hi all,
I had an arror in my code and i think i have reconstructed it as follows:
---
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
session = Session()
user = User(name = 'bob')
session.add(user)
session.commit()
#user.name
del user.name #error in sqlalchemy file attributes.py line 529
#user.name
session.commit()
assert user.name == None #error: user.name is still 'bob'
These two errors do not occur if i access the attributes before the delete
or the commit (i.e. uncomment the #user.name lines).
I am using version 7.5; are these errors solved by the latest version?
I would like to avoid upgrading at this point, but if i could be
reasonably sure that upgrading solves the problem, then no problem ..
Cheers, Lars
--
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/groups/opt_out.
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
Can i just override __delattr__ in a subclass of a declarative base to achieve
this anyway?
Cheers, Lars
Lars van Gemerden
l...@rational-it.com
+31 6 26 88 55 39
On 12 jul. 2013, at 18:28, Michael Bayer mike...@zzzcomputing.com wrote:
We've never supported del obj.attrname as a means of setting an attribute
to None (which translates to NULL in SQL). Setting the value to None
explicitly is preferred.
On Jul 12, 2013, at 12:16 PM, lars van gemerden l...@rational-it.com wrote:
Hi all,
I had an arror in my code and i think i have reconstructed it as follows:
---
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
session = Session()
user = User(name = 'bob')
session.add(user)
session.commit()
#user.name
del user.name #error in sqlalchemy file attributes.py line 529
#user.name
session.commit()
assert user.name == None #error: user.name is still 'bob'
These two errors do not occur if i access the attributes before the delete
or the commit (i.e. uncomment the #user.name lines).
I am using version 7.5; are these errors solved by the latest version?
I would like to avoid upgrading at this point, but if i could be reasonably
sure that upgrading solves the problem, then no problem ..
Cheers, Lars
--
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/groups/opt_out.
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/TRUmFTaDCF8/unsubscribe.
To unsubscribe from this group and all its topics, 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/groups/opt_out.
--
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/groups/opt_out.
Re: [sqlalchemy] possible bug in InstrumentedAttribute.__delete__?
After thinking on it some more, should InstrumentedAttribute.__delete__ even
exist then, or maybe raise a NotImplementedError?
CL
Lars van Gemerden
l...@rational-it.com
+31 6 26 88 55 39
On 12 jul. 2013, at 18:28, Michael Bayer mike...@zzzcomputing.com wrote:
We've never supported del obj.attrname as a means of setting an attribute
to None (which translates to NULL in SQL). Setting the value to None
explicitly is preferred.
On Jul 12, 2013, at 12:16 PM, lars van gemerden l...@rational-it.com wrote:
Hi all,
I had an arror in my code and i think i have reconstructed it as follows:
---
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
Base.metadata.create_all(engine)
session = Session()
user = User(name = 'bob')
session.add(user)
session.commit()
#user.name
del user.name #error in sqlalchemy file attributes.py line 529
#user.name
session.commit()
assert user.name == None #error: user.name is still 'bob'
These two errors do not occur if i access the attributes before the delete
or the commit (i.e. uncomment the #user.name lines).
I am using version 7.5; are these errors solved by the latest version?
I would like to avoid upgrading at this point, but if i could be reasonably
sure that upgrading solves the problem, then no problem ..
Cheers, Lars
--
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/groups/opt_out.
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/TRUmFTaDCF8/unsubscribe.
To unsubscribe from this group and all its topics, 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/groups/opt_out.
--
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/groups/opt_out.
Re: [sqlalchemy] finding table/column name of unique constraint failure
Thanks, Michael On Mon, May 20, 2013 at 12:52 AM, Michael Bayer mike...@zzzcomputing.comwrote: On May 19, 2013, at 4:39 AM, lars van gemerden l...@rational-it.com wrote: Hi all, I generate webpages in which end-users can input data that is stored in a database via sqla.The datamodel is not pre-defined but created by (other) users/designers. My question is how can i best let the end-user know which field contains the error if the end-user violates a unique constraint; e.g. if the user creates an account by filling in a username/password, the username must be unique so the user must get an error (username already exists oss) if he tries to fill in an existing username. If i commit the data, the exception thrown is somewhat cryptic (is the message created by sqla or the database?). Some related questions: - are there arguments in the exception i could use? - should i just query the table for the existence of the unique constrained value (i seem to remember that is impossible in the validate solution, so how else)? I might be able to come up with some sort of solution, but i am looking for a good/the best (structurally sound, not too slow, not overly complex) solution, also useable for other constraints (e.g. non-null, maybe other query/multi-record based constraints). the messages are generated by the database and the DBAPI in use, SQLAlchemy just passes them through. There's no standardization for those messages, so if your target database isn't providing enough parseable information, you may need to do a quick query against the unique fields on the table first in order to produce validation logic. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/taHEXCvX2p0/unsubscribe?hl=en . To unsubscribe from this group and all its topics, 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Lars van Gemerden l...@rational-it.com +31 6 26 88 55 39 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] finding table/column name of unique constraint failure
Hi all, I generate webpages in which end-users can input data that is stored in a database via sqla.The datamodel is not pre-defined but created by (other) users/designers. My question is how can i best let the end-user know which field contains the error if the end-user violates a unique constraint; e.g. if the user creates an account by filling in a username/password, the username must be unique so the user must get an error (username already exists oss) if he tries to fill in an existing username. If i commit the data, the exception thrown is somewhat cryptic (is the message created by sqla or the database?). Some related questions: - are there arguments in the exception i could use? - should i just query the table for the existence of the unique constrained value (i seem to remember that is impossible in the validate solution, so how else)? I might be able to come up with some sort of solution, but i am looking for a good/the best (structurally sound, not too slow, not overly complex) solution, also useable for other constraints (e.g. non-null, maybe other query/multi-record based constraints). Cheers, Lars -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] adding validates() decorator after class creation
Hi, I am trying to use validates() to dynamically add validation to my sqla classes, but i cannot figure out how to do this. I have read python decorator docs and docs n validates, but something escapes me (or it is just not possible). A code snippet (this does not work): def create_validators(self): for cls in self.itervalues(): names = tuple([fld.key() for fld in cls._type_.fields()]) def validator(obj, name, value): if not cls._type_[name].validate(value): raise ValueError(incorrect value '%s' for '%s' in '%s' % (str(value), name, cls.__name__)) return value setattr(cls, 'validator', validates(*names)(validator)) #does NOT work in which: 'self' is a dictionary of sqla classes, 'cls' is an sqla class (derived from declarative_base(...)), 'cls._type_' contains the information for (further) generation of 'cls' (e.g. cls._type_[name].validate(value) calls a validate method for value for a field (say String(50)) with name 'name') The method above runs without problem, but the validator function is never called when setting field values. I have tried all alternatives i can think of, but maybe i am missing something basic. Can anyone help? Cheers, Lars -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/-F7Lqaw1AagJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: adding validates() decorator after class creation
Perfect (almost ;-), thanks a lot!
for future reference, the attribute name (key) was needed
in create_validator(cls) to access the correct validate method:
@staticmethod
def _create_validator(key, cls):
def validate(target, value, oldvalue, initiator):
if not cls._type_[key].validate(value):
raise ValueError(incorrect value '%s' for '%s' in '%s' %
(str(value), key, cls.__name__))
return validate
def create_validators(self):
for cls in self.itervalues():
for fld in cls._type_.fields():
key = fld.key()
attribute = getattr(cls, key)
event.listen(attribute, set, self._create_validator(key,
cls))
debug_print('created validator for %s in %s' % (key,
cls.__name__))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/I-TQaC4YCqYJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: automating inheritance
Well thats the thing, my users will determine the data structure
(graphically) and it is hard to predict what they will come up with.
On the other hand, I am only building a prototype at the moment, so
speed issues (if not easily solved) will have to wait.
I'll stick with joined inheritance for now (though I'll probalby take
out the unique base class for all classes).
Thank you again for all the help,
Lars
On Apr 27, 3:59 pm, Michael Bayer mike...@zzzcomputing.com wrote:
concrete inheritance is very challenging overall, if you expect there to be
any kind of polymorphic interaction between the classes. if you want to
query polymorphically then speed will be probably worse. If you can do
without polymorphic and stick to each subclass directly it wont have an issue.
Usually though if I'm inheriting more than two levels, I'll use joined
inheritance for level one-two then single table for all levels beyond that.
Depends on what you're trying to do.
On Apr 27, 2012, at 3:05 AM, lars van gemerden wrote:
Ok, so speed might become an issue for me as well;
Do you think a similar metaclass approach would work for concrete
inheritance would work without major drawbacks (before i do a major
overhaul)?
Is there any indication about how much faster concrete inheritance is,
compared to joined inheritance?
Cheers, Lars
On Friday, April 20, 2012 9:32:49 PM UTC+2, Michael Bayer wrote:
On Apr 20, 2012, at 8:51 AM, lars van gemerden wrote:
Ok, thank you, that helps, but now i cannot inherit from Engineer, as in:
class BaseMixin(object):
discriminator = Column(String(50))
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
return Column(Integer, primary_key = True)
@declared_attr
def __mapper_args__(cls):
if not has_inherited_table(cls):
return {'polymorphic_on': 'discriminator'}
else:
return {'polymorphic_identity': cls.__name__}
class InheritMixin(BaseMixin):
@declared_attr
def id(cls):
super_id = super(InheritMixin, cls).id
return Column(Integer, ForeignKey(super_id), primary_key = True)
class Person(BaseMixin, Base):
name = Column(String(50))
class Engineer(InheritMixin, Person):
job = Column(String(50))
class MasterEngineer(InheritMixin, Engineer):
specialty = Column(String(50))
Gives an MRO() error and if i would reverse the baseclasses (like class
Engineer(Person, InheritMixin): ... ), the inheriting classes pick up
the wrong id.
Do you see any solution for this?
yeah I suppose if you're building out joined inheritance more than one
level then this becomes awkward. I never use joined inh more than one
level because it has too much of an impact on queries.
the metaclass as you mention is always the last resort when the various
declarative trickery reaches its limit. I'm not thrilled about the
metaclass approach because it quickly gets confusing and shouldn't be
necessary. though in this case without some extra mechanism on
declarative, such as a __pre_declare__() method of some kind, it might be
the only approach.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web
visithttps://groups.google.com/d/msg/sqlalchemy/-/MH4tZazKT0EJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: automating inheritance
Ok, so speed might become an issue for me as well;
Do you think a similar metaclass approach would work for concrete
inheritance would work without major drawbacks (before i do a major
overhaul)?
Is there any indication about how much faster concrete inheritance is,
compared to joined inheritance?
Cheers, Lars
On Friday, April 20, 2012 9:32:49 PM UTC+2, Michael Bayer wrote:
On Apr 20, 2012, at 8:51 AM, lars van gemerden wrote:
Ok, thank you, that helps, but now i cannot inherit from Engineer, as in:
class BaseMixin(object):
discriminator = Column(String(50))
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
return Column(Integer, primary_key = True)
@declared_attr
def __mapper_args__(cls):
if not has_inherited_table(cls):
return {'polymorphic_on': 'discriminator'}
else:
return {'polymorphic_identity': cls.__name__}
class InheritMixin(BaseMixin):
@declared_attr
def id(cls):
super_id = super(InheritMixin, cls).id
return Column(Integer, ForeignKey(super_id), primary_key = True)
class Person(BaseMixin, Base):
name = Column(String(50))
class Engineer(InheritMixin, Person):
job = Column(String(50))
class MasterEngineer(InheritMixin, Engineer):
specialty = Column(String(50))
Gives an MRO() error and if i would reverse the baseclasses (like class
Engineer(Person, InheritMixin): ... ), the inheriting classes pick up the
wrong id.
Do you see any solution for this?
yeah I suppose if you're building out joined inheritance more than one
level then this becomes awkward. I never use joined inh more than one
level because it has too much of an impact on queries.
the metaclass as you mention is always the last resort when the various
declarative trickery reaches its limit. I'm not thrilled about the
metaclass approach because it quickly gets confusing and shouldn't be
necessary. though in this case without some extra mechanism on
declarative, such as a __pre_declare__() method of some kind, it might be
the only approach.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/MH4tZazKT0EJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: automating inheritance
this is the testcase:
from sqlalchemy import *
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base, declared_attr
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base(bind = engine)
Session = sessionmaker(bind = engine)
def setup(engine):
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
return Session()
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
if Base in cls.__bases__:
print 'base in id(cls): ', cls
return Column(Integer, primary_key = True)
else:
print 'in id(cls): ', cls, cls.__bases__[0]
return Column(Integer, ForeignKey(cls.__bases__[0].id),
primary_key = True)
@declared_attr
def __mapper_args__(cls):
if Base in cls.__bases__:
print 'base in __mapper_args__(cls): ', cls
return {'polymorphic_on': 'discriminator'}
else:
print 'in __mapper_args__(cls): ', cls, cls.__bases__[0],
cls.id, (cls.id is cls.__bases__[0].id)
return {'polymorphic_identity': cls.__name__}
class Person(Base, InheritMixin):
discriminator = Column(String(50))
name = Column(String(50))
class Engineer(Person):
job = Column(String(50))
if __name__ == '__main__':
session = setup(engine)
a = Person(name = 'ann')
b = Engineer(name = 'bob', job = 'car repair')
session.add_all([a, b])
session.commit()
people = session.query(Person).all()
print people
Note that i left out the 'inherit_condition', because without there is
already a problem:
base in id(cls): class '__main__.Person'
base in mapper_args(cls): class '__main__.Person'
in mapper_args(cls): class '__main__.Engineer' class
'__main__.Person' Person.id True
Traceback (most recent call last):
File D:\Documents\Code\Eclipse\workspace\process_data3\src
\little_tests2.py, line 40, in module
class Engineer(Person):
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1336, in __init__
_as_declarative(cls, classname, cls.__dict__)
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1329, in _as_declarative
**mapper_args)
File C:\Python27\lib\site-packages\sqlalchemy\orm\__init__.py,
line 1116, in mapper
return Mapper(class_, local_table, *args, **params)
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
197, in __init__
self._configure_inheritance()
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
473, in _configure_inheritance
self.local_table)
File C:\Python27\lib\site-packages\sqlalchemy\sql\util.py, line
303, in join_condition
between '%s' and '%s'.%s % (a.description, b.description, hint))
sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships
between 'Person' and 'Engineer'.
What am i missing?
Cheers, Lars
On Apr 19, 4:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 19, 2012, at 6:23 AM, lars van gemerden wrote:
I am trying to my my joined inheritance code clearer, for the dynamic
generation of sa classes and tried to do something like this:
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
if cls.__name__ == 'Object':
return Column(Integer, primary_key = True)
else:
print 'in id: ', cls.__name__, cls.__bases__[0].__name__
return Column(Integer,
ForeignKey(cls.__bases__[0].__name__ + '.id'), primary_key = True)
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'Object':
return {'polymorphic_on': 'discriminator'}
else:
print 'in mapper_args: ', cls.__name__,
cls.__bases__[0].__name__
return {'polymorphic_identity': cls.__name__,
'inherit_condition': (cls.id ==
cls.__bases__[0].id)}
Object = type('Object', (Base, InheritMixin), clsdict)
Where Object should be the (not necessarily direct) baseclass of all
inheriting classes. However I get errors: Mapper Mapper|person|person
could not assemble any primary key columns for mapped table 'Join
object on Object(65389120) and person(65428224)' etc ..
im not sure of the cause of that error, can you attach a full test case which
illustrates this message being generated ?
I noticed that the method __mapper_args__(cls) is always called before
id(cls) (which is never called, probably due to the error.
the __mapper_args__(cls) method here directly calls upon .id, so if you see
.id() not being called it suggests some other form of .id is being used.
Is it possible that Base or something else has a conflicting id attribute?
Also, is there a way to add the discriminator column to the mixin (if
i just directly add
Re: [sqlalchemy] Re: automating inheritance
Ok, thank you, that helps, but now i cannot inherit from Engineer, as in:
class BaseMixin(object):
discriminator = Column(String(50))
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
return Column(Integer, primary_key = True)
@declared_attr
def __mapper_args__(cls):
if not has_inherited_table(cls):
return {'polymorphic_on': 'discriminator'}
else:
return {'polymorphic_identity': cls.__name__}
class InheritMixin(BaseMixin):
@declared_attr
def id(cls):
super_id = super(InheritMixin, cls).id
return Column(Integer, ForeignKey(super_id), primary_key = True)
class Person(BaseMixin, Base):
name = Column(String(50))
class Engineer(InheritMixin, Person):
job = Column(String(50))
class MasterEngineer(InheritMixin, Engineer):
specialty = Column(String(50))
Gives an MRO() error and if i would reverse the baseclasses (like class
Engineer(Person, InheritMixin): ... ), the inheriting classes pick up the
wrong id.
Do you see any solution for this?
BTW: could i just move
@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': cls.__name__}
to InheritMixin instead of doing the 'has_inherited_table' if-statement in
BaseMixin?
Cheers, Lars
On Friday, April 20, 2012 12:41:28 PM UTC+2, Michael Bayer wrote:
On Apr 20, 2012, at 4:59 AM, lars van gemerden wrote:
this is the testcase:
What am i missing?
the issue here is one of Python inheritance mechanics. Declarative calls
upon @declared_attr in terms of the class, that is, we look through the
class to find each @declared_attr, but when we find one, we invoke it by
just calling it as a method, that is, getattr(cls, name). This works for
things like __mapper_args__ which remain as callable methods on classes
like Person, Engineer. But id, when that is declared on Person is
immediately replaced with a mapping. By the time you get to Engineer, the
id() method is gone.
So for inheriting cases you need to build a mixin that is applied to every
subclass. This makes sense because a mixin with a column on it implies
that the column is being associated only with that immediate class - if you
wanted a subclass to act as single table inheritance, you'd omit this
class. In this case you want the same column on all subclasses.So you
can do it like this (note also using declarative.has_inherited_table
helper):
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
return Column(Integer, primary_key = True)
@declared_attr
def __mapper_args__(cls):
if not has_inherited_table(cls):
return {'polymorphic_on': 'discriminator'}
else:
return {'polymorphic_identity': cls.__name__}
class Inherits(InheritMixin):
@declared_attr
def id(cls):
super_id = super(Inherits, cls).id
return Column(Integer, ForeignKey(super_id),primary_key = True)
class Person(InheritMixin, Base):
discriminator = Column(String(50))
name = Column(String(50))
class Engineer(Inherits, Person):
job = Column(String(50))
this should be in the docs so I've added ticket #2471 to handle this.
Cheers, Lars
On Apr 19, 4:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 19, 2012, at 6:23 AM, lars van gemerden wrote:
I am trying to my my joined inheritance code clearer, for the dynamic
generation of sa classes and tried to do something like this:
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
if cls.__name__ == 'Object':
return Column(Integer, primary_key = True)
else:
print 'in id: ', cls.__name__, cls.__bases__[0].__name__
return Column(Integer,
ForeignKey(cls.__bases__[0].__name__ + '.id'), primary_key = True)
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'Object':
return {'polymorphic_on': 'discriminator'}
else:
print 'in mapper_args: ', cls.__name__,
cls.__bases__[0].__name__
return {'polymorphic_identity': cls.__name__,
'inherit_condition': (cls.id ==
cls.__bases__[0].id)}
Object = type('Object', (Base, InheritMixin), clsdict)
Where Object should be the (not necessarily direct) baseclass of all
inheriting classes. However I get errors: Mapper Mapper|person|person
could not assemble any primary key columns for mapped table 'Join
object on Object(65389120) and person(65428224)' etc ..
im not sure of the cause of that error, can you attach a full test case
which illustrates this message being generated ?
I noticed
Re: [sqlalchemy] Re: automating inheritance
Hi Mike,
How about this approach with a custom metaclass; so far it works and seems
the cleanest to me:
from sqlalchemy import *
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base,
has_inherited_table, DeclarativeMeta
engine = create_engine('sqlite:///:memory:', echo=False)
class InheritMeta(DeclarativeMeta):
def __init__(cls, name, bases, clsdict):
cls.__tablename__ = cls.__name__
if not has_inherited_table(cls):
cls.id = Column(Integer, primary_key = True)
cls.discriminator = Column(String(50))
cls.__mapper_args__ = {'polymorphic_on': 'discriminator'}
else:
cls.id = Column(Integer, ForeignKey(bases[0].id), primary_key =
True)
cls.__mapper_args__ = {'polymorphic_identity': cls.__name__}
super(InheritMeta, cls).__init__(name, bases, clsdict)
InheritableBase = declarative_base(bind = engine, metaclass = InheritMeta)
class Person(InheritableBase):
name = Column(String(50))
class Engineer(Person):
job = Column(String(50))
class MasterEngineer(Engineer):
specialty = Column(String(50))
if __name__ == '__main__':
InheritableBase.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
a = Person(name = 'ann')
b = Engineer(name = 'bob', job = 'car repair')
c = MasterEngineer(name = 'carl', job = 'car repair', specialty =
'tires')
session.add_all([a, b, c])
session.commit()
people = session.query(Person).all()
print people
Do you see any drawbacks, gotchas for later on?
Regards, Lars
On Friday, April 20, 2012 12:41:28 PM UTC+2, Michael Bayer wrote:
On Apr 20, 2012, at 4:59 AM, lars van gemerden wrote:
this is the testcase:
What am i missing?
the issue here is one of Python inheritance mechanics. Declarative calls
upon @declared_attr in terms of the class, that is, we look through the
class to find each @declared_attr, but when we find one, we invoke it by
just calling it as a method, that is, getattr(cls, name). This works for
things like __mapper_args__ which remain as callable methods on classes
like Person, Engineer. But id, when that is declared on Person is
immediately replaced with a mapping. By the time you get to Engineer, the
id() method is gone.
So for inheriting cases you need to build a mixin that is applied to every
subclass. This makes sense because a mixin with a column on it implies
that the column is being associated only with that immediate class - if you
wanted a subclass to act as single table inheritance, you'd omit this
class. In this case you want the same column on all subclasses.So you
can do it like this (note also using declarative.has_inherited_table
helper):
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
return Column(Integer, primary_key = True)
@declared_attr
def __mapper_args__(cls):
if not has_inherited_table(cls):
return {'polymorphic_on': 'discriminator'}
else:
return {'polymorphic_identity': cls.__name__}
class Inherits(InheritMixin):
@declared_attr
def id(cls):
super_id = super(Inherits, cls).id
return Column(Integer, ForeignKey(super_id),primary_key = True)
class Person(InheritMixin, Base):
discriminator = Column(String(50))
name = Column(String(50))
class Engineer(Inherits, Person):
job = Column(String(50))
this should be in the docs so I've added ticket #2471 to handle this.
Cheers, Lars
On Apr 19, 4:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 19, 2012, at 6:23 AM, lars van gemerden wrote:
I am trying to my my joined inheritance code clearer, for the dynamic
generation of sa classes and tried to do something like this:
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
if cls.__name__ == 'Object':
return Column(Integer, primary_key = True)
else:
print 'in id: ', cls.__name__, cls.__bases__[0].__name__
return Column(Integer,
ForeignKey(cls.__bases__[0].__name__ + '.id'), primary_key = True)
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'Object':
return {'polymorphic_on': 'discriminator'}
else:
print 'in mapper_args: ', cls.__name__,
cls.__bases__[0].__name__
return {'polymorphic_identity': cls.__name__,
'inherit_condition': (cls.id ==
cls.__bases__[0].id)}
Object = type('Object', (Base, InheritMixin), clsdict)
Where Object should be the (not necessarily direct) baseclass of all
inheriting classes. However I get errors: Mapper Mapper|person|person
[sqlalchemy] automating inheritance
I am trying to my my joined inheritance code clearer, for the dynamic
generation of sa classes and tried to do something like this:
class InheritMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
@declared_attr
def id(cls):
if cls.__name__ == 'Object':
return Column(Integer, primary_key = True)
else:
print 'in id: ', cls.__name__, cls.__bases__[0].__name__
return Column(Integer,
ForeignKey(cls.__bases__[0].__name__ + '.id'), primary_key = True)
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'Object':
return {'polymorphic_on': 'discriminator'}
else:
print 'in mapper_args: ', cls.__name__,
cls.__bases__[0].__name__
return {'polymorphic_identity': cls.__name__,
'inherit_condition': (cls.id ==
cls.__bases__[0].id)}
Object = type('Object', (Base, InheritMixin), clsdict)
Where Object should be the (not necessarily direct) baseclass of all
inheriting classes. However I get errors: Mapper Mapper|person|person
could not assemble any primary key columns for mapped table 'Join
object on Object(65389120) and person(65428224)' etc ..
I noticed that the method __mapper_args__(cls) is always called before
id(cls) (which is never called, probably due to the error.
Is there some way to fix this, while keeping the inheritance code in a
mixin?
Also, is there a way to add the discriminator column to the mixin (if
i just directly add it to the declaration, this gave another maybe
related error)?
Cheers, Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: dynamically adding a validates method
Thank you, i got it working now. For future reference: Before creating a sa class with something like: type(typename, (Base,), classdict) I first create the classdict in which I define columns, __tablename__, etc, and validators (relationships I add later). The validators i create(using a subclass of dict as classdict in the type() call, and typedef being an object holding the definition to be used to create a sa class/table) with: def createValidator(self, typedef): # get names of attributes to be validated: names = [attr.name for attr in typedef.attributes \ if attr.validates()] #create validator method from validating method in the attr #definition in typedef: def validator(obj, name, value): return typedef[name].validate(value) #turn into sa @validate descriptor (i don't get the #mechanics completely, but still): validates(*names)(validator) #add to classdict self['validator'] = validator Hope this helps someone, open to questions ... Cheers, Lars On Apr 15, 7:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: If you can't establish the event at class declaration time, then the @event.listens_for/event.listen() paradigm (seehttp://docs.sqlalchemy.org/en/latest/core/event.htmlfor background) can be applied to the class-bound attribute (which here is Positive.value) at any time. Positive.value is an attribute generated by the mapping which is a result of using the declarative base. On Apr 15, 2012, at 1:10 PM, lars van gemerden wrote: OK, this helps, so how do i do this if i do not know the name of the attribute to be checked in advance(the value in @validates(value) )? On Apr 15, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: using type() is equivalent to using a class declaration. The end result is the same, as are the mechanics of what goes on both from a Python as well as a SQLAlchemy perspective. So this works: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 and also, since type() is equivalent to a class declaration, you can still use @validates, if you pass the function into the class dictionary, so that it is part of the class before declarative sends it off to mapper(): @validates(value) def checkvalue(self, name, value): assert value 0 return value Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer), checkvalue=checkvalue)) On Apr 15, 2012, at 12:37 PM, lars van gemerden wrote: I don't know what @validates hangs a marker of the method that mapper() uses when it instruments the class means. I guess my question now becomes: How do I add the event.listens_for descriptor to the class, since i do not have a class declaration in the traditional sense? On Apr 15, 4:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: @validates hangs a marker of the method that mapper() uses when it instruments the class, so if the class is already mapped then that train has left the station. Taking a cab instead, you can just add the attribute event directly: @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 if you want to return a new, mutated value then add retval=True to listens_for(). On Apr 15, 2012, at 8:22 AM, lars van gemerden wrote: Hi, I need a way to dynamically add a validates method to a already created sqla class. In a normal class declaration you can add a validator by: class Positive(Base): __tablename__ = positives value = Column(Integer) def checkvalue(self, name, value): assert value 0 return value validates(value)(checkvalue) However if you get the class dynamically: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) I can't figure out how to add the validator, either in the type() call or afterwards. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You
[sqlalchemy] Re: dynamically adding a validates method
PS: the validate(value) method in typedef[name] is a normal method, throwing exception on errors and returning the value when OK. On Apr 16, 2:01 pm, lars van gemerden l...@rational-it.com wrote: Thank you, i got it working now. For future reference: Before creating a sa class with something like: type(typename, (Base,), classdict) I first create the classdict in which I define columns, __tablename__, etc, and validators (relationships I add later). The validators i create(using a subclass of dict as classdict in the type() call, and typedef being an object holding the definition to be used to create a sa class/table) with: def createValidator(self, typedef): # get names of attributes to be validated: names = [attr.name for attr in typedef.attributes \ if attr.validates()] #create validator method from validating method in the attr #definition in typedef: def validator(obj, name, value): return typedef[name].validate(value) #turn into sa @validate descriptor (i don't get the #mechanics completely, but still): validates(*names)(validator) #add to classdict self['validator'] = validator Hope this helps someone, open to questions ... Cheers, Lars On Apr 15, 7:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: If you can't establish the event at class declaration time, then the @event.listens_for/event.listen() paradigm (seehttp://docs.sqlalchemy.org/en/latest/core/event.htmlforbackground) can be applied to the class-bound attribute (which here is Positive.value) at any time. Positive.value is an attribute generated by the mapping which is a result of using the declarative base. On Apr 15, 2012, at 1:10 PM, lars van gemerden wrote: OK, this helps, so how do i do this if i do not know the name of the attribute to be checked in advance(the value in @validates(value) )? On Apr 15, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: using type() is equivalent to using a class declaration. The end result is the same, as are the mechanics of what goes on both from a Python as well as a SQLAlchemy perspective. So this works: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 and also, since type() is equivalent to a class declaration, you can still use @validates, if you pass the function into the class dictionary, so that it is part of the class before declarative sends it off to mapper(): @validates(value) def checkvalue(self, name, value): assert value 0 return value Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer), checkvalue=checkvalue)) On Apr 15, 2012, at 12:37 PM, lars van gemerden wrote: I don't know what @validates hangs a marker of the method that mapper() uses when it instruments the class means. I guess my question now becomes: How do I add the event.listens_for descriptor to the class, since i do not have a class declaration in the traditional sense? On Apr 15, 4:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: @validates hangs a marker of the method that mapper() uses when it instruments the class, so if the class is already mapped then that train has left the station. Taking a cab instead, you can just add the attribute event directly: @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 if you want to return a new, mutated value then add retval=True to listens_for(). On Apr 15, 2012, at 8:22 AM, lars van gemerden wrote: Hi, I need a way to dynamically add a validates method to a already created sqla class. In a normal class declaration you can add a validator by: class Positive(Base): __tablename__ = positives value = Column(Integer) def checkvalue(self, name, value): assert value 0 return value validates(value)(checkvalue) However if you get the class dynamically: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) I can't figure out how to add the validator, either in the type() call or afterwards. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed
[sqlalchemy] dynamically adding a validates method
Hi, I need a way to dynamically add a validates method to a already created sqla class. In a normal class declaration you can add a validator by: class Positive(Base): __tablename__ = positives value = Column(Integer) def checkvalue(self, name, value): assert value 0 return value validates(value)(checkvalue) However if you get the class dynamically: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) I can't figure out how to add the validator, either in the type() call or afterwards. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: dynamically adding a validates method
I don't know what @validates hangs a marker of the method that mapper() uses when it instruments the class means. I guess my question now becomes: How do I add the event.listens_for descriptor to the class, since i do not have a class declaration in the traditional sense? On Apr 15, 4:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: @validates hangs a marker of the method that mapper() uses when it instruments the class, so if the class is already mapped then that train has left the station. Taking a cab instead, you can just add the attribute event directly: @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 if you want to return a new, mutated value then add retval=True to listens_for(). On Apr 15, 2012, at 8:22 AM, lars van gemerden wrote: Hi, I need a way to dynamically add a validates method to a already created sqla class. In a normal class declaration you can add a validator by: class Positive(Base): __tablename__ = positives value = Column(Integer) def checkvalue(self, name, value): assert value 0 return value validates(value)(checkvalue) However if you get the class dynamically: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) I can't figure out how to add the validator, either in the type() call or afterwards. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: dynamically adding a validates method
OK, this helps, so how do i do this if i do not know the name of the attribute to be checked in advance(the value in @validates(value) )? On Apr 15, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: using type() is equivalent to using a class declaration. The end result is the same, as are the mechanics of what goes on both from a Python as well as a SQLAlchemy perspective. So this works: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 and also, since type() is equivalent to a class declaration, you can still use @validates, if you pass the function into the class dictionary, so that it is part of the class before declarative sends it off to mapper(): @validates(value) def checkvalue(self, name, value): assert value 0 return value Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer), checkvalue=checkvalue)) On Apr 15, 2012, at 12:37 PM, lars van gemerden wrote: I don't know what @validates hangs a marker of the method that mapper() uses when it instruments the class means. I guess my question now becomes: How do I add the event.listens_for descriptor to the class, since i do not have a class declaration in the traditional sense? On Apr 15, 4:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: @validates hangs a marker of the method that mapper() uses when it instruments the class, so if the class is already mapped then that train has left the station. Taking a cab instead, you can just add the attribute event directly: @event.listens_for(Positive.value, set) def checkvalue(target, value, oldvalue, initiator) assert value 0 if you want to return a new, mutated value then add retval=True to listens_for(). On Apr 15, 2012, at 8:22 AM, lars van gemerden wrote: Hi, I need a way to dynamically add a validates method to a already created sqla class. In a normal class declaration you can add a validator by: class Positive(Base): __tablename__ = positives value = Column(Integer) def checkvalue(self, name, value): assert value 0 return value validates(value)(checkvalue) However if you get the class dynamically: Positive = type(Positive, (Base,), dict(__tablename__ = positives, value = Column(Integer))) I can't figure out how to add the validator, either in the type() call or afterwards. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: append to children in hierarchical data
The question was based on a misconception on my part (in a visual
representation of a tree, while dragging a node you drag the whole
subtree with it, so you can't drop a node in it's own subtree).
Sorry about that ...
PS: Checked that when updating the children (via append) the exception
occurs right away (is there a flush happening in the background?),
while when updating the parent, the exception happens during the flush
afterwards.
On Apr 11, 5:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 11, 2012, at 3:34 AM, lars van gemerden wrote:
Hi all,
I am looking for a way to append a child to a tree like structure. I
am using adjacency to represent the tree.
Let's say I have a relationship configured with:
children = relationship(TreeNode,
primaryjoin = TreeNode.id ==
TreeNode.parent_id,
backref = backref( parent,
remote_side=[TreeNode.id]),
cascade = all, delete-orphan,
use_list = True)
and I do:
t1 = TreeNode()
t2 = TreeNode(parent = t1)
t3 = TreeNode(parent = t2)
then i can set:
t1.parent = t3,
without problem, but:
t3.children.append(t1)
gives a CircularDependencyError, while i would like this to give the
same result as t1.parent = t3, perhaps by altering the behaviour of
Treenode.children.append.
Am I missing something?
Is there some way to arrange for this?
t1.parent = t3 means you aren't persisting a tree anymore - it's no longer a
hierarchy, since t3 is a descendant of t1 already via t2. It's a cycle.
The exception on the children side is probably because mutating children
has the effect of both t3 and t1 being considered in the flush (due to
t3.children as well as t1.parent_id changing), whereas t1.parent = t3 does
not actually consider t3 to modified. You'd still get a cycle if you
created everything via x.parent=y at once (below I just put it into the
adjacency_list.py example - boom):
node = TreeNode('rootnode')
n1 = TreeNode('node1', parent=node)
n2 = TreeNode('node2', parent=n1)
node.parent = n2
session.add(node)
session.flush()
sqlalchemy.exc.CircularDependencyError: Circular dependency detected. Cycles:
...
though I wasn't able to reproduce the behavior being different by mutating
children vs. the parentI'm sure with more digging I could figure that
out. But in any case, you aren't safe from cycles if you're building them,
the usual way to insert rows with cycles to themselves is to use the
post_update feature described
athttp://docs.sqlalchemy.org/en/latest/orm/relationships.html#rows-that
You probably need to put it on both sides in this case as the unit of work
code will consider one or both of the children or parent relationships in
a particular flush depending on where it sees changes.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] append to children in hierarchical data
Hi all, I am looking for a way to append a child to a tree like structure. I am using adjacency to represent the tree. Let's say I have a relationship configured with: children = relationship(TreeNode, primaryjoin = TreeNode.id == TreeNode.parent_id, backref = backref( parent, remote_side=[TreeNode.id]), cascade = all, delete-orphan, use_list = True) and I do: t1 = TreeNode() t2 = TreeNode(parent = t1) t3 = TreeNode(parent = t2) then i can set: t1.parent = t3, without problem, but: t3.children.append(t1) gives a CircularDependencyError, while i would like this to give the same result as t1.parent = t3, perhaps by altering the behaviour of Treenode.children.append. Am I missing something? Is there some way to arrange for this? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: return type of relationship
Thanks, Is there a more direct way to do this for the Column return type as well? Now i have: @staticmethod def column(cls, name): for cl in cls.mro(): if hasattr(cl, '__table__'): for c in cl.__table__.c: if c.name == name: return c @staticmethod def type(obj, name): try: return XSALfactory.column(type(obj), name).type.python_type except AttributeError: return type(getattr(obj, name)) (Without the relationship type implemented yet.) On Mar 30, 1:38 am, Michael Bayer mike...@zzzcomputing.com wrote: we're working on a more documented API for getting info like this but at the moment it would be: MyClass.someattribute.property.mapper.class_ On Mar 29, 2012, at 7:16 PM, lars van gemerden wrote: I am generating xml from SAclass objects and need to get the return type (referred SA class) of an relationship attribute (even if the returned value is None). The type will be used as tagname during XML generation. Can anyone tell me how to get this type? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: help on SAWarning
Found another bug and the warning is gone; can't pinpoint why though ... On Mar 30, 1:03 am, lars van gemerden l...@rational-it.com wrote: OK, thanks, I still get the warning in some cases and i am still zooming in on the problem for a small test case. At least i have solved the problem of the id of InstrumentedLists changing after commit(by bypassing one layer of recursion in the xml tree parsing, i am not looking up the id of the IList anymore). Cheers On Mar 29, 4:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 29, 2012, at 9:50 AM, lars van gemerden wrote: I have found that this specific behaviour (re-initialization of the InstrumentedList) does not happen if expire_on_commit = False is set on the sesssion. However this leads to other errors in my code. I guess I don't quite understand why expire would be called after a commit; shouldn't the commit result in the session being perfectly synchronized with the DB? It does. The expire of the attributes doesn't cause any SQL to be emitted until you access the attributes, in which case a new transaction is begun (note the DBAPI's default mode of operation is to always be in a transaction, and SQLAlchemy does not challenge this). In between your commit() and the start of a new transaction, any amount of state within the database may have been changed by other transactions. If expire_on_commit=False is leading to errors, that would imply that this is actually happening in your case. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: help on SAWarning
I think I have an odd case of the first possibility you mentioned: I am generating XML from SAclass objects opdating these objects from XML. To detect cyclic references and double objects, I maintain a registry of objects based on id() (reg[str(id(obj))] = obj). The id is stored as the attribute ID in the XML tag. When updating the objects I first look them up in the registry obj = reg[etree.get(ID)] and update them afterwards. I do this as well with relationships/ InstrumentedLists (but not with database fields like Integer). What I noticed was that InstrumentedList changes id() after a commit (with a = Address(), Address.persons = relationship(...) ): print id(a), id(a.persons), id(a.persons[0]) session.commit() print id(a), id(a.persons), id(a.persons[0]) gives: 47148168 47174264 47114112 47148168 47175784 47114112 (the middle id has changed) So I assume that after a commit a new InstrumentedList is created. - Could it be conceivable that is causes the warning mentioned above(an old version of the InstrumentedList being updated with an valid SAclass object)? - Is there a way to get around this new INstrumetnedList being created? Cheers, Lars On Mar 29, 1:53 am, Michael Bayer mike...@zzzcomputing.com wrote: you've got a situation like this: ft = session.query(FieldType).first() f = Field() ft.of_members.append(f) assert f in session # normally returns True, assuming normal cascade on of_members session.expunge(f) # remove f from the session session.flush() # generates warning, since ft.of_members has changed but the contents aren't in the Session Another cause could be, of_members has a cascade that doesn't include save-update, like cascade='delete' is a common mistake. On Mar 28, 2012, at 6:45 PM, lars van gemerden wrote: Hi all, I am getting the error: SAWarning: Object of type Field not in session, add operation along 'FieldType.of_members' will not proceed and have no idea where to start looking for the error. Can anyone help me with some idea of why this warning occurs or what direction to look in order to fix it? The warning occurs only during the first call of a specific method, while i think i reset everything (e.g. dropping all tables in the database) between calls. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: help on SAWarning
OK, thanks, I still get the warning in some cases and i am still zooming in on the problem for a small test case. At least i have solved the problem of the id of InstrumentedLists changing after commit(by bypassing one layer of recursion in the xml tree parsing, i am not looking up the id of the IList anymore). Cheers On Mar 29, 4:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 29, 2012, at 9:50 AM, lars van gemerden wrote: I have found that this specific behaviour (re-initialization of the InstrumentedList) does not happen if expire_on_commit = False is set on the sesssion. However this leads to other errors in my code. I guess I don't quite understand why expire would be called after a commit; shouldn't the commit result in the session being perfectly synchronized with the DB? It does. The expire of the attributes doesn't cause any SQL to be emitted until you access the attributes, in which case a new transaction is begun (note the DBAPI's default mode of operation is to always be in a transaction, and SQLAlchemy does not challenge this). In between your commit() and the start of a new transaction, any amount of state within the database may have been changed by other transactions. If expire_on_commit=False is leading to errors, that would imply that this is actually happening in your case. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] return type of relationship
I am generating xml from SAclass objects and need to get the return type (referred SA class) of an relationship attribute (even if the returned value is None). The type will be used as tagname during XML generation. Can anyone tell me how to get this type? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: names of foreign keys from unknown table/class (short question)
Thanks you Michael (i thought i remembered that the name attribute of the column wasn't set unless explicitely given to the Column constructor, the one thing i didn't check ... ) On Mar 26, 3:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: Column has a collection foreign_keys, a collection since a column in a relational database can have any number of foreign key constraints applied to it. If this collection is non-empty, then that Column is associated with a ForeignKey and thus a ForeignKeyConstraint (note that a ForeignKey is ultimately an element in a larger ForeignKeyConstraint object). Not sure what you mean by the name - assuming you mean the name of the column it refers to, ForeignKey has an accessor .column that will give you the Column to which it refers to: http://docs.sqlalchemy.org/en/latest/core/schema.html?highlight=forei... That gives you the Column itself with a .name attribute. If by name you mean the name of the constraint itself, like for the purposes of ADD CONSTRAINT/DROP CONSTRAINT, the ForeignKey object of each Column is by default unnamed - in relational DDL, you don't need to give explicit names to FK constraints, the database will name these automatically. So unless you give them a name, the name can't be determined on the Python side. When reflecting a table, the ForeignKeyConstraint is reflected along with the name, if the database supports giving us the name, and the name should be associated with each associated ForeignKey object. On Mar 25, 2012, at 7:29 PM, lars van gemerden wrote: Hello, Does anyone know a way get the names of the foreign key columns of a table, if the table/class is unknown beforehand? I tried with Column.foreign_keys, which gives a set (why?) of ForeignKey objects but the name attribute of ForeignKey is not automatically set. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] names of foreign keys from unknown table/class (short question)
Hello, Does anyone know a way get the names of the foreign key columns of a table, if the table/class is unknown beforehand? I tried with Column.foreign_keys, which gives a set (why?) of ForeignKey objects but the name attribute of ForeignKey is not automatically set. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: xml approach
Hi Julian, Thanks, I am looking into it and it looks interesting. Have you done much testing yet? How do you (plan to; haven't looked at too much detail yet) check for circular references (like backrefs)? Cheers, Lars On Thursday, March 22, 2012 11:36:41 AM UTC+1, lars van gemerden wrote: Hi, I am looking for a way to convert SQLalchemy objects to XML and back, in order to support a webapplication. I made a mixin class that does a decent job on 'normal' python objects (basically works for in the same cases as pickle, though more testing is required). I would prefer to have a simple mixin interface with something like: def toXML(): 'convert attributes to xml' return xmlstring def fromXML(xmlstring) 'update attributes from xml' The most simple case would be that the web user request indicates the primary key of the object, gets the corrsponding xml string in return, changes fields in the xml string and posts it back, which leads to an update of the object. I have formulated a couple of initial questions: - what attributes should be converted to and from xml? - how do i handle foreign keys on the 'other' side? Note that the object remains in memory during the user interaction, so it does not have to be reinitialized from scratch. Cheers, Lars -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QNBLxpPxDBgJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: short question about primary keys
Perfect, thank you! On Mar 23, 10:09 pm, Benjamin Trofatter bentrofat...@gmail.com wrote: Not knowing the names of the primary keys shouldn't be a problem since they're pretty easy to figure out with the object_mapper. from sqlalchemy.orm import object_mapper class MyMixin(object): def get_pk(self): om = object_mapper(self) keys = [k.key for k in om.primary_key] return tuple(getattr(self, k) for k in keys) On Fri, Mar 23, 2012 at 2:48 PM, lars van gemerden l...@rational-it.comwrote: Hi, Does anyone know a way get the the value(s) of the primary key(s), if you don't know the name of the primary key column(s). This to be used in a mixin class that can be used for different tables/classes. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] xml approach
Hi, I am looking for a way to convert SQLalchemy objects to XML and back, in order to support a webapplication. I made a mixin class that does a decent job on 'normal' python objects (basically works for in the same cases as pickle, though more testing is required). I would prefer to have a simple mixin interface with something like: def toXML(): 'convert attributes to xml' return xmlstring def fromXML(xmlstring) 'update attributes from xml' The most simple case would be that the web user request indicates the primary key of the object, gets the corrsponding xml string in return, changes fields in the xml string and posts it back, which leads to an update of the object. I have formulated a couple of initial questions: - what attributes should be converted to and from xml? - how do i handle foreign keys on the 'other' side? Note that the object remains in memory during the user interaction, so it does not have to be reinitialized from scratch. Cheers, Lars -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QaC2CW65FVgJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: deleting a sqlite database
Thank you for the extensive reply. It makes things a lot clearer; still i am not sure about how to continue. Conceptually i would like to create 2 sets of tables/classes in a database (as part of a prototype): 1) one set of tables/classes with the parameters to generate other classes/tables from, 2) one set of tables/classes that is automatically generated from the parameters in the first set. It will feature joined inheritance with only one root base table/class. The only database link between these two sets is the 'polymorphic on' column in the root base table in set 2, which is a foreign key to a Type table in set 1. For a typical test i would like to: 1) create records in set 1 of tables (representing classes/tables with their attributes/foreign keys and fields), 2) from these records generate the tables/classes, where the tables will be in set 2. 3) add records to the generated tables/classes and test whether adding, updating, deleting and querying works as intended. To be able to perform multiple of these tests in one run, i need to empty the tables of set 1. However i need to completely remove any data (mappings, class definitions, records, tables) from set 2, between individual tests. I (naively) thought of some ways this might be possible: 1) use two separate metadata objects for the same database, bind them to separate 'Base' classes, one for each set and replace the one representing set 2 before each individual test, 2) find some way to remove all data concerning set 2 of tables from mappings, metadata, database, etc. between tests, 3) use two databases, one for each set of tables and forego the foreign key realtionship between then (or maybe copy set 1 to the second database) Please advise on which of these approaches are possible, more straightforward, ... or whether another approach might be more appropriate. Cheers, Lars On Feb 26, 10:47 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 26, 2012, at 12:47 PM, lars van gemerden wrote: I was wrong, the method emptied the database, but I was checking the tables in the metadata. This time I am also removing the tables from the metadata, but if i generate the same tables in two separate test methods (with a call to tearDown ans setUp in between), I still get an error about a backref name on a relationship already existing. OK I think you're mixing concepts up here, a backref is an ORM concept. The Table and Metadata objects are part of Core and know absolutely nothing about the ORM or mappings. Removing a Table from a particular MetaData has almost no effect as all the ORM mappings still point to it. In reality the MetaData.remove() method is mostly useless, except that a create_all() will no longer hit that Table, foreign key references will no longer find it, and you can replace it with a new Table object of the same name, but again nothing to do with the ORM and nothing to do with the state of that removed Table, which still points to that MetaData and will otherwise function normally. If you want to remove mappings, you can call clear_mappers(). The use case for removing individual mappers is not supported as there is no support for doing all the reverse bookkeeping of removing relationships(), backrefs, and inheritance structures, and there's really no need for such a feature. Like MetaData.remove(), there's almost no real world use case for clear_mappers() except that of the SQLAlchemy unit tests themselves, or tests of other ORM-integration layers like Elixir, which are testing the ORM itself with various kinds of mappings against the same set of classes. Unit tests in an outside world application would normally be against a schema that's an integral part of the application, and doesn't change with regards to classes. There's virtually no reason in normal applications against a fixed schema to tear down mappings and table metadata between tests. SQLAlchemy docs stress the Declarative pattern very much these days as we're really trying to get it across that the composition of class, table metadata, and mapping is best regarded as an atomic structure - it exists only as that composite, or not at all. Breaking it apart has little use unless you're testing the mechanics of the mapping itself. Throughout all of this, we are *not* talking about the tables and schema that are in the actual database. It is typical that unit tests do drop all those tables in between test suites, and recreate them for another test suite. Though I tend to favor not actually dropping / recreating and instead running the tests within a transaction that's rolled back at the end as it's much more efficient, especially on backends like Oracle, Postgresql, MSSQL where creates/drops are more expensive. Dropping and recreating the tables in the database though is independent of the structure represented by Metadata/Table, though, that structure
[sqlalchemy] Re: short drop_all question
Actually, now I think about it, i had a couple of reasons: - easy to start with (no separeate installation is i remember correctly) - no decision required about which database (mysql, postgress) to learn - no problem with removal of database (in memory), but that is between runs, not during unit testing Main reason that i want to remove the database, is that i create tables dynamically, from another database with table definitions. To run multiple tests I need to clear the database in between. Lars On Feb 24, 5:14 pm, Chris Withers ch...@simplistix.co.uk wrote: Why are you testing with sqlite? Chris On 24/02/2012 14:32, lars van gemerden wrote: OK, the file isn't closed ofcourse and i can't figure out how to close it. I'll try some more and if nothing works probably start a new post ... Thanks anyway, Chris On Feb 24, 3:02 pm, lars van gemerdenl...@rational-it.com wrote: The recipe didn't work; it collects all tables, but no foreign keys and in the end removes nothing. I am using this in tearDown of unit tests. I have the DB in memory and even create a new engine in setUp ... maybe I am overlooking somthing dumb, but I don't get it. I'll try using a sqlite file and closing and deleting the file in tearDown. Cheers, Lars On Feb 24, 2:28 pm, lars van gemerdenl...@rational-it.com wrote: Thanks very much, Nothing is ever easy ... :-( why not .. :-) On Feb 24, 2:11 pm, Chris Withersch...@simplistix.co.uk wrote: On 24/02/2012 12:40, lars van gemerden wrote: can it be possible that drop_all does not empty the database of there are circular references between tables? You want: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything ...which is available here too: http://packages.python.org/mortar_rdb/api.html#mortar_rdb.drop_tables cheers, Chris PS: I am using sqlite. Why? ;-) -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] deleting a sqlite database
Hi all, I looking for a way to delete/empty a sqlite database completely (can be in memory or file). I have tried: - metadata.drop_all(engine); which does not work with cyclic references - solution from http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything; it requires names of foreign key columns to be returned by the inspector class, however these names are empty - some other ways. I am unittesting dynamic generation of tables/classes dynamically from definitions in another database. To be able to run multiple tests in one run, my tearDown method must delete/empty the database. Any tips or solutions? Have I overlooked something obvious? It seems that it should not be that complicated. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] short drop_all question
can it be possible that drop_all does not empty the database of there are circular references between tables? I am using sqlite. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: short drop_all question
Thanks very much, Nothing is ever easy ... :-( why not .. :-) On Feb 24, 2:11 pm, Chris Withers ch...@simplistix.co.uk wrote: On 24/02/2012 12:40, lars van gemerden wrote: can it be possible that drop_all does not empty the database of there are circular references between tables? You want: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything ...which is available here too: http://packages.python.org/mortar_rdb/api.html#mortar_rdb.drop_tables cheers, Chris PS: I am using sqlite. Why? ;-) -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: short drop_all question
The recipe didn't work; it collects all tables, but no foreign keys and in the end removes nothing. I am using this in tearDown of unit tests. I have the DB in memory and even create a new engine in setUp ... maybe I am overlooking somthing dumb, but I don't get it. I'll try using a sqlite file and closing and deleting the file in tearDown. Cheers, Lars On Feb 24, 2:28 pm, lars van gemerden l...@rational-it.com wrote: Thanks very much, Nothing is ever easy ... :-( why not .. :-) On Feb 24, 2:11 pm, Chris Withers ch...@simplistix.co.uk wrote: On 24/02/2012 12:40, lars van gemerden wrote: can it be possible that drop_all does not empty the database of there are circular references between tables? You want: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything ...which is available here too: http://packages.python.org/mortar_rdb/api.html#mortar_rdb.drop_tables cheers, Chris PS: I am using sqlite. Why? ;-) -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: problem with dynamic tables/classes and inheritance
Seems a finally got what I wanted to achieve. Changed a lot of things,
so I cannot pinpoint the error(s) in the code above. If anyone is
interested I will help/share the solution I found.
Cheers, Lars
On Feb 22, 8:35 pm, lars van gemerden l...@rational-it.com wrote:
I am trying to generate tables/classes dynamically. The code below is
my latest attempt, but I cannot get it to work.
-
class TableName(object):
@declared_attr
def __tablename__(cls): return cls.__name__
class Inherit(object):
@declared_attr
def id(cls): #= is not called for S
base = cls.__bases__[len(cls.__bases__) - 1]
print class, base:, cls.__name__, base.__name__
return Column(Integer, ForeignKey(base.__name__ + '.id'),
primary_key = True)
@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': cls.__name__}
class Object(Base, TableName):
association_tables = {}
id = Column(Integer, primary_key = True)
type_name = Column(String(50), nullable = False)
__mapper_args__ = {'polymorphic_on': type_name}
if __name__ == '__main__':
session = setup(engine)
T = type('T', (Inherit, Object), {'Tdata': Column(String(50))})
S = type('S', (T,), {'Sdata': Column(String(50))}) #= Error
session.commit()
print S.__table__.c
-
the output is:
-
class, base: T Object
class, base: T Object
class, base: T Object
Traceback (most recent call last):
File D:\Documents\Code\Eclipse\workspace\SQLAdata\src\test4.py,
line 55, in module
S = type('S', (T,), {'Sdata': Column(String(50))})
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1336, in __init__
_as_declarative(cls, classname, cls.__dict__)
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1329, in _as_declarative
**mapper_args)
File C:\Python27\lib\site-packages\sqlalchemy\orm\__init__.py,
line 1116, in mapper
return Mapper(class_, local_table, *args, **params)
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
197, in __init__
self._configure_inheritance()
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
473, in _configure_inheritance
self.local_table)
File C:\Python27\lib\site-packages\sqlalchemy\sql\util.py, line
303, in join_condition
between '%s' and '%s'.%s % (a.description, b.description, hint))
sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships
between 'T' and 'S'.
-
What is wrong with this approach. Is there a good way to approach this
problem (I have tried a couple already).
Also: Why is Inherit.id() called 3 times for T
Please help!
Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] order of initialization __mapper_args__ 'inherit_condition' and primary key
Hi all,
I reproduced an error I was having with the code below (basically
pasted from the tutorial for joined inheritance) and added an
'inherit_condition' to __mapper_args__ of the subclass. At that point
the code started throwing a sqlalchemy.exc.InterfaceError. The code
is:
-
from sqlalchemy import *
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base(bind = engine)
Session = sessionmaker(bind = engine)
def setup(engine):
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
return Session()
class Person(Base):
__tablename__ = 'Person'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__tablename__ = 'Engineer'
__mapper_args__ = {'polymorphic_identity': 'Engineer',
'inherit_condition': (id == Person.id)}
id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
if __name__ == '__main__':
session = setup(engine)
e = Engineer()
session.add_all([e])
session.commit()
print e.discriminator
and the error:
Traceback (most recent call last):
File D:\Documents\Code\Eclipse\workspace\SQLAdata\src\test5.py,
line 38, in module
print e.discriminator
File C:\Python27\lib\site-packages\sqlalchemy\orm\attributes.py,
line 168, in __get__
return self.impl.get(instance_state(instance),dict_)
File C:\Python27\lib\site-packages\sqlalchemy\orm\attributes.py,
line 451, in get
value = callable_(passive)
File C:\Python27\lib\site-packages\sqlalchemy\orm\state.py, line
285, in __call__
self.manager.deferred_scalar_loader(self, toload)
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
1701, in _load_scalar_attributes
only_load_props=attribute_names)
File C:\Python27\lib\site-packages\sqlalchemy\orm\query.py, line
2378, in _load_on_ident
return q.one()
File C:\Python27\lib\site-packages\sqlalchemy\orm\query.py, line
2050, in one
ret = list(self)
File C:\Python27\lib\site-packages\sqlalchemy\orm\query.py, line
2093, in __iter__
return self._execute_and_instances(context)
File C:\Python27\lib\site-packages\sqlalchemy\orm\query.py, line
2108, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1405, in execute
params)
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1538, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1646, in _execute_context
context)
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1639, in _execute_context
context)
File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py,
line 330, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding
parameter 0 - probably unsupported type. u'SELECT Person.type AS
Person_type, Engineer.id AS Engineer_id, Person.id AS
Person_id \nFROM Person JOIN Engineer ON Person.id = ? \nWHERE
Person.id = ?' (built-in function id, 1)
-
I fixed the error by moving the id Column in Engineer above the
__mapper_args__ :
-
class Engineer(Person):
__tablename__ = 'Engineer'
id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity': 'Engineer',
'inherit_condition': (id == Person.id)}
-
Now we get to the problem: In my code I dynamically generate tables
with type(name, (base,), dicty). In dicty are the __mapper_args__ and
id Column. Because of the dictionary used I have no control over the
order of the arguments and that seems to cause my exception. If I add
the __mapper_args__ with 'inherit_condition' to the class later, I
get another exception (ArgumentError: Can't determine join between
'person' and 'child'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.)
Could the order in 'dicty' be the problem? Can anyone help me with a
solution or workaround for this problem.
Cheers, Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: order of initialization __mapper_args__ 'inherit_condition' and primary key
OK, thanks, the order wasn't the problem, I am using this for the
initialization of my classes:
dicty= {'__tablename__': name,
'id': Column(Integer, ForeignKey(basename + '.id'),
primary_key = True),
'__mapper_args__': {'polymorphic_identity': name,
'inherit_condition': (id == classes[basename].id)}}
and as you said 'id' in the last line refers to the id() function. I
think I fixed it by changing the code to:
id_ = Column(Integer, ForeignKey(basename + '.id'), primary_key =
True)
out = {'__tablename__': name,
'id': id_,
'__mapper_args__': {'polymorphic_identity': name,
'inherit_condition': (id_ == classes[basename].id)}}
But I am getting a (maybe unrelated) error. Should this solution work?
Cheers, Lars
On Feb 23, 4:07 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 23, 2012, at 9:56 AM, lars van gemerden wrote:
Hi all,
I reproduced an error I was having with the code below (basically
pasted from the tutorial for joined inheritance) and added an
'inherit_condition' to __mapper_args__ of the subclass. At that point
the code started throwing a sqlalchemy.exc.InterfaceError. The code
is:
class Person(Base):
__tablename__ = 'Person'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__tablename__ = 'Engineer'
__mapper_args__ = {'polymorphic_identity': 'Engineer',
'inherit_condition': (id == Person.id)}
id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
Well OK, right here, when you say id, that's the Python built-in function
id(). This because the name id has not yet been assigned to point to
your new Column object. The class Engineer(Person): is just another
Python block, just like an if condition: statement. So yes, in that
case you need to ensure id is pointing to that Column before you stick it
into the inherit_condition. This has nothing to do with the order of
things being looked at in the actual dict of the class, just the order of
how Python is assigning meaning to the name id within the class: block.
The declarative tutorial doesn't have this bug, but yes I can see we might be
better off illustrating __mapper_args__ at the bottom of the class
declaration just to make this kind of error less likely.
Now we get to the problem: In my code I dynamically generate tables
with type(name, (base,), dicty). In dicty are the __mapper_args__ and
id Column. Because of the dictionary used I have no control over the
order of the arguments and that seems to cause my exception.
I'm not getting how you'd have this issue there - you of course have total
control over what goes into a dict() to use with type(), and in what order
(it's only on the taking things out part of a dict that is not ordered).
You should generate the Column object, then you put it in the dictionary.
The id builtin function never gets involved:
id = Column(...)
dict = {
'id':id,
'__mapper_args__':{'inherit_condition':id==Person.id}
}
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: order of initialization __mapper_args__ 'inherit_condition' and primary key
Yep, they are the same class (i don't create any class of which i
don't have the superclass yet).
Thanks for your help; on to the next bug ...
On Feb 23, 5:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 23, 2012, at 10:47 AM, lars van gemerden wrote:
OK, thanks, the order wasn't the problem, I am using this for the
initialization of my classes:
dicty= {'__tablename__': name,
'id': Column(Integer, ForeignKey(basename + '.id'),
primary_key = True),
'__mapper_args__': {'polymorphic_identity': name,
'inherit_condition': (id == classes[basename].id)}}
and as you said 'id' in the last line refers to the id() function. I
think I fixed it by changing the code to:
id_ = Column(Integer, ForeignKey(basename + '.id'), primary_key =
True)
out = {'__tablename__': name,
'id': id_,
'__mapper_args__': {'polymorphic_identity': name,
'inherit_condition': (id_ == classes[basename].id)}}
But I am getting a (maybe unrelated) error. Should this solution work?
that approach seems fine though classes[basename] must be already
established, I might pull it just from the superclass that you're passing
into type().
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] problem with dynamic tables/classes and inheritance
I am trying to generate tables/classes dynamically. The code below is
my latest attempt, but I cannot get it to work.
-
class TableName(object):
@declared_attr
def __tablename__(cls): return cls.__name__
class Inherit(object):
@declared_attr
def id(cls): #= is not called for S
base = cls.__bases__[len(cls.__bases__) - 1]
print class, base:, cls.__name__, base.__name__
return Column(Integer, ForeignKey(base.__name__ + '.id'),
primary_key = True)
@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': cls.__name__}
class Object(Base, TableName):
association_tables = {}
id = Column(Integer, primary_key = True)
type_name = Column(String(50), nullable = False)
__mapper_args__ = {'polymorphic_on': type_name}
if __name__ == '__main__':
session = setup(engine)
T = type('T', (Inherit, Object), {'Tdata': Column(String(50))})
S = type('S', (T,), {'Sdata': Column(String(50))}) #= Error
session.commit()
print S.__table__.c
-
the output is:
-
class, base: T Object
class, base: T Object
class, base: T Object
Traceback (most recent call last):
File D:\Documents\Code\Eclipse\workspace\SQLAdata\src\test4.py,
line 55, in module
S = type('S', (T,), {'Sdata': Column(String(50))})
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1336, in __init__
_as_declarative(cls, classname, cls.__dict__)
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1329, in _as_declarative
**mapper_args)
File C:\Python27\lib\site-packages\sqlalchemy\orm\__init__.py,
line 1116, in mapper
return Mapper(class_, local_table, *args, **params)
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
197, in __init__
self._configure_inheritance()
File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line
473, in _configure_inheritance
self.local_table)
File C:\Python27\lib\site-packages\sqlalchemy\sql\util.py, line
303, in join_condition
between '%s' and '%s'.%s % (a.description, b.description, hint))
sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships
between 'T' and 'S'.
-
What is wrong with this approach. Is there a good way to approach this
problem (I have tried a couple already).
Also: Why is Inherit.id() called 3 times for T
Please help!
Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] @validates clean-up
I am having some trouble cleaning up after my @validates method throws an exception. The validation is of a relationship attribute and the foreign record should be removed. Are there some general tips? Otherwise I will post a code example. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Elixir question
OK, thank you,
I went back to SQLA and came up with this for now (simplified):
class Pairs(Base):
__tablename__ = 'Pairs'
name = Column(String(20), primary_key=True)
other_name = Column(String(20), ForeignKey('Pairs.name'), nullable
= False)
other = relationship('Pairs',
primaryjoin = 'Pairs.name ==
Pairs.other_name',
remote_side=[name])
def __init__(self, name):
self.name = name
def __repr__(self):
return (%s, %s) % (self.name, self.other.name)
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other_name = name2
p2.other_name = name1
return p1, p2
if __name__ == '__main__':
p1, p2 = pair('apple', 'pear')
session.add_all([p1, p2])
session.commit()
for p in session.query(Pairs).all():
print p
assert p1.other.other is p1
--
Note that there is no backref on other and that the primaryjoin is
completely written out (otherwise a got a mysterious (to me) error,
when using joined inheritance at the same time).
This solution is key to my datamodel. Does anyone see any drawbacks?
Cheers, Lars
On Feb 5, 10:50 am, Gaëtan de Menten gdemen...@gmail.com wrote:
On 02/03/2012 12:08 PM, lars van gemerden wrote:
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerdenl...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
You can't have a OneToOne as inverse for a OneToOne, even less for
itself. Valid relationship pairs are:
ManyToOne - OneToOne
ManyToOne - OneToMany
ManyToMany - ManyToMany
In your case you want:
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other1 = ManyToOne('Pairs', inverse = 'other2')
other2 = OneToOne('Pairs', inverse = 'other1')
and if your database really only stores pairs, a property might make it
more elegant:
@property
def other(self):
return self.other1 if self.other1 is not None else self.other2
As a side note, you probably do not want to use Elixir for a new
project, as Elixir is not maintained anymore.
-G.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Elixir question
Sorry, scrap the remark about primaryjoin ... inheritance. INheritance
wasn't the problem.
On Feb 5, 1:27 pm, lars van gemerden l...@rational-it.com wrote:
OK, thank you,
I went back to SQLA and came up with this for now (simplified):
class Pairs(Base):
__tablename__ = 'Pairs'
name = Column(String(20), primary_key=True)
other_name = Column(String(20), ForeignKey('Pairs.name'), nullable
= False)
other = relationship('Pairs',
primaryjoin = 'Pairs.name ==
Pairs.other_name',
remote_side=[name])
def __init__(self, name):
self.name = name
def __repr__(self):
return (%s, %s) % (self.name, self.other.name)
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other_name = name2
p2.other_name = name1
return p1, p2
if __name__ == '__main__':
p1, p2 = pair('apple', 'pear')
session.add_all([p1, p2])
session.commit()
for p in session.query(Pairs).all():
print p
assert p1.other.other is p1
--
Note that there is no backref on other and that the primaryjoin is
completely written out (otherwise a got a mysterious (to me) error,
when using joined inheritance at the same time).
This solution is key to my datamodel. Does anyone see any drawbacks?
Cheers, Lars
On Feb 5, 10:50 am, Gaëtan de Menten gdemen...@gmail.com wrote:
On 02/03/2012 12:08 PM, lars van gemerden wrote:
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerdenl...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
You can't have a OneToOne as inverse for a OneToOne, even less for
itself. Valid relationship pairs are:
ManyToOne - OneToOne
ManyToOne - OneToMany
ManyToMany - ManyToMany
In your case you want:
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other1 = ManyToOne('Pairs', inverse = 'other2')
other2 = OneToOne('Pairs', inverse = 'other1')
and if your database really only stores pairs, a property might make it
more elegant:
@property
def other(self):
return self.other1 if self.other1 is not None else self.other2
As a side note, you probably do not want to use Elixir for a new
project, as Elixir is not maintained anymore.
-G.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Elixir question
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
def __init__(self, name):
self.name = name
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
if __name__ == '__main__':
setup_all()
create_all()
pair('p1', 'p2')
#--
I am not very famiiar with SQL etc. but logically this seems possible
(please orrect me if I am wrong). However I get the following error:
File D:\Documents\Code\Eclipse\workspace\SQLAdata\src\tests.py, line
22, in module
setup_all()
File build\bdist.win-amd64\egg\elixir\__init__.py, line 94, in
setup_all
File build\bdist.win-amd64\egg\elixir\entity.py, line 951, in
setup_entities
File build\bdist.win-amd64\egg\elixir\entity.py, line 198, in
create_pk_cols
File build\bdist.win-amd64\egg\elixir\entity.py, line 481, in
call_builders
File build\bdist.win-amd64\egg\elixir\relationships.py, line 448,
in create_pk_cols
File build\bdist.win-amd64\egg\elixir\relationships.py, line 791,
in create_keys
File build\bdist.win-amd64\egg\elixir\relationships.py, line 521,
in inverse
AssertionError: Relationships 'other' in entity 'Pair' and 'other' in
entity 'Pair' cannot be inverse of each other because their types do
not form a valid combination.
Can anyone help me to understand the error and possibly fix it?
Thanks in advance, Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Elixir question
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerden l...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
def __init__(self, name):
self.name = name
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
if __name__ == '__main__':
setup_all()
create_all()
pair('p1', 'p2')
#--
I am not very famiiar with SQL etc. but logically this seems possible
(please orrect me if I am wrong). However I get the following error:
File D:\Documents\Code\Eclipse\workspace\SQLAdata\src\tests.py, line
22, in module
setup_all()
File build\bdist.win-amd64\egg\elixir\__init__.py, line 94, in
setup_all
File build\bdist.win-amd64\egg\elixir\entity.py, line 951, in
setup_entities
File build\bdist.win-amd64\egg\elixir\entity.py, line 198, in
create_pk_cols
File build\bdist.win-amd64\egg\elixir\entity.py, line 481, in
call_builders
File build\bdist.win-amd64\egg\elixir\relationships.py, line 448,
in create_pk_cols
File build\bdist.win-amd64\egg\elixir\relationships.py, line 791,
in create_keys
File build\bdist.win-amd64\egg\elixir\relationships.py, line 521,
in inverse
AssertionError: Relationships 'other' in entity 'Pair' and 'other' in
entity 'Pair' cannot be inverse of each other because their types do
not form a valid combination.
Can anyone help me to understand the error and possibly fix it?
Thanks in advance, Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] a more general elixir question
Is it possible to mix Elixir classes and SQLA classes in one database (with foreign keys between them)? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: a more general elixir question
Thanks, Can you also mix Elixir Fields and SQLA Column/relationships in the same class? Cheers, Lars On Feb 3, 12:56 pm, erikj tw55...@gmail.com wrote: yes Elixir classes are in fact SQLA classes, only defined with a different syntax you can mix Elixir, Declarative and plain python objects mapped with SQLA On Feb 3, 12:13 pm, lars van gemerden l...@rational-it.com wrote: Is it possible to mix Elixir classes and SQLA classes in one database (with foreign keys between them)? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: a more general elixir question
Too bad, thanks On Feb 3, 1:38 pm, erikj tw55...@gmail.com wrote: no, that's not possible Elixir does some custom postprocessing on a class definition, and so does Declarative, but they are not interoperatable On Feb 3, 1:20 pm, lars van gemerden l...@rational-it.com wrote: Thanks, Can you also mix Elixir Fields and SQLA Column/relationships in the same class? Cheers, Lars On Feb 3, 12:56 pm, erikj tw55...@gmail.com wrote: yes Elixir classes are in fact SQLA classes, only defined with a different syntax you can mix Elixir, Declarative and plain python objects mapped with SQLA On Feb 3, 12:13 pm, lars van gemerden l...@rational-it.com wrote: Is it possible to mix Elixir classes and SQLA classes in one database (with foreign keys between them)? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: multiple inheritance experiment
Thank you, In the last suggestion: @property def users_and_orders(self): return self.users + self.orders or to simulate a polymorphic union, do object_session(self).query().union(object_session(self).query()) etc. Do you have any suggestions to make the result have an append/remove method which persists to the database? Perhaps with a descriptor or custom collection type? Cheers, Lars On Apr 15, 6:49 am, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 13, 2011, at 5:52 AM, Lars wrote: Hi Michael, I am trying to figure out the two suggestions you did, and not getting very far. Some basic questions: - if A, B, C are mapped classes, can you do A.join(B, A.id == B.id).join(C, B.id == C.id).join( ? usually if you want to use join() you'd deal with tables, like table_a.join(table_b, ...).join(...). though the orm.join() function will receive classes directly, its inhttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins - Would using join in such a way make access to mapped attributes in one of the joined tables excessively slow? joins are slower than straight single table selects especially in MySQL, if thats the question - What is the difference between using association_proxy and relationship(... secondary = .., ..., secondaryjoin = ...)? three concepts. one is many-to-many:http://www.sqlalchemy.org/docs/orm/relationships.html#many-to-many next is the association pattern, a many to many where extra data is linked with each association:http://www.sqlalchemy.org/docs/orm/relationships.html#association-object next is association proxy, when you've worked with an association for awhile and are tired of saying parent.association.child and want to just skip the .association part in the usual case - in the example in poly_assoc_generic.py, is there a way to define an attribute on address that returns a list with both orders and users with that address (and be able to append that list) ? these collections load from entirely different tables. Usually you'd need to do it manually: @property def users_and_orders(self): return self.users + self.orders or to simulate a polymorphic union, do object_session(self).query().union(object_session(self).query()) etc. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] short question on Table
Hi all, Does anyone know how I can add a Column to an existing not yet mapped Table? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] short question on Table
Hi all, Does anyone know how I can add a Column to an existing not yet mapped Table? Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: multiple inheritance experiment
Hi Michael,
I am trying to figure out the two suggestions you did, and not getting
very far. Some basic questions:
- if A, B, C are mapped classes, can you do A.join(B, A.id ==
B.id).join(C, B.id == C.id).join( ?
- Would using join in such a way make access to mapped attributes in
one of the joined tables excessively slow?
- What is the difference between using association_proxy and
relationship(... secondary = .., ..., secondaryjoin = ...)?
- in the example in poly_assoc_generic.py, is there a way to define an
attribute on address that returns a list with both orders and
users with that address (and be able to append that list) ?
Please help, Lars
On Apr 11, 8:45 pm, Lars gemer...@gmail.com wrote:
Hi again,
On Apr 10, 11:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr
10, 2011, at 1:27 PM, Lars wrote:
Hi Michael,
Thank you for the suggestions.
I noticed that all the foreign keys to superclasses are identical
(same id number). What would be the disadvantage of using only primary
keys and using those in setting up relationships (compared to using
foreign keys)?
When two columns in a relational database, one being a primary key, the
other being possibly a primary key, are logically constrained to have the
same value, that's known as a foreign key. It's then best practice to
actually place a real foreign key constraint in the database to enforce
this at the data level. SQLite and MySQL by default don't enforce it,
though. SQLAlchemy doesn't particularly care if an actual constraint is
defined in the database, it can be told to join on any combination of
columns. The presence of a foreign key within table metadata just makes
this automatic.
Does that mean that if you don't set the foreign key constraint and
you want to be able to set an attribute, you also have to write code
to copy the primary key of the parent to the child?
If I use a root base class (similar to object in python) and add a
type column/attribute to refer to the actual class of the object/
record, is there an elegant way to get/set/del the polymorphic
attribute object of the correct class using this type?
Which table is this type column present in, base1 or base2 ?
Neither, I was thinking of using a class that is the root base class
of all classes (not present in code above) to store the type and the
primary key of all objects. This type should be accessible in all
classes, since they would all be direct or indirect subclasses of this
root class. Say that I store all classes in a dictionary called
registry, then registry[type] would give the actual class of the
object identified with the primary key in the root class table. This
key would also identify the correct record in all superclasses of this
class, e.g. to be used in a join over these classes.
(hope this is somewhat clear ...)
Would it be possible to redefine query() using this type to first
resolve the correct table and use the query method SA provides on that
(without this resulting in otherwise changing the API) ?
but what's the correct table, base1, base2, claz ? with multiple
inheritance the path is not a straight line. If you have a mostly straight
inheritance model with an occasional offshoot, say its base1 - subbase1 -
(subbase1 , base2) - claz, I'd use traditional inheritance and have just
base2 via relationship().
The correct table would be the table corresponding to the value in the
type column. I am working on a framework where others will design the
data structure and would like to use a uniform metadata model to avoid
extra choices for the designer.
Cheers again, Lars
On Apr 7, 8:42 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 7, 2011, at 2:30 PM, Lars wrote:
Hello,
I am pretty determined to find a way to get (a simplified version of)
multiple inheritance working with SA. The simplification lies in that
no overriding of attributes will be possible (but I don't know whether
that is significant). I was thinking of a schema as follows:
---
---
metadata = MetaData()
base1_table = Table(base1_table, metadata,
Column('id', Integer, primary_key=True),
Column('str', String)
)
base2_table = Table(base2_table, metadata,
Column('id', Integer, primary_key=True),
Column('int', Integer)
)
claz1_table = Table(claz1_table, metadata,
Column('id', Integer, primary_key=True),
Column('base1_id', None, ForeignKey('base1_table.id')),
Column('base2_id', None, ForeignKey('base2_table.id')),
Column('assoc_id', None, ForeignKey('assoc_table.id'))
)
assoc_table = Table(assoc_table, metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50), nullable=False),
Column('type', String(50), nullable
[sqlalchemy] Re: Avoiding spaghetti inheritance
Hi Luca, There are a couple of things you could try: 1) Analyse the business logic further to figure out what fields structurally necessary for different types of product and/or important for queries, decide how far you want to take the inheritance tree from there. 2) Fields that are likely to change you could combine and store in a text field (e.g. in XML) and parse in your python application, some queries are still possible by searching the text (SQL: ..LIKE.. I think). 3) Use a one to many attribute properties (or some such) .. I guess that is the tags approach .. with a properties table having a foreign key to the product/service table, a property name and a property value (probably text, so if it would have to represent an e.g. an integer, querying for e.g. would be a problem). I think it is a well known problem, especially for companies with many different products, like an electronics web shop, where customers want to search for products with specific properties. Having a separate table for each type of product is a pain, especially if new types come out rapidly (tv, lcd, plasma, 3D TV), thats why you often get choices for e.g. screensize instead of being able to set 36 . I guess they use option 3 or maybe 2. Hope this helps, Lars On Apr 7, 10:36 pm, Luca Lesinigo l...@lesinigo.it wrote: Hello there. I'm using SA-0.7 to develop an application that should help me manage my company's services. A central concept here is the order, it could be a service (like one year of web hosting) or a physical item (like a pc we sell). So far I generalized them in two classes: the Order and the ServiceOrder - the latter simply inherits the former and adds start and end dates. Now I need to add all various kinds of metadata to orders, for example: - a ServiceOrder for a domain hosting should contain the domain name - a ServiceOrder for a maintenance service should contain the service level for that service (say, basic or advanced) - an Order for a PC we delivered should contain its serial number - and so on... I could easily add child classes, but that would mean to keep and maintain that code forever even after we stop using it (ie, next year we stop doing hosting) or when it's not really useful (many things will just have some 'metadata' in them like a serial number or similar things). I'd also like to avoid having to add code every time we just hit something slightly different to manage, when we just have some additional data to keep track of. I wonder what could be an intelligent approach to such a situation. One idea I got could be to add an 'OrderTags' table / class that would associate (tag, value) tuples to my orders, and somehow access them like a dictionary (eg. Order.tags['serialnumber'] = 'foo' or ServiceOrder.tags['domainname'] = 'example.com'). But that will probably keep them out of standard SA queries? For example, if I want to retrieve the full history of a domain we're hosting, how could I query for all orders with (tags['domainname'] == something)? I'm looking for advice on how to structure this data, and how to best implement it with python and sqlalchemy-0.7. Thank you, Luca -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: multiple inheritance experiment
Hi again,
On Apr 10, 11:17 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 10, 2011, at 1:27 PM, Lars wrote:
Hi Michael,
Thank you for the suggestions.
I noticed that all the foreign keys to superclasses are identical
(same id number). What would be the disadvantage of using only primary
keys and using those in setting up relationships (compared to using
foreign keys)?
When two columns in a relational database, one being a primary key, the other
being possibly a primary key, are logically constrained to have the same
value, that's known as a foreign key. It's then best practice to actually
place a real foreign key constraint in the database to enforce this at the
data level. SQLite and MySQL by default don't enforce it, though.
SQLAlchemy doesn't particularly care if an actual constraint is defined in
the database, it can be told to join on any combination of columns. The
presence of a foreign key within table metadata just makes this automatic.
Does that mean that if you don't set the foreign key constraint and
you want to be able to set an attribute, you also have to write code
to copy the primary key of the parent to the child?
If I use a root base class (similar to object in python) and add a
type column/attribute to refer to the actual class of the object/
record, is there an elegant way to get/set/del the polymorphic
attribute object of the correct class using this type?
Which table is this type column present in, base1 or base2 ?
Neither, I was thinking of using a class that is the root base class
of all classes (not present in code above) to store the type and the
primary key of all objects. This type should be accessible in all
classes, since they would all be direct or indirect subclasses of this
root class. Say that I store all classes in a dictionary called
registry, then registry[type] would give the actual class of the
object identified with the primary key in the root class table. This
key would also identify the correct record in all superclasses of this
class, e.g. to be used in a join over these classes.
(hope this is somewhat clear ...)
Would it be possible to redefine query() using this type to first
resolve the correct table and use the query method SA provides on that
(without this resulting in otherwise changing the API) ?
but what's the correct table, base1, base2, claz ? with multiple
inheritance the path is not a straight line. If you have a mostly straight
inheritance model with an occasional offshoot, say its base1 - subbase1 -
(subbase1 , base2) - claz, I'd use traditional inheritance and have just
base2 via relationship().
The correct table would be the table corresponding to the value in the
type column. I am working on a framework where others will design the
data structure and would like to use a uniform metadata model to avoid
extra choices for the designer.
Cheers again, Lars
On Apr 7, 8:42 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 7, 2011, at 2:30 PM, Lars wrote:
Hello,
I am pretty determined to find a way to get (a simplified version of)
multiple inheritance working with SA. The simplification lies in that
no overriding of attributes will be possible (but I don't know whether
that is significant). I was thinking of a schema as follows:
---
---
metadata = MetaData()
base1_table = Table(base1_table, metadata,
Column('id', Integer, primary_key=True),
Column('str', String)
)
base2_table = Table(base2_table, metadata,
Column('id', Integer, primary_key=True),
Column('int', Integer)
)
claz1_table = Table(claz1_table, metadata,
Column('id', Integer, primary_key=True),
Column('base1_id', None, ForeignKey('base1_table.id')),
Column('base2_id', None, ForeignKey('base2_table.id')),
Column('assoc_id', None, ForeignKey('assoc_table.id'))
)
assoc_table = Table(assoc_table, metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50), nullable=False),
Column('type', String(50), nullable=False)
)
base3_table = Table(base3_table, metadata,
Column('id', Integer, primary_key=True),
Column('assoc_id', None, ForeignKey('assoc_table.id')),
Column('bool', Boolean)
)
claz2_table = Table(claz2_table, metadata,
Column('id', Integer, primary_key=True),
Column('base3_id', None, ForeignKey('base3_table.id')),
Column('date', Date)
)
class base1(object):
pass
class base2(object):
pass
class base3(object):
pass
class claz1(base1, base2):
pass
class claz2(base3):
pass
# do mappings, relationships and e.g. be able to
c1 = claz1(str = hello, int = 17)
setattr(c1, name, claz2(bool = True, date = Date(2010,9,10)))
You can just forego the inherits flag and map each class to the
appropriate join or base
[sqlalchemy] Re: multiple inheritance experiment
Hi Michael,
Thank you for the suggestions.
I noticed that all the foreign keys to superclasses are identical
(same id number). What would be the disadvantage of using only primary
keys and using those in setting up relationships (compared to using
foreign keys)?
If I use a root base class (similar to object in python) and add a
type column/attribute to refer to the actual class of the object/
record, is there an elegant way to get/set/del the polymorphic
attribute object of the correct class using this type? Would that be
possible with the first option you described above?
Would it be possible to redefine query() using this type to first
resolve the correct table and use the query method SA provides on that
(without this resulting in otherwise changing the API) ?
Cheers, Lars
On Apr 7, 8:42 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 7, 2011, at 2:30 PM, Lars wrote:
Hello,
I am pretty determined to find a way to get (a simplified version of)
multiple inheritance working with SA. The simplification lies in that
no overriding of attributes will be possible (but I don't know whether
that is significant). I was thinking of a schema as follows:
---
---
metadata = MetaData()
base1_table = Table(base1_table, metadata,
Column('id', Integer, primary_key=True),
Column('str', String)
)
base2_table = Table(base2_table, metadata,
Column('id', Integer, primary_key=True),
Column('int', Integer)
)
claz1_table = Table(claz1_table, metadata,
Column('id', Integer, primary_key=True),
Column('base1_id', None, ForeignKey('base1_table.id')),
Column('base2_id', None, ForeignKey('base2_table.id')),
Column('assoc_id', None, ForeignKey('assoc_table.id'))
)
assoc_table = Table(assoc_table, metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50), nullable=False),
Column('type', String(50), nullable=False)
)
base3_table = Table(base3_table, metadata,
Column('id', Integer, primary_key=True),
Column('assoc_id', None, ForeignKey('assoc_table.id')),
Column('bool', Boolean)
)
claz2_table = Table(claz2_table, metadata,
Column('id', Integer, primary_key=True),
Column('base3_id', None, ForeignKey('base3_table.id')),
Column('date', Date)
)
class base1(object):
pass
class base2(object):
pass
class base3(object):
pass
class claz1(base1, base2):
pass
class claz2(base3):
pass
# do mappings, relationships and e.g. be able to
c1 = claz1(str = hello, int = 17)
setattr(c1, name, claz2(bool = True, date = Date(2010,9,10)))
You can just forego the inherits flag and map each class to the appropriate
join or base table. claz1 would be mapped to a join of the three tables
involved. The difficult part of course is the query side, if you're
looking to query base1 or base2 and get back claz1 objects.
Alternatively, each class can be mapped to one table only, and relationship()
used to link to other tables. Again if you don't use the inherits flag,
you can maintain the class hierarchy on the Python side and use association
proxies to provide local access to attributes that are normally on the
related class. This would still not give you polymorphic loading but would
grant a little more flexibility in which tables are queried to start.
---
--
I am still pretty new to SA. Can anyone give me any hints, tips,
issues with this scheme (e.g. about how to do the mappings,
descriptors, etc)?
The step after will be to write factory functions/metaclasses to
generate these dynamically.
Multiple inheritance is very important for my use case.
Cheers, Lars
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: when is object.id initialized
OK, thanks, this was part of the ActiveRecord kind of approach I was playing with, which after reading your article at zzzeek and the alternative described there I will probably shelve. On Apr 6, 9:59 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 6, 2011, at 6:38 AM, farcat wrote: Thank you, I now experiment with putting session.add and session.flush in object.__init__ .. Are there any general disadvantages of that approach? Using add() inside of __init__ is somewhat common. Using flush() has the significant downside that flushes occur too often which is wasteful and performs poorly for larger scale operations (like bulk loads and such). The ORM is designed such that primary key identifiers are not needed to be explicitly accessed outside of a flush except for query situations that wish to avoid the usage of relationships. When you build your application to be strongly dependent on primary key identifiers being available within otherwise fully pending object graphs, you begin to work against the usage goals of the ORM. On Apr 3, 7:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: Integer primary key identifiers are generated by the database itself using a variety of techniques which are all database-dependent. This process occurs when the session flushes. If you read the object relational tutorial starting athttp://www.sqlalchemy.org/docs/orm/tutorial.html#setting-up-the-mappi...working down through the end ofhttp://www.sqlalchemy.org/docs/orm/tutorial.html#adding-new-objectsyouwill see that this interaction is described. You can of course set .id to any value you'd like and that will be the value used when the flush happens. On Apr 3, 2011, at 1:09 PM, farcat wrote: Hi all, I use a kind of dynamic reference from parent_table to other tables. For that parent_table uses columns table_name and a record_id. This makes it possible to have a reference from parent_table to any record in any table in the database. However, say that i want to reference a record of table_name, i need the record.id to initialize parent_table.record_id. However, when i create a record and session.add it to the database, record.id == None. I was wondering when and how record.id is initialized and how it can be forced. Cheers, Lars -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to delete record (special case)
Hi Michael,
I am trying to run the alternative you described in the article, but
the following code is most likely from an old version of SA a don't
know how to update (I am working with 6.6):
mapper = class_mapper(cls)
table = mapper.local_table
mapper.add_property(attr_name, relationship(GenericAssoc,
backref=backref('_backref_%s' % table.name, uselist=False)))
class_mapper is unknown or moved.
What does it do/how can I fix this?
Cheers, Lars
On Apr 6, 10:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Apr 6, 2011, at 5:43 AM, farcat wrote:
Hello,
I am experimenting with a pattern where records hold the table name
and record id of the next record in any other table, chaining records
in different tables. This works, but I can't figure out how to clean
op references to the next record in another table when I delete a
record (the pattern does not use foreign keys in the normal sense).
.. in that it doesn't use foreign keys. Since you're working against the
relational database's supported patterns, you'd need to roll the deletion of
related rows yourself. The pattern is also called a polymorphic
association and I blogged about it years ago here:
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-s
The code is:
=
from sqlalchemy import *
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base,
declared_attr, DeclarativeMeta
#--
-
Base = declarative_base()
reg = dict()
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind = engine)
#--
-
class chainmeta(DeclarativeMeta):
#--
-
class Base(object):
session = Session()
@declared_attr
def __tablename__(cls):
return cls.__name__
id = Column(Integer, primary_key = True)
next_table = Column(String(64))
next_id = Column(Integer) #in table with name stored in
next_table!
def __init__(self, data, next = None):
self.data = data
self.prev = None
self.next = next
self.session.add(self)
self.session.flush()
def _getnext(self):
if self.next_table and self.next_id:
return
self.session.query(reg[self.next_table]).filter(self.next_id ==
reg[self.next_table].id).one()
else: return None
def _setnext(self, next):
if next:
if self.next:
self.next.prev = None
self.next_table = next.__tablename__
self.next_id = next.id
next.prev = self
elif self.next:
self.next.prev = None
self.next_table = None
self.next_id = None
def _delnext(self):
self.next.prev = None
self.next_table = None
self.next_id = None
next = property(_getnext, _setnext, _delnext)
def __repr__(self):
out = type: + type(self).__name__ + [
for name in self.__dict__:
out += name + ,
out += ]
return out
#--
-
def __new__(mcls, name, coltype):
return DeclarativeMeta.__new__(mcls, name, (chainmeta.Base,
Base),{data: Column(coltype, nullable = False)})
def __init__(cls, name, coltype):
reg[name] = cls
return DeclarativeMeta.__init__(cls, name, (chainmeta.Base,
Base),{})
#--
-
if __name__ == '__main__':
Base.metadata.drop_all(engine)
session = chainmeta.Base.session = Session()
Ni = chainmeta(Ni, Integer)
Nb = chainmeta(Nb, Boolean)
Nt = chainmeta(Nt, String(200))
Base.metadata.create_all(engine)
ni1 = Ni(5)
ni2 = Ni(12)
nb1 = Nb(True)
nb2 = Nb(False)
nt1 = Nt(text in nt1)
nt2 = Nt(text in nt2)
ni1.next = ni2
ni2.next = nb1
nb1.next = nb2
nb2.next = nt1
nt1.next = nt2
nt2.next = ni1 #circular
print OBJECTS
n = ni1
count = 0
print nexts: .
while n and count 10:
print n.data
count += 1
n = n.next
n = ni1
count = 0
print prevs: .
while n and count 10:
print n.data
count += 1
n = n.prev
print
--
-
nts = session.query(Nt).all()
print QUERIES
