[sqlalchemy] Filtering by an attribute of a related class (abusing generative filters...)
Hi all, I've got a requirement that seems like it should be fairly straightforward, but my own attempts to work out the necessary code have just become more and more contrived, and still don't work :( so I'm hoping someone more familiar with SQLA's design can suggest a better approach! Background: I have a function which creates a query 'q' on a mapped class (Foo), then applies one or more filter criteria, before finally executing the query and returning a list of results. In general the individual criteria might apply to any attribute of the mapped class; I have the attribute name (at this point anyway) as a string 'attr'. The criteria may also use various modes; I convert these into an SQL operator 'sqlop' (so for example 'exact' becomes '=', 'exclude' becomes ' NOT LIKE ', etc.) So the generative filter() call is basically just: q = q.filter(attr + sqlop + :val).params(val=test) Now the problem: That all works fine until I want to filter on attributes that are relational. Say Foo has a one-to-many or many-to-one relation to Bar ('rBar'). When I try to filter Foo on rBar, I want those Foos whose rBar collection includes at least one Bar whose Title attribute matches the criterion if it's one-to-many, or those Foos whose rBar points to a Bar whose Title matches the criterion if it's many-to- one. I can do this using select_from(some_join) followed by a filter() call similar to the above, but I can't figure out how to combine that with generative, since it replaces the underlying table. (I need to stay generative, because I might subsequently want to filter the same attribute again using another criterion.) What should I be doing instead? Huge bonus points if your solution also works for a self-referential many-to-many relation that uses an association table (there are two, 'rFooPrecedes' and 'rFooFollows', which are Foo-Foo links via the FooPrecedence mapped class; here I'd want to filter based on the titles of the attached Foos, not on the titles of the FooPrecedence entries that link them). Cheers, -- Ben --~--~-~--~~~---~--~~ 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] Inspecting DDL of a Table object, without executing the code
I suspect the answer to this is obvious, but it eludes me. I have defined some SQlAlchemy table classes, using declarative_base, to interact with some tables in an SQL Server database. I'm unsure that the types I've chosen are correct. I want to print the CREATE TABLE ddl, without executing it. How can I do this? With thanks, Alex CONN_STR = 'mssql://user:p...@host/database' engine = sqlalchemy.create_engine(CONN_STR) Base = declarative_base() class LVService(Base): '''Map service for LocalView Intranet. ''' __tablename__ = 'localview_Services' ServiceId = Column(types.Integer, Sequence('service_id', 1, 1), primary_key=True, ) ServiceServer = Column(types.String(50)) #nvarchar ServiceType = Column(types.String(50)) #nvarchar ServiceDataFrame = Column(types.String(50)) #nvarchar ServiceUsername = Column(types.String(50)) #nvarchar ServicePassword = Column(types.String(50)) #nvarchar ServiceDomain = Column(types.String(50)) #nvarchar ServiceLayerManager = \ Column(types.String(50)) #nvarchar IsLive =Column(types.Integer, nullable=False, default=1) ServiceDisplayName = \ Column(types.String(50)) #nvarchar ... --~--~-~--~~~---~--~~ 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] foreignkey and relation synchronization
Hello, There's a behaviour in SA that is not clear to me: if we look at the example (User and Address) from the docs, we can change either the related object or the foreign key For example: ad = Address(email_address='j...@google.com') ad.user = jack OR ad.user_id = 1 What is the prefered behaviour ? And what happens if the two fields are not in synch ? (ie, what if jack.id=1 and we set explicetly ad.user = 2) Thanks for clarifying . --~--~-~--~~~---~--~~ 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: Connecting with Windows Auth?
On Feb 25, 4:16 pm, Adam Pletcher adam.pletc...@gmail.com wrote: Is it possible to connect to a server using Windows Authentication using sqlalchemy? Yes it is, the syntax is: 'mssql://hostname/database?trusted_connection=yes' If you're connecting to a SQLEXPRESS instance, this becomes: 'mssql://hostname\\SQLEXPRESS/database?trusted_connection=yes' I'm using pyodbc as the underlying DBAPI provider. Alex --~--~-~--~~~---~--~~ 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: export and import JSON from database (JSON type)
On Feb 25, 12:17 pm, Roger Demetrescu roger.demetre...@gmail.com wrote: Note that this implementation is very simple. Depending of your use case, you probably should take a look at MutableType [1] and types.TypeEngine.is_mutable(). [1] -http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sql... On Wed, Feb 25, 2009 at 10:27 AM, Roger Demetrescu roger.demetre...@gmail.com wrote: I did something like that recently: - from sqlalchemy import types import simplejson class JsonString(types.TypeDecorator): impl = types.String def process_result_value(self, value, dialect): if value is None: return None else: return simplejson.loads(value) def process_bind_param(self, value, dialect): if value is None: return None else: return simplejson.dumps(value) - This looks like something I should be using, but I'm not sure. I've been playing around with simplejson and loads but not getting very far when it comes to getting my json object's properties into my python SA object's (model's) fields. I have a simple model/class named Comment with columns id (int), comment (text), and postdate (datetime). Below are the main bits of the python code I'm using to test how I should handle this: # json from the client's browser json = r'{id:1,postdate:Sat Jan 31 2009 22:18:15 GMT-0500 (Eastern Standard Time),comment:Comment text.}' # create comment instance comment = model.Comment() # decode json to python object (dict) json_obj = simplejson.loads(json) # ??? So now I have json_obj as a python dict object with key/value pairs... and I need to somehow get this into: comment.id comment.comment comment.postdate I've searched and searched and have only found one good example piece of code that I may be able to use to at least handle the parsing/ generation of the datetime type from a javascript Date string; but, I'm still stuck on how to iterate over the dict keys and do an assignment to the properties in the comment object instance in a way that's reusable for other model classes. Thanks for your time, -e --~--~-~--~~~---~--~~ 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: Inspecting DDL of a Table object, without executing the code
see the recipe in http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring . On Feb 27, 2009, at 4:32 AM, Alex Willmer wrote: I suspect the answer to this is obvious, but it eludes me. I have defined some SQlAlchemy table classes, using declarative_base, to interact with some tables in an SQL Server database. I'm unsure that the types I've chosen are correct. I want to print the CREATE TABLE ddl, without executing it. How can I do this? With thanks, Alex CONN_STR = 'mssql://user:p...@host/database' engine = sqlalchemy.create_engine(CONN_STR) Base = declarative_base() class LVService(Base): '''Map service for LocalView Intranet. ''' __tablename__ = 'localview_Services' ServiceId = Column(types.Integer, Sequence('service_id', 1, 1), primary_key=True, ) ServiceServer = Column(types.String(50)) #nvarchar ServiceType = Column(types.String(50)) #nvarchar ServiceDataFrame = Column(types.String(50)) #nvarchar ServiceUsername = Column(types.String(50)) #nvarchar ServicePassword = Column(types.String(50)) #nvarchar ServiceDomain = Column(types.String(50)) #nvarchar ServiceLayerManager = \ Column(types.String(50)) #nvarchar IsLive =Column(types.Integer, nullable=False, default=1) ServiceDisplayName = \ Column(types.String(50)) #nvarchar ... --~--~-~--~~~---~--~~ 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: Filtering by an attribute of a related class (abusing generative filters...)
On Feb 27, 2009, at 3:10 PM, Ben Zealley wrote: Hi all, I've got a requirement that seems like it should be fairly straightforward, but my own attempts to work out the necessary code have just become more and more contrived, and still don't work :( so I'm hoping someone more familiar with SQLA's design can suggest a better approach! Background: I have a function which creates a query 'q' on a mapped class (Foo), then applies one or more filter criteria, before finally executing the query and returning a list of results. In general the individual criteria might apply to any attribute of the mapped class; I have the attribute name (at this point anyway) as a string 'attr'. The criteria may also use various modes; I convert these into an SQL operator 'sqlop' (so for example 'exact' becomes '=', 'exclude' becomes ' NOT LIKE ', etc.) So the generative filter() call is basically just: q = q.filter(attr + sqlop + :val).params(val=test) you might want to look into a functional approach here, i.e. sqlop(MyClass.attr, test). More extensible, less typing (no need for params()), etc.. For example: from operator import eq def exact(x, y): return eq(x,y) You can also create custom comparison operations on any mapped attribute using the comparator argument to column_property() or relation(). http://www.sqlalchemy.org/docs/05/mappers.html#id2 note that you can add any kind of method to a Comparator in the most recent release of SQLAlchemy (such as your exact() method). Now the problem: That all works fine until I want to filter on attributes that are relational. Say Foo has a one-to-many or many-to-one relation to Bar ('rBar'). When I try to filter Foo on rBar, I want those Foos whose rBar collection includes at least one Bar whose Title attribute matches the criterion if it's one-to-many, or those Foos whose rBar points to a Bar whose Title matches the criterion if it's many-to- one. SQLA has operators has() and any() which support this directly. the joins are created via correlation within an EXISTS clause. http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-exists Huge bonus points if your solution also works for a self-referential many-to-many relation that uses an association table (there are two, 'rFooPrecedes' and 'rFooFollows', which are Foo-Foo links via the FooPrecedence mapped class; here I'd want to filter based on the titles of the attached Foos, not on the titles of the FooPrecedence entries that link them). has() and any() all handle this automatically. --~--~-~--~~~---~--~~ 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: foreignkey and relation synchronization
On Feb 27, 2009, at 3:51 PM, laurent wrote: Hello, There's a behaviour in SA that is not clear to me: if we look at the example (User and Address) from the docs, we can change either the related object or the foreign key For example: ad = Address(email_address='j...@google.com') ad.user = jack OR ad.user_id = 1 What is the prefered behaviour ? And what happens if the two fields are not in synch ? (ie, what if jack.id=1 and we set explicetly ad.user = 2) Thanks for clarifying . upon flush, the ad.user association event is translated into a dependency rule that will place the primary key of user onto the user_id attribute of ad. So the object association wins. the primary use case with the ORM is to deal with object connections and collections, without worrying about any primary or foreign key attributes. the FAQ talks about this - you can manipulate foreign key attributes if you want, but the connections they represent wont show up until those connections have been persisted (i.e. via flush, explicit or not) and loaded back (i.e. via lazyload, or after a commit() operation when all attributes are expired). --~--~-~--~~~---~--~~ 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: export and import JSON from database (JSON type)
On Feb 27, 2009, at 11:24 PM, eLuke wrote: I have a simple model/class named Comment with columns id (int), comment (text), and postdate (datetime). Below are the main bits of the python code I'm using to test how I should handle this: # json from the client's browser json = r'{id:1,postdate:Sat Jan 31 2009 22:18:15 GMT-0500 (Eastern Standard Time),comment:Comment text.}' # create comment instance comment = model.Comment() # decode json to python object (dict) json_obj = simplejson.loads(json) # ??? So now I have json_obj as a python dict object with key/value pairs... and I need to somehow get this into: comment.id comment.comment comment.postdate I've searched and searched and have only found one good example piece of code that I may be able to use to at least handle the parsing/ generation of the datetime type from a javascript Date string; but, I'm still stuck on how to iterate over the dict keys and do an assignment to the properties in the comment object instance in a way that's reusable for other model classes. A method like this would work: def from_json(self, json): json_obj = simplejson.loads(json) for k, v in json_obj.values(): setattr(self, k, v) if you're concerned about dates you can use a date-based TypeDecorator which can receive a fully qualified datestring as an argument. Or use descriptors on your mapped classes (i.e. date = property(def get_date()/def set_date())) --~--~-~--~~~---~--~~ 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: Filtering by an attribute of a related class (abusing generative filters...)
Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ Or, broken down M = orm.class_mapper(Foo)# Mapper for the class p = M.get_property(attr) # The property Mt = p._get_target() # Mapper for the target tC = Mt.class_ # Target class. But I'm fairly sure any sequence of calls that involve methods from someone else's code which begin with an underscore counts as bad form ;) is there a more direct way of getting this? Cheers, -- Ben --~--~-~--~~~---~--~~ 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: Filtering by an attribute of a related class (abusing generative filters...)
On Feb 28, 2009, at 12:26 PM, Ben Zealley wrote: Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ id say Foo.someattr.property.mapper.class_ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---