RE: [sqlalchemy] In case of joinedload_all how do I order by on a columns of those relations
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Marc Van Olmen Sent: 29 June 2011 04:19 To: sqlalchemy Subject: [sqlalchemy] In case of joinedload_all how do I order by on a columns of those relations Hi I'm trying to order by a column from a relationship. Taken example from: http://www.sqlalchemy.org/docs/orm/loading.html#routing-explicit- joins-statements-into-eagerly-loaded-collections In case of query.options(joinedload_all('orders.items.keywords'))... or query.options(joinedload_all(User.orders, Order.items, Item.keywords)) I would like to do something like: query.options(joinedload_all('orders.items.keywords')).order_by('user .orders.items.keywords.name') Tried this above but didn't work. Searched for some sample/tutorials but with no luck. thanks for any direction. marc I think this is in the FAQ: http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOU TERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYL IMITetc.whichreliesupontheOUTERJOIN (That link has probably wrapped - search for ORDER BY on http://www.sqlalchemy.org/trac/wiki/FAQ) Hope that helps, Simon -- 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] SA and IBM DB2
Hello there. I'd like to use SQLalchemy with an existing db2 database (I can already access it with plain SQL using pyODBC from a python-2.6/ win32 system). Googling around, I found http://code.google.com/p/ibm-db and it seems to have an updated DB-API driver for python-2.6/win32, but the latest SA adapter is for sqlalchemy-0.4. Is there any way to access DB2 from sqlalchemy-0.6 or -0.7? If that helps, I'm gonna use it in read-only (ie, no INSERT, UPDATE, DELETE queries will be issued nor would they be accepted by the db) thanks, 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.
Re: [sqlalchemy] SA and IBM DB2
On Jun 29, 2011, at 6:43 AM, Luca Lesinigo wrote: Hello there. I'd like to use SQLalchemy with an existing db2 database (I can already access it with plain SQL using pyODBC from a python-2.6/ win32 system). Googling around, I found http://code.google.com/p/ibm-db and it seems to have an updated DB-API driver for python-2.6/win32, but the latest SA adapter is for sqlalchemy-0.4. Is there any way to access DB2 from sqlalchemy-0.6 or -0.7? If that helps, I'm gonna use it in read-only (ie, no INSERT, UPDATE, DELETE queries will be issued nor would they be accepted by the db) A project I'd like to take on at some point, or to get someone else to do it, would be to write a modernized SQLAlchemy 0.7 dialect for DB2, where we would use DB2's DBAPI, but not their SQLAlchemy dialect which is out of date and they appear to not be doing much with. I'd write a new dialect rather than porting/looking at the one IBM wrote just so there's no potential licensing issues. The new DB2 dialect would live with all the other dialects under the SQLAlchemy project itself. I understand DB2 has a free express edition so it would be a matter of getting that going and working out the dialect. Dialects aren't too hard to write so we do get them contributed, but for the moment we don't have a DB2 story for modern SQLAlchemy versions. thanks, 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. -- 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] Problem with DeclarativeMeta
On Jun 28, 2011, at 11:24 PM, Mike Conley wrote: We have a database of about 100 tables with timestamp audit columns on most, but not all tables, and use declarative to describe the database. I am attempting to use a metaclass to create a base class that defines the audit columns so we can stop defining them on every class. This seems to work OK in most cases, but when I mix classes derived from my metaclass with classes derived from the out-of-the-box base class I get an UnmappedClassError. I am using version 0.5.8 (we will be able to upgrade soon, but not yet), but get the same error using a metaclass customized for 0.6. The stripped down example below should gives the error. the interesting thing I have seen is that when running with code for our full database, the error does not always point at the same table and occasionally gives an Attribute error instead of the UnmappedClassError. In this example, if you remove the product relationship property on SubSystemModule; the error disappears. Also, if both classes are derived from AuditBase, there is no error. Since we generate most of the SQLAlchemy classes directly from our data model, we can go back to adding audit columns to every class, but we don't really want to do that. its finding Product as a Table inside of the metadata when it tries to resolve your string based primaryjoin. It doesn't find Product the class since the two bases don't share the same _decl_class_registry. So back on 0.5 here's approach one: AuditBase = declarative_base(metaclass=AuditMeta, metadata=metadata) AuditBase._decl_class_registry = Base._decl_class_registry approach two: class AuditMeta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): if classname == 'AuditBase': return type.__init__(cls, classname, bases, dict_) dict_['ModificationTS'] = Column(ModificationDate,DateTime ,default=datetime.datetime.now ,onupdate=datetime.datetime.now) return DeclarativeMeta.__init__(cls, classname, bases, dict_) class AuditBase(Base): __metaclass__= AuditMeta I'm assuming you're aware when you go to 0.7 (I'd go straight to 0.7, its not very different from 0.6, but this works in 0.6 too), you should instead use mixins: class AuditBase(object): ModificationTS = Column(ModificationDate,DateTime ,default=datetime.datetime.now ,onupdate=datetime.datetime.now) class Product(AuditBase, Base): # ... import datetime from sqlalchemy import __version__ as sa_ver from sqlalchemy import (Column, ForeignKeyConstraint, PrimaryKeyConstraint, create_engine, MetaData, DateTime, Integer, String) from sqlalchemy.orm import relation, backref from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta Base = declarative_base() metadata = Base.metadata class AuditMeta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): dict_['ModificationTS'] = Column(ModificationDate,DateTime ,default=datetime.datetime.now ,onupdate=datetime.datetime.now) return DeclarativeMeta.__init__(cls, classname, bases, dict_) AuditBase = declarative_base(metaclass=AuditMeta, metadata=metadata) class Product(AuditBase): __tablename__ = 'Product' PID = Column(Integer) Name = Column(String) __table_args__ = ( PrimaryKeyConstraint('PID'), {}) class SubSystemModule(Base): __tablename__ = 'SubSystemModule' SSMID = Column(Integer) PID = Column(Integer) Name = Column(String) __table_args__ = ( PrimaryKeyConstraint('SSMID'), ForeignKeyConstraint(['PID'],['Product.PID']), {}) product = relation('Product', backref=backref('subsystemmodule', cascade='all'), primaryjoin='SubSystemModule.PID==Product.PID') if __name__ == '__main__': print 'SQLAlchemy version:',sa_ver from sqlalchemy.orm import compile_mappers compile_mappers() -- Mike Conley -- 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] Re: Slightly confusing error when session set up incorrectly
On Jun 29, 6:46 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 28, 2011, at 9:34 PM, Ben Sizer wrote: What does the None None signify? Would it be possible to change this exception to be a bit more descriptive and a little less cryptic? eg. Include the URI that failed? That error is raised by SQLite and we just propagate it out. Ok, that's a bit annoying, but it does sound like something that is not your problem to solve. Maybe if I get time I'll contact the pysqlite maintainers as I think it would be useful to have a pseudo- statement in there at least. Additionally, I notice that sqlalchemy doesn't attempt to make an actual connection to the database until you perform the first query. That is true, all connections/transactions are lazy initializing. Sorry this was a surprise, though I don't think this is much of an issue once you get used to dealing with lazy initializing objects. The formal pattern at play with SQLAlchemy's connection pool, Engine and Session behavior is called the Proxy Pattern, a decent description is at http://sourcemaking.com/design_patterns/proxy. Yeah, that's all fine. I don't have a problem with this choice, just that it's not immediately obvious when following the docs. the lazy initializing behavior of the Session is documented: http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-transaction Not exactly. It says it maintains a connection for each SQL statement, which is not logically equivalent to saying there is no connection unless there has been an SQL statement. There is a similar implication in the 'What does the Session do?' paragraph also. But that doesn't have anything to do with the Engine, which is its own thing. As far as the Engine, the docs currently use the term connect in conjunction with create_engine() which is for simplicities' sake, but is technically inaccurate, perhaps come up with some term other than connect, configure a connection source perhaps. We can add an explicit sentence to the top ofhttp://www.sqlalchemy.org/docs/core/connections.html. I must admit I don't fully understand engines, connections, sessions, etc. I will study the docs further! However when writing manual DB code I would usually have a step where I initialise the driver (which I had been thinking of as the create_engine stage), a step where I can issue statements (which I think of as the session stage), and a point temporally between those where I explicitly connect to the physical storage, which I had thought was automatically taking place when creating the engine but I now realise only happens on demand. It's not a problem as such, just a surprise. I expect I am not unusual in wanting an error in session configuration to fail as soon as possible, Nobody has ever raised this issue before to my recollection. The problem with show-stopper bugs is that people often just give up and switch to something else rather than tell you about it. ;) (Of course in this case, the bug is mine, but developers do like to blame their tools...) I'm not sure lots of users are phased whether the stack trace starts at the Session.configure() line or if it starts later as soon as their first unit test tries to hit the database - they get the same error, see that the connection URL is bad, and fix it. But this is exactly the problem: I had one unit test for setting up the database, and one for actually using the database. The first one passed - the second one failed. So I immediately think, my usage of the database is wrong, not I set up the database connection wrongly, because all the code for setting up the connection was covered the test that passed, too. That is what the unit tests were there for, after all - to separate out where a problem appears to be coming from, by splitting your code coverage across tests. I suppose inefficiency is the only issue but its so unnecessarily inefficient, most people would consider it to be wrong behavior. A Session may be configured such that depending on what's requested of it, it can connect to any number of different engines - connecting to several/dozens/hundreds of engines unconditionally upon construction is not an option. Yeah, that's fine. I just think it would be good to have had something about this documented right in at the top of the Session docs so that anyone starting out or writing unit tests for this sort of thing knows that they're not actually testing the db connection just because they made an engine and Session without error. Anywhere you pass in invalid data, you hope that the system catches that as soon as possible so that the problem's narrowed down, ideally to the exact statement that is incorrect. In this case, it's not efficient to do so, therefore it would be great for the docs to explicitly highlight that fact. Feel free to suggest what verbiage you're looking for, it's not hitting me strongly what the confusion is - the
Re: [sqlalchemy] Re: Slightly confusing error when session set up incorrectly
On Jun 29, 2011, at 12:24 PM, Ben Sizer wrote: the lazy initializing behavior of the Session is documented: http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-transaction Not exactly. It says it maintains a connection for each SQL statement, OK, this happens a lot with me, if anyone can please recommend what university I should go to in order to learn to speak english correctlyHere's the sentence: As the Session receives requests to execute SQL statements using a particular Engine or Connection, it adds each individual Engine encountered to its transactional state and maintains an open connection for each one (note that a simple application normally has just one Engine). You're taking each one to mean, the SQL statement. When I wrote it, each one means, each Engine. Ticket http://www.sqlalchemy.org/trac/ticket/2204 has been added. There is a similar implication in the 'What does the Session do?' paragraph also. Not seeing the implication in that one, please add some details to ticket 2204 as to the verbiage you find confusing (log in as guest/guest). But that doesn't have anything to do with the Engine, which is its own thing. As far as the Engine, the docs currently use the term connect in conjunction with create_engine() which is for simplicities' sake, but is technically inaccurate, perhaps come up with some term other than connect, configure a connection source perhaps. We can add an explicit sentence to the top ofhttp://www.sqlalchemy.org/docs/core/connections.html. I expect I am not unusual in wanting an error in session configuration to fail as soon as possible, Nobody has ever raised this issue before to my recollection. The problem with show-stopper bugs is that people often just give up and switch to something else rather than tell you about it. ;) show stopper bug is quite an exaggeration over what is essentially a small documentation insufficiency. There's more than 25000 messages on the Google Groups list as well as several thousand on previous mailing lists as well as the several thousand tickets in trac, virtually all of which originate from a user experiencing a bug either in SQLAlchemy, in its documentation, or within his or her own usage of the product. The vast majority of these issues have been resolved and those users went away happy. So there is a class of users, and a large one at that, that experience bugs both large and small and don't immediately abandon the project. This class of users continues to expand as the project has grown much stronger over the course of many years, responding to user requests, competing products, etc.There are of course users who abandon the project within 5 minutes, and I would say if their level of tenacity is that low then they're probably better off with whatever tool they end up using. SQLAlchemy is more about a big payoff for some upfront investment. I suppose inefficiency is the only issue but its so unnecessarily inefficient, most people would consider it to be wrong behavior.A Session may be configured such that depending on what's requested of it, it can connect to any number of different engines - connecting to several/dozens/hundreds of engines unconditionally upon construction is not an option. Yeah, that's fine. I just think it would be good to have had something about this documented right in at the top of the Session docs so that I think the specifics of how Session interacts with engines at a more detailed level is appropriate to be farther down the page, while language can be adjusted higher up as to not introduce misunderstandings. It's common that we get a user who had some kind of problem, that user got frustrated, then they request that very specific documentation for their specific issue be placed as some prominent paragraph somewhere.Unfortunately if we did that every time, the docs would be an enormous list of bulletpoints with no narrative at all. With user confusion, we always take into account the problem they had and that thinking goes into subsequent documentation revisions. They exist because the intended usage is that sessionmaker() be placed as a global variable in the module space of an application - it is then often the case that the actual Engine does not exist at this point, so cannot be passed to the sessionmaker(). A later configuration step can then call configure() to add new Engine configurations.This usage is driven by the needs of web frameworks and such. Ok, so: Session = sessionmaker(bind=some_engine) is for when you know which engine you need at import time; Session.configure(bind=engine) is when you have decided on the engine and want all subsequent sessions to use that engine, without needing to keep a reference to that engine; session = Session(bind=engine) is when you want to be able to specify the engine on a
Re: [sqlalchemy] SQLAlchemy mapped classes and __new__ (ClassManager.new_instance undocumented)
Hi Michael, On Tue, 2011-06-28 at 10:28 -0400, Michael Bayer wrote: ClassManager class is not documented. What should I be using instead? Instrumentation has to establish state on a new object independent of __new__() - during pickling, the state is restored naturally as __dict__ is restored, during fetch of rows, new_instance() is used, during normal construction, __init__() is used. class_manager() is documented we'd only need to get new_instance() and the use case documented, seems to me that would be bug fixed. Fine with me. Thanks! :-) Still I wonder why __init__ is still supported when using ORM mapped classes and __new__ is not. Is there any reason why the latter is harder to support? Or is it a matter of too little gain (who is using __new__ anyway?) for too much work? Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] UniqueConstraint breaks combination of ordering_list and association_proxy
Hi Michael, hi *, here is another issue I ran into with SQLAlchemy. Basically, I am trying to map a filesystem like structure to SQL. Unfortunately, there is a difference in that the users can reorder the tree. I reduced my code to the attached example. Mapping the structure worked quite good so far, until I noticed that I had duplicate entries in the same folder. So I added a unique constraint with the result that I can't overwrite the list of entries anymore: folder.children = list(folder.children) alone causes the problem. SQLAlchemy adds the 'new' entries first, which violates the unique constraint. Is there a way to do what I want without bigger changes to the code? For now, I will disable the unique constraint again and add some checks to the Python code. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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. Example of using association_proxy with ordering_list and how to break it ;-) Basically, assigning the same entries again in a different order will violate the unique constraint below. Remove it and you see the reason: INSERT INTO folder_entry (folder_id, entry_id, entry_order) VALUES (?, ?, ?) (2, 4, 0) INSERT INTO folder_entry (folder_id, entry_id, entry_order) VALUES (?, ?, ?) (2, 3, 1) DELETE FROM folder_entry WHERE folder_entry.surrogate_key = ? ((2,), (3,)) The new entries are inserted first and the old entries are deleted last. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.orderinglist import ordering_list from sqlalchemy.ext.associationproxy import association_proxy Base = declarative_base() class Entry(Base): __tablename__ = entry id = Column(Integer, primary_key=True) entry_type = Column(String) __mapper_args__ = {'polymorphic_on': entry_type} name = Column(String) class File(Entry): __tablename__ = file __mapper_args__ = {'polymorphic_identity': file} id = Column(Integer, ForeignKey(entry.id), primary_key=True) content = Column(LargeBinary) class FolderEntry(Base): __tablename__ = folder_entry surrogate_key = Column(Integer, primary_key=True) folder_id = Column(Integer, ForeignKey(folder.id), index=True, nullable=False) entry_id = Column(Integer, ForeignKey(entry.id), nullable=False) entry_order = Column(Integer) entry = relation(Entry) __table_args__ = (UniqueConstraint(folder_id, entry_id), {}) def __init__(self, entry): uConstructor for association_proxy, which passes only association target. self.entry = entry class Folder(Entry): __tablename__ = folder __mapper_args__ = {'polymorphic_identity': folder} id = Column(Integer, ForeignKey(entry.id), primary_key=True) children_relation = relation(FolderEntry, order_by=[FolderEntry.entry_order], cascade='save-update,merge,delete,delete-orphan', collection_class=ordering_list(entry_order)) children = association_proxy(children_relation, entry) engine = create_engine(sqlite:///, echo=True) Base.metadata.create_all(engine) Session = sessionmaker(engine) session = Session() root = Folder(name=root) src = Folder(name=src) root.children = [src] src.children = [File(name=test.py, content=# Some content), File(name=bar.py, content=# More content)] session.add(root) session.commit() src.children = list(src.children) session.commit()
Re: [sqlalchemy] SQLAlchemy mapped classes and __new__ (ClassManager.new_instance undocumented)
On Jun 29, 2011, at 2:46 PM, Torsten Landschoff wrote: Hi Michael, On Tue, 2011-06-28 at 10:28 -0400, Michael Bayer wrote: ClassManager class is not documented. What should I be using instead? Instrumentation has to establish state on a new object independent of __new__() - during pickling, the state is restored naturally as __dict__ is restored, during fetch of rows, new_instance() is used, during normal construction, __init__() is used. class_manager() is documented we'd only need to get new_instance() and the use case documented, seems to me that would be bug fixed. Fine with me. Thanks! :-) Still I wonder why __init__ is still supported when using ORM mapped classes and __new__ is not. Is there any reason why the latter is harder to support? Or is it a matter of too little gain (who is using __new__ anyway?) for too much work? I'm not sure offhand if __new__() can be wrapped in a monkeypatch the way we do for __init__(), or if so what are the side effects of that, and it also would mean there's no way to create a new instance without ._sa_instance_state being tacked on.Particularly with pickling this is problematic. -- 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] UniqueConstraint breaks combination of ordering_list and association_proxy
On Jun 29, 2011, at 3:22 PM, Torsten Landschoff wrote: Hi Michael, hi *, here is another issue I ran into with SQLAlchemy. Basically, I am trying to map a filesystem like structure to SQL. Unfortunately, there is a difference in that the users can reorder the tree. I reduced my code to the attached example. Mapping the structure worked quite good so far, until I noticed that I had duplicate entries in the same folder. So I added a unique constraint with the result that I can't overwrite the list of entries anymore: folder.children = list(folder.children) alone causes the problem. SQLAlchemy adds the 'new' entries first, which violates the unique constraint. yeah ordering_list can't handle this use case. It doesn't know how to reverse the order of two keys where a unique exists between them, that would imply it would know how to temporarily update the row to a third neutral value - only the flush procedure could allow an operation like this which is outside the scope of the ordering list itself. Is there a way to do what I want without bigger changes to the code? For now, I will disable the unique constraint again and add some checks to the Python code. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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. fsdemo.py -- 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] SQLAlchemy mapped classes and __new__ (ClassManager.new_instance undocumented)
On Wed, 2011-06-29 at 15:31 -0400, Michael Bayer wrote: I'm not sure offhand if __new__() can be wrapped in a monkeypatch the way we do for __init__(), or if so what are the side effects of that, and it also would mean there's no way to create a new instance without ._sa_instance_state being tacked on.Particularly with pickling this is problematic. Good point, I missed that __new__ might be needed for unpickling. Thanks! Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Full-fledged objects as mapped attributes?
Hi All, I am building a Pyramid app using SQLAlchemy for the model, and I would like to be able to use the attributes as full-fledged objects (i.e., I would like to be able to define methods attached to the attributes), ideal example usage (although this is a somewhat contrived example): class Phone(Base): id = Column(Integer, primary_key=True) phone = Column(MyPhoneType) p1 = Phone(phone=9995551212) session.add(p1) p1.phone '9995551212' p1.phone.prettyPrint() (999) 555-1212 p1.phone.foo() Now doing foo... I have made some attempts towards this end using TypeDecorator and by trying to extend UserDefinedType, but it seems that the instantiated objects always have their mapped attributes converted into regular python types (string, unicode, etc..) losing whatever methods/class level stuff I try to define. I am not trying to affect any aspect of the descriptor protocol, object management, database interaction or any other ORM type stuff - I just want some helper methods, and maybe some instance-level attributes to be available for certain types of mapped attributes. Is this possible? Should I be doing this using TypeDecorator / UserDefinedType / other ? Is anyone else attempting this type of thing, or am I totally crazy? Thanks, Jason -- 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] How to get setter-like behaviour universally across a custom type? (with UTC DateTime examples)
I have a typical case where I want to ensure that datetime values sent to the database are UTC, and values read from the database come back as offset-aware UTC times. I see several threads on the issue (eg: http://goo.gl/FmdIJ is most relevant), but none address my question that I can see. UTC datetimes are what I'm working with, but not really what the question is about (although hopefully the code may help others looking for UTC stuff!). I originally tried implementing this with a custom type (UTCDateTime) where I did appropriate conversions on the data to/from the database. That used code something like this: http://pastebin.com/xSrV9QcS That implementation worked well when creating new objects and committing them, and worked well when querying for existing objects. However, it failed when committing objects that I updated (query-make a change-commit). This is because the UTC conversion only happens on commit, and some SQLAlchemy internals were doing a comparison on the new value (naive datetime) and the old value (offset-aware datetime from DB) and raising an exception. When I realized that process_bind_param only happens on commit, I decided to switch my strategy to simply confirming that all incoming outgoing datetime values are offset-aware UTC using this simpler code: http://pastebin.com/gLfCUkX3 but in order for that to work, I also needed to and start looking into how to implement a setter on all of my datetime properties (there are lots) that would do the UTC assignment/calculation. I use declarative mixins which seemed to make this more complicated, but a setter can be set up using a solution something like this: http://stackoverflow.com/questions/5821947/5822301#5822301 However, I have a LOT of properties to do this on and it would be far better to do it at a single choke point like I initially tried. So... what I want is all assignments to a UTCEnforcedDateTime column/ property (self.start_date = x) to be converted to UTC at setting time, not at commit time. How do I do this? I can't see how to do it in the docs (coerce_compared_value was initially tempting but not a fit)/ Digging into it manually I see that the existing auto-instrumentation of my UTCEnforcedDateTime dips through InstrumentedAttribute, ScalarAttributeImp, etc, but I can't locate a good hook point to put my fixing code for non-UTC assignments. Does anyone have any recommendations? -- 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 get setter-like behaviour universally across a custom type? (with UTC DateTime examples)
When I realized that process_bind_param only happens on commit, I decided to switch my strategy to simply confirming that all incoming outgoing datetime values are offset-aware UTC using this simpler code: http://pastebin.com/gLfCUkX3 Sorry - I messed up that code segment on edit for pastebin. Here is a cleaned up version: http://pastebin.com/HcnnmXtV -- 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.